The fastest way to delete duplicates in Excel

Excel has a built-in tool that finds and removes duplicate rows in seconds. Open your spreadsheet, select all the data you want to check (including headers), then go to the Data tab and click Remove Duplicates. A dialog box appears asking which columns to compare — by default it selects all of them, which works for most cases. Click OK and Excel deletes every row that matches another row exactly.

This method works best when you have a complete dataset and want to clean it all at once. If you only want to see duplicates without deleting them yet, or if you need more control over which columns matter, the other methods below give you that choice.

Key Takeaways

  • The Data tab's Remove Duplicates tool deletes entire rows that match other rows, not individual cell values.
  • You can choose which columns Excel compares — for example, checking only email addresses instead of every column.
  • Filtering for duplicates lets you review them before deleting, which is safer if you are unsure whether the matches are real mistakes.
  • Conditional formatting highlights duplicates in place so you can see them without removing anything.
  • Always copy your data to a backup sheet before deleting, because the Remove Duplicates action cannot be undone.

Selecting which columns to compare when removing duplicates

When you click Remove Duplicates, the dialog shows a checkbox for every column in your data. By default all are checked. If you only care about duplicates in one column — say, email addresses in a contact list — uncheck all the others and leave only that column checked. Excel then deletes rows only when that column matches, even if other columns differ.

This is useful when you have imported data from two sources and want to remove people who appear in both lists. Check only the email column, and Excel removes the second occurrence of each email address while keeping the first one. The rows with different names, phone numbers, or addresses stay because those columns were not part of the comparison.

If you want to keep the first occurrence of a duplicate and delete later ones, Excel does this automatically — it removes the second, third, and fourth matches while keeping the first row it found.

Using filters to find duplicates before you delete them

If you want to see duplicates first and decide which ones to remove, use the AutoFilter feature instead of Remove Duplicates. Select your data, go to the Data tab, and click AutoFilter. A dropdown arrow appears in each column header.

Click the dropdown in the column you want to check — for example, a Product ID column. The list shows every unique value in that column. Scroll down and look for values that appear more than once in the data itself. You can manually select and delete rows one at a time, or use the filter to show only the duplicates and then delete them all together.

This method takes longer but gives you a chance to review the data. You might find that what looks like a duplicate is actually a legitimate repeat — the same customer ordering twice, or the same product in different sizes. Filtering lets you catch those before they disappear.

Highlighting duplicates with conditional formatting

Conditional formatting colors duplicate cells so you can see them without removing anything. Select your data, go to the Home tab, click Conditional Formatting, then choose Highlight Cell Rules and Duplicate Values. Excel highlights every cell that appears more than once in the selected range.

This is the safest way to start if you are not sure whether your duplicates are mistakes. You can review the highlighted cells, understand why they are there, and then decide whether to delete them. Once you are confident, you can use Remove Duplicates or manually delete the rows.

The highlighting stays in place until you remove it, so you can print the spreadsheet or share it with someone else to confirm the duplicates before taking action.

Why Remove Duplicates deletes entire rows, not single cells

Excel's Remove Duplicates tool compares whole rows, not individual values. If you have a list of names and email addresses, and two rows have the same email but different names, Excel sees them as different rows and keeps both. It only deletes a row if every column you selected matches another row exactly.

This is usually what you want — if you have imported a customer list twice by mistake, you want to remove the entire duplicate record, not just the email address. But if you have a column of product codes where some codes appear multiple times and you only want to remove the duplicate codes themselves (not the whole row), you need a different approach.

For that situation, copy the column to a new location, select it, use Remove Duplicates on just that column, and you will have a list of unique codes. Then you can use that list to filter or look up data in your original table.

Removing duplicates from a sorted or unsorted list

Excel's Remove Duplicates tool works the same way whether your data is sorted or not. You do not have to arrange your rows in any particular order first. However, sorting can help you spot duplicates visually before you delete them, especially if you want to review the data with a colleague.

If you sort by the column you are checking for duplicates — for example, sorting alphabetically by email address — all the matching rows appear next to each other. You can then scroll through and see exactly which rows will be removed. After you are confident, you can run Remove Duplicates and know what to expect.

Sorting is optional, but it is a good safety step if you are working with a large dataset or if the consequences of deleting the wrong row are serious.

Backing up your data before deleting duplicates

The Remove Duplicates action cannot be undone with Ctrl+Z if you close the file. Before you delete anything, save a copy of your spreadsheet with a different name — for example, save the original as "Customer List Backup" and work on "Customer List Working". If something goes wrong, you still have the original.

You can also copy your data to a new sheet within the same file. Select all your data, copy it, right-click on the sheet tab at the bottom, choose Insert Sheet, and paste the data there. Now you have two versions in one file, and you can delete duplicates from one while keeping the other intact.

This takes two minutes and prevents the frustration of realizing you deleted rows you needed to keep.

Frequently Asked Questions

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

Only exact matches. If one row says "John Smith" and another says "Jon Smith", Excel treats them as different and keeps both. The comparison is character-by-character, so even a single space or punctuation mark difference means the rows are not duplicates.

Can I undo Remove Duplicates after I close the file?

No. Once you close the file, the deletion is permanent. This is why backing up before you delete is important. If you delete duplicates and when ready realize you made a mistake, close the file without saving and reopen it to get your original data back.

What happens if I remove duplicates from a column that has headers?

Make sure your header row is included in your selection when you click Remove Duplicates. Excel usually recognizes headers automatically and does not delete them, but if your headers look like data (for example, if they are just numbers), Excel might treat them as a duplicate row and remove them. Review the dialog before clicking OK.

Can I remove duplicates based on multiple columns but not all columns?

Yes. In the Remove Duplicates dialog, uncheck the columns you do not want to compare. For example, if you want to find rows where both the email and phone number match, check only those two columns and uncheck the rest. Excel then deletes rows only when both of those columns match.

Does Remove Duplicates keep the first occurrence or the last one?

It keeps the first occurrence and deletes all later matches. If the same email appears in rows 5, 12, and 18, Excel keeps row 5 and removes rows 12 and 18. If you need to keep a different row, sort your data first so the row you want is first.