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!


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

    Thanks it is v v Good


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

    Thank you! Very helpful.


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

    good example, use full tip


  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?


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

    Thank you shamanstears!
    :o )


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

    awsome dude :=) it’s very handy…


  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?


  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?


    • 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


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



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

    thank you. Ur an angel! :-)


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



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

    That is simply great!


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

    thanksss million


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

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


  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


  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!


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

    Thanks for the help


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

    Good stuff!


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

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


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

    Great, really helpful for me.


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

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


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


    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.



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

    Thanks for Help ! its great


  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!


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



  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…………..


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

    It works :-)


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

    Thank You for helping. Its really great function.


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

    Thanks , nice sloution


  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 :-)


Leave a Reply