Unique Excel List Creation: Formulas, Tools, and Tips

To create a unique list in Microsoft Excel for Microsoft 365 on Windows, enter =UNIQUE(A2:A100) in an empty cell, replacing the range with your source data; Excel spills one copy of each distinct value into the cells below. If you need a one-time result rather than a live formula, use Data > Remove Duplicates on a copy of the source.

What a unique list means

A unique list contains each distinct value once. If a source column reads North, South, North, East, South, the resulting list is North, South, East. It is different from a list of values that occur only once: in that example, East appears once, while North and South appear more than once. Excel can produce either result, depending on the method and formula options.

Unique lists are useful for making category menus, checking which customers or products appear in a dataset, preparing lookup values, and summarizing records. They can also help reveal inconsistent entries. For instance, a list that contains both “West” and “West ” may indicate an extra trailing space rather than two intended regions.

Before choosing a method, decide whether the list should update when the source changes, whether it should be sorted, and whether every record or only qualifying records should be included. These choices determine whether a formula, a built-in data tool, or a PivotTable is the best fit.

Create a live list with UNIQUE

UNIQUE is a dynamic-array function available in Microsoft 365 and Excel 2021 or later. Enter it once in a blank cell outside an Excel Table, and the results spill into nearby cells. With names in A2:A8 containing Maya, Lee, Maya, Omar, Lee, Priya, Omar, enter this formula in C2:

With names in A2:A8 and the formula entered in C2, =UNIQUE(A2:A8) returns Maya, Lee, Omar, and Priya in first-appearance order.

The basic syntax is UNIQUE(array,[by_col],[exactly_once]). The optional by_col setting determines whether Excel compares columns instead of rows; for an ordinary vertical list, leave it out. The exactly_once setting changes the meaning from “show each distinct item once” to “show only items whose original count is exactly one.” For the sample data above, that second choice returns Priya alone.

With names in A2:A8 and the formula entered in C2, =UNIQUE(A2:A8,FALSE,TRUE) returns only values that appear exactly once in the source, so the result is Priya.

Do not type into cells where the result needs to spill. If one of those cells is occupied, Excel displays a #SPILL! error; clear the obstructing cell or move the formula. A spilled formula also cannot be placed inside an Excel Table, although it can refer to a table column.

Sort the results and exclude blanks

UNIQUE preserves the order in which values first appear. To alphabetize the result, nest it inside SORT. With names in A2:A8, this formula entered in C2 returns the four distinct names alphabetically: Lee, Maya, Omar, Priya.

With names in A2:A8 and the formula entered in C2, =SORT(UNIQUE(A2:A8)) returns the distinct values in ascending order.

Blank cells in the source can produce a blank entry in the spill range. To exclude empty cells, FILTER the source before applying UNIQUE. With names in A2:A100, this formula ignores cells that are genuinely empty and returns each remaining value once:

With names in A2:A100 and the formula entered in C2, =UNIQUE(FILTER(A2:A100,A2:A100<>"")) returns distinct nonblank names.

For a sorted, nonblank result, combine all three functions. With names in A2:A100, enter =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))) in C2. It filters out empty cells, removes repeated values, then sorts what remains. These functions are available in Microsoft 365 and Excel 2021 or later. If your Excel version does not recognize UNIQUE, use one of the alternatives below.

Create a unique list from a table or selected records

For data formatted as an Excel Table, structured references can make the source easier to maintain. Suppose a table is named Sales and has a Region column. With the formula in a worksheet cell outside the table, =UNIQUE(Sales[Region]) returns the distinct region entries. The table reference expands as rows are added to the table, and the spilled result recalculates.

To get distinct values that meet a condition, use FILTER inside UNIQUE. Suppose a table named Sales has Region and Status columns. To list regions with a status of “Open,” enter this formula outside the table:

With data in the Sales table’s Region and Status columns, and the formula in an empty worksheet cell, =UNIQUE(FILTER(Sales[Region],Sales[Status]="Open")) returns each region having at least one Open record.

When building a conditional formula, confirm that the filtered range and condition range have the same number of rows. A mismatch can cause a calculation error. If no rows meet the condition, FILTER can return an error unless you provide its optional if_empty argument; choose a message or value that makes sense for the worksheet.

Use Remove Duplicates for a fixed result

Remove Duplicates is useful when you want to clean a list in place rather than maintain a formula. Because it deletes repeated rows from the selected data, first make a copy if the original records matter. Select the data, then choose Data > Remove Duplicates. In the dialog, select the columns Excel should use to decide whether rows are duplicates, then confirm.

For one column, select that column as the comparison field to keep one instance of each value.
For a table of records, select every column to remove only rows that match across all selected fields.
Selecting only some columns means Excel treats records with the same values in those columns as duplicates, even if other fields differ. Review this choice carefully.

