Remove duplicate records while keeping a source copy in Excel

Quick answer: Decide which columns define a duplicate before using Data → Remove Duplicates. Keep an untouched copy, select the complete record range, and compare the remaining rows against the business rule rather than trusting the removal count alone.

Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.

Use a fixture where the key matters

Enter the following at A1:C6 in a new workbook. Save this original as duplicate-source.xlsx, then work in a separate copy.

Order	Item	Amount
O101	Cable	20
O102	Hub	35
O101	Cable	20
O101	Cable	25
O103	Stand	40

There are five records totaling 140. Rows 2 and 4 are exact repeated records; row 5 has the same order and item but a different amount. That difference could represent a correction or a separate event. Excel cannot decide which interpretation is valid for your business.

For this exercise, remove only exact duplicate records across all three fields. The expected remaining four records total 120. Do not deduplicate on Order alone, because that would also discard a record with a distinct amount.

Inspect, then remove

Optionally select A2:A6 and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. The repeated O101 identifiers should be highlighted. This is only a single-column warning: it does not prove that the entire records are identical. O101’s 25 amount needs separate inspection.

Select A1:C6, including every field and the heading row. Choose Data → Remove Duplicates. Confirm that the first row is treated as headers and keep Order, Item and Amount all selected in the Columns list. Review those selections before confirming with OK.

The expected result is one exact duplicate removed and four distinct records remaining. Check each survivor, not only the message: O101/Cable/20, O102/Hub/35, O101/Cable/25 and O103/Stand/40. Their amounts are 20 + 35 + 25 + 40 = 120. In particular, the distinct 25 record must still exist. Save this four-record result as duplicate-clean.xlsx, close it, and reopen it to verify four records totaling 120 before starting the separate experiment.

Compare a changed-input case safely

Return to a fresh copy of the untouched original, not duplicate-clean.xlsx. Name this separate experiment duplicate-changed-test.xlsx. Change the third data record’s amount, C4, from 20 to 21. Run Remove Duplicates with the same all-three-column comparison. All five records are now distinct and should remain; the new total is 141.

This boundary case proves that Amount participates in the key. If a record is removed anyway, inspect the Columns selections. If you deliberately choose only Order and Item, the 20, 21 and 25 entries are treated as the same key even though their amounts differ. Do not use that narrower key without a separately decided retention rule.

Recovery and limitations

Microsoft warns that Remove Duplicates deletes the duplicate data. Use your saved original for recovery rather than relying on a future undo session. Filtering or highlighting would preserve the source, but neither is the same operation as deleting duplicate rows.

Unexpected counts can involve blank cells or spaces. Inspect those entries instead of assuming every visually identical label is identical data. Remove outlines or subtotals before using the command, and avoid selecting only one column of a multi-column record. Keep the five-record, 141-total changed-input experiment separate. Return to duplicate-clean.xlsx and verify its four records still total 120; do not overwrite it with the experiment. Keep the original until the cleanup rule and output have been accepted.

Official references

• Find and remove duplicates

Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.