Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

Time-saving
Pivot Table add-in

 

 

Excel Conditional Formatting -- Examples

Hide Errors

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

  1. Select cells C2:C5
  2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, from the first dropdown, choose Formula Is
  4. In the rule box, enter a formula that refers to the active cell in the selection. In this example, we selected C2:C5, and cell C2 is the active cell, so we'll check for an error in cell C2.
       =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

Video: Hide Duplicate Values

Use Excel conditional formatting to hide duplicate headings on a worksheet, to make a list easier to read. This video shows you the steps, and the written instructions are below the video.

Hide Duplicate Values

In a table, each row should have all data entered, to enable sorting and filtering. However, you can use Excel conditional formatting to hide the duplicate values, and 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. You can see the text if you select the cells.

Follow these steps to hide the duplicates, and you can see the steps in the video above.

  1. Select range A2:A5
  2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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

Highlight Duplicates in Column

Use Excel conditional formatting to highlight duplicate entries in a specific column, or in a range of cells (multiple rows and columns):

In Excel 2007 or later:

  1. Select the cells to format -- range A2:A11 in this example
  2. On the Ribbon's Home tab, click Conditional Formatting
  3. Click Highlight Cell Rules, then click Duplicate Values
  4. Select one of the formatting options, and click OK

    highlight duplicate values

For Excel 2003:

  1. Select the cells to format -- range A2:A11 in this example
  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 or fill colour for highlighting.
  7. Click OK, click OK

Video: Highlight Duplicate Records in a List

To highlight the duplicate records in a list, you can use conditional formatting. Add a formula in one column first, to string all the data together.

You can see the steps in this video, and the written instructions are below the video.

Highlight Duplicate Records in a List

Use Excel conditional formatting to highlight duplicate records in a list. Use a formula to combine all the fields into one column, then test that column for duplicates.

Create a formula to combine the data:

  1. In this example, the data is in cells A2:F8
  2. In cell G1, add the column heading "AllData"
  3. In cell G2, enter the formula to combine all the data:
    =CONCATENATE(A2,B2,C2,D2,E2,F2)
  4. Copy the formula down to the last row of data

Add the conditional formatting:

  1. Select the cells to format -- range A2:F8 in this example
  2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, from the first dropdown, choose Formula Is
  4. For the formula, enter
    =COUNTIF($G$2:$G$8,$G2)>1
    The COUNTIF function will count the occurences of each row's combined text. If there is more than one, the row will be highlighted.
    NOTE: If you only want to highlight the duplicate records, and not the first instance of a duplicated record, use the following formula:
    =COUNTIF($G$2:$G2,$G2)>1
    NOTE: The COUNTIF function only works for 255 characters or fewer. For longer strings use the following formula:
    =COUNT(FIND($G5,$G$5:$G$11))>1
  5. Click the Format button.
  6. Select a font colour for highlighting.
  7. Click OK, click OK

conditional format for duplicate rows

Highlight Items in a List

Use Excel 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  4. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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

Video: Show Temperatures With a Color Scale

To see the steps for using a color scale on a temperature cell, please watch this short video. The written instructions are below the video.

Show Temperatures With a Color Scale

To show hot temperatures in a red cell, and cold temperatures in a blue cell, you can use Excel's conditional formatting color scale. This feature is available in Excel 2007 and later versions.

conditional formatting color scale

  1. Enter the temperature in cell B3
  2. In cells F6:F25, enter the numbers 140 to -50, in increments of 10
  3. In cell G6, enter a formula that will show an empty string if the current temperature is equal to the temperature in that row, or between that temperature, and the one above:
  4. =IF($B$3=F6,"",IF(AND($B$3>F6,$B$3<F5),"",F6))

  5. Copy the formula down to row 25
  6. Select cell B3, then press the Ctrl key and select cells G6:G25 (the cells with the formulas)
  7. On the Ribbon's Home tab, click Conditional Formatting
  8. Click Color Scales, then click on the Red - White - Blue scale
  9. Change the temperature in cell B3, and the cell color will change, based on the color scale.
  10. (optional) Hide columns F:G, so the color scale is not visible.

    conditional formatting color scale

