Excel tip: Highlight rows containing duplicates

This excerpt from David Ringstrom’s High Impact Excel series on our US sister site shows how you can apply conditional formatting to rows that include duplicates.

A previous tip in 2013 explained how to identify duplicate cells in a worksheet, but David was challenged to find a way to not only identify duplicates in a single column, but also flag the adjacent cells in the same row.

This tip presents his solution in two parts. The first explains how to identify duplicate values in a single column, and the second part shows how to extend the technique. The tip is passed on Excel 2007 and later editions. To highlight rows with duplicates using these techniques in in Excel 2003 and Excel for Mac 2011, see David’s original article on AccountingWEB.com.

The exercise starts with a list like this, from which the goal is to find repetitions of “Joe” in column A:

The procedure to highlight the dupes is as follows:

1. Select cells A1:A8.
2. Choose Conditional Formatting from the Home tab, then Highlight Cells Rules, and Duplicate Values.
3. Excel will open the Duplicate Values dialogue box and preview the formatting to be applied to your worksheet, illustrated. Notice that the Duplicate Values dialog box is dual-purpose, as you can choose Unique Values instead of Duplicates.
4. If you don’t like the default formatting, click the drop-down list on the right and then choose Custom Format to display the Format Cells options and choose an alternative.

This approach only applies to duplicates in a given range, and won’t interrogate cells outside the chosen range. To achieve this:

1. Select cells A1:D8 - so the whole data table rather than a single column is included.
2. Click Conditional Formatting on the Home Tab, then New Rule.
3. Choose Use a Formula to Determine Which Cells to Format from the New Formatting Rule dialogue box.
4. Enter this formula in to the “Format Values Where This Formula is True” box:
   =COUNTIF($A$1:$A$8,$A1)>1
   COUNTIF has two arguments:
   Range: In this example the cell range is $A$1:$A$8. The dollar signs are absolute references that tell Excel to look at all rows in the first column.
   Criteria: The Criteria in this case is $A1. There is no absolute reference $ in front of the row number, because the formula is designed to look at column A, but to shift the row number for each row of our data set. The >1 portion of the formula tells Excel the rule should only be get applied when the result of the COUNTIF function is greater than 1, which means only rows with a duplicate value in column A will be formatted.

5. Click the Format button, make formatting selections to your liking, and then click OK twice to apply the formatting. This should be the result:

The dollar sign absolute cell references are crucial for this technique because you’re entering one Conditional Formatting formula to cover multiple rows. If you omit any of the dollar signs referenced above the range that the conditional formatting looks at, the formula may shift and either not find the duplicate values, nor highlight the given rows.

With Conditional Formatting, it’s often a case of “if at first you don’t succeed, try, try again”. If your formula doesn’t work as expected, choose Conditional Formatting, Edit Rule, and then double-click on your rule and verify your formula.