Depending on the purpose and type of Excel table, you may not expect to have only unique values, but some will repeat. However, duplicates are sometimes introduced by mistake , especially when multiple people are working 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 the duplicates with the built-in function is not the only option, there is also the possibility to filter and show the duplicates first . To find out how to do it, don't miss our article on how to show duplicates in Excel.
In the sections that follow, we will 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 coincide with all the values in the same entry, or if it is a matter of looking for only single values that are repeated..
To delete duplicates from Excel tables, no extraordinary measures are necessary . As we have already mentioned, the program can show the duplicates, without the need to delete them, but it can also dispense with preliminary precaution and delete them directly. In both cases there are two possibilities to define what a duplicate is:
Probably the most frequent use of the function to eliminate duplicates is the need to free the table from totally identical entries. For the Microsoft application this means, therefore, that you must find 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:
At this point, in order for Excel to delete duplicates, first just select any cell , either with the right or left mouse button.
Then select the menu? Data ? and, in the group? Data tools? , click on? Remove duplicates ? (Remove Duplicates)..
In the next dialog box, check the option? Select all ? and then click? Accept ?:
In this way, Excel will try to delete duplicates that are completely identical and, after doing so, will display the corresponding warning . In our example table, the delete process worked properly and Excel has deleted two of the three identical rows :
While the most frequent use of the function at hand is undoubtedly the elimination of totally identical duplicates, there are also spreadsheets in which the columns must be considered duplicates even if the different entries only coincide in individual values . This is the case, for example, of tables that contain columns with optional fields or with outdated information that is no longer required.
So, in our spreadsheet example, if the values in the Priority level and Year columns were irrelevant, even after discarding the totally identical entries, we would still have duplicates in rows 7 and 9 :
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:
In the dialog box for removing duplicates, uncheck the boxes for the columns that should not be taken into account when searching for duplicates: in our example, the Priority level and Year columns . Then save the changes accepting.
After this process, Excel will also display a notice indicating how many duplicates have been removed.
You can select as many columns as you want to define the search for duplicates, you don't have to limit yourself to two columns as in our example.