Excel Unique List Without Duplicates: How to Make One

Formulas & functions · Updated November 19, 2026

To make a unique list in Microsoft Excel for Microsoft 365 on Windows, 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 nearby cells; if you need a one-time list instead, use Data > Remove Duplicates.

What a unique list means in Excel

A unique list contains one instance of each distinct value in a range. If a source column contains Pear, Apple, Pear, and Banana, its unique list is Pear, Apple, Banana. The function keeps the order in which values first appear unless you sort the result separately.

“Unique” can also mean something narrower: values that occur exactly once. In the example above, Apple and Banana each occur once, while Pear does not. Excel’s UNIQUE function can handle both meanings, so it is worth deciding which result you intend before building a report.

Excel compares the contents of cells, not just how they look. For example, numbers stored as text can behave differently from numeric values, and dates can display in different formats while having the same underlying serial value. Extra spaces can also make two apparently identical names distinct. If the result looks surprising, check the source data before assuming the formula is wrong.

Use the UNIQUE formula

UNIQUE is available in Microsoft 365 and Excel 2021 or later. It is a dynamic-array function: enter it once, and Excel places the result in the cells below or beside the formula cell. The destination cells must be empty, or Excel displays a spill error instead of placing the full list.

With source entries in A2:A11, enter this formula in an empty cell, such as C2:

=UNIQUE(A2:A11)

For this example, suppose A2:A11 contains Pear, Apple, Pear, Banana, Apple, Pear, Kiwi, Banana, Pear, and Apple. The formula returns Pear, Apple, Banana, and Kiwi, in that order. It does not change or delete anything in column A.

The function’s syntax is UNIQUE(array,[by_col],[exactly_once]). The first argument is the range or array to examine. The optional second argument controls whether Excel compares rows or columns; the default is rows. The optional third argument asks for values that appear exactly once when set to TRUE.

Sort the result and exclude blanks

UNIQUE preserves first-occurrence order, which is useful when the sequence in the source matters. To alphabetize a text list, wrap it in SORT. With the source values in A2:A11, enter this in an empty cell:

=SORT(UNIQUE(A2:A11))

For the example list, the result is Apple, Banana, Kiwi, Pear. Sorting is a presentation choice; it does not affect which items count as distinct. SORT and UNIQUE are available in Microsoft 365 and Excel 2021 or later.

Blank cells in the selected range can produce a blank entry in the result. To ignore blanks, combine FILTER with UNIQUE. With data in A2:A100, enter:

=UNIQUE(FILTER(A2:A100,A2:A100<>""))

FILTER removes cells whose value is an empty string before UNIQUE builds the list. This also excludes formulas that return an empty string. If the source contains no nonblank entries, FILTER without an optional if-empty value can return a #CALC! error; in that situation, check for data or add an error-handling approach suited to your workbook. FILTER is available in Microsoft 365 and Excel 2021 or later.

Return values that occur exactly once

For a list of items that appear only one time, set UNIQUE’s third argument to TRUE. With the example data in A2:A11, enter this formula in an empty cell:

=UNIQUE(A2:A11,,TRUE)

The second argument is omitted, so Excel uses its default row-by-row comparison. The result is Kiwi, because Pear, Apple, and Banana each appear more than once. This is different from a normal unique list, which includes each of those repeated values one time. The distinction matters for tasks such as finding one-off product codes, names, or transactions.

Make a permanent list with Remove Duplicates

Use Remove Duplicates when you want to edit a copy of the data into a fixed list rather than maintain a live formula result. First copy the source column to a safe location if the original values need to remain unchanged. Select the copied range, then choose Data > Remove Duplicates on the Windows ribbon.

  • If the selection includes a heading, check My data has headers so Excel does not treat the heading as a value.
  • Select the column or columns that define a duplicate. For a one-column list, leave that column selected.
  • Choose OK. Excel reports how many duplicate values it removed and how many unique values remain.

