Easy steps - use Excel conditional formatting to hide duplicates, highlight expiry dates, mark lottery numbers, and more. Videos, written steps, free workbooks
After you set up Conditional Formatting rules, you might want to review the rules, or do some troubleshooting. To see the Conditional Formatting rules in the active worksheet, follow these steps:
On the Excel Ribbon, click the Home tab
Click the Conditional Formatting command
Click Manage Rules
At the top of the Conditional Formatting Rules Manager, select "This Worksheet" from the drop down list
Each rule is listed, and shows its formula, format, range it applies to, and check box for "Stop if True".
Quickly Check the Formulas
Only a small part of each formula is visible, and you can't show more. Unfortunatley,
you cannot adjust the size of the Rules Manager window
you cannot adjust the column widths in the list of rules
However, if you don't have to click the Edit Rule button, to see a full formula.
To see a full formula, point to one of the rules
Its full formula will appear in a popup.
After viewing the formula, if you need to edit it, click the Edit Rule button, and make your changes.
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
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
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.
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
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:
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
For Excel 2003:
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
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:
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
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
Select all the cells where you want the formatting -- range A2:C9
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:
=ISFORMULA(A2)
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
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
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.
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
Both examples use the same list -- the months of the year, and the quantity sold each month.
This video shows the steps, and the written instructions are below the video.
Top 3 Values
In this example, we'll highlight the 3 highest numbers in the list of monthly sales.
The conditional formatting rule will use the LARGE function, which has 2 arguments:
the list of numbers to check -- $C$2:$C$13
the nth largest number to return -- 3
The rule will compare each number in the list, to see if it is greater than or equal to that nth number
C2>=LARGE($C$2:$C$13,3)
Follow these steps to apply the conditional formatting:
Select the numbers in the Sales column (C2:C13)
On the Ribbon's Home tab, click Conditional Formatting, then click
New Rule
Click Use a Formula to Determine Which Cells to Format
In the formula box, enter the LARGE formula, =C2>=LARGE($C$2:$C$13,3)
Click the Format button.
Select formatting options (light green fill),
click OK
Click OK
Bottom X Values
In this example, we'll highlight the lowest numbers in the list of monthly sales. Instead of typing a specific number in the formula, we'll set up a cell on the worksheet, where that number can be entered. Then, the conditional formatting formula will refer to that cell.
First, set up the number cell:
In cell E1, type the heading: How Many?
In cell E2, type the number 2 -- that can be changed at any time later
Put a border around the 2 cells, centre horizontally, and add yellow fill colour in E2
TIP: Later, you could type a zero, to temporarily remove any highlighting
To highlight the bottom values, the conditional formatting rule will use the SMALL function, which has 2 arguments:
the list of numbers to check -- $C$2:$C$13
the nth smallest number to return -- a reference to cell $E$2
The rule will compare each number in the list, to see if it is less than or equal to that nth number
C2<=SMALL($C$2:$C$13,$E$2)
Next, follow these steps to apply the conditional formatting:
Select the numbers in the Sales column (C2:C13)
On the Ribbon's Home tab, click Conditional Formatting, then click
New Rule
Click Use a Formula to Determine Which Cells to Format
In the formula box, enter the SMALL formula, =C2<=SMALL($C$2:$C$13,$E$2)
Click the Format button.
Select formatting options (light orange fill),
click OK
Click OK
To test the conditional formatting:
Type a different number in cell E2, to change the number of cells that are highlighted
Delete the number in cell E2, or type a zero, so that none of the numbers are highlighted
Highlight Weather Data
In this conditional formatting example, temperatures and weather descriptions are highlighted in a weather log. You can download the sample data below.
In the weather log table,
a Red-White-Blue color scale is used on the Temperature column.
In the weather column, there are 4 rules set up, to colour the cells based on weather type - Sun, Cloud, Rain or Snow.
In another worksheet, there are 2 named Excel tables - one for weather types, and one for weather descriptions.
There are also 3 named ranges, shown in the screen shot below
The named ranges are also used in the INDEX/MATCH formulas in the conditional formatting rules. This screen shot shows the formula in a worksheet cells, where it was used for testing the formula, before creating the rule.
Here are the 5 rules, listed in the Rules Manager.
You can download the Conditional Formatting for Weather Data sample data below.
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.
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.
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
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
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.
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
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.
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
Hide Follow-Up Questions
In this example, there is a short questionnairei, and some of the questions have a follow-up item. The follow-up question might appear, based on the first answer.
At first, only the main questions are visible. The follow-up items are in white font with white fill.
For example, "Do you have dependents?"
If you select No, nothing happens.
If you select Yes, and the next question appears.
To set this up:
Follow-up cells have simple conditional formatting rules, such as: =E2="Yes"
The conditional formatting adds a black font, fill colour, and a border.
For step-by-step video and written instructions, see my Hidden Questions blog post. The sample file is in the download section, below.
Cross Off Completed Items
If you have a list of the tasks that you have to work on, use conditional formatting to cross off completed items. In this example, completed tasks are marked with an X in the "Done" column.
There is a conditional formatting on the list, to cross off completed items, and change the font to light grey. That makes it easier to focus on the tasks that still need to be finished.
To set up this conditional formatting, follow these steps:
Select cells A2:C4
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 =$C2<>""
In each cell, the formula checks if column C in that row is empty
Click the Format button.
On the Font tab, choose Strikethrough, and select light grey as the colour
Click OK, click OK
After you set up the conditional formatting rule, the item will be crossed off, if you type anything in the "Done" column.
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.
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
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
Add Conditional Formatting
Next, add conditional formatting to country cells in the data range. The formula is explained below.
Select cells D5:D14, where the country names are listed for the orders
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 =AND($B$2=$E$2,D5=$F$2)
Click the Format button.
Select formatting options (red fill colour, in this
example)
Click OK, click OK
If US is entered in cell B2, and a cell in D5:D14 contains "United States", it is 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:
Does cell B2 match the condition entered in cell E2
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.
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
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.
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
Shade Bands by Group
You can use Excel conditional formatting to shade bands by group. In this example, the sales rows for the dates are in alternating colours - blue and no fill. This technique was adapted from Chip Pearson's site.
First, to prepare the table for shade bands by group, follow these steps:
In cell D1, type the heading for a new column - TRUE
In cell D2, enter this formula, which returns TRUE or FALSE.
=IF(A1=A2,D1,NOT(D1))
Next, follow these steps to add the conditional formatting:
Select all the data cells in the table
On the Home tab, click Conditional Formatting, New Rule
Click on "Use a formula to determine which cells to format"
In the formula box, type this formula, referring to the active data cell: =$D2=TRUE
Click the Format button, and choose a fill colour
Click OK, twice, to apply the formatting
(Optional) Hide the TRUE/FALSE column, to tidy up the worksheet.
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.
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
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
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
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
Next, create the icons in column C:
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.
Hidden Data Warning: This sample file shows warnings, if rows or columns are hidden. Formula checks for hidden rows, and conditional formatting marks hidden column. Zipped file does not contain any macros.
Conditional Formatting for Weather Data: This sample file uses color scale for temperatures, and 4 formula rules for weather conditions - Sun, Cloud, Rain and Snow. The zipped file is in xlsx format, and does not contain macros