Excel keeps the first occurrence in the selected range and removes later matching entries. It reports how many duplicate values or rows were removed and how many remain. The command is not a live link to the source: if new data is added later, run it again. On a Mac, the command is also available from the Data tab; labels and dialog layout can vary slightly by Excel release. In Excel for the web, availability and interface details can differ, so check the Data tab in the browser version you use.

Use Advanced Filter or a PivotTable

Advanced Filter is another built-in option for extracting distinct values, including in versions that do not support UNIQUE. On Windows, select the source range, then choose Data > Sort & Filter > Advanced. Choose Copy to another location, set the destination, and select Unique records only. Include the column heading when setting up the source range, and choose a destination that will not overwrite existing content. The copied output is a snapshot, so repeat the operation when the source changes.

A PivotTable can also display distinct labels. Select the data and choose Insert > PivotTable, then place the field you want to list in the Rows area. Excel displays each label as a row item. This is especially handy when the goal is a summary that also counts or groups records, rather than a plain list to reuse in another formula. Refresh the PivotTable after its source data changes; it does not update in exactly the same way as a spilled UNIQUE formula.

Choose the right method

The best method depends on what the result is for, not simply how many entries are in the source. Consider these differences:

UNIQUE: Best for a list that should update automatically and remain linked to source data. It requires a supported Excel version and room for the spill range.
Remove Duplicates: Best for permanently cleaning a copied dataset or preparing a one-time list. It changes the selected data, so preserve a backup if needed.
Advanced Filter: Best for a copied snapshot of distinct records, including for older desktop versions. It requires another run to reflect later edits.
PivotTable: Best when distinct labels belong in a report or need counts and other summaries. It requires refreshes after source changes.

Excel for the web supports many modern worksheet functions, but some menu options and dialog layouts differ from the Windows desktop app. On Mac, common formulas work similarly, while ribbon locations or shortcuts may differ. If a shortcut is involved, use the displayed menu command when the keyboard combination on your device does not match Windows.

Understand what Excel treats as the same

UNIQUE compares underlying values and does not normally distinguish text by letter case. For example, “MAY” and “May” are treated as the same text for ordinary duplicate removal. If case-sensitive distinctions are essential, UNIQUE by itself is not a case-sensitive deduplication solution; additional formula logic or a carefully designed Power Query process may be necessary.

Whitespace and data types also matter. “Acme” and “Acme ” can appear different because one includes a trailing space. A number stored as text may not behave like a numeric value in every comparison or calculation. Clean the source when entries that look identical are unexpectedly kept as separate items.

For a simple cleanup, TRIM removes extra ordinary spaces from text, but it does not remove every nonprinting character that may arrive from web pages or exported systems. A practical workflow is to clean a helper column, inspect the results, then create the unique list from the cleaned values. Avoid overwriting raw source data until you know the cleaned result is correct.

Common problems and practical checks

A #SPILL! error usually means an occupied cell, merged cell, or another obstruction is blocking the result. Click the error indicator or inspect the cells below and beside the formula, then clear the space or move the formula. A #NAME? error may mean the Excel version does not support the function, or the function was misspelled.

If the output includes an unexpected blank, check for blank cells, formulas that return an empty string, and spaces that only look blank. If items that seem repeated appear twice, compare their spelling, spaces, punctuation, and stored data types. If the output fails to include new rows, check whether the source is a fixed range that does not cover those rows; using a properly maintained Excel Table can make a formula reference more robust.

For a reliable result, test a small sample and compare the output with the original records. Verify whether blanks should count, whether case should matter, and whether the output is supposed to be sorted. These checks prevent a list that looks tidy from silently excluding useful categories or treating a data-entry mistake as a legitimate new value.

Frequently Asked Questions

How do I remove duplicates in Excel without deleting the original data?
Copy the source to another worksheet or range, then select the copy and use Data > Remove Duplicates. Alternatively, use UNIQUE in an empty cell to create a separate, automatically updating result.

How do I make a unique list in alphabetical order?
With the source values in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. The result contains distinct values sorted in ascending order; add FILTER inside UNIQUE if blank entries should be excluded.

Can a unique Excel list update automatically?
Yes. A UNIQUE formula recalculates when its referenced source data changes. A fixed range only includes cells within that range, so an Excel Table column reference is often more convenient when records will be added.

Why does UNIQUE return a #SPILL! error?
The formula needs to place results in adjacent cells, and something is blocking that spill area. Clear the obstructing cells, unmerge any conflicting cells, or move the formula to a larger empty area.

Does UNIQUE distinguish uppercase and lowercase text?
Not in the usual case-sensitive sense: values such as “West” and “WEST” are treated as duplicates by ordinary UNIQUE use. Normalize text or use more specialized logic if case differences must remain distinct.

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.