How to highlight duplicate values

1. How to highlight duplicate values

The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified.

To sort all duplicates to the bottom of the list for removal, creating a unique distinct list. See this blog post
How to create a unique list using conditional formatting in excel 2007

To color duplicate cells I use conditional formatting in excel. The conditional formatting formula in B3:B11:

Back to top

1.1 Explaining CF formula

Step 1 - Expanding cell reference

The first argument in the COUNTIF function expands as the CF moves to cells below, this makes it easy to spot duplicate values as their count is 2 or larger.

Step 2 - Absolute cell reference

The second argument changes to the current cell.

Step 3 - Count current value in expanding cell range

COUNTIF($B3:$B$3, B3)

becomes

COUNTIF("VV", "VV")

and returns 1

Step 4 - Check if value is larger than 1

COUNTIF($B3:$B$3, B3)>1

becomes

1>1

and returns FALSE. Cell B3 is not highlighted.

Back to top

1.2 How to highlight duplicate values occurring the second time or more using conditional formatting in Excel

1. Select the range (B3:B10)
2. Press with left mouse button on the "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type COUNTIF($B3:$B$3, $B3)>1
8. Press with left mouse button on "Format" button
9. Go to tab "Fill"
10. Pick a color
11. Press with left mouse button on OK button
12. Press with left mouse button on OK button again to return to Excel

1.3 Get Excel example file

highlight-duplicates-using-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Back to top

2. Highlight the smallest duplicate number

Question: How do I highlight the smallest duplicate value in a column using conditional formatting?

Answer:

Conditional formatting formula in A2:

2.1 How to apply conditional formatting

1. Select cell range
2. Go to "Home" tab
3. Press with left mouse button on Conditional formatting
4. Press with left mouse button on "New Rule.."
5. Press with left mouse button on "Use a formula to determine what cells to format"
6. Copy the above conditional formatting formula to "Format values where this formula is true:"
7. Press with left mouse button on Format button
8. Select a formatting you like.
9. Press with left mouse button on OK
10. Press with left mouse button on OK

Back to top

2.2 Explaining CF formula in cell A2

Step 1 - Identify duplicates

The COUNTIF function counts values based on a condition or criteria.

COUNTIF($A$2:$A$11, $A$2:$A$11)>1

becomes

COUNTIF({6;4;6;12;18;12;10;3;11;8}, {6;4;6;12;18;12;10;3;11;8})>1

becomes

{2;1;2;2;1;2;1;1;1;1}>1

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 2 - Extract duplicate numbers

The IF function uses a logical expression in order to determine which argument to return.

IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, $A$2:$A$11, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, {6;4;6;12;18;12;10;3;11;8}, "")

and returns

{6;"";6;12;"";12;"";"";"";""}.

Step 3 - Identify the smallest number in array

The MIN function extracts the minimum value in a cell range or array.

MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))

becomes

MIN({6;"";6;12;"";12;"";"";"";""})

and returns 6.

Step 4 - Compare smallest number to current cell

MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))=A2

becomes

6=A2

becomes

6=6

and returns TRUE. Cell A2 is highlighted.

Back to top

Get Excel *.xls file

highlight-smallest-duplicate-value-in-a-column.xls

3. Highlight more than once taken course in any given day

Answer:

Conditional formatting formula:

Explaining Conditional formatting formula

Step 1 - Count record

The next COUNTIF function counts values based on a condition or criteria.

COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*COUNTIF($C2, $C$2:$C$35)

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

Step 2 - Multiply arrays

All three arrays must return 1 in order to indicate a row as duplicate, to accomplish this we need to use AND logic. AND logic is performed using the asterisk charcater between arrays, in other words, arrays are multiplied.

COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

Step 3 - Sum numbers in array

The SUM function adds numbers in a cell range or array and returns a total.

SUM(COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35))

becomes

SUM({1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 1.

Step 4 - Check if record is a duplicate

If total is larger than 1 then we know it is a duplicate, the larger than character allows us to compare the total to a given condition, the returned value is a boolean value, TRUE or FALSE.

SUM(COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35))>1

becomes

1>1 and returns FALSE. Cell A2 is not highlighted.

Get Excel *.xls file

Highlight duplicate online classes using conditional formatting

4. Highlight duplicates in two columns

Question:

Answer:

The following animated image shows you two lists in column B and D.

Conditional formatting formula:

How to apply the conditional formatting formulas:

1. Select cell range
2. Press with left mouse button on "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type =(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1
8. Press with left mouse button on Format button
9. Press with left mouse button on "Fill" tab
10. Pick a color
11. Press with left mouse button on OK!
12. Press with left mouse button on OK!

Explaining formula

Step 1 - Check if current value B3 exists in cell range $B$3:$B3

The first argument in the COUNTIF function is a cell reference that expands downwards.

COUNTIF($B$3:$B3,B3)

Step 2 - Check if current value B3 exists in cell range $D$3:$D3

The first argument in the COUNTIF function is a cell reference that expands downwards.

COUNTIF($D$3:$D3,B3)

Step 3 - Add results

COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3)

Step 4 - Check if result is larger than 1

The parentheses makes sure that the order odf calculatiuon is correct.

(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1

becomes

(0 (zero) + 0 (zero))>1

0>1

and returns FALSE. Cell B3 is not highlighted.

Get excel *.xlsx file

Highlight duplicates in two lists using conditional formatting.xlsx

Recommended reading

Built-in conditional formatting

Highlight cells rule

Top bottom rules

Basic CF formulas

Search using CF

Unique distinct

Duplicates

Compare

Min max

Dates

Misc

Excel categories

Comments (2)