Excel 2007: Highlight Duplicates

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.

32 Responses to “Excel 2007: Highlight Duplicates”

  1. November 24, 2008 at 5:42 pm, Jonathan loves Innovating said:

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

    Reply

  2. February 03, 2009 at 12:24 pm, waqi said:

    Thanks it is v v Good

    Reply

  3. February 03, 2009 at 3:55 pm, MattA said:

    Thank you! Very helpful.

    Reply

  4. February 06, 2009 at 9:07 am, abdul said:

    good example, use full tip

    Reply

  5. July 02, 2009 at 4:30 am, mzerika said:

    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?

    Reply

  6. April 03, 2010 at 1:38 pm, Anonymous said:

    Thank you shamanstears!
    :o )

    Reply

  7. April 15, 2010 at 4:25 am, Syed said:

    awsome dude :=) it’s very handy…

    Reply

  8. April 27, 2010 at 9:52 pm, Anonymous said:

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

    Reply

  9. April 27, 2010 at 9:53 pm, Anonymous said:

    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?

    Reply

    • July 05, 2010 at 7:19 pm, Smarazvi said:

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

      Reply

  10. June 23, 2010 at 1:23 pm, Rohit Bkt92 said:

    wonderful!!!!!!!!!!!!!!!!!!!!

    Reply

  11. July 16, 2010 at 6:54 pm, sj said:

    thank you. Ur an angel! :-)

    Reply

  12. August 17, 2010 at 10:15 am, kishan said:

    thanks

    Reply

  13. September 08, 2010 at 9:14 am, Shiblee Mehdi said:

    That is simply great!

    Reply

  14. September 20, 2010 at 4:05 pm, Mohamed Yossef said:

    thanksss million

    Reply

  15. September 21, 2010 at 8:58 pm, N.Bejarano said:

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

    Reply

  16. November 04, 2010 at 9:58 am, siva kumar said:

    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
    hr

    Reply

  17. December 07, 2010 at 1:17 am, UGG Highkoo Boots said:

    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!

    Reply

  18. February 08, 2011 at 6:49 pm, Rajkumar P said:

    Thanks for the help

    Reply

  19. March 21, 2011 at 12:29 pm, WhoKnows said:

    Good stuff!

    Reply

  20. July 14, 2011 at 5:00 am, M_garg25 said:

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

    Reply

  21. October 22, 2011 at 7:36 am, Mohsin said:

    Great, really helpful for me.

    Reply

  22. December 08, 2011 at 12:34 am, Avish said:

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

    Reply

  23. February 15, 2012 at 11:05 pm, Arun said:

    Hi,

    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.

    thanks
    arun

    Reply

  24. June 01, 2012 at 8:53 am, Mazhar Shahzad said:

    Thanks for Help ! its great

    Reply

  25. July 11, 2012 at 5:46 pm, J G said:

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

    Reply

  26. September 03, 2012 at 2:58 pm, Prashant said:

    Excellent!!!

    Reply

  27. September 27, 2012 at 9:35 am, Satya said:

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

    Once again Thanks…………..

    Reply

  28. December 19, 2012 at 7:57 am, Saim said:

    It works :-)

    Reply

  29. December 24, 2012 at 1:20 pm, SURENDRA said:

    Thank You for helping. Its really great function.

    Reply

  30. February 17, 2013 at 5:38 am, vinay said:

    Thanks , nice sloution

    Reply

  31. June 10, 2013 at 7:51 am, james said:

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

    Reply

Leave a Reply