Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Conditional Formatting -- Introduction

  1. What is Conditional Formatting?
  2. Video: Color Cells Based on Cell Value
  3. Apply Conditional Formatting to a Cell
  4. Apply 2nd Conditional Format
  5. Download a Sample File
  6. Watch the Video
  7. Excel Conditional Formatting -- Based on another cell
  8. Excel Conditional Formatting -- Examples

Click here for Excel 2003 instructions

What is Conditional Formatting?

With conditional formatting, you can select one or more cells, and create rules (conditions) for when and how those cells are formatted. The conditions can be, based on the selected cell's contents, or based on the contents of another cell.

You can control the following formats:

  • Number format
  • Font, font style, and font colour (but not font size)
  • Fill colour and fill pattern
  • Border colour and border style (but not border thickness)

If the rules (conditions) that you specified are met, then the formatting is applied.

For example, you can set conditional formatting so that a cell turns red if its value is low, and turns green if its value is high.

Sample Conditional Formatting

Video: Color Cells Based on Cell Value

To view the steps for adding condtional formatting, watch this short video. 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 condtional 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

Download the Sample File

Click here to download a zipped sample file for this tutorial.

Learn how to create Excel dashboards.

Related Tutorials

  1. Excel Conditional Formatting -- Introduction
  2. Excel Conditional Formatting -- Based on another cell
  3. Excel Conditional Formatting -- Examples
  4. Excel Conditional Formatting -- Documentation  
  5. Excel Conditional Formatting -- Data Bars  
   

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.