Excel Remove Duplicates from a Column: What to Know

How-to basics · Updated November 21, 2026

To remove duplicates from a column in Microsoft Excel for Microsoft 365 on Windows, select the column or the full data range, then choose Data > Remove Duplicates, confirm whether your data has headers, select the columns Excel should check, and click OK. Excel keeps the first occurrence of each matching value and removes later ones, so make a copy first if you may need the original rows.

What Remove Duplicates does

Remove Duplicates is a built-in data-cleaning command. It changes the selected data by deleting duplicate entries, rather than creating a separate cleaned list. If a column contains North, South, North, Excel keeps the first North and removes the later one. The remaining entries stay in their existing order; Excel does not automatically sort them.

The command can work on a single column or on a wider range of related data. That distinction matters. If you remove duplicates from a list of email addresses, you may want to remove repeated addresses only. If each row contains an order number, customer, and date, you may instead want Excel to remove rows only when the selected combination of fields repeats. Choosing the wrong range or comparison columns can remove information you meant to keep.

Prepare the data before removing duplicates

First, decide what counts as a duplicate for your task. A repeated name might be a duplicate in a mailing list, but the same customer name could legitimately appear on several separate orders. In that case, the name alone is not enough to identify duplicate records; you may need to compare several columns, such as customer, order number, and date.

Before changing the source data, consider these checks:

  • Make a backup. Copy the worksheet or duplicate the workbook if the original records need to remain available. You can also copy the source column to a new sheet.
  • Check the headers. A clearly labeled first row helps you identify the fields in the Remove Duplicates dialog.
  • Look for inconsistent entries. A trailing space, a different spelling, or a number stored as text can make entries look alike while Excel sees them as different values.
  • Keep related columns together when needed. If you need to delete complete records, select the entire data range, not just one field.

Removing duplicates is usually undoable immediately with Ctrl+Z on Windows or Command+Z on Mac. However, do not rely on Undo as your only backup if you will continue editing or close the workbook.

Step-by-step: remove duplicates from one column

  1. Select the cells in the column, including the header if there is one. If the data is part of a structured table, click a cell in the table or select the relevant table range.
  2. Open the Data tab on the ribbon and choose Remove Duplicates.
  3. In the dialog, check My data has headers if the first selected row contains a column label. If you selected only data cells, leave that option unchecked.
  4. In the columns list, select the column or columns Excel should use to identify duplicates. For a single-column list, select only that column.
  5. Click OK. Excel reports how many duplicate values it removed and how many unique values remain. Review the result before continuing with other edits.

If Excel asks whether to expand the selection, pause and consider what you want removed. Choosing to expand usually includes adjacent data so entire rows can be handled together; choosing to continue with the current selection limits the operation to the selected cells. If neighboring columns contain information tied to each entry, expanding the selection is often the safer choice for removing whole records. Read the dialog carefully rather than accepting a choice automatically.

Choose whether to check one column or whole rows

When you select multiple columns, the checkboxes in the dialog define the duplicate test. Excel keeps the first row for each value or combination of values in the checked columns. Unchecked columns do not determine whether two rows count as duplicates, but their data can be removed along with a duplicate row if the selected range covers them.

For example, imagine a range with Customer, Order ID, and Region. If you check only Customer, two rows for the same customer count as duplicates even when they have different order IDs. Excel retains the first matching customer row and removes the other selected row. If you check all three columns, rows count as duplicates only when their customer, order ID, and region values all match. That is a different result and may be more appropriate when you want to remove repeated records rather than repeated customers.

Use the narrowest comparison that accurately describes the duplicate. Comparing too few fields can discard legitimate records; comparing too many can leave records that are duplicates for your actual purpose. When unsure, test the approach on a copy of the data and compare the before-and-after row counts.

How Excel decides two values are duplicates

Remove Duplicates compares cell contents, not just how the cells look. Formatting such as font color or number format is not a reliable way to make otherwise matching entries distinct. Capitalization alone generally does not make text values separate for this command. However, extra spaces or a spelling difference can matter: Harbor Cafe and Harbor Cafe may not be treated as the same entry because the second value has a trailing space.

Numbers can also be inconsistent. A cell containing the number 25 and a cell containing text that looks like 25 may behave differently in comparisons. Dates can be stored as actual Excel date values or as text strings, and mixed formats may complicate a cleanup. If two entries appear identical but remain after the command, inspect the underlying data and check for spaces, text-versus-number differences, or inconsistent dates.

