Question: I’m using conditional formatting to find duplicate values in a column, and it’s working in that it highlights both values if they’re duplicated. How do I ignore the first instance and highlight only the second?
Answer: By using the COUNTIF function
Process (Excel 2003, 2007 and 2010):
Conditional formatting is a very useful feature in Excel, and can be used to highlight duplicate values in a column – in Excel 2003, you can use a COUNTIF function and there is the option of highlighting duplicate values as a straight command in Excel 2007 and 2010.
However, the command will highlight all instances of duplication. The following graphic shows a table of information, with some of the staff codes in Column A being repeated and two regions repeated multiple times in Column B. This tip will use Excel 2007/2010 to demonstrate the functionality; however the formula is still valid for Excel 2003’s conditional formatting.
Straight Conditional Formatting
1. Select cells A2 to A19. It’s important to NOT select the heading in this range selection.
2. Select the Conditional Formatting option from the Home Tab on the Ribbon.
3. A Duplicate Values formatting box will appear. Select the formatting you would like and click OK.
You will see that certain cells in column A become formatted, highlighting that they contain duplicate values. However, both instances of the duplication are highlighted.
Ignore First Instance of Duplication
1. Select cells B2 to B19. Again, it’s important to NOT select the heading in this range selection.
2. On the Home Tab on the ribbon, select Conditional Formatting, and then select New Rule.
3. The New Formatting Rule box appears. In the Select Rule Type area, select the final option, Use a formula to determine which cells to format, then in the Format values where this formula is true box type in the following formula:
=COUNTIF($B$2:$B2,B2)>1
4. Select the Format… box, and on the Font tab, format the text to be red in color, then click OK. You will also need to click OK again at the New Formatting Rule window.
You will now see that every duplicate apart from the first instance in the cell is highlighted.
