Article updated on December 27, 2017

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>0

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>0
  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