Identify duplicates in row with conditional formatting

[Carolyn]

I having trouble setting up my conditional format to identify duplicate
entries in a single column. Can someone please assist me with how to set it
up? In my spreadsheet of data, I have only 1 column that I want to identify
duplicates with a color.

 

[Teethless mama]

=COUNTIF($A$1:$A$10,A1)>1

 

[Jacob Skaria]

Try the below formula to highlight all dupliate entries
1. Select the cell/Range column A
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=COUNTIF(A:A,A1)>1
Please note that the cell reference A1 mentioned in the formula is the
active cell in the selection. Active cell will have a white background even
after selection
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK


If this post helps click Yes

 

[Jacob Skaria]

If an item is found twice; if you want only the 1st instance to be
highlighted; then try the below

=COUNTIF(A1:A$100,A1)>1

If this post helps click Yes

 

[Carolyn]

For some reason, I can't get it to work, I feel dumb. Can you verify that I'm
not missing anything, please?

I have a column titled "Serial Number" - located in A1
Then each line, from A2 - A513, each cell has a serial number in it, but I
know I have duplicates in there.

I have tried to select the cell and then do the conditional formatting and I
have set my cursor on A2 and go to the top and select the range for the
'count if' formula and when I hit enter, it doesn't color the duplicate cells.
How do I set it for the entire selection?

 

[Jacob Skaria]

1. Select the range (A2 - A513)
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=COUNTIF(A$2:A$513,A2)>1

OR (to highlight the 1st duplicate incase there are two)
=COUNTIF(A2:A$513,A1)>1

Please note that the cell reference A2 mentioned in the formula is the
active cell in the selection. Active cell will have a white background even
after selection
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes

 

[Carolyn]

OHHH!!!! I got it! I found where I was going wrong. When I entered in A2
(after the range selection in the formula), I was physically clicking on A2
which appeared as $A$2, so I took out and typed in 'A2' and I got it. Thank
you! It's usually the little things that trip you up!!

 

[Jacob Skaria]

Good to know.. The point to be noted while working with CF is that MS Excel
assigns the formula to all the cells in the selection. The cell reference
made in the formula should be the active cell in the selection... and as
mentioned earlier active cell will have a white background even after
selection...


If this post helps click Yes