Unique List From Excel Column: Formulas and Options

Formulas & functions · Updated November 20, 2026

To create a unique list from an Excel column in Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell, replacing the range with your data. Excel returns each distinct value once and spills the results into the cells below; use =SORT(UNIQUE(A2:A100)) if you also want the list in order.

What a unique list means in Excel

A unique list contains one instance of each distinct value in a range. If a column contains Seattle, Denver, Seattle, and Austin, the result is Seattle, Denver, and Austin. The UNIQUE function preserves the order in which values first appear unless you add a sorting function. It does not rearrange or remove anything from the source column.

Excel uses “unique” in two related ways that are easy to confuse. A distinct list includes one copy of every value that appears, even if that value occurs many times. A list of values that occur exactly once excludes values that repeat. UNIQUE can produce either result, so choose the option that matches the question you are answering.

The function works with text, numbers, dates, and other cell values. It compares the underlying values rather than the cells’ visual formatting. For instance, a date displayed as “Jan 1” and the same date displayed as “January 1, 2026” can be treated as the same value if their underlying date serial number is the same. Formatting the output is a separate step.

How the UNIQUE function works

UNIQUE is a dynamic array function available in Microsoft 365 and Excel 2021 or later. It takes an array or range and returns results that expand automatically into neighboring cells. You enter the formula once; you do not need to copy it down as you would with many older formula methods.

With values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell, such as C2. Excel displays the distinct values starting in C2 and fills as many cells below as needed. The formula itself remains in C2. If the source list changes, the results generally recalculate automatically.

UNIQUE has optional arguments for searching by rows or columns and for returning values that occur exactly once. Its basic syntax is UNIQUE(array,[by_col],[exactly_once]). For a normal vertical column, the default is to compare rows, so you can usually omit the optional arguments. For a horizontal range, set the second argument to TRUE to compare columns.

If you are working in an Excel Table, structured references can make the formula easier to maintain. With a table named Sales and a column named Region, enter =UNIQUE(Sales[Region]) in an empty cell outside the table. When new rows are added to the table, the referenced column expands automatically. The formula’s spilled results must still have clear space outside the table.

Make the list sorted and remove blank entries

UNIQUE returns values in first-seen order, which is useful when the source order carries meaning. To sort the output alphabetically or numerically, combine it with SORT. With source values in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. For text, the result is sorted alphabetically according to Excel’s sorting behavior; numbers are sorted numerically.

Blank source cells can appear as a blank-looking result or as a zero, depending on the contents and how Excel interprets the range. To omit empty cells, filter them out before asking for unique values. With the source in A2:A100, use =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))). FILTER keeps entries that are not equal to an empty string, UNIQUE removes repeats, and SORT orders the result.

This blank filter also excludes formulas that return an empty string. It does not remove cells containing only spaces, because a space is a character rather than an empty value. If apparent blanks remain, inspect the source for spaces or other invisible characters. Cleaning inconsistent source text may be necessary before creating a reliable list.

In a formula displayed in Excel, the comparison operator is written as <>"", meaning “not equal to an empty string.” If you enter the formula directly into Excel, use ordinary quotation marks around the empty string. The HTML notation in this article represents those characters so they display correctly in the page.

Distinct values versus values occurring exactly once

Use the third UNIQUE argument when you want only values with a frequency of one. With data in A2:A100, enter =UNIQUE(A2:A100,FALSE,TRUE). The second argument, FALSE, tells Excel to compare rows; the third, TRUE, requests values that occur exactly once.

For example, if the source contains Red, Blue, Red, Green, Blue, and Yellow, the ordinary formula returns Red, Blue, Green, and Yellow. The exactly-once formula returns Green and Yellow, because Red and Blue each appear more than once. This distinction matters in tasks such as finding unpaired IDs or spotting one-off entries. It is not the right choice when you simply want a clean list of categories.

If every item is repeated, the exactly-once result is empty. That is not necessarily an error: it means no value in the selected range occurs just once. Check the range boundaries too, since excluding a row can change which values qualify.

Get unique rows from multiple columns

