Excel: Specify Which Duplicate to Remove?

How-to basics · Updated November 21, 2026

Yes—but Excel’s Remove Duplicates command does not let you click a particular occurrence to delete. You choose which columns define a duplicate, then Excel keeps the first matching row in the current order and removes later matches; sort the rows first if you want a particular record to survive.

What Excel means by a duplicate

In Microsoft Excel for Microsoft 365 on Windows, go to Data > Remove Duplicates to open the duplicate-removal dialog. Its column checkboxes determine what counts as a match. If you select every column, Excel removes rows whose values match across all those columns. If you select only a key column, such as Customer ID, rows with the same ID count as duplicates even when their names, dates, or other details differ.

This distinction matters because Excel removes whole rows, not just the repeated cell or the columns you checked. For example, if two rows share a Customer ID but have different order dates, selecting only Customer ID means Excel treats one entire row as a duplicate and deletes it. The other columns help define the records that disappear, but they do not remain separately attached to the surviving row.

So the command answers two questions: which fields identify a duplicate, and which matching row appears first in the current list? It does not provide a separate rule such as “keep the newest date” or “delete the row with a blank address.” You control that choice by organizing or flagging the data before removing rows.

Choose the record you want to keep

Excel keeps the first matching record it encounters as it scans the selected range from top to bottom. To keep the oldest, newest, highest-priority, or most complete record, sort the full dataset so that the preferred row comes first. Then run Remove Duplicates using the column or columns that define a match.

For instance, suppose a list has Customer ID, Last Updated, and Status. If there are several records per customer and you want the latest record, sort the whole list by Last Updated from newest to oldest, then remove duplicates using Customer ID only. The first row for each customer is now the newest one, so it is the row Excel keeps. If you want the earliest record instead, sort the date from oldest to newest.

  • Keep newest: Sort the date or timestamp newest to oldest.
  • Keep oldest: Sort the date or timestamp oldest to newest.
  • Keep the highest-ranked entry: Sort the priority or score from highest to lowest.
  • Keep the most complete entry: Sort or otherwise identify completeness before deleting; a blank-versus-filled choice is not built into Remove Duplicates.

When sorting, select the entire table or use a properly structured Excel table. Sorting just the date column can detach dates from their names and IDs, corrupting the records. If several sorting rules matter, use a multi-level sort—for example, sort Status by priority first and Last Updated second—so the desired record appears first within each group.

Remove duplicates step by step

  1. Save a copy of the workbook or duplicate the worksheet if you may need to compare the original data later.
  2. If you need to choose which record survives, sort the full dataset first. Confirm that every field in a record moves together.
  3. Click a cell in the range or table. Check that the first row contains headers; if it does, the dialog should treat those labels as headers.
  4. Choose Data > Remove Duplicates.
  5. In the dialog, select the columns that together define a duplicate. For a duplicate customer record, that might be Customer ID alone; for a repeated transaction, it might be Account ID, Transaction Date, and Amount.
  6. Check the header setting if it is shown, verify the selected columns, and confirm the operation.
  7. Read Excel’s summary of how many duplicate values were found and removed and how many unique values remain. Check representative rows before continuing with other edits.

In many Excel layouts, clicking inside a table is enough for the command to use the table’s data. For a plain range, selecting the data first helps avoid including unrelated cells. The dialog’s column list is the important check: make sure it reflects your intended definition of a duplicate, not simply every column by default.

Use the right columns to define a match

A duplicate is not necessarily an identical row. If you select only Email Address, two people with the same email are treated as a match even if their names or signup dates differ. If you select Email Address and Signup Date, Excel removes only rows matching on both fields. In general, the checked fields act together: a row is considered a duplicate when the selected-column combination matches an earlier row.

Use the narrowest set of fields that accurately identifies the repeated record. A product name alone may not identify a unique item if separate products share a name; a product code may be a better key. Conversely, selecting every field can leave repeated customers in place if one row has a different phone number or spelling, even though you intended to keep only one row per customer.

  • One-column key: Select Order Number when each order number should occur only once.
  • Combined key: Select Employee ID and Pay Period when a person can have many records but should have only one for each period.
  • Entire-row match: Select all relevant fields when you want to remove only records that match across the full dataset.

