You merged two customer lists, exported a report twice, or pasted data from a few different sources, and now the same names and emails show up three or four times. Before you start deleting rows by hand, know that Excel has several built-in ways to remove duplicates, and each one fits a different situation. This guide shows you how to remove duplicates in Excel safely, including how to check them first and how to keep your original data intact.
The steps work in Excel for Microsoft 365, Excel 2021 and Excel 2019 on Windows. Mac and Excel for the web have the same main tools, with slightly different menu positions.
Before you remove duplicates: make a copy
Removing duplicates deletes data. Right-click the sheet tab at the bottom, choose Move or Copy, tick Create a copy and click OK. Work on the copy. If something goes wrong, Ctrl + Z also undoes the last change, but a backup copy is safer once you’ve saved the file.
It also helps to clean up the data first, because Excel only treats values as duplicates when they match exactly:
- Extra spaces make “Ana Lopez” and “Ana Lopez ” different. Use
=TRIM(A2)in a helper column to clean them. - Numbers stored as text won’t match real numbers. Look for the small green triangle in the cell corner and choose Convert to Number.
- Uppercase and lowercase don’t matter for the Remove Duplicates tool: it treats “ANA” and “ana” as the same.
Method 1: Highlight duplicates first (no deleting)
If you want to see the duplicates before removing anything, start here.
- Select the column or range you want to check.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Pick a color and click OK.
Every repeated value is now colored. To group them together, click the column header filter arrow (turn filters on with Ctrl + Shift + L) and choose Filter by Color. This is a good way to review duplicates before deciding which ones to delete.
Method 2: The Remove Duplicates button (fastest)
This is the quickest way to remove duplicates in Excel.
- Click any cell inside your data.
- Go to Data > Remove Duplicates (in the Data Tools group).
- Make sure My data has headers is ticked if your first row has column names.
- Choose which columns Excel should compare. This step matters a lot.
- Click OK. Excel tells you how many duplicates it removed and how many unique values remain.
Choosing the right columns
Excel only deletes a row when all the selected columns match. Two examples:
- Select only Email and Excel keeps one row per email address, even if names are spelled differently.
- Select Name, Email and Date and Excel only removes rows where all three match, so the same person on two different dates stays.
Excel always keeps the first occurrence and deletes the ones below it. If you want to keep the most recent record instead, sort the data by date from newest to oldest before using Remove Duplicates.
Method 3: The UNIQUE function (keeps your original data)
In Excel for Microsoft 365 and Excel 2021 or newer, the UNIQUE function creates a clean list in another place without touching the original.
=UNIQUE(A2:A500)
Type it in an empty cell and the unique values spill down automatically. It updates by itself when the source changes. A few useful variations:
=UNIQUE(A2:C500)returns unique rows across several columns.=SORT(UNIQUE(A2:A500))returns the unique list in alphabetical order.=UNIQUE(A2:A500,,TRUE)returns only the values that appear exactly once.
If you see a #SPILL! error, something is blocking the cells below the formula. Clear them and the list appears.
Method 4: Advanced Filter (works in older versions)
If you’re on Excel 2016 or older and don’t have UNIQUE, Advanced Filter can copy unique records to a new spot.
- Click inside your data and go to Data > Advanced (in the Sort & Filter group).
- Choose Copy to another location.
- Check the List range, then click in Copy to and select an empty cell.
- Tick Unique records only and click OK.
Method 5: Flag duplicates with COUNTIF
Sometimes you don’t want to delete duplicates, just mark them, for example to review them with a coworker. Add a helper column and use:
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First")
Copy it down. The first time a value appears it says “First” and every repeat says “Duplicate”. You can then filter the helper column and decide what to do with each row.
Which method should you use?
- Quick cleanup of a one-time list: Remove Duplicates.
- You need to review before deleting: Conditional Formatting or COUNTIF.
- The original data must stay untouched: UNIQUE or Advanced Filter.
- The data refreshes every week from another file: Power Query (Data > From Table/Range, then Home > Remove Rows > Remove Duplicates), so the cleanup repeats each time you refresh.
Once your list is clean, you can summarize it in seconds with a pivot table in Excel, or bring in matching details from another sheet with VLOOKUP and XLOOKUP. Prefer a free spreadsheet program? Our list of free alternatives to Microsoft Office covers options that handle this too.
FAQ
Does Remove Duplicates delete the whole row?
Yes. When it finds a duplicate based on the columns you selected, Excel deletes the entire row within your data range, not just the cell. That’s why choosing the right columns and working on a copy are so important.
Is Remove Duplicates case-sensitive?
No. Excel treats “Apple” and “APPLE” as the same value. If uppercase and lowercase matter for your data, use a helper column with the EXACT function or Power Query instead.
How do I remove duplicates but keep the latest entry?
Sort your data by date from newest to oldest first. Remove Duplicates always keeps the first occurrence, so after sorting, the newest record is the one that stays.
How do I remove duplicates in Google Sheets?
Select the range and go to Data > Data cleanup > Remove duplicates. Google Sheets also supports the UNIQUE function with the same syntax as Excel.
