+3 votes
46 views
in Sell ​​on the Internet by (76.4k points)
reopened
Remove duplicates in Excel: how it works

1 Answer

+4 votes
by (506k points)
edited
 
Best answer

image

Remove duplicates in Excel: how it works

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 ..

advice

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..

Index
  1. Tutorial: how to remove duplicates in Excel?
    1. Remove duplicates from Excel where all values ​​are identical
    2. Remove duplicate Excel entries that only contain identical single values

Tutorial: how to remove duplicates in Excel?

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:

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

Remove duplicates from Excel where all values ​​are identical

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:

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

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)..

image
Function to eliminate duplicates in Excel on the toolbar of the "Data" tab.

In the next dialog box, check the option? Select all ? and then click? Accept ?:

image
Activating the option? Select all ?, Excel will only delete duplicates whose values ​​match in all columns.

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 :

image
A message will appear whether Excel has removed repeating values ​​or if no matches were found.

Remove duplicate Excel entries that only contain identical single values

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 :

image
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 left or right mouse button.
  2. Open the menu ? Data ?.
  3. Go to? Data tools ? and click? Remove duplicates ?

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.

image
Deactivate the option? My data has headers? (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 deduplication function.

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

Note

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.


...