This command deletes duplicate rows from the selected data; it does not create a formula that updates when new entries are added. For a multi-column table, Excel considers the selected columns together. Selecting Name and Department, for example, keeps one row for each distinct name-and-department combination; the same name in two departments is not a duplicate row under that selection.

Because the command changes the selected range, confirm that you have selected the intended columns and have a backup if the original records matter. On Excel for Mac, the command is also on the Data tab, although the ribbon grouping and dialog appearance can differ. Excel for the web has a similar Remove Duplicates command on the Data tab, but interface details can vary as Microsoft updates the web app.

Use Advanced Filter for a copied list

Advanced Filter is another built-in option for producing a one-time list, including in Excel versions that do not support dynamic arrays. On Windows, select the source range, then choose Data > Advanced in the Sort & Filter group. In the dialog, choose Copy to another location, specify the destination, and check Unique records only before confirming.

If the source range has a heading, include it and identify the range accordingly. Advanced Filter copies distinct records to the destination rather than removing records from the source. For one column, the copied output is a unique list; for several columns, it returns distinct combinations of the selected fields. This is useful when preserving the original table is important but a dynamic formula is not required. The Mac ribbon can present the command differently, and availability and behavior may depend on the Excel version.

Choose the right method for your task

  • Use UNIQUE for a live list that should recalculate when the source changes. It is concise and keeps the source intact, but requires a compatible Excel version and clear cells for the spill range.
  • Use Remove Duplicates to permanently clean a copy of a list or table. It works in older versions too, but it changes the selected data and will not update automatically.
  • Use Advanced Filter to copy unique records to another location without a dynamic formula. It is a practical built-in method for a static result, though its dialog takes more setup.

A copied formula result can be turned into fixed values if you need to share a static snapshot: copy the spilled results, then use Paste Special > Values at the destination. Keep the formula version if you expect the source to change; values pasted this way no longer update when it does.

Common problems and how to fix them

  • #SPILL!: One or more cells in the intended output area are occupied, merged, or otherwise blocking the result. Clear the obstructing cells or move the formula to an area with room.
  • Unexpected separate entries: Look for leading or trailing spaces, nonprinting characters, inconsistent spelling, or numbers stored as text. Correct the source data if those entries should represent the same item.
  • A blank appears: The selected source range contains blanks or formulas returning empty strings. Use the FILTER version above to exclude them.
  • The list does not update: Check whether you used Remove Duplicates or pasted values rather than leaving a UNIQUE formula in place. Also confirm calculation is set to automatic if other formulas in the workbook are not recalculating.
  • The formula name is rejected: UNIQUE requires Microsoft 365 or Excel 2021 or later. Older Excel versions need a different approach, such as Advanced Filter.

UNIQUE does not ordinarily distinguish text by capitalization: for example, Excel’s standard comparisons treat “pear” and “Pear” as equivalent for this purpose. If capitalization itself carries meaning, do not assume the basic formula will preserve both as separate items; use a case-sensitive method designed for that requirement.

Frequently Asked Questions

How do I get a unique list in Excel without a formula?
Copy the source range and use Data > Remove Duplicates on the copy, or use Data > Advanced and select Unique records only to copy unique records elsewhere. Both create a static result rather than a list that automatically recalculates.

Does UNIQUE remove duplicates from the original column?
No. It returns a separate result and leaves the source range unchanged. Use Remove Duplicates if you intend to delete repeated rows from a selected range, and keep a copy if the original records must be preserved.

Can I make the unique list alphabetical?
Yes. With values in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. SORT orders the returned values while UNIQUE determines which entries appear.

How do I list only values that appear once?
With data in A2:A100, use =UNIQUE(A2:A100,,TRUE). That returns items with a single occurrence, not one copy of every distinct item.

Why does my Excel not recognize UNIQUE?
UNIQUE is available in Microsoft 365 and Excel 2021 or later, but not in many older perpetual versions. Use Advanced Filter or Remove Duplicates for a static result in an older edition.

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.