How to Remove Duplicates in Excel

In Microsoft Office Excel, one of the major issues many users face is getting duplicate values and rows. Finding and removing duplicates is a basic task when you want to analyze your data. Excel offers several ways to filter unique values or find and remove duplicates in your data.

You wish to remove duplicate values in Excel, but don’t know how? then follow our step-by-step guide on how to find and remove duplicate values from your data in Excel.

Finding Duplicates Using Conditional Formatting

Sometimes, you want to find the duplicates but not remove them. In that case, you can find duplicates using the conditional formatting method. In our example, we want to find if any names of the ‘Representatives’ repeated. First, select the data set where you want to find the duplicates, you may select columns, rows, or even the entire worksheet.

Next, click on the ‘Conditional Formatting’ on the ‘Home’ tab and click ‘Highlight Cells Rules’. Then, select ‘Duplicate Values’.

In the ‘Duplicate Values’ dialog box, you can choose whether you want to find duplicates or unique values. Now, you are looking for duplicates, so choose ‘Duplicate’ and in the drop-down menu next to ‘values with’, and specify the format in which you want to highlight the duplicate values.

This option will highlight all the duplicate values in the column. You can now delete the duplicates values if you choose to.

If you want to remove the highlights, select the highlighted range of cells. Again go to the ‘Conditional Formatting’ drop-down list in the ‘Home’ Tab. Click ‘Clear Rules’ and then Select ‘Clear Rules from selected cells’. Now, the highlighted format will be gone.

Removing Duplicates using Remove Duplicates feature

With the help of the Remove Duplicates feature, you can immediately find and remove duplicates from rows or columns.

First, select the data set. You can select range cells that have duplicate values you want to remove or you can click any cell inside the data set.

Go to the ‘Data’ tab in the Excel Ribbon. In the ‘Data Tools group’ click on ‘Remove Duplicates’.

A ‘Remove Duplicates’ dialog box will appear containing your column headers, select the columns that have duplicates values you want to remove. Check the boxes of one or more columns that contain possible duplicates and click ‘OK’. You may choose all the headers if you want but in this case, we are removing duplicates in Rep names and their Region.

A pop-up will appear to inform you of the number of duplicate values that were found and removed and how many unique values remain.

Now all duplicate row values from the selected columns were deleted, and you have only the unique values. This feature can be used to delete duplicates in entire rows or partially matching data.

Removing Duplicates Using Advanced Filters in Excel

Another method to remove duplicate values in Excel is Advanced Filters in Excel. First, select the data set in Excel.

Go to the ‘Data’ tab and click ‘Advanced’ in the ‘Sort & Filter’ group.

In the ‘Advanced Filter’ dialog box, select ‘Filter the list, in-place’ under Action and check the ‘Unique records only’ checkbox, and click ‘OK’.

This will remove the duplicate rows and leaving only the unique records behind.

If you want to copy all the unique records from the selected range to the new location, choose the second option, ‘copy to another location’, and add cell references in the ‘Copy to’ box.

In this example, we want to find the unique values from the cell range B1:C12 and copy them to cell F1:G12. Add ‘$’ before the column letter and row number to specify the cell location.