How to delete duplicates in Excel

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

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

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.

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.

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

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à !

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

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

Highlight duplicate rows in Excel

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

Notepad++ remove duplicate lines an...
Notepad++ remove duplicate lines and sort

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 :

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

Complete 2019 Excel for Beginners in video

Comments (0)

Leave a comment