Excel Formula to Remove Duplicates From One Column

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365 or Excel 2021, enter =UNIQUE(A2:A100) in an empty cell to return a live list of distinct values from column A. The formula does not delete anything from the source column; to remove duplicates from the original data instead, use Data > Remove Duplicates.

The UNIQUE formula and what it returns

UNIQUE is the simplest formula-based answer when the goal is to show each value once. It examines the range you provide and returns one instance of every distinct value. The result is a dynamic array: Excel places the first result in the formula cell and spills the remaining results into cells below it. The source list stays as it is, so the result updates when values in the referenced range change.

For example, suppose A2:A8 contains North, South, North, a blank cell, East, South, and West. With that layout, enter =UNIQUE(A2:A8) in C2. It returns North, South, a blank, East, and West, in that order. The repeated North and South values appear only once. The blank is also a distinct item in the range, which is why it appears in the result.

Use a range that includes the data but not its heading. If A1 contains the heading Region, a formula that starts at A1 will treat that heading as an item and include it in the output. Put the formula outside the source range, with enough empty cells below for the results to spill.

How to exclude blank cells

A source range may include empty cells because the list is shorter than the selected range. If you do not want a blank in the output, combine UNIQUE with FILTER. FILTER removes empty cells before UNIQUE creates the distinct list.

With the example values in A2:A8, enter =UNIQUE(FILTER(A2:A8,A2:A8<>"")) in C2. The result is North, South, East, and West, with no blank entry. The test A2:A8<>"" keeps cells whose contents are not an empty string. The blank cells are excluded before the unique values are returned.

FILTER, like UNIQUE, is available in Microsoft 365 and Excel 2021. If a cell contains a formula that returns an empty string rather than being truly empty, this test also excludes it. A cell containing spaces is not empty for this test, however, and will remain in the results unless you clean or transform the source text.

Sort the distinct results

UNIQUE preserves the order in which distinct values first appear; it does not automatically sort them alphabetically. To produce a sorted list, wrap the unique result in SORT. This changes the order of the displayed results, not the order or contents of the original data.

With the same entries in A2:A8, enter =SORT(UNIQUE(FILTER(A2:A8,A2:A8<>""))) in C2. The output is East, North, South, and West. FILTER first excludes the blank, UNIQUE removes repeated values, and SORT orders what remains. For numbers, SORT returns values in ascending order by default; its optional arguments can change the sort order or direction when needed.

What counts as a duplicate

For a one-column list, UNIQUE compares the values in the supplied range and returns one item for each distinct value. It is not a fuzzy matching tool: similar-looking entries are not automatically treated as the same. For example, a name with a trailing space can remain separate from the same name without that space. Differences in punctuation, spelling, or hidden characters can also produce separate results.

Excel text comparisons are generally not case-sensitive for this purpose, so entries such as North and NORTH are not normally useful as a way to preserve two separate labels in a UNIQUE list. If capitalization itself must define distinct values, that requires a case-sensitive approach rather than the basic formula. Likewise, an amount stored as text may not behave like the same amount stored as a number. Check the source data if two entries that look alike appear separately, or if entries you expected to differ are combined.

There is also an important difference between a distinct list and removing duplicate records. UNIQUE applied to A2:A100 only returns values from that one column. It does not return the other columns associated with each value, and it does not decide which complete record to retain. If each row is a customer record, for example, a unique list of customer IDs is not the same as a cleaned table containing one full row per customer.

One-column duplicates versus duplicate rows

When the question is whether complete rows repeat, provide the full table range to UNIQUE rather than only the key column. With records in A2:C8, enter =UNIQUE(A2:C8) in E2. Excel returns each distinct combination of values across columns A, B, and C once; two rows with the same value in column A but different values in B or C remain distinct. In this example, each output row represents a different complete combination from A2:C8.

This behavior matters when one column is an identifier and the other columns contain details. If you want one row per identifier while keeping a related name or date, decide which record should survive. A plain UNIQUE formula on the identifier alone cannot make that choice for the other columns. A built-in removal operation can use selected columns to identify duplicate rows, but it keeps one of the matching records and deletes the others from the chosen range.

Formula results compared with permanent removal

