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