Microsoft Excel - Remove duplicates

Learn about removing duplicates

Spreadsheet programs such as Excel are often used as databases for things like inventories, sales records, and mailing lists.

Databases are normally organized into rows of data called records. In a record, the data in each cell or field in the row is related - such as a company's name, address and phone number.

A common problem that occurs as a database grows in size is that of duplicate records.

This duplication can occur if:

• entire records are entered into the database more than once resulting in two or more identical records
• multiple records have one or more fields - such as a name field or address - containing the same data.

Either way, duplicate records can cause a whole host of problems so it is a good idea to scan for and remove duplicate records on a regular basis.

To help you accomplish this task Excel has a built in data tool called Remove Duplicates.

In this example we will use the Remove Duplicates data tool to remove the second of two identical records.

How?

Firstly, click to download the example file, or add the data seen in the image below in an Excel worksheet.

The entry about the company "Delta" in "France" is in both rows 4 and 13.

1. Click on any cell containing data in the sample database.
2. Click the Data tab on the Ribbon.
   
   
3. Click on the Remove Duplicates icon.
   
   
4. Clicking on the icon highlights all data in the database and opens the Remove Duplicates dialog box.
   
   When the dialog box opens, by default all of the column headings or field names from our data sample are selected.
   Depending on what we want to remove, we need to leave at least one of the column headings checked.
   In this example, we want to remove the second of two identical records about companies in the same country.
   So we deselect the "Creation Date" column heading.
   
   
5. Click OK.
6. Excel will delete records from your dataset. It will report that x duplicates were removed and y records remain.
   
   And the spreadsheet will show only unique records about headings "Company Name" and "Company Country".
   The duplicate "Delta" in "France" record will have been removed from the database and the records in rows 14 and 15 moved up to fill the gap.
   
   

Note: In case you want to obtain a unique list of companies based on their "Company Name", you can redo the procedure by selecting only the "Company Name" heading:

Excel will delete duplicate company names from your dataset.

Here is the result: