Conditional Formatting in Excel

 

Use conditional formatting in Excel to automatically highlight cells based on their content. Apply a rule or use a formula to determine which cells to format.

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 use this category (see step 3) to highlight cells that are less than a value, between two values, equal to a value, cells that contain specific text, dates (today, last week, next month, etc.), duplicates or unique values.

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 average, 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 use this category (see step 3) to highlight the top n items, the top n percent, the bottom n items, the bottom n percent or cells that are below average.

Conditional Formatting with Formulas

Take your Excel skills to the next level and use a formula to determine which cells to format. Formulas that apply conditional formatting must evaluate to TRUE or FALSE.

1. Select the range A1:E5.

Cells to Format

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

Click Conditional Formatting

3. Click New Rule.

Click New Rule

4. Select 'Use a formula to determine which cells to format'.

5. Enter the formula =ISODD(A1)

6. Select a formatting style and click OK.

Use a formula to determine which cells to format

Result: Excel highlights all odd numbers.

Odd Numbers

Explanation: always write the formula for the upper-left cell in the selected range. Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =ISODD(A2), cell A3 contains the formula =ISODD(A3), etc.

Here's another example.

7. Select the range A2:D7.

Select Range

8. Repeat steps 2-4 above.

9. Enter the formula =$C2="USA"

10. Select a formatting style and click OK.

Format values where this formula is true

Result: Excel highlights all USA orders.

Conditional Formatting with a Formula

Explanation: we locked the reference to column C by placing a $ symbol in front of the column letter ($C2). As a result, cell B2, C2 and cell D2 also contain the formula =$C2="USA", cell A3, B3, C3 and D3 contain the formula =$C3="USA", etc.

Color Scales

Use awesome color scales to assign different colors to different values. This allows you to quickly identify high and low points in your dataset.

Heat Map with Numbers

Tip: learn more about color scales and learn how to create this heat map.

Highlight Blank Cells

You can also use conditional formatting in Excel to format blank cells. This is useful for ensuring data completeness and quickly shows where information is missing.

Highlight Blank Cells

Tip: learn how to highlight blank cells on our page about blanks.