Highlight Duplicate Values in Excel Before Deleting Rows
Use conditional formatting and a saved copy to inspect duplicates before applying Excel's destructive removal command.
What is the problem?
Excel's duplicate-removal feature deletes rows based on the columns you select. Two people can share a name, and repeated purchases may both be valid records, so review the comparison key before removing anything.
Step-by-step solution
Copy the worksheet and define the duplicate key
Save a backup and duplicate the working sheet. Decide whether identity depends on an order ID, a name or several combined columns before deciding that two records are duplicates.
Use conditional formatting to reveal matches
Microsoft documents highlighting duplicate values through Home and Conditional Formatting. Review highlighted records to identify legitimate repeats and separate transactions that only share one visible value.
Remove verified duplicates and compare the totals
Only after reviewing should you use Data > Remove Duplicates and select the exact comparison columns. Compare remaining rows and important totals with the untouched copy.
Alternative method
Advanced filtering can show or copy unique records without permanently removing source rows. It is useful when a report needs distinct values but the history must stay intact.
Known limitations
Identical displayed values do not necessarily represent identical entities. Recovery can be impossible after later saves, so preserve a backup before deleting rows.
Frequently asked questions
Not necessarily. Excel can remove the entire matching row based on selected comparison columns, including data in columns not used as the key.
Official primary sources
This article uses the official references below; consult them for the full guidance.
