Remove Duplicates in Excel 2010



  1. copied exactly from an original
  2. identical
  3. existing as a pair or in pairs; twofold

Many times when Excel is used to manage lists of data, duplicates may exist in the lists of data and needed to be identified and deleted. How can we identify the duplicates in our Excel spreadsheet?

In the spreadsheet below we want to find the duplicates for the Product column, (imagine the data being many more than this actual example). Instead of going through our data one by one in order to locate the duplicate we can do the following:


First of all, we must select a cell inside the area of the data and then activate the Data tab and from the area of the ribbon named Data Tools we select the command Remove Duplicates as we can see in the image below.


Once selected the Remove Duplicates window appears as we can see in the image below. By default the My Data Has Headers command is activated, and all the headings of our spreadsheet are selected.


If we do not have headers in our spreadsheet we need to de-activate the My Data Has Headers command, and the Remove Duplicates window will appear as the image below.


If we want to uncheck all the headings we just press the Unselect All button. Then we click on the check box at the left of the column name that we want to check for duplicates. We then press the OK button in order for Excel to search and delete the duplicates.


The information dialog box appears notifying us that one duplicate value was found and removed, and that six unique values remain as we can see in the image below.


If we have selected a cell outside of the data area and try to use the Remove Duplicates function the following warning dialog box will appear notifying us that the command could not be completed because of the range specified.


If we don’t want to remove the duplicates in our spreadsheet, but to highlight them in order to be examined we can use the Conditional Formatting command.

In order to use it, we select the Home tab and from the area of the ribbon named Styles we select the command Conditional Formatting as we can see below.


Once the drop down menu appears we select the Highlight Cells Rules and from the right of the menu we navigate to the bottom of the secondary menu and select the Duplicate Values command.

The Duplicate Values dialog box appears where we can select what sort of formatting the Duplicates or Unique values will have as we can see in the image below.


Straight away our duplicate or unique values will be highlighted according to the format that we had chosen.

Below you can check out the video describing how to use the Remove Duplicates function in Excel 2010.


About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on June 11, 2012, in Excel 2010 English, Microsoft Office 2010 English and tagged , , , , , , , , , , . Bookmark the permalink. Comments Off on Remove Duplicates in Excel 2010.

Comments are closed.