Conditional Formatting

 

Highlight Cells Rules | Clear Rules | Top/Bottom Rules

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.

Conditional Formatting Example

2. On the Home tab, in the Styles group, click Conditional Formatting.

Click Conditional Formatting

3. Click Highlight Cells Rules, Greater Than.

Highlight Cells Rules

4. Enter the value 80 and select a formatting style.

Greater Than

5. Click OK.

Result. Excel highlights the cells that are greater than 80.

Greater Than Result

6. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically.

Conditional Formatting in Excel

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.

Select Cells

2. On the Home tab, in the Styles group, click Conditional Formatting.

Click Conditional Formatting

3. Click Clear Rules, Clear Rules from Selected Cells.

Clear Rules

Top/Bottom Rules

To highlight cells that are above the average of the cells, execute the following steps.

1. Select the range A1:A10.

Top/Bottom Rules Example

2. On the Home tab, in the Styles group, click Conditional Formatting.

Click Conditional Formatting

3. Click Top/Bottom Rules, Above Average.

Top/Bottom Rules

4. Select a formatting style.

Above Average

5. Click OK.

Result. Excel calculates the average (42.5) and formats the cells that are above this average.

Above Average Result

Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!