How to Remove Duplicates in Excel Without Deleting the Wrong Rows

Excel's Remove Duplicates command is easy to use and just as easy to misuse. You select a table, choose some columns, click OK, and discover that Excel kept an old address instead of the new one, retained the blank record instead of the completed record, or deleted rows that were not duplicates in the way your business defines them.

The problem is not random behavior. In a worksheet, Excel keeps the first occurrence of each duplicate key and removes later occurrences. It does not know that “newest,” “approved,” or “most complete” should win. You must put the preferred row first and define the comparison columns correctly before you delete anything.

This guide shows how to do that safely. If you need a quick browser-based option for an XLSX workbook, you can also use Remove Duplicates for Excel. Keep an untouched original first, and do not upload confidential or regulated workbooks unless the tool and your organization's policy permit it.

Why Excel keeps the wrong duplicate row

Imagine a customer table with two rows for account 1042. The first row has an old email address and a blank phone number. The second row has yesterday's update and all fields completed. If you select Account ID as the duplicate key, Excel sees both rows as the same account. Remove Duplicates keeps the one that appears first in the selected range. If the old row is above the new row, the old row survives.

That first-occurrence rule is useful because it makes the worksheet command predictable. However, it also means that the order of your data is part of the deletion logic. Sort newest to oldest to keep the newest. Sort oldest to newest to keep the oldest. Create and sort by a quality score if the most complete record should win.

The second source of mistakes is column selection. The checked columns in the Remove Duplicates dialog form a combined comparison key:

• If only Customer ID is checked, every row with the same ID belongs to one duplicate group.
• If Customer ID and Order Date are checked, the pair must repeat before a row is considered a duplicate.
• If every column is checked, only rows that match across every selected column are duplicates.

Do not choose columns merely because they are present. Choose the fields that answer: What makes two rows represent the same real-world record?

Choose the result you actually want

Make a reversible copy before removing anything

Microsoft describes Remove Duplicates as a permanent deletion operation. Excel's Undo command may rescue an immediate mistake, but it is not a data-governance plan. Before deduplicating:

1. Save the original workbook under a new name or copy the source sheet.
2. Turn the range into an Excel Table with Ctrl+T so headers and boundaries are explicit.
3. Add a temporary source-row column, such as OriginalRow, if you may need to trace a surviving record back to the import.
4. Highlight or count duplicates before deleting them. Compare the expected number with Excel's removal summary.
5. Decide the duplicate key and the winner rule in plain language.

A useful specification might be: “One row per Customer ID; keep the row with the latest UpdatedAt timestamp; if two timestamps tie, keep the row with the most populated contact fields.” That sentence is much safer than “remove duplicates.”

Keep the newest row for each ID

Suppose column A is Customer ID and column F is UpdatedAt. The worksheet workflow is:

1. Confirm that UpdatedAt contains real Excel date/time values, not mixed text that only looks like dates.
2. Select the entire table, not only the date or ID column.
3. Open Data > Sort and sort UpdatedAt from newest to oldest.
4. If timestamps can tie, add a second sort level, such as Approved = Yes first or CompletenessScore largest first.
5. Open Data > Remove Duplicates.
6. Check My data has headers.
7. Uncheck all columns, then check only Customer ID.
8. Click OK and compare the number removed with your pre-deletion count.

Because the latest row is now first inside each Customer ID group, it is the one Excel retains. To keep the oldest record, reverse the date sort. To keep the highest invoice amount, sort that amount largest to smallest before removing duplicates by the invoice's business key.

Do not sort only one column inside a multi-column dataset. That can detach IDs, dates, names, and amounts from one another. Expand the selection or work inside a Table so entire rows move together.

Keep the most complete row

Sometimes no date tells you which record is better. One row has the phone number, another has the address, and a third has both. Excel cannot infer “most complete,” but you can turn it into a score.

If the fields that matter are Name through Postal Code in columns B:F, add a helper column named CompletenessScore and enter:

=COUNTA(B2:F2)

Fill the formula down, sort CompletenessScore largest to smallest, and then remove duplicates using only the real record key, such as Customer ID. The row with more nonblank fields rises to the top and survives.

COUNTA treats any nonempty cell as populated, including a formula that returns an empty-looking string in some situations. If some fields matter more than others, use a weighted score instead:

=IF(B2<>"",1,0)+IF(C2<>"",2,0)+IF(D2<>"",2,0)+IF(E2<>"",1,0)

Here, the two critical fields receive two points each. Document the rule so another person can reproduce it. If equally complete rows can occur, add a date or source-priority sort as a tiebreaker.

Remove all members of a duplicate group

Some users do not want to keep any duplicate. For example, a payment-review list may require every repeated transaction ID to be quarantined, including the first occurrence. Remove Duplicates cannot produce that result because it always retains one row from each matching group.

For one key column, add a helper formula:

=COUNTIF($A$2:$A$10000,A2)

