Contextures

Conditional Formatting Introduction

With Excel conditional formatting, you can highlight worksheet cells automatically, based on rules that you set. Make the cells a different colour, or change the border, font or number format. Your rules can be based on a selected cell's contents, or the contents of another cell. See the simple examples below.

What is Conditional Formatting?

With conditional formatting, you can:

  • select one or more cells
  • create one or more rules (conditions) for when and how those cells are formatted

For example, you can set conditional formatting rules so that a cell:

  • turns red if its value is below 50
  • turns green if its value is above 75

Sample Conditional Formatting

How Does Conditional Formatting Work?

Your conditional formatting rules can be based on:

  • the value in the cell that you want to format
    • e.g. change that cell's colour to green, if its value is above 75
  • OR, the value in another cell
    • e.g. change all cells in a row green, if the number in column B is above 75

What Formatting Can You Change With Rules?

You can control the following formats with your rules:

  • Number: apply different number formats
  • Font: show a different font type, font style, and font colour (but not font size)
  • Fill: change the cell fill colour and fill pattern
  • Border: change the cell border colour and border style (but not border thickness)

Video: Color Cells Based on Cell Value

To see the steps for adding Excel conditional formatting, watch this short video. It shows how to

  • turns cells red if their value is below 50
  • turns cells green if their value is above 75

The written instructions are below the video.

Apply Conditional Formatting to a Cell

In this example, you'll set conditional formats so that a cell:

  • turns green if it contains a value higher than 75 and
  • turns red if it contains a value lower than 50.

Follow these steps to apply conditional formatting to cells:

  1. In cell I1, type the high value -- 75
  2. In cell I2, type the low value -- 50
  3. enter high low values

  4. Select the cells to be formatted. In this example, cells E2:E7 are selected.
  5. On the Ribbon's Home tab, click Conditional Formatting
  6. conditional formatting command

  7. To format the high values, click Highlight Cell Rules, then click Greater Than...
  8. enter high low values

  9. In the Greater Than window, delete the value that appears, and click on cell I1, where the High value is entered.
  10. enter cell reference

  11. Click the drop down list for formats, and click Custom Format.
  12. select custom format

  13. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.
  14. select custom format

  15. Click OK to close the Format Cells window, and click OK to close the Greater Than window.

The cells with values greater than 75 are now coloured green.

high values formatted

Apply 2nd Conditional Format

To colour the low values in red fill, you can apply a second conditional formatting rule to the cells.

  1. Select the cells to be formatted. In this example, cells E2:E7 are selected.
  2. On the Ribbon's Home tab, click Conditional Formatting
  3. conditional formatting command

  4. To format the high values, click Highlight Cell Rules, then click Less Than...
  5. enter high low values

  6. In the Less Than window, delete the value that appears, and click on cell I2, where the Low value is entered.
  7. enter cell reference

  8. Click the drop down list for formats, and click Custom Format.
  9. select custom format

  10. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.
  11. select custom format

  12. Click OK to close the Format Cells window, and click OK to close the Less Than window.

The cells with values greater than 75 are now coloured green, and cells less than 50 are red.

high and low values formatted

Fix Conditional Formatting Extra Rules

After you set up conditional formatting rules, you might see a problem with new rules being created automatically -- you could end up with hundreds of extra rules!

This usually happens because a conditional formatting rule refers to a cell in a different row.

  • In the screen shot below, a new rule was added automatically when the entry in row 10 was deleted. The rule refers to a cell in the current row (A3), and a cell in the row above (A2)
    • =$A2<>$A3

You can see how to clean up those extra rules, manually or with a macro, on the Fix Conditional Formatting Extra Rules page.

duplicate conditional formatting rules

Get the Sample File

Click here to get a zipped sample Conditional Formatting file for this tutorial.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra

Related Tutorials

Conditional Formatting - Based on another cell

Conditional Formatting - Examples

Fix Conditional Formatting Extra Rules

Conditional Formatting - Currency

Conditional Formatting - Documentation  

Conditional Formatting - Data Bars  

Last updated: September 10, 2021 12:50 PM