Contextures - Microsoft
Excel and Access consulting

Search Contextures Sites

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

download a zipped sample Excel
Conditional Formatting file

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.

Select cells C2:C5

Choose Format|Conditional Formatting

From the first dropdown, choose Formula Is

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)

Click the Format button.

Select a font colour to match the cell colour.

Click OK, click OK

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.

In Excel 2003:

Select range A2:A5

Choose Format|Conditional Formatting

From the first dropdown, choose Formula Is

For the formula, enter
=A2=A1

Click the Format button.

Click OK, click OK

In Excel 2007:

Select range A2:A5

On the Ribbon, go to the Home tab and click Conditional Formatting

Click New Rule

Click Use a Formula to Determine Which Cells to Format

For the formula, enter
=A2=A1

Click the Format button.

Select a font colour to match the cell colour.

Click OK, click OK

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

Highlight Duplicates in Column

Use Excel conditional formatting to highlight duplicate entries in a
column:

Select range A2:A11

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 colour for highlighting.

Click OK, click OK

Highlight Items in a List

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

Choose Format|Conditional Formatting

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

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

Select cells B2:G4

Choose Format|Conditional Formatting

From the first dropdown, choose Formula Is

For the formula, use the CountIf
function:
=COUNTIF($B$6:$G$6,B2)

Click the Format button.

Select formatting options (green pattern, in this example), click
OK

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.

Select cells A2:A4

Choose Format|Conditional Formatting

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

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.

Select cells B2:F4

Choose Format|Conditional Formatting

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

Shade Alternating Rows

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.

Choose Format|Conditional Formatting

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

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.

Click the Select All button, above the Row 1 button, to select all
the cells on the worksheet.

Choose Format|Conditional Formatting

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

Shade Alternating Filtered Rows

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

Choose Format|Conditional Formatting

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.

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.

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

Choose Format|Conditional Formatting

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 the Add button, and for Condition 2, choose
Format|Conditional Formatting

From the first dropdown, choose Formula Is

For the formula,enter:  =$B3>30

Click the Format button, and select Green as the font colour, then
click OK.

Click OK

Excel Conditional Formatting Tutorials

Excel
Conditional Formatting -- Introduction

Excel
Conditional Formatting -- Multiple Cells

Excel Conditional Formatting -- Examples

Excel
Conditional Formatting -- Documentation

Excel
Conditional Formatting -- Data Bars

Home     Excel
Tips     Excel
Files     The Excel
Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright 2011
All rights reserved.

Debra Dalgleish

Last updated: July 11, 2011 8:15 AM