Remove duplicates in Excel while preserving your source

Two rows can share an email address and still represent different orders. Before using Remove Duplicates, decide what makes a record a duplicate. Then preserve the original data and work on a copy. Excel’s command removes data; a highlighted cell or a unique-value formula gives you a safer way to review first.

The procedures below are based on Microsoft’s Excel documentation. Ribbon labels can vary across desktop and web versions. Use a small copied sample to confirm the result before processing an important list.

1. Preserve the workbook and define the duplicate

Save a separate workbook copy with a descriptive name, such as Customer-Export-Cleanup.xlsx. Keep the untouched export in its original location. Inside the working workbook, create a clearly labeled source sheet and a separate sheet for cleanup.

Write down the comparison rule before changing rows. For a mailing list, an email address might identify one contact. For order data, the combination of Order ID and Product ID might identify one line. Choosing only a customer name could discard legitimate repeat purchases.

Microsoft’s unique-value reference explains a crucial detail: the selected comparison columns form the key, but a matching record is removed across the entire selected row range. Keep every column belonging to a record together.

2. Highlight repeated values before removing anything

1. On the working sheet, select the column you want to inspect.
2. Choose Home, Conditional Formatting, Highlight Cells Rules, then Duplicate Values.
3. Choose a visible format and confirm.
4. Review highlighted entries alongside their neighboring columns.

This Microsoft-supported review method changes appearance rather than deleting records. Highlighting an email column answers whether an address repeats; it does not establish whether the whole record is redundant.

For an illustrative example, imagine three rows: one address with Order 101, the same address with Order 102, and a second copy of Order 102. The repeated address is expected. Only the repeated order line may be a cleanup candidate. This is why the business meaning of the columns matters more than the color.

3. Use a separate unique list when deletion is unnecessary

If you only need distinct names or addresses, consider a formula result in an empty area of the workbook. In a supported version, =UNIQUE(A2:A100) produces distinct entries from that range while leaving the source in place. Leave room below the formula because its results spill into neighboring cells.

For distinct complete rows, use the complete range, such as =UNIQUE(A2:C100), rather than a single identifying column. Microsoft’s UNIQUE documentation lists supported versions, including Microsoft 365, Excel 2024, and Excel 2021. Older editions may require another method.

Do not confuse distinct entries with entries that occur only once. The optional exactly_once argument changes that behavior. A distinct contact list normally keeps one instance of a repeated address; an exactly-once result excludes that repeated address entirely.

4. Remove records only from the cleanup copy

1. Select the full working range or a cell inside the correct table.
2. Choose Data, then Remove Duplicates.
3. Check the header setting if the dialog offers it.
4. Select only the columns that define your duplicate key.
5. Confirm, then record Excel’s removed and remaining counts.

Excel keeps the first matching occurrence. If you need a particular version, such as the most recent record, do not assume the first row is correct. Review the relevant dates and decide the retention rule before running the command. Undo immediately if the result is unexpected, and keep the preserved source regardless.

5. Verify the result instead of trusting the total

Compare several known records with the original. Check an address that repeated legitimately, a deliberate duplicate, and an entry near the bottom of the range. Make sure adjacent columns still belong to the correct person or transaction.

A helper formula such as =COUNTIF($A$2:$A$100,A2) can show how often one value appears. Microsoft notes that COUNTIF ignores letter case and can be affected by unwanted spaces or characters. Treat differing formats and suspicious whitespace as review questions rather than automatically rewriting the source. Save the cleaned result under its own name when the checks make sense.

Prepared with AI assistance from the linked documentation. This is a source-led explanation, not a claim of hands-on testing across every device. Read the editorial policy.