How to do Conditional Formatting for Duplicate Values in Excel

Written By: admin on March 7, 2010 2 Comments

This is a tutorial on how to do conditional formatting for duplicate values in Excel 2010, Excel 2007, Excel 2003 and earlier.Also watch a video demonstration of the steps outlined in the tutorial.

In Excel 2007 and Excel 2010, do the following:

1) Highlight cells with duplicate values
2) On the Home ribbon, go to the Styles group => Conditional Formatting => Highlight Cells Rules=>Duplicate Values

In Excel 2003 and earlier versions of Excel, do the following.

1) go to Format menu => Conditional Format
2) Select Is Formula from the first textbox
3) Enter the formula – OUNTIF($A$1:$A$8,A1) Greater than 1 (the video below demonstrates a simple example where names are in range A1:A8.
4) Add formatting

Find below the video demonstrations for the above methods.