Before deleting, ask what makes two records the same in the real-world task. A matching name may not be enough; a matching ID often is. If your key fields contain inconsistent spelling, extra spaces, or different representations of the same value, decide whether to standardize them first. Removing duplicates cannot infer that two differently written entries refer to the same person or item.

Flag duplicates when you need more control

If you want to review the rows Excel would remove, add a helper column rather than deleting immediately. For example, with customer IDs in A2:A7 and records arranged in the order you want to keep them, enter the following in D2 and fill it down. The result is TRUE for a repeated ID after its first occurrence and FALSE for its first occurrence.

=COUNTIF($A$2:A2,A2)>1

With A2:A7 containing C104, C104, C207, C104, C207, and C311 in that order, the formula returns FALSE, TRUE, FALSE, TRUE, TRUE, FALSE. It counts each ID from the first data row through the current row, so the first appearance has a count of one and later appearances have a count greater than one. Filter the helper column for TRUE to inspect the rows marked for removal; you can delete them manually or rerun the process on a copy after checking.

The order of the rows determines what the formula treats as the first occurrence. To keep the most recent record, sort the complete dataset by its date from newest to oldest before filling or recalculating the helper column. This approach is useful when the rule is more specific than “keep the first row,” because it lets you inspect the proposed removals before changing the data.

What Remove Duplicates does—and does not do

Remove Duplicates deletes matching rows from the selected data range or table; it is not merely a highlight or a temporary filter. It does not create a separate list of removed records, and it does not merge information from duplicate rows into the survivor. If one copy has a phone number and another has a different phone number, the command will not combine them into a single completed record.

It is also different from Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Conditional formatting identifies matching cell values visually, but it does not remove records and its selection is not the same as choosing matching columns in Remove Duplicates. Likewise, a filter can hide rows from view without deleting them. Use those options when you want to inspect or display matches rather than permanently change the data.

On Windows, Ctrl+Z can undo the removal immediately. On Mac, use Command+Z. Undo is helpful, but it should not replace keeping a backup: later workbook actions can make it harder to return to the exact earlier state. In Excel for Mac, the command is also on the Data tab, though the ribbon layout and dialog appearance can differ. In Excel for the web, available commands and their placement can vary with the current interface, so check the Data tab; the same key principle applies wherever the command is offered: the first matching row is retained.

Common mistakes to avoid

  • Checking the wrong columns: Selecting only a name may remove distinct people with the same name. Select a reliable identifier or a combination of fields.
  • Forgetting row order: Excel does not decide that the newest or most complete row is best. Sort or flag records to make that choice explicit.
  • Sorting only one column: Always sort the entire table so every value stays with its record.
  • Expecting a merge: Removing a row does not fill blanks or reconcile conflicting values from the removed row.
  • Skipping a review copy: The command changes the data. Preserve an original copy if the removed information could matter.

Another useful check is to verify the result against the rule you intended. If the goal was one row per customer, count the remaining customer IDs or inspect several customers that previously had multiple entries. If the goal was identical full rows, compare the outcome against the exact fields you selected. A removal count confirms that Excel found matches under its selected-column rule; it does not confirm that the rule was the right one for your data.

Frequently Asked Questions

Can I tell Excel to delete the second duplicate instead of the first?
Not directly in the Remove Duplicates dialog. Reorder the records so the row you want to keep comes first, or use a helper column to mark later occurrences and review them before deletion.

Does Excel keep the first or last duplicate?
It keeps the first matching row in the current order and removes later matching rows. Sort the complete dataset before using the command if you want a different record to be retained.

Can I remove duplicates based on just one column?
Yes. In the Remove Duplicates dialog, select only that column. Excel then removes entire rows that repeat a value in that field, even if other columns differ.

Can I remove only completely identical rows?
Yes. Select all columns that define the row when you use Remove Duplicates. Rows that differ in any selected field are not a full match under that selection.

Does highlighting duplicate values delete anything?
No. Conditional Formatting can mark repeated cell values visually, but it leaves the data in place. Use Data > Remove Duplicates to delete matching rows, or use filtering and a helper column to review them first.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.