How to highlight duplicate values

Author: Oscar Cronquist Article last updated on March 31, 2022

How to highlight duplicate values
Highlight the smallest duplicate number

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:

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

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.

Cell	First argument
B3	$B3:$B$3
B4	$B4:$B$3
B5	$B5:$B$3

Step 2 - Absolute cell reference

The second argument changes to the current cell.

Cell	Second argument
B3	B3
B4	B4
B5	B5

Step 3 - Count current value in expanding cell range

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

becomes

COUNTIF("VV", "VV")

and returns 1

Cell	COUNTIF	Evaluates to	Result
B3	COUNTIF($B3:$B$3, B3)	COUNTIF("VV", "VV")	1
B4	COUNTIF($B4:$B$3, B4)	COUNTIF({"VV","AA"}, "AA")	1
B5	COUNTIF($B5:$B$3, B5)	COUNTIF({"VV","AA","DD"}, "DD")	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.

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

Select the range (B3:B10)
Press with left mouse button on the "Home" tab on the ribbon
Press with left mouse button on "Conditional formatting"
Press with left mouse button on "New rule..."
Press with left mouse button on "Use a formula to determine which cells to format"
Press with left mouse button on "Format values where this formula is true" window.
Type COUNTIF($B3:$B$3, $B3)>1
Press with left mouse button on "Format" button
Go to tab "Fill"
Pick a color
Press with left mouse button on OK button
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)

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:

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

2.1 How to apply conditional formatting

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

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.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form