Formula cells are evaluated by their results for the purpose of identifying matching entries; the formulas themselves may differ while producing the same displayed result. Removing a duplicate row still removes that row from the selected range, including its other contents. That is one reason to check the full row and the comparison columns before confirming.

Keep the source intact with UNIQUE

If you want a distinct list without deleting anything from the original column, use the UNIQUE function instead. It returns a separate list of distinct values and leaves the source data untouched. This is useful for building a summary, preparing a dropdown source list, or checking what values appear in a column before deciding whether to alter the original.

With values in A2:A10, enter =UNIQUE(A2:A10) in an empty cell. Excel returns the distinct entries in the cells below the formula, provided there is room for the results to spill. For example, if A2:A10 contains Red, Blue, Red, Green, Blue, Red, Yellow, Green, Red, the formula returns Red, Blue, Green, and Yellow: four distinct values. UNIQUE is available in Microsoft 365 and Excel 2021 or later. In Excel for the web, it is also available where dynamic array functions are supported.

Unlike Remove Duplicates, UNIQUE does not delete source rows. Its result can update when the source values change, which is helpful for a live list. If you need a static list, copy the formula results and use Paste Special > Values in a new location. Remember that a formula result is not the same as cleaning the original records; choose based on whether you need a new list or a changed dataset.

Common problems and how to avoid them

  • Excel removes too many rows. You may have checked only a field that repeats naturally, such as customer name, rather than the combination of fields that identifies a duplicate record. Undo promptly, then rerun the command with the correct columns selected.
  • Nothing seems to be removed. The entries may differ by spaces, spelling, or data type. You can inspect suspicious cells, use Find and Replace carefully, or normalize the data on a copy before trying again.
  • Adjacent data becomes misaligned. This can happen when a single column is cleaned independently even though neighboring cells belong to the same records. Undo if possible and repeat the operation on the full range, using the intended comparison columns.
  • The header is treated like a value. Check My data has headers when the selected range includes a header row. If the selection begins with ordinary data, do not mark its first entry as a header.
  • The output order changes unexpectedly. Remove Duplicates is not a sorting command. It keeps the first encountered instance and removes later matches; use the Sort command separately if you need a particular order.

For a large or important dataset, consider adding a temporary helper column or copying the data to a review sheet before deleting records. A separate check makes it easier to catch a mistaken duplicate rule before it affects the original list.

Excel for 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 ribbon layout can vary slightly by version and window size. The dialog serves the same purpose: identify whether the range has headers and choose the columns that define a duplicate. Use Command+Z to undo on Mac rather than the Windows Ctrl+Z shortcut.

Excel for the web includes a Remove Duplicates option on the Data tab in current versions, but the exact ribbon arrangement can differ from the desktop app. If you cannot find the command or need a more controlled cleanup, open the workbook in the desktop Excel application. UNIQUE formulas are another option for producing a separate list in supported web versions, without deleting the source entries.

Frequently Asked Questions

Does Remove Duplicates keep the first or last value?
It keeps the first occurrence in the selected range and removes later matching entries. If you want a particular record to remain, sort or arrange the data so that record appears first before removing duplicates, and retain a backup in case the result is not what you expect.

Can I remove duplicates from one column without deleting the other columns?
Yes, but be careful: removing duplicates from only one selected column can leave neighboring data untouched and no longer aligned with the cleaned list. To remove complete records based on one field, select the full data range and check only that field in the duplicate dialog.

How do I remove duplicate rows but keep unique values in a column?
Select the entire table or range and check all columns that together define an identical row. Excel then removes later rows whose checked values match the first occurrence. If you check just one column, rows with the same value in that field can be removed even when other fields differ.

Can I highlight duplicates before deleting them?
Yes. Select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to mark repeated values visually. This can help you inspect the data first, but highlighting does not remove entries and does not replace checking the correct columns in the Remove Duplicates dialog.

Can I get a unique list without changing the original column?
Yes. In Microsoft 365 or Excel 2021 and later, enter =UNIQUE(A2:A10) in an empty cell when the source values are in A2:A10. The results spill into the cells below, so make sure that area is clear; the source list remains unchanged.

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.