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 range you want to clean, go to the Data tab, click Remove Duplicates, and choose which columns should be checked for matches. Excel will delete the duplicate rows and show you how many it removed.

This works best when your data has headers (column titles) and you want to keep the first occurrence of each row while deleting the rest. If you need more control — like keeping the last occurrence instead, or reviewing duplicates before deletion — you'll need a different approach.

Key Takeaways

  • The Data tab's Remove Duplicates tool deletes matching rows in one step and tells you how many were removed.
  • You must select your data range first, including headers if your spreadsheet has them.
  • Excel keeps the first occurrence of each row and deletes later matches by default.
  • If Remove Duplicates isn't available, your data might be in a table format that requires converting back to a range first.
  • For more control over which duplicates to keep, use sorting or filtering instead of the automatic tool.

Using the Remove Duplicates button

Start by selecting all the data you want to check. Click the first cell in your data range, then hold Shift and click the last cell. If your spreadsheet has headers in the first row, include them in your selection — Excel recognizes headers automatically and won't treat them as data to compare.

Open the Data tab at the top of the ribbon. Look for the Remove Duplicates button. If you don't see it, your data might be formatted as a table instead of a range; convert it back to a normal range first by right-clicking the table and selecting Convert to Range.

A dialog box will appear listing all your columns. By default, all columns are checked. This means Excel will only mark a row as a duplicate if every single column matches another row exactly. If you want to check only certain columns — for example, only the Name and Email columns — uncheck the others. Click OK and Excel will delete the duplicates and show a message telling you how many rows were removed.

What happens to your data after deletion

Excel permanently deletes duplicate rows when you use Remove Duplicates. It keeps the first occurrence of each row and removes all later matches. If you have a row that appears three times, Excel keeps the first one and deletes the second and third.

The deletion is when ready and cannot be undone with the normal Undo button after you close the file. Before running Remove Duplicates on important data, save a backup copy. This way, if the results aren't what you expected, you still have the original.

When Remove Duplicates doesn't work the way you want

If you need to keep the last occurrence instead of the first, or if you want to review duplicates before deleting them, the automatic tool won't help. Instead, sort your data by the columns you want to check for duplicates, then scan through manually and delete the rows you don't need. This takes longer but gives you complete control.

Another option is to use filtering. Click any cell in your data, go to Data, and click AutoFilter. Small dropdown arrows appear in each header cell. You can then sort or filter to group duplicates together, making them easier to spot and delete by hand.

If your duplicates are based on only one or two columns — for example, you have multiple rows for the same customer ID but different order dates — select those specific columns in the Remove Duplicates dialog instead of checking all columns. This way, Excel treats any row with the same customer ID as a duplicate, even if other details differ.

Checking for partial matches and near-duplicates

Remove Duplicates only finds exact matches. If one row says "John Smith" and another says "john smith" (different capitalization), Excel treats them as different entries and keeps both. The same applies to extra spaces: "John Smith" and "John Smith" (with two spaces) will not be recognized as duplicates.

To catch these near-duplicates, clean your data first. Use Find and Replace to standardize capitalization or remove extra spaces before running Remove Duplicates. Go to Home, click Find & Replace, and search for patterns like extra spaces or inconsistent formatting.

Removing duplicates from specific columns only

Sometimes you have data where certain columns matter for finding duplicates, but others don't. For example, you might have a customer list with Name, Email, and Phone columns, and you only want to remove rows where both Name and Email are identical — Phone differences don't matter.

In the Remove Duplicates dialog, uncheck the columns you don't want to compare. If you uncheck Phone, Excel will only look at Name and Email when deciding whether a row is a duplicate. This keeps rows that have the same name and email but different phone numbers, while still removing rows where both name and email match exactly.

Recovering data after accidental deletion

If you accidentally deleted rows you needed, press Ctrl+Z when ready to undo. This works only if you haven't closed the file or performed other actions since the deletion. If you've already closed the file, check your computer's file history or backup system — most systems keep previous versions of files for a set number of days.

On Windows, right-click the file, select Restore previous versions, and choose a version saved before you ran Remove Duplicates. On Mac, use Time Machine to browse and restore an earlier version of the file. This is why saving a backup before removing duplicates is always the safer choice.

Frequently Asked Questions

Does Remove Duplicates delete the entire row or just the duplicate data?

It deletes the entire row. If a row is marked as a duplicate, all columns in that row are removed from the spreadsheet. The remaining rows shift up to fill the gap.

What if my data doesn't have headers?

You can still use Remove Duplicates, but tell Excel that your data has no headers. In the Remove Duplicates dialog, uncheck the "My data has headers" option. Excel will then treat the first row as data, not as column titles.

Can I remove duplicates from just one column?

Yes. In the Remove Duplicates dialog, uncheck all columns except the one you want to check. Excel will then delete any row where that column matches a previous row, regardless of what's in other columns.

Why doesn't the Remove Duplicates button appear in my Data tab?

Your data is probably formatted as a table instead of a range. Right-click the table, select Convert to Range, and the button will appear. You can also select your data and use Format as Table to convert it the other way if needed.

Will Remove Duplicates work on data in different sheets?

No. Remove Duplicates only works on data within a single sheet. If you need to remove duplicates across multiple sheets, copy all the data into one sheet first, then run the tool.