Delete duplicates in Excel: column, formula

Delete duplicates in Excel: column, formula

When you want to clean up data in your Excel sheets, you should first check if there are any duplicates and if so, delete them. Sometimes it's not obvious, but it's actually quite easy to do. In this article, you will find two ways to delete duplicate data from your Excel sheet.

How to find duplicate data in Excel?

If you want to check for any duplicate data in your Excel file before deleting it, follow this procedure:

Select and highlight all the data you want to search for duplicates (don’t include headers), then select the Home tab and in the Styles section choose Conditional Formatting > Highlight Cells Rules > Duplicate Values.

duplicate values excel
© Excel

In the new pop-up window select duplicate values in the selected range and choose the colour you’d like to use to highlight the duplicates. Click OK. Now cells that have the duplicates will be highlighted with a colour you have chosen.

How to delete duplicates in Excel?

To remove duplicates in Excel sheets follow these simple steps: Select the rows from which you’d like to remove duplicate data, go to Data tab and select Remove Duplicates.

In the dialog box choose the columns from which you wish to delete duplicates and click on Remove Duplicates.

remove duplicates excel
© Excel

How to delete duplicate data with filters?

The second way to delete duplicates is to use a filter for unique values. To do so, select any cell or specific data to be filtered. Next, go to Data and in the Sort & Filter section select Advanced.

In the new dialog box choose Unique records only. If you’d like to apply changes in this document, select Filter the list, in-place, otherwise, you can copy and save it to another location by selecting the second option. Click OK and the duplicates will be removed instantly.

unique records only excel
© Excel

How to select empty rows in Excel?

  • If your Excel table contains empty rows/cells, here's a simple tip to select them:
  • First select the concerned cells and press the F5 key.
  • Click on the Special button.
  • In the Go to Special dialog, select Blanks.
  • Click on the OK button an all the empty rows/cells will be selected.
  • From there you can delete or edit them.
Do you need more help with excel? Check out our forum!