Excel - Conditional Formatting

Background Information

Conditional Formatting applies formats such as cell shading or a font color to cells that meet certain conditions.  An example of conditional formatting: all selected cells with a value higher than 100 should have a background color of red with bold text.

Add Conditional Formatting

  1. Select the cells to which you want to add conditional formatting.
  2. On the Home tab, click Conditional Formatting, and choose from the following options:
    1. Highlight Cells Rules
    2. Top/Bottom Rules
    3. Data Bars
    4. Color Scales
    5. Icon Sets
    6. New Rule
  3. Choose additional options if prompted.
  4. Remove formatting by using the Clear Rules option from the Conditional Formatting drop-down menu.  Rules can be cleared one at a time, or for the entire worksheet.

Types of Formatting

Cells are highlighted because they meet criteria (i.e., are the highest/lowest value, are within a particular range, etc.) As data in the source cells is changed, the formatting will update according to the set rules.

Cell Rules - Will highlight cells that:

  • Are within a certain range of numbers
  • Contain specific text
  • Have Duplicate Values

Top/Bottom Rules - Will highlight cells that:

  • Are the top or bottom of a range of values
  • Are above or below the average numbers

Data Bars - Will highlight cells by:

  • Superimposing a colored bar in the cell.  The length of the bar corresponds to the value in the cell.

Color Scales - Will highlight cells by:

  • Coloring cells based on their value.  Higher values will be colored differently than lower values.

Icon Sets - Will highlight cells by:

  • Superimposing an icon in the cell.  The type of icon corresponds to the value in the cell.

 

To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.

Print Article