How to Highlight Duplicate Values in Excel

In past versions of Excel, there was a several-step process using a COUNTIF formula in Conditional Formatting to highlight duplicate values in your data.

Starting with Excel 2007, however, finding and highlighting your duplicate values suddenly became a lot easier to manage.

Follow the steps below to learn how:

  1. On the Home tab of your Ribbon, simply click Conditional Formatting.
  2. Click Highlight Cell Rules | Duplicate Values.
  3. Click OK.


Now be aware that this is not just for a quick look at the duplicate values in your worksheet. Once you have done this, you have applied Conditional Formatting to the range of cells you selected so that in the future, when you add more data, any duplicates will also be highlighted automatically. So the bottom line is, you can apply this formatting to any range of cells in which you do not want duplicate values.


Posted in:
About the Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol's Corner Office or follow Carol on FaceBook and Twitter.