Contextures

Excel Conditional Formatting Examples

Use conditional formatting to create rules (conditions) for when and how specific cells are formatted. For example, make cells red, if an expiry date has passed, or color cells green, if they match numbers in a lottery draw

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)

    ISERROR formula

  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.

white font colour hides text

You can see the text if you select the cells.

white font colour shows when selected

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 Cells With Formulas

Use Excel conditional formatting to highlight cells that contain a formula. In this example, there are values in cells A2:B8, and totals in cells C2:C8 and in A9:C9

  1. Select all the cells where you want the formatting -- range A2:C9
  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
  4. For the formula, enter: =ISFORMULA(A2)
  5. Click the Format button.
  6. Select a font colour for highlighting.
  7. Click OK, click OK

highlight formula cells

Highlight Items in a List

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

highlight items that are in a list

  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: Highlight Lottery Numbers

You can use Excel conditional formatting to highlight the ticket numbers that have been drawn in a lottery. Winning numbers are entered on the sheet, and those numbers are highlighted in the list of purchased tickets. Written instructions are below the video, and you can download the sample file to follow along with the video.

Highlight Lottery Numbers

You can use Excel conditional formatting to highlight the ticket numbers that have been drawn in a lottery, or the tickets that have 3 or more winning numbes. In this example the ticket numbers are in cells C6:H8, and the drawn numbers are entered in cells C3:H3.

highlight the ticket numbers that have been drawn in a lottery

To highlight the winning numbers:

  1. Select cells C6:H8
  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
  4. For the formula, use the CountIf function:
       =COUNTIF($C$3:$H$3,C6)>=1
  5. Click the Format button.
  6. Select formatting options (green fill, in this example), click OK
  7. Click OK

To highlight the winning tickets:

  1. Select cells B6:B8
  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
  4. For the formula, use the SUM and COUNTIF functions:
       =SUM(COUNTIF($C6:$H6,$C$3:$H$3))>=3
  5. Click the Format button.
  6. Select formatting options (yellow fill, in this example), click OK
  7. Click OK

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:

    =IF($B$3=F6,"",IF(AND($B$3>F6,$B$3<F5),"",F6))

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

    conditional formatting color scale

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.

highlight payments that are due in the next thirty days

  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.

Highlight Expired Dates

  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

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.

Hide Cell Contents When Printing

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

Colour Cells Based on 2 Conditions

Use Excel conditional formatting to colour cells if 2 condidtions are met. In this example, a country code is entered in cell B2. If the code "US" is entered, cells that contain "United States" are coloured red.

data entry cells coloured red

Enter the Conditions

You could enter the conditions in the conditional formatting formula, but if you enter them in worksheet cells it's easier to see the conditions, and change them, if necessary.

In this example, the conditions are on the same sheet as the data entry cells, but you can store them on a different sheet. You could also name the cells, and use those names in the conditional formatting formula

To set up the conditions:

  • In cell E1, type Cond01
  • In cell B2, type US.
  • In cell D1, type Cond02
  • In cell D2, type United States

set up the conditions

Add the Country Code Cell

Next, set up the cell where a country code can be entered:

  • In cell B1, type Code
  • In cell B2, type US. (This can be changed later)
  • Format cell B2 with yellow fill colour, to show that it is a data entry cell

cell where a country code can be entered

Add Conditional Formatting

Next, add conditional formatting to country cells in the data range. The formula is explained below.

  1. Select cells D5:D14, where the country names are listed for the orders
  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
  4. For the formula, enter    =AND($B$2=$E$2,D5=$F$2)
  5. Click the Format button.
  6. Select formatting options (red fill colour, in this example)
  7. conditional formatting rule
  8. Click OK, click OK

If US is entered in cell B2, and a cell in D5:D14 contains "United States", it is coloured red.

data entry cells coloured red

How It Works

The conditional formatting formula is: =AND($B$2=$E$2,D5=$F$2)

The AND function checks the 2 conditions:

  1. Does cell B2 match the condition entered in cell E2
  2. Does the data entry cell match the condition entered in cell F2

Some notes about the cell references in the formula:

  • Cell D5 is used in the formula, because that was the active cell when the conditional formatting was applied.
  • A relative reference is used for the data entry cell (D5), because it should adjust to match each cell where the conditional formatting is applied.
  • Absolute references are used for $B$2, $E$2 and $F$2 because no matter where the conditional formatting is applied, it should always check those cells.

Shade Alternating Rows

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

shade alternating rows

  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.

shade bands of rows

  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.

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.

Create Coloured Shapes

  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.

Create Coloured Icons

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 2007 and newer versions: zipped sample Excel Conditional Formatting file

Excel 2003: zipped sample Excel Conditional Formatting file

More Tutorials

Conditional Formatting Intro

Conditional Formatting Multiple Cells

Conditional Formatting Documentation

Conditional Formatting Data Bars

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

Custom Search

30 Excel Functions in 30 Days

 

 

 

 

Excel Tools Add-in

 

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

 

 

30 Excel Functions in 30 Days

 

Peltier Tech Charts for Excel 3.0

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

 

30 Excel Functions in 30 Days

 

Last updated: September 8, 2017 9:17 AM