Highlight Lottery Numbers

You can use Excel conditional formatting to 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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 use Excel conditional formatting to 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, from the first dropdown, choose Formula Is
  4. For the formula, use the Today function to 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

Highlight Expired Dates

You can use Excel conditional formatting to highlight policies with dates that have expired. In this example, Due dates are entered in cells B2:B7.

  1. Select cells B2:B7
  2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, from the first dropdown, choose Formula Is
  4. For the formula, use the Today function to find expired dates:
       =B2<TODAY()
  5. Click the Format button.
  6. Select formatting options (Red fill color, in this example), click OK
  7. Click OK

Video: Highlight Weekend Dates

To highlight the weekend dates in a list, you can use conditional formatting. The WEEKDAY function returns a number for each day of the week, so you could adjust this formula to highlight other days of the week.

You can see the steps in this video.

Hide Cell Contents When Printing

You can use Excel 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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 Excel 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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 Excel 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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 Excel conditional formatting to shade alternating rows in a filtered list.

  1. Select the cells in the list (A2:B29 in this example).
  2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, 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 Excel 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. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  6. Click Use a Formula to Determine Which Cells to Format
    • NOTE: In Excel 2003, choose Format|Conditional Formatting
    • Then, from the first dropdown, choose Formula Is
  7. For the formula, enter:   =$B3<10
  8. Click the Format button, and select Red as the font colour, then click OK.
  9. Click New Rule, and click Use a Formula to Determine Which Cells to Format
  10. For the formula,enter:  =$B3>30
  11. Click the Format button, and select Green as the font colour, then click OK.
  12. Click OK

Create Coloured Icons

In Excel 2007 and later, you can use icon sets to highlight the results in a group of cells. In Excel 2010 and later, you can customize these sets, but can't change the color of the icons.

If you don't have icons, or want to change the colors, you can use symbols, and a formula in an adjacent cell, to create your own icon sets.

Or, you can use custom Number Formats, as shown in the sample file, on the ColorIconsNum sheet.

First, set up the lookup table in cells G3:I5

  1. In G3:G5, type the percentages: 67%, 33%, 0%
  2. In H3, enter the formula: =PERCENTILE($D$2:$D$11,G3)
  3. Copy the formula down to rows 4 and 5
  4. Format cells H3:H5 in Wingding3 font
  5. Use Excel's Insert Symbols feature to add the up, right, and down arrows in those cells, from the Wingdings 3 font.
  • NOTE: You could use different percentages, or just type values into H3:H5

Next, create the icons in column C:

  1. In cell C2 enter the formula that creates the icon:
        =IF(D2="","",IF(D2>=$H$3,$I$3,IF(D2>=$H$4,$I$4,$I$5)))
  2. Copy the formula down to cell C11
  3. Format cells C2:C11 with Wingding3 font, and yellow font color
  4. Select cells C2:C11
  5. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  6. Click Use a Formula to Determine Which Cells to Format
  7. For the formula, enter:   =$B2<$H$4
  8. Click the Format button, and select Green as the font colour, then click OK.
  9. Click New Rule, and click Use a Formula to Determine Which Cells to Format
  10. For the formula,enter:  =$B2>$H$3
  11. Click the Format button, and select Red as the font colour, then click OK.
  12. Click OK
  13. To make the icons appear to be in the same cell as the number, you can create an outside border around the two cells.

Download the Sample File

Excel 2010/2007: zipped sample Excel Conditional Formatting file

Excel 2003: zipped sample Excel Conditional Formatting file

More Tutorials

  1. Excel Conditional Formatting -- Introduction
  2. Excel Conditional Formatting -- Multiple Cells
  3. Excel Conditional Formatting -- Examples
  4. Excel Conditional Formatting -- Documentation
  5. Excel Conditional Formatting -- Data Bars  

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: February 23, 2014 12:53 AM