Excel Remove Duplicates: Keep One Copy of Each Row

Yes. In Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates; Excel keeps the first matching record and removes later matches. Choose which columns define a duplicate: selecting every column removes repeated rows, while selecting just one column removes later rows that share that value—even if other details differ.

What Excel means by a duplicate

Excel does not decide what counts as a duplicate based on your intention; the columns you select define the comparison. If you select all columns in a table, two rows count as duplicates only when their values match across all those columns. If you select only Email, rows with the same email count as duplicates even when the names, dates, or other fields are different.

This distinction matters when a repeated identifier may be attached to legitimate differences. For example, two orders from the same customer share a customer ID but are not duplicate orders. Removing duplicates based on that ID would delete one order. On the other hand, if a customer list should contain one record per email address, selecting Email may be appropriate.

The command removes the later matching rows from the selected data; it does not merely hide them or mark them. Excel keeps the first occurrence in the current row order. If you want a different record to survive—such as the newest entry—sort the data into the order you want before removing duplicates, or use a method that lets you review the results first.

Remove duplicates step by step

Save a copy of the workbook or duplicate the worksheet. Removal changes the data, and Ctrl+Z may not be available after later actions.
Click a cell inside the data range. If the range has clear headers and no blank rows or columns breaking it up, Excel can usually identify the table area. Otherwise, select the full range yourself.
Choose Data > Remove Duplicates on the ribbon.
In the dialog, confirm whether My data has headers is checked. If the first row contains field names such as Email or Order ID, leave it checked so Excel does not treat those labels as data.
Check the columns that should determine whether records are duplicates. Use Select All if the entire row must match; clear all boxes and check just the relevant field if one column defines uniqueness.
Select OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the surviving records, especially if the selected key had conflicting details.

For a table, the command applies to its rows and preserves the table structure. If the data is an ordinary range, make sure you selected every column belonging to each record. Removing duplicates from just one column can leave names and other fields out of alignment with the values they originally belonged to.

Choose the right columns before deleting

Suppose a list has columns for Customer ID, Name, Email, and City. If you select all four columns, Excel keeps one copy only when all four values match. If you select Customer ID alone, it keeps the first row for each ID and deletes every later row with that ID, even if the later row has a different email or city. This may be correct for a one-row-per-customer list, but it can also discard an update or expose inconsistent source data.

A useful way to decide is to ask what one record represents and what field, or combination of fields, should be unique:

For repeated imports of the same complete row, compare all columns.
For one row per person, use a stable identifier such as a customer number rather than a name that several people may share.
For transaction data, a transaction ID is usually a better key than the customer ID, because one customer can have many valid transactions.
If a record is unique only by a combination, select all columns in that combination, such as Invoice Number and Line Number.

When duplicate keys have different associated details, pause before deleting. Filter or sort the records to inspect the conflicts, decide which row should be authoritative, and retain a copy of the original data. Remove Duplicates is a cleanup command, not a reconciliation tool: it will not merge fields, choose the most complete record, or explain why two records disagree.

How Excel keeps the surviving row

For each repeated set defined by the selected columns, Excel keeps the first row it encounters and removes subsequent matches. “First” means the row that appears earliest in the current order, not necessarily the oldest, newest, most complete, or best record. If you need the newest date to survive, sort by date in descending order first. If there are ties or multiple fields that determine priority, inspect the order and criteria carefully before deletion.

Cell formatting is not a reliable way to distinguish records. Different colors, fonts, or number formats do not make otherwise matching values unique. Conversely, values that look alike on screen can differ in the underlying data. A trailing space in “North ”, for example, can keep it from matching “North”; numbers stored as text can also behave differently from numeric values in related operations. Clean inconsistent text or data types when appropriate, then inspect the results rather than assuming a visual match guarantees a duplicate match.

Blank cells deserve attention too. If the selected key column contains blanks, multiple rows with blank keys may be treated as duplicates for that comparison, so later rows could be removed. Decide whether blank-key records should be kept, corrected, or excluded before running the command.

