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. Click "Home" tab on the ribbon
  3. Click "Conditional formatting"
  4. Click "New rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Click "Format values where this formual is true" window.
  7. Type COUNTIF($B3:$B$3, $B3)>1
  8. Click "Format" button
  9. Go to tab "Fill"
  10. Pick a color
  11. Click OK button
  12. Click OK button again to return to Excel

Download 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