How to highlight duplicates in Excel 2010?

To highlight duplicates in Excel version 2007 or 2010 first select the range of cells which you wish to highlight then choose the 'Home' ribbon and from the 'Styles' group select the 'Conditional Formatting' button. From the 'Highlight cell rules' option choose 'Duplicate values'. Choose the formatting you require and then click OK.

The result will show all cells that have duplicate values and you can then use the autofilter feature to filter your list by color. You can then sort the data by one of the text columns and finally check to see whether in fact the data for each cell in a row is duplicated in another row. Now if you wish to remove the duplicates you can do this from the data ribbon.

One of the benefits of using conditional formatting to find duplicates first before using the remove duplicates option is that you gain peace of mind. The peace of mind comes from knowing that you have checked for duplicate data before simply letting the system remove the data without checking first.

Related forum posts:

Data sorting

Hello If I have a lot of non numerical cells, is there a way of highlighting them all and finding out if there are any duplicates? The sort function wouldn't work as the data in each cell is quite lengthly, so its not easily detectable. If there is a quicker way to look, rather than going through each comment that would be great (i have over 200 cells of qualitative data) Many thanks

Read forum post

Related articles

How To Avoid The Embarrassment Of Dirty Data

More than 25% of the most critical data in the world's top companies is flawed. The problem, however, is due to bad business practice and not to any IT glitches. Here we find out how Excel can help you to keep your data clean.

Read article

How To Learn From Your Excel Mistakes

It's official - we definitely do not learn from our mistakes. However, we do learn from getting things right. We take a look at how the Excel 2010 Error Checker is here to help you get all your formatting right - again and again.

Read article

Some Lesser Known Time Saving Tips In Excel

Excel is renowned for its familiar time saving features such as AutoFill and AutoComplete. There are, however, some less well known time savers hidden within this vast application. This article looks at some of these corner cutters.

Read article

Training courses

98.7 % recommend us More live stats...