Filter the result:

• 1 means the key appears exactly once.
• Greater than 1 means the row belongs to a repeated group.

Delete or move the greater-than-one rows if the requirement is to remove every member. For a two-column key, use COUNTIFS:

=COUNTIFS($A$2:$A$10000,A2,$B$2:$B$10000,B2)

This distinguishes “remove extra copies but keep one” from “exclude every ambiguous record.” Those are different operations and should never share the same acceptance check.

When duplicates look identical but Excel does not remove them

Imported data often contains invisible differences. A product code may have a trailing ordinary space, a nonbreaking space copied from a web page, a line break, or another nonprinting character. The screen shows the same value while the underlying text differs.

Create a clean helper column instead of modifying the source immediately. For text in A2, a practical first pass is:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

This replaces the common nonbreaking space with an ordinary space, removes the nonprinting ASCII characters handled by CLEAN, and collapses ordinary extra spaces with TRIM. Microsoft specifically notes that TRIM by itself does not remove the nonbreaking space character, which is why the SUBSTITUTE step matters.

Then compare the cleaned helper values, paste them as values into a controlled output if appropriate, and deduplicate on the clean key. Also check these common causes:

• numbers stored as text in one row and numeric values in another
• dates imported in inconsistent regional formats
• leading apostrophes
• formula results versus literal text
• columns that were accidentally included in the duplicate key
• blank cells and spaces that alter Excel's reported unique and duplicate counts

If the source uses unusual Unicode spaces or control characters, inspect character lengths and codes rather than stacking random cleanup formulas. A normalization rule should match the source system and preserve meaningful distinctions.

Use UNIQUE when you do not want to delete source rows

In Microsoft 365 and versions of Excel that support dynamic arrays, UNIQUE can produce a clean output list while leaving the source intact:

=UNIQUE(A2:A10000)

The result spills into adjacent cells and updates as the source changes. For unique combinations of multiple columns, pass the multi-column range:

=UNIQUE(A2:C10000)

To return only values that occur exactly once—and exclude every member of a repeated group—use the optional third argument:

=UNIQUE(A2:A10000,,TRUE)

UNIQUE is ideal for a report, validation list, or separate clean view. It is not a substitute for row-priority logic when two rows share an ID but contain different dates or contact details. In that case, the rows are not identical across all fields, and you still need to define which one wins.

Use Power Query for repeatable imports, with two cautions

Power Query is usually the better home for a cleanup process that must run every week or month. It records steps, refreshes from the source, and lets you normalize columns before you identify duplicate keys. It can also keep duplicate rows for audit instead of deleting them immediately.

However, worksheet behavior should not be copied blindly into Power Query:

1. Power Query duplicate comparison is case-sensitive. Microsoft recommends transforming text to uppercase or lowercase first when case should not distinguish records.
2. Power Query does not guarantee that Remove Duplicates keeps the first visible instance. Query optimization and folding can change row behavior. A simple sort followed by Remove Duplicates is therefore not a reliable production rule for “keep newest.”

If the retained row matters, build that rule explicitly. One robust pattern is to normalize the key, group by that key, calculate the maximum date or rank within each group, and join or filter back to the qualifying row. Add a deterministic tiebreaker when two rows can share the same maximum date. Test the refresh with deliberately duplicated sample records before trusting it with production data.

A five-minute verification after deduplication

Do not stop at Excel's “duplicate values found and removed” message. Validate the result:

1. Record the row count before and after. The difference should equal the number you intended to remove.
2. Check several known duplicate groups, including the newest, oldest, blank, and tied examples.
3. Confirm that the chosen business key is unique with COUNTIF or COUNTIFS.
4. Verify that formulas, totals, PivotTables, named ranges, and data connections still point to the expected table.
5. Save the cleaned result as a new version and preserve the untouched source.

For high-impact data such as payments, inventory, customer consent, or regulated records, keep an exception sheet containing the removed rows, source-row IDs, the rule used, and the run date. That turns an irreversible click into an auditable transformation.

Define the winner before you delete the losers

Safe duplicate removal starts with two decisions: which columns define the same record, and which record should survive. Once those rules are explicit, the Excel tools become straightforward. Sort first for a one-off worksheet cleanup, use a helper score for completeness, use COUNTIF or COUNTIFS when an entire repeated group must be excluded, and use UNIQUE when you need a separate non-destructive list.

For recurring imports, move the logic into Power Query but account for case-sensitive comparison and its lack of a guaranteed first-row retention rule. Whatever method you choose, preserve the original and test known edge cases. The goal is not simply fewer rows; it is the correct rows, with a result you can explain and reproduce.

Sources and further reading

• Microsoft Support: Filter for unique values or remove duplicate values
• Microsoft Support: UNIQUE function
• Microsoft Learn: Working with duplicate values in Power Query
• Microsoft Support: TRIM function
• Microsoft Support: CLEAN function