How to delete duplicates in Excel

Quick links

How to eliminate duplicates in Excel

How to find duplicates in Excel, delete duplicate rows in Excel, and eliminate duplicates in Excel are all the same thing, with one simple function included in Excel to perform data deduplication.

Below, we will also propose another solution with an Excel remove duplicates formula, which can also be used to identify duplicates in Excel and hide duplicates in Excel, without necessarily having to eliminate duplicates in Excel.

One quick solution with a simple button to delete all duplicates, and one solution using functions and formulas, a little more complicated, to identify them.

How to get rid of duplicates in Excel

Ordering a list and removing duplicates is a common and useful operation, when working with data lists : email addresses, invoice numbers, values, ...

One solution to quickly process large sets of data can be to use Notepad++.

Another possibility is using Excel 2013 from Microsoft Office 2013 suite.

How to delete duplicates in Excel : How to delete duplicates in Excel - unordered data set
How to delete duplicates in Excel - unordered data set
How to delete duplicates in Excel : Excel remove duplicates from list - unordered data set selected
Excel remove duplicates from list - unordered data set selected

How to remove duplicates in Excel

Starting with your set of data, select the column(s) in which you want to remove duplicates.

Then, from the DATA menu, select the "Remove duplicate" options.

How to delete duplicates in Excel : How to remove duplicates in Excel
How to remove duplicates in Excel

In the popup, select if your data has header or not (meaning, a first line that will not be considered in the operation), and the columns you want to consider, and click Ok. This is how to remove duplicate rows in Excel !

It even works with two columns, three columns, or more. All you have to do, is select as many columns containing data as necessary, and this function will delete duplicates. This is how to compare two columns to find duplicates in Excel, and works with more columns if necessary.

How to delete duplicates in Excel : Excel data remove duplicates popup options
Excel data remove duplicates popup options

You should get a confirmation with the amount of duplicates deleted and unique values remaining.

How to delete duplicates in Excel : Remove duplicates in Excel 2013 success message
Remove duplicates in Excel 2013 success message

Finally, you might want to sort the data, now that the duplicates have been eliminated, to have a cleaner list.

Keeping your selection, in the same DATA menu, select ascending or descending sort, and voilà !

How to delete duplicates in Excel : Excel data sort menu
Excel data sort menu

Sort either from smallest to largest, or from largest to smalles, depending on the data set, and the need for it.

How to delete duplicates in Excel : Excel data sorted without duplicates
Excel data sorted without duplicates

Hide duplicates in Excel

To hide the duplicates in Excel, and have a data deduplication that will keep the whole data instead of deleting the duplicates, do the following. It is also another way how to identify duplicates in Excel with formulas, instead of using the builtin function. It will also find unique values in Excel multiple columns, in only a few quick and easy steps :

- Add a new column in the data set, either before or after the data,

- Order the data by the column. If two columns or more are necessary for the duplicate check, create another column with the concatenation of these two or more columns, and order by that concatenation,

- In the empty column, add the following formula (if column is A and first empty cell is A2, and B2 is the first cell to check for duplicate).

Formula to find duplicates in Excel

=if(A2=B3;"Duplicate";"Original")

Highlight duplicate rows in Excel

Now, simply select all rows with the value Duplicate in column A, and highlight them !

Identify duplicates in Excel

All the duplicates are now identified, and you can do what you want with them : highlight duplicates, hide duplicates, or see how to delete duplicate rows in Excel, ...

Excel remove duplicates from column

To remove the duplicates, or hide duplicates in Excel, simply filter on column A value equal to Duplicate, and perform the corresponding action. This is how to delete duplicates in Excel 2013, to perform Excel remove duplicates from a list, but also how to remove duplicates in Excel 2013.

Count duplicates in Excel

To count the duplicates, in the current sheet, add an empty column at the right of the column with the comparison function, filter on column A equal Original, and add the following formula in column B. You should still have the key in column C :

=countif(C:C;A2)

And extend the formula to the whole column. It will therefore display the count of duplicates for each unique value !

Similar articles

Comments (0)

Leave a comment