Skip to main content

Remove duplicates in Excel (Easy step-by-step tutorial)

Duplicate values can make information less clear and more difficult to get an accurate assessment. In this tutorial, we'll explore four distinct methods to remove, select or hide duplicates, or simply view your data without duplicates in Excel.

Video: Remove duplicates in Excel

Remove duplicates with built-in functionality

Important Note: Before you remove duplicates, always make a copy of your worksheet so that you will have the original data if you accidentally delete something important.

Step 1: Select the cell range from which to remove duplicates

Select the cells that may have duplicate values: You can select a whole dataset; if your data is too large, you can click on any single cell inside the dataset, Excel will automatically select the dataset in the next step.

Step 2: Select Data > Remove Duplicates

Step 3: Select which columns to check for duplicates

In the pop-up dialog box, select which columns to check for duplicates, and then click on OK.

Note:
  • To delete duplicate rows with completely identical values across all columns, leave the checkboxes all selected as shown above.
  • To delete duplicate rows with the same values in certain columns, check those columns only.
  • If your data has column headers, check the My data has headers option, so that the header (first row) will not be considered for removing duplicates.

Step 4: View summary

A dialog box pops up showing how many duplicate values are found and removed, and the count of remaining unique values. Click OK.

Result

As you can see, the duplicate rows excluding their first appearances are removed.

Select duplicates with Kutools with more options

Kutools for Excel’s Select Duplicate & Unique Cells feature helps you select duplicates with more options - You could select duplicate rows (or cells) including or excluding the first appearances; or select unique rows (or cells) that appear exactly once or every distinct row or value. You could find case-sensitive duplicates; or mark duplicate values with a specific background or font color.

To select duplicates excluding their first appearances, please select the cell range that contains duplicates, and then click Kutools > Select > Select Duplicate & Unique Cells, and do as follows:

  1. Select Each row or Single cell according to how you want to check for duplicates.
  2. Select Duplicates (Except 1st one) in Rule section.
Note:

Hide duplicates with Advanced Filter

If you’d rather have duplicate values hidden instead of removed, you can use Advanced Filter to filter for unique values.

Step 1: Select the cell range from which to filter out duplicates

Select the cells that may have duplicate values: You can select a whole dataset; if your data is too large, you can click on any single cell inside the dataset, Excel will automatically select the dataset in the next step.

Step 2: Select Data > Advanced

Step 3: Filter for unique values

In the pop-up dialog box, please do as follows:

  1. In the Action section, select Filter the list, in-place.
  2. See if the cell range in the List range box is the dataset you will filter. If not, click the range-selecting icon to select your dataset.
  3. Check the Unique records only option and then click OK.

Result

You would see the row numbers where the filter is applied turned blue, which indicates that the duplicate rows (pointed by green arrows) are filtered out.

Note:
  • You can also get rid of duplicates by copying only unique values (including the first duplicate occurrences) to another worksheet or workbook by selecting Copy to another location in the Advanced Filter dialog box in Step 3, and then specifying the first cell of the destination range where to paste the filter results in the Copy to box.
  • You can click on Clear in Sort & Filter group on Data tab to clear the filter and reverse the data to its original look.

Remove duplicates with Excel formulas

In this section, we will talk about two approaches with Excel formulas to help you either delete duplicates easily with IF and COUNTIF functions, or view your data without duplicates with UNIQUE, an awesome new Excel function.

Delete duplicates with IF and COUNTIF functions

Suppose your data that may have duplicates is in range A2:C10, you can use an Excel formula with IF and COUNTIF to filter for unique values and thus to remove duplicates.

Step 1: Combine columns' values

Combine the cells on the same row with the concatenation operator (&). In our example, you should enter the below formula in cell D2, and then copy the formula to below cells.

=A2&B2&C2

Note:
  • In the formula, A2, B2 and C2 are cells in the first row to check for duplicates. You should change them to the actual cells in first row of your data.
  • If your data only contains one column, please skip this step.

Step 2: Get duplicate marks

Mark a row as duplicates by entering the one of the formulas below in cell E2 according to your needs, and copy the formula to below cells. (Here I will use the first formula to mark duplicates excluding first occurrences.)

  • Duplicates excluding first occurrences
  • =IF(COUNTIF($D$2:D2,D2)>1,"Duplicates","")
  • Duplicates including first occurrences
  • =IF(COUNTIF($D$2:$D$10,D2)>1,"Duplicates","")

Note: In the above formulas, D2 is the first cell in the Conbine column, and D10 is the last cell in the Conbine column. You should keep the dollar signs ($) the way they are in the formula.

Step 3: Filter for duplicates

  1. Select any cell within the dataset, and click Filter on the Data tab.
  2. Click the arrow at the top of the Mark column, and select Duplicates to keep only duplicate values visible on the screen.

Step 4: Delete duplicates

Select all the duplicate rows, then right-click and select Delete Row from the menu.

Result

On the Data tab, click on the Filter button again to remove the filter. You will see the table now contains only unique values with duplicate rows deleted.

Note: If you don't want to delete duplicates but just filter out duplicate rows, instead of selecting Duplicates in the step 3, you can select (Blanks) in the filter box. So that you can make the duplicate values invisible without deleting them.
View without duplicates with UNIQUE function (Excel 365/2021)

If you are Microsoft 365 or Excel 2021 user, and you don’t want to remove duplicates from original data, nor apply a filter, but just view the data without duplicates, Excel’s new UNIQUE function can do you good.

Step 1: Apply the UNIQUE formula

In a blank cell, type in the below formula (Note that you should replace A1:C10 with your actual cell range that may have duplicates), and then press Enter.

=UNIQUE(A1:C10)

Result

As you can see, the duplicate rows are removed from the formula result in no time.

Note:
  • #SPILL errors are returned when the spill range (E1:G8 in this case) for the formula isn't blank.
  • With the formula, you can easily view your dataset without duplicates. However, you could not edit the formula result. In order to edit the cell value or formats, you can use Kutools’ To Actual feature, which converts formula results to constant values in one click.