Remove Duplicates in Excel and Keep One Matching Row

To remove duplicates and keep one record in Excel for Microsoft 365 on Windows, select your full data range, then choose Data > Remove Duplicates. In the dialog, check the columns that define a duplicate and select OK; Excel removes matching rows and keeps the first occurrence in the current order.

What “remove duplicates” means in Excel

Excel’s Remove Duplicates command is a data-cleaning tool, not a way to mark repeated entries for review. It deletes duplicate rows from the selected data range, leaving one row for each combination of values in the columns you choose. The remaining row is normally the first matching row from top to bottom at the time you run the command.

“Duplicate” depends on your chosen comparison columns. If you select every column, Excel removes rows only when their values match across the entire row. If you select just an ID column, it treats rows with the same ID as duplicates—even if other columns, such as names or dates, differ. That distinction matters when you want one complete record per customer, product, or transaction rather than just a list of unique values.

The command changes the data in place, so it is different from highlighting duplicates or displaying a separate unique list. It does not combine information from repeated rows, calculate totals, or decide which record is most accurate. It simply retains one matching row and removes the others.

Prepare the data before deleting rows

Make a copy of the worksheet or workbook before removing duplicates, especially if the records are difficult to recreate. You can usually undo the operation immediately with Ctrl+Z on Windows or Command+Z on Mac, but relying on Undo is risky if you continue editing, close the file, or need to preserve the original data for auditing.

Check that the data is arranged as a single table: one header row, one record per row, and consistent fields in each column. Remove blank rows within the dataset if they could interrupt your selection. If the range is an Excel table, click inside it and confirm that the table includes all the records you intend to clean. Filtering or sorting the data first can also help you inspect which record will be kept.

Confirm the key: Decide whether a duplicate means the same email address, order number, full row, or another combination of fields.
Check for inconsistencies: Extra spaces, spelling differences, and inconsistent codes can make records that look alike count as different.
Preserve the source: Save a copy or duplicate the worksheet before making an irreversible change to the working data.

Steps to remove duplicates and keep one

Select the complete data range, including all columns whose information must stay together. If the range has headers, include the header row.
On the ribbon, choose Data > Remove Duplicates.
In the dialog, select My data has headers if the first row contains column names. This keeps the headings out of the comparison.
Under Columns, check the fields that define a duplicate. Choose all columns for identical-row duplicates, or only the key field or fields for one record per key.
Select OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the result and save the workbook.

If Excel asks whether to expand the selection or continue with the current selection, choose Expand the selection when you want entire records removed together. Continuing with only one selected column can separate values from the rest of their row, leaving the table misaligned. In Excel for Mac, the equivalent command is available from the Data tab under Remove Duplicates; dialog wording and ribbon layout can vary slightly by version. Excel for the web also provides a Remove Duplicates command on the Data tab, though its interface may differ from the Windows desktop dialog.

Choose the right columns for comparison

The most important decision is which columns to check. Imagine a list with Customer ID, Name, Email, and Last Contact Date. If you select all four columns, two rows with the same customer ID but different contact dates are not duplicates. If your goal is one row per customer, select Customer ID as the comparison column while ensuring the full range remains selected. Excel will remove the other row or rows for a repeated ID.

For a more precise key, select multiple columns. For example, an order line might be identified by the combination of Order Number and Product Code. Selecting both fields removes repeated copies of the same order line, while preserving separate products under the same order number. Before deleting, ask whether two records with the same key but different details are truly redundant. The tool will not reconcile those differences; it will keep one version and discard the rest.

When the first record is not the one you want to keep, sort the data before using the command. For instance, sort Last Updated from newest to oldest to put the latest row first, then remove duplicates using the relevant ID. Sorting changes which matching row appears first, so check the sort order and any tie-breaking rule before you proceed. If the choice depends on several conditions, such as the latest date and then the highest quality score, review the records manually or use a more controlled workflow.

A concrete example: one row per email address

Suppose a contact list has columns for Name, Email, and City. The email address is the identifier, and a few people appear more than once because their details were imported from two lists. Select the whole three-column range, open Data > Remove Duplicates, and check only Email in the column list. Excel keeps one complete row for each email address and removes the other rows with that email.

