Duplicate removal is easy to click and easy to misunderstand. Two rows may share a customer name while representing different orders. Decide what counts as the same record before deleting anything. Then perform the cleanup on a copy, keeping enough information to check whether the result is correct.
Copy the data first. Select the full table, choose Data → Remove Duplicates, and select the columns that together identify a duplicate record.
Go straight to the stepsBefore you begin
Remove Duplicates deletes matching records from the selected range. Review the matching columns carefully; a repeated value in one field does not always mean the entire record is unnecessary.
What you’ll need
- Excel
- A copy of the original workbook
- A clear definition of a duplicate
Let’s do this
Step by step
- 01
Preserve the original
Save a separate workbook copy or duplicate the data to a separate sheet. Give the working copy a clear name before changing records.
- 02
Define the matching key
Write one sentence describing a duplicate. For an order-lines file, an order number alone may be insufficient; order number plus product code may be appropriate. Use the actual meaning of your data.
- 03
Select the complete data range
Include all associated columns so related values remain together. In Data, choose Remove Duplicates and check the header setting if it is shown.
- 04
Choose the comparison columns
Check the fields that define the duplicate key. Excel keeps the first occurrence and removes later matches within the selected data.
- 05
Review before saving over anything
Read the removal count, inspect several known examples, and compare the cleaned result with the original. Undo immediately if legitimate records disappeared.
A small example that prevents a large mistake
Imagine two rows for order 104: one for a lamp and one for a shade. Matching only on order number could remove a legitimate item. Matching on order number and product code distinguishes those lines. Your correct key might require additional fields, such as a shipment or line number.
Now imagine two rows with the same email address but different delivery addresses. Whether one should be removed depends on the purpose of the list. Excel cannot decide whether you are counting people, addresses, orders, or visits. That decision belongs in your cleanup plan.
Keep an audit note
Record the starting row count, the selected comparison columns, and the number removed. Save that note with the cleaned file. If someone questions a missing entry later, you will have a practical explanation and an unchanged source to inspect.
A quick final check
- An untouched original exists.
- The comparison key matches the meaning of the data.
- Known legitimate records remain.
Common questions & sticking points
Can I find duplicates without deleting them?
Yes. Conditional Formatting → Highlight Cells Rules → Duplicate Values can help inspect repeated values first; that is not the same as checking a multi-column record key.
Why are apparent duplicates still present?
Check the selected columns and differences in displayed values, including spaces and formatting. Compare a specific pair before applying broader cleanup.
Sources & further reading Optional
References used to check this guide. Follow your product’s own safety instructions where its design differs.
- Microsoft: Duplicate keys, retained rows, and removal behavior
- Microsoft: Highlight duplicates before removing them