Author: Oscar Cronquist Article last updated on November 18, 2018

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

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.

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 "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 formual 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

Get excel example file

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

Functions in this article

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition