Excel's Remove Duplicates button is fast, but it doesn't ask which copy you want to keep. If a customer appears three times with three different order dates, a careless click can keep the oldest record and delete the one you needed. This guide shows how Excel decides what to keep, how to control that choice (first entry, latest date or highest value), and why the button sometimes seems to ignore obvious duplicates.
The one rule: Excel keeps the first row
Microsoft's documentation says that when Remove Duplicates runs, the first occurrence of each value in the list is kept and the later identical rows are deleted. "First" means highest up the sheet. So the whole trick is to sort the rows you want to keep to the top before you remove anything.
Before you start: right-click the sheet tab and choose Move or Copy > Create a copy. Remove Duplicates deletes rows for good. Undo works straight afterwards, but not once you've saved and closed the file.
Prepare a clean match key
Excel only treats two cells as duplicates if they match exactly, and a single trailing space makes jane@shop.com different from jane@shop.com. Before deduplicating, add a helper column that normalizes the value you're matching on:
=LOWER(TRIM(A2))Fill it down, and deduplicate on this Key column instead of the raw one. If your data came from a web page or a PDF, also remove non-breaking spaces, which TRIM doesn't touch:
=LOWER(TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")))Keep the first instance
- Click any cell inside your data.
- Go to Data > Remove Duplicates.
- Tick My data has headers if row 1 contains column names.
- Click Unselect All, then tick only the column(s) that define a duplicate, such as your Key column.
- Click OK. Excel reports how many duplicates were removed and how many unique values remain. Write both numbers down.
Even though you ticked only one column, Excel removes entire rows, so the other columns stay lined up. That's how you remove duplicates based on one column while keeping the rest of the data.
If you selected a single column before clicking the button, Excel may ask whether to Expand the selection. Choose expand. Otherwise only that one column is deduplicated, and every row after the first deletion is misaligned.
Keep the latest date or highest value
Because Excel keeps the top row, you sort first and then remove:
- Select your data and go to Data > Sort.
- Sort by the date column, Newest to Oldest. For the highest value, sort the number column Largest to Smallest.
- Run Data > Remove Duplicates on the Key column only.
- Re-sort however you like afterwards.
The same pattern handles other choices too. To keep the last instance in the original order, add a column numbered 1, 2, 3 down the rows, sort it largest first, and then remove duplicates.
Power Query users: Power Query's Remove Duplicates also keeps the first row, but it's case-sensitive, and a sort applied in an earlier step isn't always respected. Many people add Table.Buffer after the sort step to fix the order. If you only need a one-off clean, the worksheet method above is simpler.
Flag duplicates without deleting anything
If you'd rather see what would be removed first, or someone else has to sign off on it, use formulas to label each row. Then filter on the label. We tested both formulas below on a sample list with mixed case and trailing spaces. Here the Key is in column D and the dates are in column B, rows 2 to 500.
Mark first vs repeat
=IF(COUNTIF($D$2:D2,D2)=1,"First","Repeat")The range grows as you fill down, so the first time a key appears it's labeled First, and every later copy is labeled Repeat.
Mark the latest record per key
=IF(B2=MAXIFS($B$2:$B$500,$D$2:$D$500,D2),"Keep","Remove")MAXIFS works in Excel 2019, Excel 2021 and later, and Microsoft 365. If two rows share the same latest date, both are marked Keep. Filter for those and decide by hand.
Count how many times each value appears
=COUNTIF($D$2:$D$500,D2)Anything above 1 is a duplicate. Sorting by this count puts the worst offenders at the top, which is useful for spotting data-entry problems at the source.
In Microsoft 365, =UNIQUE(D2:D500) returns a list of distinct values without touching the original data.
Why Remove Duplicates isn't working
- Hidden spaces or characters. Trailing spaces, non-breaking spaces and line breaks make values look identical when they aren't. Use the Key column above, and wrap it in
CLEAN()to remove line breaks. - Numbers stored as text.
1050as text and1050as a number don't match. Convert the column first (see fixing numbers stored as text). - Blank rows or columns. Excel guesses the range from the cell you clicked, and a blank row stops it. Select the full range yourself, or press Ctrl+T to turn it into a table first.
- Too many columns ticked. If you tick every column, rows only count as duplicates when every column matches. Two orders from the same customer on different dates aren't duplicates by that test.
- Merged cells or subtotals. Unmerge cells and remove subtotal rows (Data > Subtotal > Remove All) before deduplicating.
- Dates with hidden times. Two cells that both show 3/9/2026 can differ by a time of day. Use
=INT(B2)in a helper column to compare dates only.
A quick final check
After deduplicating, confirm that rows before − rows removed = rows after, and that =COUNTA(UNIQUE(range)) (or the count from Excel's message) equals your new row count. If you're working with sales or inventory data, compare total quantities before and after. They should only fall by the amount in the removed rows.
FAQ
Does Excel's Remove Duplicates keep the first or the last row?
The first. Microsoft documents that the first occurrence is kept and later identical rows are deleted, so sort the rows you want to keep to the top first.
How do I remove duplicates but keep the latest date?
Sort the data by date from newest to oldest, then run Data > Remove Duplicates and tick only the column that identifies a duplicate, such as email or customer ID.
Why is Excel not removing duplicates?
The values aren't really identical. Common causes are trailing or non-breaking spaces, numbers stored as text, hidden times in dates, or ticking too many columns in the dialog.
Can I remove duplicates based on one column but keep the other columns?
Yes. Select the whole table, and in the Remove Duplicates dialog tick only that one column. Excel removes entire rows, so the other columns stay aligned.