Remove Duplicates Button Table Tools Design Tab Tools Group

Excel has many applications, like keeping track of inventory, maintaining a mailing list, making sales reports, and many others.

As the database grows, one main issue many users encounter is getting duplicate values and rows. This can make your calculations inaccurate and will make people question your competency.

You could end up giving a summary report with duplicate values or even mailing a letter to the same person twice. So you'll need to find and remove the duplicates to avoid these small mistakes that could have serious implications.

Since this problem happens a lot, there is a dedicated command on a ribbon that makes it easy to deal with duplicates. This is the case with recent versions of Microsoft Office suite, such as excel 2007 up to 2016.

Select the table you'll work on

First, you need to select the cells that are targeted, as the tool can be used to remove duplicates in entire rows or partially matching records.

You can do this by selecting the table and then pressing Ctrl + A. You should make sure the original file is saved as the process deletes the dupes permanently.

Click on the data tab at the top of the screen

Once you have selected the range, check the top of the screen and click the data tab. The different commands will be shown, and you should then check for 'remove duplicates' and click on it.

A small dialogue box will pop up on the screen. The first row is automatically selected as the 'my data has header' is ticked. If there is no header and the data starts at the 1^st row, deselect that option.

Press the OK button to remove duplicates

The entire table is now selected, and thus you should go ahead and press the OK button. This will delete all duplicates. The deletion details appear on the screen with unique values remaining and the number of duplicate entries removed shown.

However, if you want to remove partial duplicates based on specific columns, you should select them, leaving the rest. If the table contains many columns, it is best to unselect all and then just select those that need dupes removal.

Once done, you should click OK. Then the duplicated information will be removed, and the details will be shown on the screen.

Option 2: Using advanced filters in Excel

The advanced filter icon helps you to identify and delete duplicates in Excel. This can be used in the latest Microsoft Office suite and the 2003 version. You need to open your Excel spreadsheet and select all by clicking Ctrl + A.

You should then click on the Data tab at the top of the screen where different sections appear beneath it. Look for the sort & filter section and click on the advanced button.

A dialogue box will pop up on the screen where you can either select 'copy to another location' or 'filter the list in-place.' The latter hides all rows with duplicates while the former generates a copy of the records.

Adjust the range of data under the "list range"

In the dialogue box, there is a list range field with data populated by Excel. If you want to change the range, you can do it by adjusting it under the 'list range.'

Leaving the criteria range blank and copying to the field is only useful if you had chosen to copy to another place. Otherwise, leave it blank for the 'filter the list in-place' option.

Tick the "unique records only" box

Under the same dialogue box, there is a field labeled "unique records only" - tick that box. This tells Excel to filter out duplicates while retaining unique entries.

Click OK to remove duplicates

After you've followed this process, you can click the OK button to get rid of the duplicates. The document then contains duplicate data except for the ones which have been removed.

As the process assumes there are headers in the document, if the 1^st row contains a duplicate, it will not be removed. Delete it manually if it is not a header.

When you're using the advanced filter process, you can only remove duplicates in the entire table as there is no choice of doing it partially. However, you can remove duplicates and, at the same time, create a copy of the data.

Apart from the in-built duplicate removers, you can use add-ons such as Ablebits duplicate remover to get rid of dupes. The tool is multi-purpose and can perform other functions as well. It works on all operating systems, and all Excel versions. Here we will focus on one way of using the tool that takes just 2 mouse clicks.

Select the cell in the table of interest

Check the table where you need to dedupe records, and on the Ablebits data click "dedupe table". This selects the the entire table, and the dialogue box will open with all columns chosen automatically.

Select delete duplicates from the drop-down list under "select the action" field

On the lower-right, there is a drop-down list under the select action field. Choose the delete duplicate option and press OK. The duplicate values will be deleted except for 1^st occurrences.

Removing duplicates in key columns

The 2 mouse click process can be used to remove duplicates on specific rows under crucial columns. To do this, uncheck the other columns leaving the ones you want to dedupe checked. Follow two points above, and the duplicates will be removed.

The select action can be used for other operations such as copying duplicates to another location without deleting them. Use the drop-down menu to select accordingly so your Excel sheet is neat and without errors.

Final Word

Wiping duplicates on Excel is simple using the three options highlighted above. The process is easy, and with this guide you can do it with a few clicks to get the ultimate assignment help and work assistance from Excel.

When you're working in Excel you should always clean your data set, eliminating any unprofessional errors. If you come across any challenges, check that the data being processed is not subtotaled or outlined.

In such cases, you need to remove the subtotals and outline then use any of the methods above. The data will then be cleaned, making it much easier to work on them.

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started