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

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.

- Select cells C2:C5
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- 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)** - Click the Format button.
- Select a font colour to match the cell colour.
- Click OK, click OK

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.

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.

- Select range A2:A5
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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* - For the formula, enter

**=A2=A1** - Click the Format button.
- Select a font colour to match the cell colour.
- Click OK, click OK

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

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

- Select the cells to format -- range A2:A11 in this example
- Choose Format|Conditional Formatting
- From the first dropdown, choose
*Formula Is* - For the formula, enter

**=COUNTIF($A$2:$A$11,A2)>1** - Click the Format button.
- Select a font or fill colour for highlighting.
- Click OK, click OK

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.

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:

- In this example, the data is in cells A2:F8
- In cell G1, add the column heading "AllData"
- In cell G2, enter the formula to combine all the data:

=CONCATENATE(A2,B2,C2,D2,E2,F2) - Copy the formula down to the last row of data

Add the conditional formatting:

- Select the cells to format -- range A2:F8 in this example
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- 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** - Click the Format button.
- Select a font colour for highlighting.
- Click OK, click OK

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

- Create a list of items you want to highlight. If the items are on a different sheet than the conditional formatting, name the list.
- Select range A2:A7
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- 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)

- Click the Format button.
- Select a font colour for highlighting.
- Click OK, click OK

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.

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.

**To highlight the winning numbers:**

- Select cells C6:H8
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- Click
*Use a Formula to Determine Which Cells to Format* - For the formula, use the CountIf function:

**=COUNTIF($C$3:$H$3,C6)>=1** - Click the Format button.
- Select formatting options (green fill, in this example), click OK
- Click OK

**To highlight the winning tickets:**

- Select cells B6:B8
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- Click
*Use a Formula to Determine Which Cells to Format* - For the formula, use the SUM and COUNTIF functions:

**=SUM(COUNTIF($C6:$H6,$C$3:$H$3))>=3** - Click the Format button.
- Select formatting options (yellow fill, in this example), click OK
- Click OK

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.

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.

- Enter the temperature in cell B3
- In cells F6:F25, enter the numbers 140 to -50, in increments of 10
- 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))** - Copy the formula down to row 25
- Select cell B3, then press the Ctrl key and select cells G6:G25 (the cells with the formulas)
- On the Ribbon's Home tab, click Conditional Formatting
- Click Color Scales, then click on the Red - White - Blue scale
- Change the temperature in cell B3, and the cell color will change, based on the color scale.
- (optional) Hide columns F:G, so the color scale is not visible.

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.

- Select cells A2:A4
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, use the Today function to count the days:

**=AND(A2-TODAY()>=0,A2-TODAY()<=30)** - Click the Format button.
- Select formatting options (Bold, Blue font, in this example), click OK
- Click OK

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.

- Select cells B2:B7
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, use the Today function to find expired dates:

**=B2<TODAY()** - Click the Format button.
- Select formatting options (Red fill color, in this example), click OK
- Click OK

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.

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.

- Select cells B2:F4
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, enter
**=$H$1="x"** - Click the Format button.
- Select formatting options (white font and white pattern, in this example)
- Click OK, click OK

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

- Click the Select All button, above the Row 1 button, to select all the cells on the worksheet.
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, enter
**=MOD(ROW(),2)** - Click the Format button.
- On the Patterns tab, select a colour for shading
- Click OK, click OK

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.

- Click the Select All button, above the Row 1 button, to select all the cells on the worksheet.
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, enter
**=MOD(ROW(),6)<3** - Click the Format button.
- On the Patterns tab, select a colour for shading
- Click OK, click OK

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

- Select the cells in the list (A2:B29 in this example).
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, enter
**=MOD(SUBTOTAL(3,$A$1:$A2),2)** - Click the Format button.
- On the Patterns tab, select a colour for shading
- Click OK, click OK
- Filter the list, and the shading will alternate in the visible rows.

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.

- In cell C3 enter the formula:

**=IF(B3="","",IF(B3<10,"l",IF(B3>30,"n","t")))** - Copy the formula down to cell C7
- Format cells C3:C7 with Wingding font, and yellow font color
- Select cells C3:C7
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- 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*

- For the formula, enter:
**=$B3<10** - Click the Format button, and select Red as the font colour, then click OK.
- Click New Rule, and click
*Use a Formula to Determine Which Cells to Format* - For the formula,enter:
**=$B3>30** - Click the Format button, and select Green as the font colour, then click OK.
- Click OK

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.

- In G3:G5, type the percentages: 67%, 33%, 0%
- In H3, enter the formula: =PERCENTILE($D$2:$D$11,G3)
- Copy the formula down to rows 4 and 5
- Format cells H3:H5 in Wingding3 font
- 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

- 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))) - Copy the formula down to cell C11
- Format cells C2:C11 with Wingding3 font, and yellow font color
- Select cells C2:C11
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
- Click
*Use a Formula to Determine Which Cells to Format* - For the formula, enter:
**=$B2<$H$4** - Click the Format button, and select Green as the font colour, then click OK.
- Click New Rule, and click
*Use a Formula to Determine Which Cells to Format* - For the formula,enter:
**=$B2>$H$3** - Click the Format button, and select Red as the font colour, then click OK.
- Click OK
- To make the icons appear to be in the same cell as the number, you can create an outside border around the two cells.

Excel 2010/2007: zipped sample Excel Conditional Formatting file

Excel 2003: zipped sample Excel Conditional Formatting file

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 Contextures Sites

__

Custom Search

Last updated: September 20, 2016 1:22 PM