UNIQUE can work on a multi-column range, not just one column. With records in A2:B100, enter =UNIQUE(A2:B100) in an empty area. Excel returns distinct combinations of values across each row. If the same person and department pair appears more than once, that pair is returned once; a person listed with two different departments remains two different rows.

This is useful for building a list of distinct combinations, such as region-and-product pairs. It does not mean “make the first column unique while keeping an arbitrary value from the second column.” The complete rows are compared. If you need one record per customer but also need a particular associated date or amount, define which record should be kept first; simply removing duplicates may discard information you intended to preserve.

For a one-column list derived from a larger table, point UNIQUE at the specific field instead. For example, with region values in a table column named Region, =UNIQUE(Sales[Region]) returns distinct regions, rather than distinct combinations of every column in the table.

Formula results versus Remove Duplicates

A formula is usually the best choice when you want a list that stays connected to the source. It updates as source values change, leaves the original data untouched, and makes the transformation visible in the worksheet. The trade-off is that the output is a spilled array: cells in its path must be empty, and the list may resize when the source changes.

Use the ribbon command when you want to remove repeated entries from the data itself or need a one-time cleaned copy. In Excel for Microsoft 365 on Windows, select the data and choose Data > Remove Duplicates. Confirm which columns Excel should check, then confirm the operation. Excel changes the selected data, so make a copy first if you need to retain the original rows. The command is also available in current Excel for Mac versions, although ribbon placement can vary by release.

Another option is Advanced Filter, which can copy distinct records to a different location without using a dynamic formula. On Windows, go to Data > Sort & Filter > Advanced, choose “Copy to another location,” specify the list range and destination, and select “Unique records only.” This is useful when a formula is unavailable or a fixed extract is needed. It does not behave like a live UNIQUE formula; run the filter again after the source changes.

These methods answer slightly different needs. Use UNIQUE for a live, non-destructive result; Remove Duplicates to edit a dataset; and Advanced Filter to create a static extract. Before applying either data-cleaning command, confirm whether duplicates should be judged using one column or all selected columns.

Common problems and how to fix them

  • #SPILL! appears: One or more cells where Excel needs to place results are occupied, merged, or otherwise unavailable. Clear the output area or move the formula to a larger empty space. Clicking the error indicator may help identify the blocking cells.
  • The formula name is not recognized: UNIQUE is available in Microsoft 365 and Excel 2021 or later. Older versions may show a name error because they do not support the function. Use Advanced Filter or Remove Duplicates instead.
  • Values that look alike both appear: The source may contain leading or trailing spaces, nonprinting characters, or genuinely different underlying values. Compare the source entries and clean them where appropriate. A difference in capitalization alone is generally not a dependable way to distinguish entries in ordinary Excel comparisons.
  • The output seems to change order: A plain UNIQUE formula follows first-appearance order. If the source order changes, the output order can change too. Wrap the formula in SORT when you need a predictable sorted list.
  • The formula is inside an Excel Table: Dynamic array formulas cannot spill within a table’s data area. Put the formula in a regular worksheet range outside the table, leaving room for the results.

Also check whether the range includes a header. If it does, the header is treated as a value and may appear in the results. Start the formula at the first data row, or adjust the selected range so it excludes the heading.

Frequently Asked Questions

How do I get a unique list from an Excel column?
With the values in A2:A100, enter =UNIQUE(A2:A100) in a blank cell. Replace the example range with your actual data range. The results spill into the cells below the formula.

How do I get a sorted unique list in Excel?
With the data in A2:A100, enter =SORT(UNIQUE(A2:A100)). UNIQUE removes repeated values and SORT orders the returned list.

How do I exclude blanks from a unique list?
With the source values in A2:A100, use =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))). This excludes empty cells and formulas returning an empty string, but not cells containing spaces.

How do I list only values that appear once?
With data in A2:A100, use =UNIQUE(A2:A100,FALSE,TRUE). Unlike the ordinary formula, this omits values that occur more than once.

Can I make a unique list without UNIQUE?
Yes. Use Data > Remove Duplicates to edit a selected dataset, or Data > Sort & Filter > Advanced to copy unique records to another location. These are useful alternatives in older Excel versions, but they do not automatically update like a dynamic formula.

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.