How to Remove Duplicates in Excel

Remove Duplicates deletes repeated rows from a range based on the columns you mark as the key. It is fast and built in, but it permanently drops rows, so the choice of key columns and a backup matter. In financial work it cleans duplicated transactions or master records before they double count in a model.

What it does and when to use it

Remove Duplicates scans the columns you select and keeps only the first occurrence of each unique combination, deleting the rest. The rows it keeps and removes depend entirely on which columns you treat as the key.

Use it when an export contains true repeats, such as a transaction pulled twice or a customer listed multiple times, and you want one clean record each. If you only need to see duplicates rather than delete them, conditional formatting or a helper count is safer.

Step by step

Always work on a copy first, because the removal is permanent once you save.

1. Click any cell inside your data range or table.
2. Go to Data > Remove Duplicates.
3. Confirm the My data has headers box matches your range.
4. Check only the columns that define a true duplicate, for example Invoice ID.
5. Click OK and read the message reporting how many rows were removed.
6. Spot check the result against the original count before saving.

Fewer key columns means more aggressive removal, so choose the columns that truly define a duplicate.

A model use case

A bank export occasionally lists the same payment twice. If that feeds a cash model unfiltered, it overstates outflows. Keying Remove Duplicates on the transaction reference column collapses the repeats to one row each, so the model counts every payment once.

For a master list of cost centers pulled from two systems, key on the center code to keep a single clean record per code. Always reconcile the before and after row counts so the cleanup is intentional, not accidental.

• Key columns decide what counts as a duplicate.
• The action is permanent, so duplicate the sheet first.
• Reconcile row counts before and after to confirm the result.

Pitfalls and limits

The biggest risk is choosing the wrong key columns and deleting rows that were not really duplicates, or leaving repeats because an extra column made near-identical rows look unique. Trailing spaces and inconsistent case can also hide true duplicates, since Excel compares the stored values.

Remove Duplicates is destructive and gives no built-in undo once the file is saved and reopened. For a reversible workflow, use a non-destructive alternative: flag duplicates with =COUNTIF() and filter, mark them with conditional formatting, or load the data through Power Query and use its Remove Duplicates step, which can be re-run and adjusted.