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.

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.

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.

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.

Image © Alexey Malkin - 123rom