The fastest way to remove duplicates in Excel

Excel has a built-in tool that finds and removes duplicate rows in seconds. Select the data you want to check, go to the Data tab, click Remove Duplicates, and choose which columns to compare. Excel will delete the extra rows and show you how many it removed. This works for any size dataset — from a few hundred rows to tens of thousands.

The tool compares entire rows by default, so it only removes a row if every column matches another row exactly. If you want to find duplicates based on just one column (like customer ID or email), you can uncheck the columns you don't care about before running the tool.

One important thing: the Remove Duplicates tool changes your spreadsheet permanently. It does not undo easily if you make a mistake, so save a copy of your file first or use Ctrl+Z when ready if something goes wrong.

Key Takeaways

  • The Remove Duplicates tool is on the Data tab and works on any selection of rows and columns.
  • Excel keeps the first occurrence of each duplicate and deletes the rest, so the order of your data matters.
  • You can choose which columns to compare — checking only one column finds duplicates based on that column alone.
  • Save your file before using Remove Duplicates, because the change cannot be undone with a straightforward undo if you wait too long.
  • If you need to keep both copies and just mark them, use conditional formatting or a helper column instead of removing them.

How to use the Remove Duplicates tool step by step

First, select all the data you want to check. Click the top-left cell of your data, then hold Shift and click the bottom-right cell. If your data has headers (like "Name" or "Email" in the first row), include those in your selection — Excel will recognize them and keep them in place.

Go to the Data tab at the top of the ribbon. Look for the Remove Duplicates button. In some versions of Excel it is in the Data Tools group; in others it is under a menu called Data Tools. Click it.

A dialog box will open showing all the columns in your selection. By default, all columns are checked. If you want to find duplicates based on only certain columns — for example, only the Email column — uncheck the columns you do not need. Then click OK. Excel will delete the duplicate rows and tell you how many rows it removed and how many remain.

When to use conditional formatting instead of removing duplicates

Sometimes you do not want to delete duplicates — you just want to see where they are. Use conditional formatting to highlight them instead. Select your data, go to the Home tab, click Conditional Formatting, then choose Highlight Cell Rules and Duplicate Values. Excel will color every duplicate cell so you can see the pattern without losing any data.

This is useful when you need to investigate why duplicates exist before deciding what to do with them. For example, if you have a customer list with duplicate names, you might want to check whether they are actually the same person (same address, same phone number) or just people with the same name. Highlighting lets you do that investigation before you delete anything.

Using a helper column to identify duplicates

If you want more control over which duplicates to keep, create a helper column with a formula. In a blank column next to your data, use the COUNTIF function to count how many times each value appears. For example, if your names are in column A, type this formula in column B: =COUNTIF($A$1:$A$100,A1). Copy the formula down for every row. Any row with a count higher than 1 is a duplicate.

Once you have identified the duplicates this way, you can sort by the helper column to group them together, then manually delete the ones you do not need. This gives you the chance to decide which copy to keep — for example, keeping the most recent entry instead of the first one. After you delete the rows you want gone, delete the helper column.

Removing duplicates based on one column only

If you have a spreadsheet where each row should have a unique ID, email, or product code, you can remove duplicates based on just that column. Select all your data including headers. Open the Remove Duplicates dialog from the Data tab. Uncheck every column except the one you want to compare — for example, uncheck everything except Email. Click OK.

Excel will keep the first row with each unique email address and delete any rows where the email repeats. All the other columns in those deleted rows will be gone too, so make sure that is what you want. If you need to preserve information from the duplicate rows, use a helper column or conditional formatting instead.

What happens when your data has headers

If your first row contains column names like "Customer Name", "Email", or "Phone Number", include it in your selection when you run Remove Duplicates. Excel will automatically detect headers and will not treat them as data. The header row will stay in place no matter what.

If Excel does not recognize your headers automatically, the dialog box has a checkbox that says "My data has headers". Check it. This tells Excel to ignore the first row when looking for duplicates and to keep it no matter what.

Handling duplicates across multiple sheets

The Remove Duplicates tool only works on one sheet at a time. If you have data spread across multiple sheets and you need to find duplicates across all of them, you have two options. First, you can copy all the data from each sheet into one temporary sheet, run Remove Duplicates on that combined sheet, then copy the cleaned data back. Second, you can use a helper column with COUNTIF that references cells on other sheets — the formula would look like =COUNTIF(Sheet1!$A:$A,A1)+COUNTIF(Sheet2!$A:$A,A1) — to count occurrences across sheets.

The first method is simpler for most people. Create a new sheet, paste all your data into it, clean it up, then delete the temporary sheet when you are done.

Frequently Asked Questions

Can I undo Remove Duplicates after I close the file?

No. Once you close the file, the undo history is gone and you cannot recover the deleted rows. This is why saving a backup copy before using Remove Duplicates is important. If you realize you made a mistake while the file is still open, press Ctrl+Z when ready to undo.

Does Remove Duplicates work on partial matches or only exact matches?

Only exact matches. If one row says "John Smith" and another says "john smith" (different capitalization), Excel treats them as different and will not remove either one. If you need to catch these kinds of duplicates, clean up the capitalization first using the UPPER or LOWER function, or use Find and Replace.

What if I only want to remove duplicates from certain columns, not the whole row?

Uncheck the columns you do not care about in the Remove Duplicates dialog. For example, if you want to find duplicate emails but keep rows with the same email if the phone number is different, uncheck the Phone column before clicking OK. Excel will then compare only the columns you left checked.

Can I remove duplicates and keep the last occurrence instead of the first?

The Remove Duplicates tool always keeps the first occurrence. If you need to keep the last one instead, sort your data in reverse order first, run Remove Duplicates, then sort back to the original order. Or use a helper column to manually identify which rows to delete.

Does Remove Duplicates work on filtered data?

No. Remove Duplicates works on all rows in your selection, including hidden ones. If you have filtered your data to show only certain rows, remove the filter first, then select and clean all the data. Otherwise you may accidentally delete rows you cannot see.