Rule types supported by Conditional Formatting
Formatting rules: Highlight cell ranges that meet specific conditions using fill color, font color, borders, and other formats based on the rules you set. Visual rules: Convert data in cells into data bars that visually represent vertical progress. In addition, custom formulas are supported, allowing you to create more advanced custom format rules. Conditional Formatting has a wide range of use cases. The following sections introduce how to configure Conditional Formatting through common scenario examples.1. Highlight: Mark duplicates
As an HR specialist entering candidate data, you want to be reminded with highlighted cells whenever a duplicate candidate name is entered, helping you avoid recording the same candidate twice. You can achieve this using Conditional Formatting.Steps
Select the cell range where you want to mark duplicates, that is, the “Candidate Name” column. Click Toolbar > Conditional Formatting > Highlight Cells > Duplicates. Select the format effect to apply when marking duplicates, and confirm to apply. Highlight Duplicates: There is also a more efficient way to mark duplicates. Select the cell range you want to check, then click Toolbar > Highlight Duplicates > Select Fill Style.2. Data bars: Display project progress
Scenario
As a project manager reviewing task progress, you want to display the completion progress of each task more intuitively. You can achieve this using Conditional Formatting.Steps
Select the area where you want to visualize progress, that is, the “Progress” column. Click Toolbar > Conditional Formatting > Data Bars. Select a data bar style you like to apply. The “Progress” column will visually display project progress through color fill based on the Percent value.3. Custom formula: Mark entire rows by condition
Scenario
As a class teacher managing student grade sheets, you want to mark the entire row of any student whose grade is below the passing mark for easier review. By default, Conditional Formatting highlights only the cell where the data itself is located. To automatically change the color of an entire row that meets a specific condition, you need to use a more advanced custom formula.Steps
Select the entire range where you want to apply the rule, that is, the entire area from “Student ID” to “Grade”. Click Toolbar > Conditional Formatting > Create Rule to open the Conditional Formatting Rule dialog. In the rule dialog, select Custom Formula. Enter the formula:=$L4<60. Note the $ sign before column L where the grade is located. Because the entire row needs to apply Conditional Formatting based on the grade in column L, the column reference L must remain fixed while the row number can change.
Then select the format you want to apply and confirm.
4. View and manage existing Conditional Formatting rules
To modify or clear Conditional Formatting, click Toolbar > Conditional Formatting, then choose Manage Rules or Clear from the dropdown menu to clear rules for the selected cell range or the entire Worksheet.Back to Spreadsheet