Excel Remove Duplicate Rows Based on One Column Safely

How-to basics · Updated November 21, 2026

To remove duplicate rows based on one column in Excel for Microsoft 365 on Windows, select a cell in the full data range, then choose Data > Remove Duplicates. In the dialog, check only the column that defines a duplicate, confirm whether the range has headers, and click OK; Excel removes the later matching rows while keeping the first row it encounters.

What Excel Removes When You Choose One Column

The column you check is the rule Excel uses to decide whether two records are duplicates. The command removes entire rows from the selected data range, not just repeated values in that one column. This is useful when a customer ID, email address, order number, or other key should appear only once, while the surviving row still needs its associated name, date, or notes.

For example, suppose a table has columns for Customer ID, Name, and Last Contacted. If ID 104 appears in three rows, checking only Customer ID leaves one of those rows and removes the other two. Excel does not combine information from the removed rows into the one it keeps. If the three records contain different contact dates or notes, those details disappear with the removed rows.

That is why the operation is best understood as keeping one complete record per key, not merging records. Before removing anything, decide which record should survive and check whether the other rows contain information you need.

Steps to Remove Duplicates Using One Column

  1. Save a copy of the workbook or duplicate the worksheet. The command changes the data directly, so a backup is useful if you later discover that the wrong record was retained.
  2. Click a cell inside the full table or contiguous data range. Include all columns whose values must stay together in each record. If the data is a formatted Excel Table, click inside that table.
  3. Choose Data > Remove Duplicates. In the dialog, check My data has headers if the first row contains column names. This prevents Excel from treating a heading such as Customer ID as a record.
  4. Click Unselect All, then check only the column that identifies duplicates. For example, select Email if each email address should appear once, even when names or dates differ.
  5. Click OK and read Excel’s result message, which reports how many duplicate values were removed and how many unique values remain. Review the resulting records before continuing with other edits.

If Excel asks whether to expand the selection, choose the option that includes the adjacent data when you want entire records removed. Selecting only the key column can leave related information untouched or risk separating values from their records. Starting with the whole table helps make the intended operation clear.

Which Duplicate Record Does Excel Keep?

Excel keeps the first occurrence it encounters in the current order of the data and removes later rows with the same selected value or combination of selected values. It does not automatically choose the newest date, the most complete record, or the row with the most information. The current row order therefore determines which record survives.

If you want to retain the most recent record, sort the full dataset by the date column with newest dates first before using Remove Duplicates on the key column. Sort the entire table, not just the date column, so each row remains intact. Then remove duplicates based on the key. The first occurrence for each key will be the newest row in that sorted order. If you need to keep the oldest record, sort oldest to newest instead.

Sorting is not a substitute for checking the result. Equal dates, blank dates, or ties may still leave multiple plausible records. If the choice has business or audit consequences, add a helper field or review duplicate groups before deleting rows.

Find and Review Duplicates Before Deleting

When the data is valuable, identify repeated keys first rather than immediately removing them. You can use conditional formatting to highlight duplicates: select the key cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in that selected range, but it does not remove rows or tell you which full record should be kept.

A helper formula can mark later repeats in a list. With identifiers in A2:A7 and the first data row in row 2, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down. The first appearance returns FALSE; each later appearance of that identifier returns TRUE. For example, if A2:A7 contains 104, 105, 104, 106, 105, 107, the results are FALSE, FALSE, TRUE, FALSE, TRUE, FALSE. You can filter the helper column to TRUE to inspect the rows before deciding what to delete.

This formula is case-insensitive, as is the usual duplicate-checking behavior in Excel, so values such as North and NORTH may be treated as matches. Extra spaces can cause values that look alike to behave differently. For instance, a key with a trailing space may not match the same key without it. If that is possible in the source data, clean or normalize the key values and review them before removal.

One Column Versus Multiple Columns

Use one checked column when that field alone defines a duplicate. Use multiple checked columns when a record is only a duplicate if several fields match together. In the Remove Duplicates dialog, checking Customer ID and Order Date means Excel compares the combination of those fields; two rows with the same ID but different dates can both remain.

