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.
Advice 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?
Table of Contents
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:
- A duplicate exists if all the values in an entry match.
- 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 “.
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 “:
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 :
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 :
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:
- Select a cell with the right or left mouse button.
- Open the ” Data ” menu.
- 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.
Deactivate the option “My data has headers” only if your Excel table does not have a row of headers, otherwise the top row will also be taken into account when applying the duplicate removal function.After this process, Excel will also display a notice stating how many duplicates have been removed.
