The fastest way to remove 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 at the top and click Remove Duplicates. Excel will show you a dialog asking which columns to check — leave them all selected unless you have a reason not to — then click Remove Duplicates again. The tool deletes the duplicate rows and tells you how many it found.
This works best when your data is clean: no extra spaces at the start or end of cells, no accidental line breaks, and consistent formatting. If Excel finds fewer duplicates than you expected, check whether cells that look identical actually contain hidden characters.
The Remove Duplicates tool is permanent — it deletes rows from your file. If you want to see what will be removed before it happens, make a copy of your spreadsheet first and test on the copy.
Key Takeaways
- Select all your data including headers, go to the Data tab, and click Remove Duplicates to delete matching rows in one step.
- Excel compares entire rows by default, so it only removes rows where every selected column matches another row exactly.
- Hidden spaces, line breaks, or formatting differences can make identical-looking rows fail to match, so clean your data first if duplicates remain.
- The Remove Duplicates tool permanently deletes rows, so copy your file before using it if you want to keep the original.
- If you need to keep both copies and just flag duplicates instead, use conditional formatting or a helper column with a formula.
How to select the right data range
The Remove Duplicates tool only works on the cells you select, so choosing the right range matters. Click the first cell of your data (usually the header row) and drag to the last cell that contains information. If your data is large, click the first cell, then hold Shift and click the last cell instead of dragging.
Always include your header row — the row with column names like "Name", "Email", or "Date". Excel needs headers to understand what each column represents. If you select data without headers, Excel treats the first row as data and may delete it by mistake.
If your spreadsheet has multiple separate tables, select only one table at a time. Running Remove Duplicates on mixed data can delete rows you did not intend to remove.
What happens when Excel finds a match
Excel compares every row to every other row using the columns you selected. If two rows have identical values in all those columns, Excel keeps the first occurrence and deletes the second one. The order matters: whichever duplicate appears first in your spreadsheet survives.
This means if you have a customer named "John Smith" appearing twice, Excel will keep the first "John Smith" row and remove the second one, along with any data in that row. If the two rows have different information in other columns — one has a phone number and the other does not — you lose the data from the deleted row.
If you need to preserve information from duplicate rows before deleting them, copy that data to a separate location first, or use a different method like sorting and reviewing manually.
Checking for hidden characters that block matching
Sometimes rows look identical but Excel does not recognize them as duplicates. This usually happens because of invisible characters: a space at the start or end of a cell, a line break inside a cell, or formatting that looks the same but is not. To find these, click a cell that should match another and look at the formula bar at the top — it shows exactly what is in the cell, including spaces.
To clean up spaces, select all your data, go to Find & Replace (Ctrl+H on Windows, Cmd+H on Mac), and search for a single space at the start of cells. In the Find field, type a space. In the Replace field, leave it empty. Click Replace All. Repeat this for spaces at the end of cells by searching for " " (space at the end) and replacing with nothing.
For line breaks inside cells, search for Ctrl+J (the line break character) and replace it with a space or nothing. After cleaning, run Remove Duplicates again.
Keeping both copies and marking duplicates instead
If you want to see which rows are duplicates without deleting them, use conditional formatting or a helper column. A helper column is the simpler approach: add a new column at the end of your data, give it a header like "Duplicate", and use a formula to flag matches.
In the first data row of your helper column, enter this formula: =COUNTIF($A$2:$A2,$A2)>1 (replace A with your first column letter). This returns TRUE if the value appears more than once in that column. Copy this formula down to every row. Rows marked TRUE are duplicates. You can then sort by this column to group duplicates together, review them, and delete manually if you choose.
This method lets you see the duplicates before removing them and gives you control over which copy to keep. It also preserves your original data — you can delete the helper column and undo if you change your mind.
Removing duplicates based on specific columns only
By default, Remove Duplicates checks all columns you selected. Sometimes you want to find rows that match in only certain columns — for example, removing duplicate email addresses but keeping rows with different names. The Remove Duplicates dialog lets you uncheck columns you want to ignore.
After selecting your data and opening Remove Duplicates, uncheck the columns that should not be compared. If you uncheck the "Name" column but keep "Email" checked, Excel will delete rows only when the email matches, regardless of whether the name is different. This is useful for finding duplicate contacts or orders even if the details vary slightly.
Be careful with this approach: if you uncheck too many columns, you may delete rows that are actually different. Test on a copy first to see what gets removed.
What to do if Remove Duplicates does not work
If you run Remove Duplicates and no rows are deleted, or fewer rows are deleted than expected, the most common cause is hidden characters or formatting differences. Check the formula bar as described above, clean spaces and line breaks, and try again.
Another possibility is that your data is not actually duplicated — the rows may look the same but contain different values in columns you are not seeing. Scroll right to check whether there are more columns beyond what is visible on screen. If columns to the right contain different data, those rows are not true duplicates.
If you are comparing text that came from different sources (like a list copied from a website and a list from your database), the text may use different character encodings or include invisible formatting. Copy the data into Notepad first to strip all formatting, then paste it back into Excel, and try Remove Duplicates again.
Frequently Asked Questions
Can I undo Remove Duplicates if I delete the wrong rows?
Yes, when ready press Ctrl+Z (or Cmd+Z on Mac) to undo. This restores the deleted rows. If you have already saved the file, undo will not work — this is why making a copy first is important. If you have closed and reopened the file, the deletion is permanent.
Does Remove Duplicates work on filtered data?
No. If you have filters applied, Remove Duplicates will still process all rows in your spreadsheet, including hidden ones. Remove your filters before using the tool, or it may delete rows you cannot see.
What if two rows are identical except for capitalization, like "John" and "john"?
Excel treats these as different values, so Remove Duplicates will not flag them as duplicates. Use Find & Replace to standardize capitalization first: search for "john" and replace with "John", or use a formula to convert all text to the same case before removing duplicates.
Can I remove duplicates from multiple columns at once, like finding rows where both Name and Email match?
Yes. Select all your data, open Remove Duplicates, and leave both the Name and Email columns checked. Excel will only delete rows where both columns match another row. Uncheck any columns you want to ignore.
Does Remove Duplicates work on data in different sheets?
No. The tool only works within a single sheet. If you need to remove duplicates across multiple sheets, copy all the data into one sheet first, run Remove Duplicates, then move the cleaned data back if needed.