How to Remove Duplicates in Excel

Duplicate rows in Excel are a common problem — they sneak in when data is imported from other systems, when multiple people contribute to the same file, or when lists get merged together. Left unchecked, duplicates skew your totals, inflate your counts, and make reports unreliable. Excel has a built-in tool that removes them in seconds, but there are also a few techniques worth knowing for finding and reviewing duplicates before you delete anything.

Back Up Your Data First

Before you remove anything, make a copy. The Remove Duplicates tool cannot be undone once you close and save the file — or even if you do quite a few other things after running it and then try to Ctrl+Z. The safest approach is to copy the entire sheet to a new tab before you start:

1. Right-click the sheet tab at the bottom of the screen.
2. Select Move or Copy.
3. Tick Create a copy and click OK.

This takes ten seconds and means you always have the original to refer back to.

The Quickest Method: Remove Duplicates Tool

Excel’s built-in Remove Duplicates tool is the fastest way to clean a dataset. Here is how to use it:

1. Click anywhere inside your data range.
2. Go to the Data tab on the ribbon.
3. Click Remove Duplicates in the Data Tools group.
4. A dialog box opens showing all the columns in your dataset.
5. Choose which columns should be used to define a duplicate (see below).
6. Click OK.

Excel will tell you how many duplicate rows were removed and how many unique rows remain.

Choosing Which Columns Define a Duplicate

This step is important. The dialog box shows all your columns with checkboxes. Excel only treats two rows as duplicates if the values in all the ticked columns match.

For example, if you have a customer list with columns for First Name, Last Name, and Email:

• Tick all three columns if you want to remove rows where all three match (safest option — avoids removing people with the same name but different emails).
• Tick only Email if you want to keep just one row per email address, regardless of name.
• Tick only First Name and Last Name if you want one row per person by name.

Think carefully about what “duplicate” actually means for your specific data before ticking or unticking columns.

Finding Duplicates Before Removing Them

If you want to review duplicates before deleting anything, there are two good approaches: a COUNTIF formula or conditional formatting.

Using COUNTIF to Flag Duplicates

Add a helper column next to your data. If your key column (the one you want to check for duplicates) is column A, put this formula in the first empty column:

=COUNTIF($A$2:$A$1000, A2)

Copy it down for all rows. This counts how many times each value appears in column A. A result of 1 means it is unique. A result of 2 or more means it appears multiple times.

To make this even clearer, wrap it in an IF:

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

Now you can filter the helper column to show only “Duplicate” rows and review them before deciding what to delete.

Using Conditional Formatting to Highlight Duplicates

For a visual approach without adding a helper column:

1. Select the column you want to check (e.g. column A).
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a highlight colour and click OK.

All duplicate values in that column will be highlighted. This lets you scan the data visually and decide which rows to keep.

Note: conditional formatting highlights all instances of a duplicate value, including the first one. So if “Smith” appears three times, all three are highlighted — not just the second and third.

Removing Duplicates From a Specific Column Only

The Remove Duplicates tool works on rows — it removes entire rows that are duplicates. If you only want to deduplicate a single column without touching the rest of the data, the tool is not the right approach.

In that case, a better option is to copy just that column to a new location, run Remove Duplicates on it there, or use the UNIQUE function (covered below). Alternatively, use the COUNTIF helper column approach to identify duplicates, then manually review and delete specific rows.

Dealing With Duplicates in Large Datasets

In a large dataset, it is worth filtering before reviewing. If you have used the COUNTIF helper column approach:

1. Click the filter dropdown on the helper column.
2. Filter to show only “Duplicate” rows.
3. Review the visible rows to check they are genuinely duplicates and not just similar entries.
4. Select and delete the rows you do not want to keep.
5. Clear the filter to see the remaining data.

This approach gives you control over which duplicate instance is kept (you can decide to keep the most recent, the most complete, or the first occurrence).

The UNIQUE Function (Excel 365)

If you have Excel 365 or Excel 2021, the UNIQUE function can extract a deduplicated list to a separate location without altering your original data at all:

=UNIQUE(A2:A100)

This spills a list of unique values into the cells below where you enter the formula. The original data in A2:A100 is untouched.

You can also use UNIQUE on multiple columns:

=UNIQUE(A2:C100)

This returns unique rows based on all three columns. Add a second argument to change behaviour — =UNIQUE(A2:A100, FALSE, TRUE) returns values that appear only once (not just the first instance of each value).

UNIQUE is particularly useful when you need a clean reference list for drop-downs or reports without modifying your raw data.

Common Gotcha: Extra Spaces Making Entries Look Different

One of the most common reasons Remove Duplicates misses obvious duplicates is extra spaces. If one cell contains “Smith” and another contains “Smith ” (with a trailing space), Excel treats them as different values — they look identical on screen but are not.

To fix this, clean your data with TRIM before deduplicating. TRIM removes leading and trailing spaces and collapses multiple spaces between words to a single space:

=TRIM(A2)

The easiest way to apply TRIM across a whole column is to add a helper column with the TRIM formula, copy the results, then paste as values back into the original column (Paste Special > Values). Once the spaces are removed, Remove Duplicates will work correctly.