Excel find equal cells

Question

Hi,

lets say I have a xls sheet with just one column which has the following content:

• 1
• 2
• 3
• 3
• 4
• 5
• 5
• 6

You see there are some cells with equal content.

Now I want Excel to format (e.g. background) all cells which are there twice. I would say that conditional formatting is the right way but there I always have to enter a certain cell.

Is there any possibility to do this?

Best,

Elias

Answer 1

Select A1:A8 and enter this conditional format - Formula Is

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

and set a format. It will format anything in column that appears more than 1 time.

Answer 2

I do this routinely. Start at the second cell and select the rest. Choose Conditional Formatting, as you thought, select VALUE IS EQUAL TO, and click the cell above (let's call it A1). Here's the trick: it will enter this as an absolute reference - $A$1 - but you need to strip out the dollar signs, so it is relative. Now (of course) assign a background color, or whatever style you want, and the first cell in any sequence of identical values will have normal formatting, but subsequent cells will have the style you choose.

This isn't exactly what you've asked for - the first 3, for instance, will still have normal formatting - and I'm not sure I know a way to get all the duplicated cells, including the first, styled differently. But it's a start...