If one repeated email has two different names or cities, the tool does not merge them. It keeps whichever matching row occurs first and deletes the other. Sort or review the list first if the newer address details should take priority. Also consider whether email is a reliable key: two people may share an address, and one person may have changed addresses. The right comparison fields depend on what a record represents in your data.

What happens to the original data

Remove Duplicates edits the selected range directly. It does not create a separate output list, and it does not preserve deleted rows in a hidden history. The command removes repeated records rather than leaving blank spaces in their place, so the remaining rows move together. Other columns within the selected range stay attached to their corresponding records.

The operation is not fuzzy matching. For example, Northwind and Northwind (with a trailing space) may be treated as different values, as can names with different spellings or punctuation. If visually identical entries are not being removed, inspect for leading or trailing spaces, inconsistent abbreviations, and data imported in different formats. Cleaning those issues first may be necessary. Be cautious with broad transformations: standardizing text can itself change meaningful distinctions.

Excel does not ask which duplicate row to keep or compare rows for quality. The first matching record in the present order survives. If you run the command again, it will not restore anything deleted in the earlier run. For that reason, the result should be checked against the original or a backup when the data is important.

Alternatives for reviewing or repeating the cleanup

If you want to see unique values without deleting the source, use the UNIQUE function in Microsoft 365 or Excel 2021 and later. With values in A2:A8 consisting of Maple, Oak, Maple, Pine, Oak, Birch, and Pine, enter this formula in an empty cell outside the source list: =UNIQUE(A2:A8). It returns Maple, Oak, Pine, and Birch once each, in the order of their first appearance. The formula produces a separate result and leaves the source cells unchanged; make sure the cells below the formula are clear so the results can spill.

For repeated cleanups or larger imported datasets, Power Query can provide a repeatable process. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range (create a table if prompted). In the Power Query Editor, select the column or columns that define duplicates, then choose Home > Remove Rows > Remove Duplicates. Choose Home > Close & Load to return the result to Excel. Power Query keeps the source data separate from the loaded result, and you can refresh the query after updating the source. If the exact surviving row matters, design and verify the query’s sorting and selection logic rather than assuming duplicate removal will choose a preferred record.

Use the in-place command for a quick one-time cleanup when the keep-first rule is acceptable. Prefer a formula or Power Query when you need to retain the original data, make the result easier to review, or repeat the same transformation later.

Common problems and how to avoid them

Only one column was selected: If Excel offers to expand the selection, expand it so whole rows are handled together. In the Remove Duplicates dialog, select only the comparison columns you need.
Excel removed too many records: You may have selected a broad key such as City or Last Name. Undo if possible, restore the backup if necessary, and use a more specific key or combination of columns.
Some apparent duplicates remain: Compare the actual text and values for spaces, punctuation, spelling, and inconsistent formats. Matching by sight does not guarantee matching cell contents.
The wrong version of a record survived: The command keeps the first match in the current order. Undo or restore the source, sort records so the desired version comes first, and run the command again.
The header was treated as a record: Include the header row in the selection and select My data has headers in the dialog.

After the cleanup, check the row count and inspect several retained records, especially where different details existed for the same key. For business, research, or audit data, keeping a copy of the input and recording which columns defined a duplicate makes the result easier to explain and reproduce.

Frequently Asked Questions

Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first matching row in the current order and removes later matches. Sort the data before running it if you want a particular record to appear first.

Can I remove duplicates based on one column but keep the other columns?
Yes. Select the full data range so entire records stay together, then check only the key column in the Remove Duplicates dialog. Excel keeps one complete row for each distinct key value.

Can I undo Remove Duplicates?
Usually, you can immediately press Ctrl+Z on Windows or Command+Z on Mac. If Undo is no longer available, restore a saved copy or use the original source; the command does not keep deleted rows in the worksheet.

Does Remove Duplicates delete the original list when I use UNIQUE?
No. UNIQUE displays a separate dynamic result and leaves the source list intact. The Remove Duplicates ribbon command, by contrast, edits the selected data in place.

Why does Excel keep duplicates that look identical?
The underlying entries may differ because of spaces, spelling, punctuation, or other inconsistencies. Inspect the relevant fields and standardize them carefully before trying the cleanup again.

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.