Preview alternatives that preserve the source

If you are not ready to delete rows, create a separate unique list instead. In Excel 2021 or Microsoft 365, the UNIQUE function returns distinct values or rows in a spilling result, leaving the source range untouched. With headers in row 1 and records in A2:C6, where the columns are ID, Name, and City, enter =UNIQUE(A2:C6) in an empty cell. In this example it returns four distinct full rows: the repeated Maya Reed–Bristol row appears once, while the Maya Reed–Bath row remains because it is different across the selected columns.

That formula compares whole rows in the supplied range. It does not mean “keep one row per ID” when the other columns differ. For one result per key, decide which associated row should be retained; a plain UNIQUE over the full table will preserve distinct combinations, not merge records by ID. The results spill into neighboring cells, so leave enough empty space below and to the right.

Another built-in option is Data > Advanced. The Advanced Filter can copy unique records to another location, using the selected fields to define distinct rows, so the original list stays in place. It is useful for a one-time copy, though the copied result does not automatically update like a formula result can.

When Power Query is a better fit

For recurring imports or larger repeatable cleanup tasks, Power Query can document the transformation so you can refresh it when the source changes. In the Power Query Editor, select the column or columns that define uniqueness, then use Home > Remove Rows > Remove Duplicates. Load the result to a worksheet or data model rather than repeatedly editing the original source.

There is an important caveat: Power Query does not guarantee which occurrence of a duplicate set it will keep. If a particular row must survive, do not assume that sorting immediately before Remove Duplicates will always enforce that choice. Design the query so the desired record is selected explicitly—for example, group by the key and apply a deliberate rule to choose the correct record—or check the current Microsoft documentation for the behavior relevant to your query. Power Query is valuable for repeatability, but it does not infer which conflicting record is better.

Common problems and safe checks

The wrong rows disappeared: You may have selected too few columns. Undo immediately with Ctrl+Z on Windows, then rerun the command with the intended comparison fields. On Mac, use Command+Z.
Nothing was removed: The rows may only look identical. Check spaces, hidden characters, number-versus-text differences, and whether every selected column actually matches.
Headers were included as records: Reopen the command and check My data has headers when the first row contains labels.
Related columns no longer line up: The selected range may not have included the entire record. Undo if possible and select the full data range before trying again.
The first copy is not the one you wanted: Put the preferred row first using a considered sort or another selection process, then remove duplicates. Keep a backup so you can compare.

After the operation, verify more than the message showing the number removed. Check a few keys that were repeated, compare the remaining row count with expectations, and confirm that totals or other important summaries still make sense. For critical data, run the cleanup on a copy and preserve the unmodified source for reference.

Frequently Asked Questions

Does Excel Remove Duplicates leave one copy?
Yes. It retains the first row for each set of matches defined by the checked columns and deletes later matching rows.

How do I remove duplicates based on one column but keep the whole row?
Choose Data > Remove Duplicates, clear the other column checkboxes, and check only the key column. Excel removes later matching records as complete rows; it does not remove just the cell in that column.

Can I keep the last duplicate instead of the first?
Remove Duplicates keeps the first occurrence in the current order. Arrange the rows so the record you want appears first before running it, and verify the result. Keep a copy because the operation deletes rows.

Will Remove Duplicates delete rows that only look the same?
It depends on the actual values in the selected columns, not their appearance or formatting. Extra spaces, different underlying values, or different values in an unchecked or checked field can affect what matches.

Can I undo Remove Duplicates?
Ctrl+Z on Windows or Command+Z on Mac may undo it if the action is still in the undo history. Do not rely on undo as your only safeguard; save a copy before changing important data.

Does UNIQUE delete the original rows?
No. UNIQUE returns a separate dynamic-array result and leaves the source unchanged. It is available in Excel 2021 and Microsoft 365; the result updates when the source range changes.

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.