How to Remove Duplicates in Microsoft Excel to Clean up Data


Share post:

If you are someone who is here to know how to “Remove Duplicates in Microsoft Excel“, then this article is for you.

Depending on the goal and the type of Excel table, you may not expect to have only unique values, but some may be repeated. However, sometimes duplicates are entered by mistake, especially when multiple people work on the same document or when trying to unify multiple data sets. In such cases, it is not necessary to delete the affected rows or cells one by one, since there is a function to remove duplicates in Excel tables, a tool that allows you to delete repeated entries automatically.

Deleting duplicates with the built-in function is not the only 
option, there is also the possibility to filter and display 
duplicates first. To know how to do it, don't miss our article 
on how to show duplicates in Excel.
Microsoft Excel

In the sections that follow, we tell you where this function is and how to use it to remove duplicates. You will be able to see the steps to follow whether the duplicates you are looking for have to match all the values in the same entry, or if it is about looking for only single values that are repeated.

Excel with Microsoft 365 and IONOS

Use Excel to organize your data and save time, included in every Microsoft 365 pack!

Tutorial: how to remove duplicates in Excel?

Deleting duplicates of Excel tables does not require extraordinary measures. As we have already mentioned, the program can show the duplicates, without having to eliminate them, but you can also do without preliminary caution and delete them directly. In both cases there are two possibilities to define what a duplicate is:

  1. A duplicate exists if all the values in an entry match.
  2. A duplicate exists if X values of an entry match.

Remove duplicates from Excel where all values ​​are identical

The most frequent use of the function to eliminate duplicates is probably the need to free the table from completely identical entries. For the Microsoft application, this means, therefore, that you should look for the rows that have exactly the same value in each column. If such a match exists, all entries except one are deleted. This is what happens with rows 2, 6, and 9 in the table shown as an example below:

Identical entries have been manually marked in this sample table to highlight them.

At this point, for Excel to delete duplicates, first just select any cell, either with the right or left mouse button.

Next, select the ” Data ” menu and, in the ” Data Tools ” gathering, click on ” Remove Duplicates “.

Menu "Data" in Excel 2016

Function to eliminate duplicates in Excel in the toolbar of the “Data” tab.

In the next dialog box, check the ” Select all ” option and then click ” OK “:

Dialog box to remove duplicates in Excel

By activating the “Select all” option, Excel will only delete duplicates whose values ​​match in all columns.

In this way, Excel will attempt to delete duplicates that are completely identical and, after doing so, will display the corresponding warning. In our example table, the removal process has worked properly and Excel has removed two of the three identical rows :

Microsoft Excel 2016: duplicate removal notice

A message will appear whether Excel has removed repeated values ​​or no matches have been found.

Remove duplicate excel entries containing only identical loose values

The most frequent use of the function at hand is without a doubt the elimination of totally identical duplicates. There are also spreadsheets in which the columns should be considered duplicates even if the different entries coincide only in single values. This is the situation, for instance, of tables that contain segments with discretionary fields or with obsolete data that is not, at this point vital.

Thus, in our spreadsheet example, if the values ​​in the Priority level and Year columns were irrelevant, even after discarding completely identical entries, we would still have duplicates in rows 7 and 9 :

Excel 2016: sample entry with duplicates

Identical values ​​in columns A and B (manually highlighted).

The steps to follow if we want Excel to also delete these duplicates do not differ from those we have seen in the previous section:

  1. Select a cell with the right or left mouse button.
  2. Open the ” Data ” menu.
  3. Go to ” Data Tools ” and click ” Remove duplicates “

In the Remove Duplicates dialog box, uncheck the boxes for columns that are not to be considered in duplicate search: in our example, the Priority level and Year columns. Then save the changes by accepting.

After this process, Excel will also display a notice stating how many duplicates have been removed.


Related articles

How to Check Starbucks Gift Card Balance (in 2024)?

Do you have a Starbucks gift card burning a hole in your pocket or wallet? Before you head...

Which statement describes an enterprise Platform?

Question: Which statement describes an enterprise platform? Options: A) It is a platform that helps people connect and share content...

What is the factorial of 100?

Question: The Other Day I was asked What is the factorial of 100? Options: a) 9.3326215443944E+157 b) 8.32535543555345+143 c) 10.943t578057945+234 d) None of...

What is Compiling?

A few decades ago, using a computer was not as easy as it is today, where we have everything ready,...