1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.
Ted French

Find Duplicates in Excel with Conditional Formatting

By May 5, 2012

Follow me on:

Find Duplicates in Excel 2007 Using Conditional Formatting
Finding Duplicates in Excel
© Ted French
Excel's conditional formatting feature is an easy way to add special formatting features to specific areas of your spreadsheets. Depending on the value in a cell, you can change things like the font color of specific data or alter the background color of a cell (or cells).

The "conditional" part of the name means that you can control when and where the formatting changes are applied. If the data meets a certain condition, then the formatting change occurs.

One common use for this feature is to find duplicate data such as duplicate names, dates, or formulas in large tables of data.

To find out all the details read the tutorial on Find Duplicates in Excel with Conditional Formatting.

Related Tutorials
November 8, 2012 at 4:40 pm
(1) Rita says:

I have two collums: Last Name and First Name.
I want to check to see if there are duplicated in the sheet. I am trying to get excel to highlight first and last names (together) that are duplicated. When I try it highlights all last names that are the same and all people with the same first name. I need to see if people with the same first and last name are in the sheet twice. help?

January 4, 2013 at 11:49 am
(2) Eric says:

Have a new column with both first and last name you can do this by using the concatenate function or just have =cell with first name&cell with last name down that entire column, use conditional formatting on the new column. This is annoying to do but the best way to do it because thinking about it in terms of probability there will be a lower chance someone has the same first and last name. Also excel and most computer programs lack functionality and the ability to take data from 2 columns and use them in the way you want in this case. There is probably a way to do it without adding the additional column but would require some very advanced formulas and waste your time trying to figure it out. Hope this helps.

January 28, 2013 at 11:31 pm
(3) Raymond says:

it help a lot if you are a beginner

July 14, 2013 at 12:55 pm
(4) Russ says:



August 8, 2013 at 2:25 pm
(5) cp says:

Russ, Eric’s suggestion is much better. What if someone has 250,000 rows to look through?

October 14, 2013 at 11:59 pm
(6) Alex says:
November 12, 2013 at 1:08 am
(7) kiran says:

very good tip……….thanks

November 13, 2013 at 8:04 pm
(8) Nihar says:

Thanks for the tip…it saves me a lot of manual work.

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>

©2014 About.com. All rights reserved.