Highlighting Duplicate Values in Excel – [Excel 2007, 2010, 2013]

One of the most common questions I hear, is around how to manage duplicate values in Excel.

I’ve already covered off how to remove duplicate values in this post.

I personally feel the ability to remove and highlight duplicates should grouped together, but whilst removing duplicates forms part its own ‘feature’, highlighting duplicates forms part of the wider Conditional Formatting functionality.

Since Excel 2007, highlighting duplicates has been very straightforward, with a pre-defined option to enabling it. So it’s this approach I will cover in this post. It can still be achieved in 2003 using a simple formula, but hopefully by now everyone is using 2007+

Highlighting Duplicates

With your data, select the range

From the HOME tab of the Ribbon, within the Styles group select the Conditional Formatting menu.

From the drop-down menu, select Highlight Cell RulesàDuplicate Values (the placement of this option may differ slightly between versions)

You’ll then see the Duplicate Values box.

You get two options initially – highlight Duplicate Values, and highlight Unique Values.

What you don’t get it the option to highlight the first unique value differently from the remaining duplicate values. So selecting Duplicate Values will select all instances where that value is duplicated, and selecting Unique will only select values that only appear the once.

In the second drop down, you can select pre-detirmined formatting options, or opt to select your own formatting if you so wish.

Click OK, and that’s all there really is to it