How to remove duplicates in Excel

Duplicate data is sometimes useful, but it often just makes the data harder to understand. Finding, highlighting, and reviewing before removal is better than removing all the duplicates straightway. Remove Duplicates in Excel, based on the entire table, click on the button present on the top left corner of the data preview.

Things to know

How to find and delete duplicates in Excel ?

Select the cells you saw some duplicates.
Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Pick the formatting to apply to the duplicate values and click OK.
Select the range of cells you want to remove duplicates.
Click Data > Remove Duplicates > Columns, check or uncheck the columns where you want to remove the duplicates.

OR

Click on any specific range in the dataset from which you want to remove duplicates.
Locate the ‘Remove Duplicates’ and select it DATA tab > Data Tools section > Remove Duplicates.
Select the columns you want to compare and check for duplicate data.
Excel will now delete the duplicate rows and display a dialog box.
The duplicate records are removed.

Conditionally Format Unique or Duplicate Values :

Select the cells you want to check for duplicate values.
Select Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Click on the Duplicate Values option.
Select “values with”, pick the formatting for the duplicate values and click OK.

Manual steps to remove duplicates in Excel :

Select the cells with duplicate values and locate the ‘Remove Duplicates’ option and select it.
Click Data tab > Data Tools section > Remove Duplicates.
Now, check or uncheck the columns and click OK.

OR

Click any single cell and Data Tools group > Remove Duplicates.
Leave all check boxes checked and click OK.
For example, remove rows with the same Last Name and Country.
Check Last Name and Country and click OK.
On the Data tab, in the Sort & Filter group, click Advanced.
Click Copy to another location.
Click in the List range box and select the range A1:A17.
Click in the Copy to box and select cell F1.
Check Unique records only and click OK.
Finally, you can use conditional formatting in Excel to highlight duplicate values.

Using VBA :

Press Alt +F11 keys simultaneously to open Microsoft Visual Basic for Applications window.
Click Insert > Module, and paste below VBA.
Press F5 key or click Run to execute the VBA, and the duplicates in list in column A have been removed.