Excel 2007: Highlight Duplicates

Posted January 15, 2007 by Rob Rogers in Microsoft Excel

Using conditional formatting, you can easily highlight duplicate values in your spreadsheet. This will make it easier to identify the duplicates so that you can remove them. To do this…

1. Select the column that is to be checked for duplicate values.

2. Go to the Ribbon and select the Home tab.

3. In the Styles section, click Conditional Formatting.

4. Select Highlight Cells Rules and click Duplicate Values.

5. In the Duplicate Values dialog box, choose the formatting you wish to use from the dropdown. The default is a light red fill with dark red text.

6. Click OK.

The background color of all values that appear more than once in the selected column will show the selected formatting.


About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

  • Jonathan loves Innovating

    Right to the point & helpful. Got just what I needed. Thanks!

  • waqi

    Thanks it is v v Good

  • MattA

    Thank you! Very helpful.

  • abdul

    good example, use full tip

  • mzerika

    for some reason it has worked on some but not all! ive got two columns of emails addresses only and some are highlighted but ive found a few that werent. Any thoughts?

  • Anonymous

    Thank you shamanstears!

  • Syed

    awsome dude :=) it’s very handy…

  • Anonymous

    This is great for identifying duplicates in individual columns, but how about an exact match with multiple columns?

  • Anonymous

    This is great for identifying duplicates in an individual column. But how about an exact match for duplicates in an entire row or multiple columns?

    • Smarazvi

      For multiple columns, try to find duplicates using the same formula after concatenating the values of the columns into a separate column

  • Rohit Bkt92


  • sj

    thank you. Ur an angel! 🙂

  • kishan


  • Shiblee Mehdi

    That is simply great!

  • Mohamed Yossef

    thanksss million

  • N.Bejarano

    excellent! exactly what i needed. 100x easier than MS help!

  • siva kumar

    very much helpful while running payroll…….thanks for giving such a valuable solutiion………

    first thanks to MIRCROSOFT….and then thanks to the person who has posted the solution in the net……

    best regards
    siva kumar.m.v

  • UGG Highkoo Boots

    This year we really have been spoiled as UGG Gissella Boots have brought out 17 new designs of boot for the womens collection! All of which are very classy without compromising in comfort. Cheap UGG Boots are successful based on these 2 points, and once you have experienced walking in you will never turn away from them. In fact there are lots of women who can’t wait for winter because it means that they get to wear them again! See in more detail the new boots!
    UGG Upside Boots is approached as the perfect boots prevent snow and rain. As kind of UGG Roxy Tall Boots are a sleeker that have more tightly designed UGG Bailey Button Boots with great features like a Vibram outsole,a waterproof Leather upper and a fully-lined fleece sockliner.What’s more UGG Classic Tall Boots removable sheepskin sockliner provides extra warmth and comfort.
    I love my UGG Boots! I decided this particular style, because each one has its original style. I like UGG Highkoo Boots being different. I wear them with boot cut Levi’s jeans and they look great. Even led them to a night club and received many compliments. They are very fashionable, if you live in New York UGG Sundance II Boots. They are very comfortable and warm as well UGG Mayfaire boots. 90% of the time used without socks. I wear a size 8 1 / 2, but I bought a size 8, because they run a little big. Worth the money and I can say that doing well in snow. Thanks to Ugg Nightfall Boots and UGGS!

  • http://pulse.yahoo.com/_MN26FNM7CBFP7LGQ33NSWQB2EY Rajkumar P

    Thanks for the help

  • WhoKnows

    Good stuff!

  • M_garg25

    Thanks..its really works for me…thatnks once again

  • Mohsin

    Great, really helpful for me.

  • Avish

    what if i need to highlight rows containing duplicate text with different colors??

  • Arun


    I want to know that how can we color the duplicate values in excel, i am not able to find any thing releted to this.
    PLease help me with this.


  • Mazhar Shahzad

    Thanks for Help ! its great

  • J G

    Worked perfectly the first time. Straight to the point and accurate. Thank you so much!

  • Prashant


  • Satya

    Thanks !!! its very use full tip to find out Duplicates in Excel 2007.

    Once again Thanks…………..

  • Saim

    It works 🙂


    Thank You for helping. Its really great function.

  • vinay

    Thanks , nice sloution

  • james

    exactly what i was looking for without all the crap in between! Major Thank YOU 🙂