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 - 123rf.com