Excel -- Conditional Formatting -- Examples

Hide Errors
Hide Duplicate Values
Highlight Duplicates in Column 
Highlight Items in a List 
Highlight Lottery Numbers
Highlight Upcoming Expiry Dates
Hide Cell Contents When Printing  
Shade Alternating Rows 
Shade Bands of Rows 
Shade Alternating Filtered Rows 
Create Coloured Shapes

 

 

Hide Errors

You can use conditional formatting to check for errors, and change the font colour to match the cell colour. In this example, if column B contains a zero, the #DIV/0! error is displayed in column C.

  1. Select cells C2:C5
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter a formula that refers to the active cell in the selection:
       =ISERROR(C2)
    or, to hide only #N/A errors: =ISNA(C2)
  5. Click the Format button.
  6. Select a font colour to match the cell colour.
  7. Click OK, click OK

    download a zipped sample file

 

Hide Duplicate Values

In a table, each row should have all data entered, to enable sorting and filtering. However, you can hide the duplicate values, to make the list easier to read. In this example, when the table is sorted by Region, the second (and subsequent) occurences of each region name will have white font colour.

In Excel 2003:

  1. Select range A2:A5
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter
    =A2=A1
  5. Click the Format button.
  6. Select a font colour to match the cell colour.
  7. Click OK, click OK
In Excel 2007:
  1. Select range A2:A5
  2. On the Ribbon, go to the Home tab and click Conditional Formatting
  3. Click New Rule
  4. Click Use a Formula to Determine Which Cells to Format
  5. For the formula, enter
    =A2=A1
  6. Click the Format button.
  7. Select a font colour to match the cell colour.
  8. Click OK, click OK

To view the steps in a
short video, click here
(Excel 2007)

Highlight Duplicates in Column

Use conditional formatting to highlight duplicate entries in a column:

  1. Select range A2:A11
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter
    =COUNTIF($A$2:$A$11,A2)>1
  5. Click the Format button.
  6. Select a font colour for highlighting.
  7. Click OK, click OK

 

Highlight Items in a List

Use conditional formatting to highlight items that are in a list on the worksheet.:

  1. Create a list of items you want to highlight. If the items are on a different sheet than the conditional formatting, name the list.
  2. Select range A2:A7
  3. Choose Format|Conditional Formatting
  4. From the first dropdown, choose Formula Is
  5. For the formula, enter
    =COUNTIF($C$2:$C$4,A2)
    or, if the list is named, use the name in the formula:
    =COUNTIF(CodeList,A2)
  6. Click the Format button.
  7. Select a font colour for highlighting.
  8. Click OK, click OK

 

Highlight Lottery Numbers

You can highlight the ticket numbers that have been drawn in a lottery. In this example the ticket numbers are in cells B2:G4, and the drawn numbers are entered in cells B6:G6

  1. Select cells B2:G4
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, use the CountIf function:
       =COUNTIF($B$6:$G$6,B2)
  5. Click the Format button.
  6. Select formatting options (green pattern, in this example), click OK
  7. Click OK

   

Highlight Upcoming Expiry Dates

You can highlight payments that are due in the next thirty days. In this example, Due dates are entered in cells A2:A4.

  1. Select cells A2:A4
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, use the Today function count the days:
       =AND(A2-TODAY()>=0,A2-TODAY()<=30)
  5. Click the Format button.
  6. Select formatting options (Bold, Blue font, in this example), click OK
  7. Click OK

   

Hide Cell Contents When Printing

You can use conditional formatting to hide cells when printing. In this example, the contents of cells B2:F4 are changed to white font, if cell H1 contains an x.

To print with the cell contents hidden, type an x in cell H1. To display the cell contents, delete the x in cell H1.

  1. Select cells B2:F4
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter    =$H$1="x"
  5. Click the Format button.
  6. Select formatting options (white font and white pattern, in this example)
  7. Click OK, click OK

   

Shade Alternating Rows

You can use conditional formatting to shade alternating rows on the worksheet.

  1. Click the Select All button, above the Row 1 button, to select all the cells on the worksheet.
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter    =MOD(ROW(),2)
  5. Click the Format button.
  6. On the Patterns tab, select a colour for shading
  7. Click OK, click OK

   

Shade Bands of Rows

You can use conditional formatting to shade bands of rows on the worksheet. In this example, 3 rows are shaded light grey, and 3 are left with no shading. In the MOD function, the total number of rows in the set of banded rows (6) is entered.

  1. Click the Select All button, above the Row 1 button, to select all the cells on the worksheet.
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter    =MOD(ROW(),6)<3
  5. Click the Format button.
  6. On the Patterns tab, select a colour for shading
  7. Click OK, click OK

   

Shade Alternating Filtered Rows

You can use conditional formatting to shade alternating rows in a filtered list.

  1. Select the cells in the list (A2:B29 in this example).
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter    =MOD(SUBTOTAL(3,$A$1:$A2),2)
  5. Click the Format button.
  6. On the Patterns tab, select a colour for shading
  7. Click OK, click OK
  8. Filter the list, and the shading will alternate in the visible rows.

   

Create Coloured Shapes

You can use conditional formatting and the Wingding font to create coloured shapes in a cell. In this example, coloured shapes will appear in cells C3:C7, depending on the value in the adjacent cell in column B. If the value is less than 10, a red circle will appear, if the value is greater than 30, a green square will appear. Otherwise, a yellow diamond will appear.

  1. In cell C3 enter the formula:
        =IF(B3="","",IF(B3<10,"l",IF(B3>30,"n","t")))
  2. Copy the formula down to cell C7
  3. Format cells C3:C7 with Wingding font, and yellow font color
  4. Select cells C3:C7
  5. Choose Format|Conditional Formatting
  6. From the first dropdown, choose Formula Is
  7. For the formula, use the Today function count the days:
       =$B3<10
  8. Click the Format button, and select Red as the font colour, then click OK.
  9. Click the Add button, and for Condition 2, choose Format|Conditional Formatting
  10. From the first dropdown, choose Formula Is
  11. For the formula, use the Today function count the days:
       =$B3>30
  12. Click the Format button, and select Green as the font colour, then click OK.
  13. Click OK

   

  1. Conditional Formatting -- Introduction
  2. Conditional Formatting -- Multiple Cells
  3. Conditional Formatting -- Examples
  4. Conditional Formatting -- Documentation
 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 25, 2008 3:37 PM