Consider a sales list in which one customer can place several orders. Removing duplicates using Customer ID alone would reduce the list to one row per customer and discard other orders. If the true record is one order per customer per date, checking both Customer ID and Order Date may better fit the data. Choose columns based on what a unique record means in the real situation, not merely on which column happens to repeat.

Similarly, checking every column only removes rows that match across all selected fields. It will not remove two records that share an email address but differ in a note or timestamp. The column selection is the definition of sameness for this operation.

Common Problems and Data Checks

  • The header disappeared: Check My data has headers in the dialog when the first row contains headings. If the data has no header row, leave it unchecked.
  • Excel removed a row you wanted: The command keeps the first row in the current order, not necessarily the best one. Undo immediately with Ctrl+Z on Windows, or Command+Z on Mac, then sort or review the groups before trying again.
  • Rows that look different were treated as duplicates: Check whether differences are only capitalization or formatting. Excel’s duplicate comparison is not a reliable way to preserve case-only distinctions.
  • Rows that look the same were not removed: Check for leading or trailing spaces, inconsistent data entry, or values stored in different forms. Clean the key column first and inspect a sample of the cleaned values.
  • Only the key cells changed: Undo if necessary and repeat with the full data range selected. The goal is to remove entire duplicate records, not merely clear repeated cells.

Remove Duplicates is a direct edit, but it can usually be reversed immediately with Undo if no intervening actions have made that impractical. It is not a good substitute for preserving a source copy. Excel’s result count is a useful check, but it does not confirm that the correct member of each duplicate group was retained.

Alternatives: Formulas and Power Query

If you need a separate list of unique values from a single column, rather than complete rows from a table, the UNIQUE function can return that list. With identifiers in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. The result spills into cells below and contains each distinct identifier once. UNIQUE is available in Microsoft 365 and Excel 2021 or later; it returns values from the specified range and does not by itself preserve the other fields from each original record.

Power Query is useful when you repeat the same cleanup on refreshed data. Select a cell in the source data and choose Data > From Table/Range. In the Power Query Editor, select the key column, then choose Home > Remove Rows > Remove Duplicates. Keep the other columns in the query so each surviving row retains its associated fields, then use Home > Close & Load to return the result to Excel. When the source changes, refresh the query rather than repeating every cleanup step manually.

For either method, be clear about which record should survive. Power Query’s duplicate-removal step should not be treated as a guarantee that a particular row, such as the newest one, will always be chosen unless the query is designed and checked to establish the desired order. For a one-time cleanup, the standard dialog is usually the quickest option; for recurring imports, a saved query is often easier to repeat consistently.

Mac and Excel for the Web

In Excel for Microsoft 365 on Mac, the Remove Duplicates command is also available from the Data tab, although the dialog’s appearance can vary slightly by release. Select the full range, use the same one-column selection logic, and use Command+Z rather than Ctrl+Z to undo. Check the header option carefully before confirming.

Excel for the web also offers duplicate removal for supported workbook ranges through the Data tab. The command and dialog may differ slightly from the Windows desktop version, and feature availability can depend on the workbook and current web release. If the command is unavailable or you need a repeatable transformation, open the workbook in the desktop app or use a formula-based review method.

Frequently Asked Questions

Can Excel remove duplicate rows based on just one column?
Yes. Select the full data range, open Data > Remove Duplicates, and check only the column that defines a duplicate. Excel removes whole later rows that repeat that value and keeps the first occurrence.

Does Remove Duplicates keep the first or last row?
It keeps the first occurrence in the current row order and removes later matching rows. To keep the newest or oldest record, sort the entire table by the relevant date before removing duplicates.

Can I remove duplicates without deleting the original data?
Make a copy of the worksheet or workbook first, or use UNIQUE to create a separate list of distinct values. Power Query can also load a cleaned result separately from the source, depending on how you set up the query.

Why does Excel still show duplicate-looking values?
They may contain extra spaces or other differences in the underlying text. Conversely, capitalization differences may not make values distinct for duplicate checking. Inspect and normalize the key column before running the command again.

Can I remove duplicates based on two columns instead?
Yes. In the Remove Duplicates dialog, check both columns. Excel then treats rows as duplicates only when the values match across the selected columns, rather than matching on either field independently.

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.