Find Duplicates in Excel Easily with Conditional Formatting

Whenever you think you might have some duplicate entries in Excel, run a quick check with Excel 2007/2010 conditional formatting.

Step 1: Select the data

Excel Conditional Formatting select data

Step 2: Click Conditional Formatting–>Highlight Cell Rules–>Duplicate Values…

Excel Conditional Formatting Duplicate Values

You will then have a chance to see the duplicates with the color you specify before you click OK.

Excel Conditional Formatting cells that contain

Final result: Red color shading over top of the duplicates.

Excel Conditional Formatting final step

Tip: Right click the “Duplicate Values…” command and then left click “add to the quick access toolbar.”


About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged , . Bookmark the permalink.

10 Responses to Find Duplicates in Excel Easily with Conditional Formatting

  1. Dan R Morris says:

    That’s awesome. I had no idea you could do that. I learned the concatenate function this year too. So much cool stuff with Excel.

  2. I prefer to use the ‘&’ operator instead of concatenate. So, instead of writing: =CONCATENATE(A1,” “,B1) I use : =A1&” “&B1

    But the MultiCat custom function suggested by submeg is awesome! Specially because you can specify a delimiter.

  3. Pingback: Use Data Bars and Icon Sets to Major League Baseball Standings using Excel 2010 | Steve Chase Docs

  4. Pingback: The Top 10 Posts of 2012 on my 1 year Blog Anniversary | Steve Chase Docs

  5. Steve P says:

    This is great! But how would I go about finding duplicates across multiple sheets within the document?

    • Steve Chase says:

      Conditional formatting only works within a worksheet range. One method would be to copy and paste all the data in 1 worksheet then apply the conditional formatting…find duplicates. Otherwise it would require programming with a macro.

  6. Farhana says:

    It is the easiest formula to find out duplicate value!!!! Fine!!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s