The fastest way to remove duplicates in Excel
Excel has a built-in tool that finds and deletes duplicate rows in seconds. Select the data you want to check, go to the Data tab, click Remove Duplicates, and choose which columns should match for a row to count as a duplicate. Excel then deletes the extras and tells you how many rows it removed.
This works best when your data is clean — meaning the same information is spelled the same way each time. If one row says "John Smith" and another says "john smith" or "Jon Smith", Excel will treat them as different entries and keep both. The tool compares exact matches only.
The Remove Duplicates feature deletes rows permanently from your spreadsheet, so save a copy of your file first if you want to keep the original data. Once you click the button, the duplicates are gone.
Key Takeaways
- Select your data, go to Data > Remove Duplicates, choose your columns, and Excel deletes matching rows in one step.
- Excel only finds exact matches, so "John Smith" and "john smith" count as different entries.
- Save your file before removing duplicates, because the deletion is permanent and cannot be undone with Undo if you close the file.
- If you need to keep both copies and just mark the duplicates, use conditional formatting or a helper column instead of Remove Duplicates.
How to select the right columns for comparison
When you click Remove Duplicates, a dialog box asks which columns should be checked. If you select all columns, Excel only removes a row if every single cell matches another row exactly. If you select only the Name column, Excel removes rows where the name is the same, even if the phone number or address differs.
Choose columns based on what makes a record unique in your situation. For a customer list, you might check only the email address, because one person should only appear once. For an inventory list, you might check the product code and warehouse location together, because the same item in two different warehouses is not a duplicate.
If you are not sure which columns to use, start with the column that should be unique — usually an ID number, email, or name. You can always run the tool again on a different set of columns if the first pass does not catch what you need.
Step-by-step: Using the Remove Duplicates tool
First, click any cell in your data range. You do not need to select every cell — Excel will find the edges of your data automatically. If your data has headers (like "Name", "Email", "Phone"), make sure the cursor is in that row or nearby.
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 Quality or similar. If you cannot find it, use the search box at the top left (type "remove duplicates") and Excel will show you where it is.
A dialog box opens showing all your columns with checkboxes. By default, all columns are checked. Uncheck any columns you do not want compared. For example, if you have a "Notes" column that might differ between duplicates, uncheck it so Excel ignores that column when looking for matches. Click OK.
Excel scans your data, removes the duplicate rows, and shows a message saying how many duplicates it found and deleted. The rows are gone from your spreadsheet. If you made a mistake, press Ctrl+Z (or Cmd+Z on Mac) when ready to undo, but only if you have not closed the file.
When to use conditional formatting instead of deleting
Sometimes you want to see which rows are duplicates without actually removing them. Use conditional formatting to highlight duplicates in a color instead. Select your data, go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values. Excel highlights every duplicate entry in the color you choose, and the rows stay in your spreadsheet.
This is useful when you need to review the duplicates before deciding what to do with them, or when you want to keep a record of what was removed. You can then manually delete the ones you want to remove, or leave them highlighted for someone else to review.
Conditional formatting also works better when your data has small differences. If you highlight duplicates by the Name column, you can see that "John Smith" and "john smith" are highlighted as the same person, even though Excel's Remove Duplicates tool would treat them as different. You can then manually fix the spelling before running Remove Duplicates.
Using a helper column to find duplicates
A helper column is an extra column you add temporarily to identify duplicates using a formula. In a new column, type the formula =COUNTIF($A$2:$A$1000,A2), replacing A with your actual column letter and adjusting the range to match your data. This formula counts how many times each value appears. If the count is 1, it is unique. If the count is 2 or more, it is a duplicate.
Copy this formula down for every row. Then sort or filter by the helper column to see all the duplicates grouped together. You can review them, decide which ones to keep, and delete the rest manually. After you are done, delete the helper column.
This method takes longer than Remove Duplicates but gives you more control. You can see exactly which rows match, compare them side by side, and choose which copy to keep based on which one has better or more recent information.
Fixing spelling differences before removing duplicates
If your data has the same person or item spelled different ways — like "McDonald's" and "Mcdonalds" or "New York" and "New york" — Excel will not recognize them as duplicates. Clean up these differences first, or use Find and Replace to standardize them.
Open Find and Replace with Ctrl+H (or Cmd+H on Mac). Type the version you want to find in the "Find" box and the correct version in the "Replace" box. Click Replace All to change every instance at once. Do this for each variation you spot, then run Remove Duplicates.
You can also use the Trim function to remove extra spaces that might make duplicates look different. In a helper column, type =TRIM(A2) to clean up the data in column A, copy it down, then copy the results and paste them back as values to replace the original data.
What happens when you remove duplicates from a large file
Excel can handle Remove Duplicates on files with hundreds of thousands of rows, but the process may take a minute or two on very large spreadsheets. If your file is extremely large and Excel seems frozen, wait — it is still working. Do not force-close the program.
If you are working with a file that has over a million rows, consider splitting it into smaller chunks, removing duplicates from each chunk separately, then combining them. This is faster and less likely to cause Excel to run out of memory.
After removing duplicates from a large file, save it with a new name so you have a record of what you changed. This is especially important if other people rely on this data or if you need to explain what happened to rows that disappeared.
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. Always save a backup copy before running Remove Duplicates. If you realize you made a mistake after closing, you can reopen the backup and try again.
Does Remove Duplicates work on filtered data?
Remove Duplicates works on your entire data range, not just the rows you can see. If you have filters applied, turn them off first, or the tool may remove rows you did not intend to delete. Remove the filter, run Remove Duplicates, then reapply the filter if you need it.
What if I only want to remove duplicates from certain rows, not the whole sheet?
Select only the rows and columns you want to check before opening Remove Duplicates. Click the first cell you want to include, then drag to the last cell, or click and Shift+click to select a range. Excel will only look at the selected area.
Can I remove duplicates based on multiple columns at once?
Yes. In the Remove Duplicates dialog, check all the columns that together should make a record unique. For example, check both Name and Email if you want to keep a person only once per email address, even if they have the same name as someone else.
Why does Remove Duplicates keep some rows that look the same?
The most common reason is invisible differences like extra spaces, different capitalization, or different characters that look similar. Use Find and Replace or the Trim function to clean up the data first, then run Remove Duplicates again.