How-To Geek

Use Conditional Formatting to Find Duplicate Data in Excel 2007

When you have a lot of data to review in an Excel file it can be tedious finding duplicate values.  Today we will look at how to easily identify duplicate values with Conditional Formatting. 

With the Excel document open highlight the section of the document to search for duplicate entries.


On The Ribbon click under the Home Tab is selected.


In the Styles section click on Conditional Formatting \ Highlight Cells Rules \ Duplicate Values.


This opens up the Duplicate Values dialog box where you can select how to format the cells with duplicate data.


Now you will be able to easily find the cells that contain the duplicate data so you can change the values or delete them.


This is a quick and easy tip to help make sure important spreadsheets contain correct information.

Brian Burgess worked in IT for 10 years before pursuing his passion for writing. He's been a tech blogger and journalist for the past seven years, and can be found on his about me page or Google+

  • Published 06/4/09

Comments (10)

  1. Rob

    Cool. That’ll be helpful when I need to change duplicate data.

    However, as long as you are only deleting the duplicate data, you can also simply use the “Remove Duplicates” function in the “Data” tab.

  2. Varun Sharma


    Thanks for the information.
    It helped me alot.

    One query: Suppose I have 8000 Rows and I applied your method to find duplicates.
    After getting the cells highlighted, do we have option to check only highlighted rows..else I need to scroll down to find the highlighted cells


  3. Gigi

    Apply a filter to the column you are interested in. Click on the drop down list and you should see the option “Filter by Color”. Click on it and you can choose to “Filter by Cell Color”.

  4. ram

    duplicate show karne ka process batayen


    Thanx a lot buddy !!! This information is really helpful!

  6. David Cotton

    Very Helpful. Thanks.

  7. Lelo

    Thanks a lot for the information!
    It is really helpful and easy to use, its helping me on Data that I ma working on, really apreciated it.

  8. Navin_raj

    That was a cool tool in Excel 2007. Thank you. The duplicate entries was irritating me to fad up with it. I just exhausted to find them out. But this information was very helpful…….Thank you Sir. :)

  9. Nicholas Kuria

    Thanks a lot for this useful information.I have a different type of challenge.Say I have a list of client names like John,Michael,John,Michael which have been duplicated.How do I identify them in a huge worksheet?

  10. noor

    thanks a lot for the info..

More Articles You Might Like

Enter Your Email Here to Get Access for Free:

Go check your email!