Remove Duplicates Records in Excel

Duplicate records are one of the most common problems in Excel data. A customer may be entered twice, an invoice may be pasted again, or two files may be combined with overlapping rows. Duplicates give wrong totals and wrong counts, so they must be removed before analysis.

This article explains how to remove duplicates in Excel with four methods: the Remove Duplicates tool, the UNIQUE function, highlighting duplicates with conditional formatting, and flagging them with COUNTIF.

Sample Data Used in This Article

All examples use the following customer table in cells A1:C7.

Rows 4 and 6 are exact copies of rows 2 and 3, so they are duplicates.

Method 1: Remove Duplicates Tool

This is the fastest method, and it deletes the duplicate rows from the sheet.

Steps:

1. Select the data range A1:C7 (or click any cell inside the data).
2. Go to the Data tab and click Remove Duplicates in the Data Tools group.
3. Keep My data has headers checked.
4. Select the columns Excel should compare, and click OK.

Output:

Excel shows this message: 2 duplicate values found and removed; 4 unique values remain.

Explanation:

Excel compares the rows using the columns you selected. When all three columns are selected, a row is removed only if every value matches an earlier row. Excel always keeps the first occurrence and deletes the later ones.

Removing Duplicates Based on One Column

If you tick only Customer ID in the dialog box, Excel treats any repeated ID as a duplicate, even if the name or city is different. This is useful when the ID must be unique, such as an employee ID or invoice number.

Important: This tool permanently deletes rows. Copy the data to another sheet first, or press Ctrl + Z immediately if the result is wrong.

Method 2: UNIQUE Function

The UNIQUE() function returns a list of unique rows without touching the original data. It is available in Microsoft 365, Excel 2021, and Excel for the web.

Example:

=UNIQUE(A2:C7)

Output:

Explanation:

The formula is entered in one cell, and the result spills into the cells below and to the right. The original table stays unchanged, which makes this method safe. If the source data changes, the unique list updates automatically. To get unique values from one column only, use =UNIQUE(A2:A7), which returns 101, 102, 103, 104.

Method 3: Highlight Duplicates with Conditional Formatting

Use this method when you want to review duplicates before deleting them.

Steps:

1. Select the column to check, for example A2:A7.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a colour and click OK.

Output:

All cells containing 101 and 102 are highlighted, because each of these values appears twice. The values 103 and 104 are not highlighted.

Explanation:

Conditional formatting highlights every occurrence of a repeated value, including the first one. It only marks the cells, so you can sort or filter by colour and decide what to delete.

Method 4: Flag Duplicates with COUNTIF

COUNTIF() can mark only the second and later occurrences, which gives you a clear column to filter on.

Example:

Enter this formula in D2 and copy it down to D7:

=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique")

Output:

Explanation:

The range $A$2:A2 is fixed at the top and grows as the formula is copied down. In row 4, the range becomes A2:A4, and Excel counts how many times 101 appears in it. The count is 2, which is greater than 1, so the row is marked Duplicate. The first occurrence always has a count of 1, so it stays unique. You can now filter the Status column for "Duplicate" and delete those rows. 

To understand why the $ signs are needed, read about absolute and relative references in Excel Interview Questions.

Why Excel Sometimes Misses Duplicates

If two values look the same but Excel does not treat them as duplicates, check these causes:

• Extra spaces: "Amit" and "Amit " (with a trailing space) are different values. Clean the column with =TRIM(A2) first. SQL has the same function, explained in SQL String Functions.
• Numbers stored as text: The number 101 and the text "101" are not matched as the same value.
• Case: Remove Duplicates and COUNTIF ignore uppercase and lowercase, so "AMIT" and "amit" are treated as duplicates.

Important Points About Removing Duplicates in Excel

• The Remove Duplicates tool deletes rows permanently and keeps the first occurrence.
• Choose only the columns that define a duplicate; otherwise, rows with small differences will not be removed.
• UNIQUE() gives a clean list without changing the original data, but it needs Excel 2021 or Microsoft 365.
• Conditional formatting and COUNTIF help you review duplicates before deleting them.
• Clean extra spaces with TRIM() before searching for duplicates.
• In SQL, the same task is done with SQL DISTINCT.