Choose the formula when you want a separate, refreshable list and need to keep the source data intact. Choose the removal command when you want to change the selected data itself. Before using the command, make a copy of the data or save a backup, especially if the range contains information that cannot easily be restored.

  1. Select a cell in the data range, or select the specific range you intend to clean.
  2. On the Windows ribbon, choose Data > Remove Duplicates.
  3. In the dialog, confirm whether the first row contains headers, then select the columns Excel should compare to identify duplicates.
  4. Choose OK and review Excel’s report of how many duplicate values were removed and how many unique values remain.

If only the values in one column should determine duplication, select that column in the dialog. If several columns are selected, Excel treats a row as a duplicate only when the selected-column values match another row. The command removes duplicate rows from the selected range, so selecting a single column by itself can leave related data in neighboring columns out of sync. For a table of records, select the complete table and carefully choose the comparison columns.

On Excel for Mac, the Remove Duplicates command is also available from the Data tab, though the dialog layout can differ by release. Excel for the web also offers duplicate removal in supported versions. The UNIQUE formula uses the same basic syntax on Windows, Mac, and the web; the main practical difference is whether the installed Excel version supports dynamic-array functions.

Using Excel versions without UNIQUE

UNIQUE requires Excel 2021 or Microsoft 365. In an older version, a helper column can mark the first occurrence of each value. This does not create a compact list by itself, but it lets you filter the source to show only the first occurrence of each item.

With values in A2:A8, enter =IF(COUNTIF($A$2:A2,A2)=1,A2,"") in B2, then fill it down through B8. For the example list North, South, North, blank, East, South, West, the helper results are North, South, blank, blank, East, blank, and West. COUNTIF counts how many times the current value has appeared from the first data row through the current row; IF returns the value only when that count is one. The empty source row also displays as blank.

To see a compact list from this method, filter the helper column to hide blank cells, or copy the nonblank results and paste them elsewhere as values. This approach is less automatic than a spilling UNIQUE formula and requires the helper formula to be filled down for new rows. For a one-time cleanup, Data > Remove Duplicates may be simpler than maintaining a helper column.

Common formula problems and fixes

  • #SPILL!: One or more cells where Excel needs to place the output are not empty, or the spill area is otherwise blocked. Clear the cells below and beside the formula as needed, and check for merged cells. A dynamic array formula must be entered outside an Excel table if it needs to spill; place it in a normal worksheet range.
  • The heading appears in the results: The formula range includes the header. Start the reference below the heading, or deliberately include it only if it should be treated as a value.
  • A blank appears: UNIQUE sees an empty entry in the selected range. Use the FILTER version above to exclude blank cells.
  • Similar entries remain separate: Check for leading or trailing spaces, nonprinting characters, inconsistent punctuation, and numbers stored as text. Clean the source values before creating the unique list when those differences are accidental.
  • The formula name is not recognized: The Excel version may not support UNIQUE or FILTER. Confirm that you are using Excel 2021 or Microsoft 365, or use the helper-column method or the Remove Duplicates command.

If the formula displays as text instead of calculating, check that the cell is not formatted as Text and that the entry begins with an equals sign. If Excel rejects commas between arguments, the computer’s regional settings may use semicolons as formula separators; use the separator Excel expects in that installation.

Frequently Asked Questions

Does UNIQUE delete duplicates from the original column?
No. It returns a separate list and leaves the source range unchanged. Use Data > Remove Duplicates to remove rows from the selected data itself.

How do I remove duplicates but keep the first occurrence?
UNIQUE returns distinct values in the order their first occurrences appear, so its output represents each value once. The source column is still untouched. The Remove Duplicates command also keeps one instance of each selected combination while deleting duplicate rows from the selected range.

Can I remove duplicates and ignore blank cells?
Yes. With data in A2:A100, use =UNIQUE(FILTER(A2:A100,A2:A100<>"")). FILTER excludes blank entries before UNIQUE returns one instance of each remaining value.

Can I make the unique list alphabetical?
Yes. With data in A2:A100, use =SORT(UNIQUE(A2:A100)). This sorts the formula’s output while leaving the original column in its existing order.

Why do I get a #SPILL! error?
Excel cannot place the full result in the cells needed for the dynamic array. Clear the obstructing cells, check for merged cells, and put the formula outside a table if the result needs to spill.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.