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.

Excel Conditional Formatting Example

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...

Highlight Cells Rules

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

Greater Than

4. Click OK.

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

Greater Than Result

5. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically.

Conditional Formatting Result

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, click Conditional Formatting, 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, click Conditional Formatting, Top/Bottom Rules, Above Average...

Top/Bottom Rules

3. Select a formatting style.

Above Average

4. 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!