Look before you delete
To see duplicates without touching anything, select the column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel colours every repeated value and deletes nothing.
This works on a single column at a time in the way most people want it to. To check whole rows, you need the counting formula below.
Count duplicates across several columns
Add a helper column and count how many rows share the same values in the columns that define a record. For a customer and an invoice date in columns A and B:
=COUNTIFS($A$2:$A$5000,A2,$B$2:$B$5000,B2)Any result above 1 is part of a duplicate group. Filter on it, sort, and read the groups side by side. To mark only the second and later copies, so the first one survives, use an expanding range:
=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1Don’t use COUNTIF or COUNTIFS on long ID numbers. They compare text that looks numeric as numbers, so two different 16-digit values can count as a match once they are read to 15 digits.
Use Remove Duplicates
When you are sure, this is the quick way. Microsoft’s steps are short:
- Copy the data to another sheet first, because this deletes permanently once you save.
- Remove any outlines or subtotals, then select the range.
- Choose Data > Remove Duplicates.
- Under Columns, tick only the columns that define a duplicate.
- Select OK and note the count Excel reports.
The choice of columns is the part that matters. Tick every column and only perfectly identical rows go. Tick Customer alone, and Excel keeps each customer’s first row and throws away all their other orders.
Where the built-in command misleads you
- Formatting counts. Excel compares what a cell displays. The same date shown as 3/8/2025 in one cell and Mar 8, 2025 in another is treated as two different values.
- Stray spaces count. “Smith” and “Smith ” are different to Excel, so trim first or those duplicates will survive.
- Hidden rows are in play. Clear any filters before you run it, so you know exactly which rows it can reach.
- The first row wins. Excel keeps the top copy, which may be the older or emptier one. Sort first so that the row you want to keep comes first.
Keep the original with UNIQUE
In Excel 2021 and Microsoft 365, UNIQUE builds a de-duplicated list somewhere else and leaves your data untouched:
=UNIQUE(A2:D5000)The result updates when the source changes, which makes it handy for a live list of customers or products. Paste it as values when you need a fixed copy.
Near-duplicates need a person
Maya Lindqvist and Maya Lindquist at one address are probably the same customer, yet no exact-match tool will say so. Excel’s Remove Duplicates won’t catch them, and an automatic fuzzy merge can join two real people.
A workable approach is to sort by a second field, such as phone number or address, and read down the list. Our file cleaner reports rows that match except for a small difference in one cell, and it leaves both in place so that you make the call.
Are identical rows always a mistake?
No. Two identical lines on a sales export can be two real sales of the same item on one day, and deleting one makes your totals wrong. Before removing anything from financial data, check whether the source has a transaction number. If it does, include that column, and real repeats will no longer look identical.