Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.
Highlight Cells Rules
To highlight cells that are greater than a value, execute the following steps.
1. Select the range A1:A10.
2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...
3. Enter the value 80 and select a formatting style.
4. Click OK.
Result. Excel highlights the cells that are greater than 80.
5. Change the value of cell A1 to 81.
Result. Excel changes the format of cell A1 automatically.
Note: you can also highlight cells that are less than a value, between a low and high value, etc.
Clear Rules
To clear a conditional formatting rule, execute the following steps.
1. Select the range A1:A10.
2. On the Home tab, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.
Top/Bottom Rules
To highlight cells that are above the average of the cells, execute the following steps.
1. Select the range A1:A10.
2. On the Home tab, click Conditional Formatting, Top/Bottom Rules, Above Average...
3. Select a formatting style.
4. Click OK.
Result. Excel calculates the average (42.5) and formats the cells that are above this average.
Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!
