Skip to main content

How to Remove Duplicates in Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

how-to-remove-duplicates-in-excel-2016

Why Remove Duplicates in Excel?

Removing duplicate records one by one from an Excel worksheet is a repetitive task for anyone. This is especially true for a user working with thousands of records. We are in luck that Excel has an automated tool to find duplicates for attributes of a record and allows you to remove those records to clean up the data.

Below I cover how to use the remove duplicates tool and cover how to remove duplicates by filtering data.

Remove Duplicates Button

The above illustration shows the many spreadsheet data tools available in the data tools section of the data tab. The remove duplicates tool stands out with a red square around it depicting a column with an x on it.

The above illustration shows the many spreadsheet data tools available in the data tools section of the data tab. The remove duplicates tool stands out with a red square around it depicting a column with an x on it.

How the Remove Duplicates Option Works

With the remove duplicates tool, Excel essentially allows you to choose the columns where you would like to remove duplicates. During this process, all records will be removed that contained duplicate data.

Tech Companies Remove Duplicates Example

Here is a simple example to see how the remove duplicates function works. The illustration below is a list of 248 tech companies and can be downloaded here. I would like to remove all the duplicates from the state attribute. This will allow me to show which states the tech companies listed are being drawn from.

The data in the spreadsheet is called Tech248 Companies and can be found in the reference list at the end of this article.

The data in the spreadsheet is called Tech248 Companies and can be found in the reference list at the end of this article.

To remove duplicates in the state column, we would first have to click on the data tab, then click on the remove duplicates icon in the data tools section shown below.

Data → Remove Duplicates

The remove duplicates tool is found in the section labeled data tools and located under the data tab in the Excel ribbon.

The remove duplicates tool is found in the section labeled data tools and located under the data tab in the Excel ribbon.

The remove duplicates window appears. At this point, you would click on the unselect all button. This will clear all of the checkboxes to allow you to customize your selection. Since I only want to remove duplicates in the state column I would select state and click OK.

Unselect All → Column(s)

The remove duplicates tool requires you to select which columns will be searched for duplicates.

The remove duplicates tool requires you to select which columns will be searched for duplicates.

The tool will want you to acknowledge the number of records that have been removed as a result of the process.

Verifying Duplicate Values Were Removed

After running the remove duplicates tool on columns of your worksheet, you will only end up with records that have unique values for the chosen attributes.

After running the remove duplicates tool on columns of your worksheet, you will only end up with records that have unique values for the chosen attributes.

Below is the result of removing duplicates from the state column where unique characters remain.

The above illustration is the result of using the remove duplicates feature on the state attribute.

The above illustration is the result of using the remove duplicates feature on the state attribute.

If you would like to keep your original data, I would suggest copying the newly cleaned data set over to another workbook followed by exiting the workbook where duplicates were removed without saving the changes so those original records can be retained.

An Alternative to Removing Duplicates

An alternative to the removing duplicates tool can be used when the data is not overwhelming. Follow these steps to filter out duplicates:

  1. Add a column and name it Unique.
  2. Select the top row & click Sort & Filter → Filter.
  3. Go through the records and mark a 1 in the Unique column for the records with duplicate values.
  4. Select the Unique header, deselect the blanks check box, and click on OK.

This option allows you to keep each record and sort out duplicates by adding this binomial attribute to help you sort through the data.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2021 Joshua Crowder