Highlighting duplicate cells in a column

[Guest]

I need to highlight duplicate cells in a column (not hide them). Someone
posted this simple solution a few weeks back:

"Assuming that A1 is a column heading and your name entries begin in A2,

1. select column A
2. data > filter > advanced filter > check Unique Records Only > click ok
3. Alt; (selects visible cells)
4. Data > filter > show all

Step 2 will hide all the duplicates.
Step 3 will highlight the remaining unique records.
Step 4 will make the entire column A reappear, with Unique Records
highlighted (grey) and the duplicates un-highlighted (white)."

My problem is Step 4 is not working. None of the cells are highlighted
(even though I know I have duplicates in my column). What am I doing wrong?

(I know how to highlight duplicates with a formula; but I would like to be
able to use this method too.)

I'll be grateful for any help.

 

[Guest]

Perhaps there's a difference in your interpretation of highlighting. The
method given basically leaves the cells that are visible while the filter is
in place selected, which applies a gray shading to them. But it does not
permanently change their colors.

In between steps 3 and 4, choose a color to highlight (shade) the selected
cells. Then when you get to step 4 and display all rows again, the unique
records will remain 'highlighted'.

 

[MartinW]

Hi Nanapat,

One more step.
Before you do the show all part you have to select a cell fill color.

But this doesn't highlight duplicates, as it says, it highlights unique
records only i.e. one instance of each cell.

To highlight duplicates.
Select all of your data in column A (making sure that A1 is
the active cell)
Go to Format>Conditional Formatting
and set Formula is =COUNTIF(A:A,A1)>1

HTH
Martin