In Excel for Microsoft 365, enter =UNIQUE(A2:A20) to extract the distinct values from a list in cells A2:A20. The results spill into the cells below automatically; change the range to match your data.
What Excel means by unique values
Excel’s UNIQUE function can answer two slightly different questions. Most often, “unique values” means a distinct list: show each different value once, even if it appears repeatedly in the source. For example, a source list containing North, South, North, and East produces North, South, and East. The function keeps the order in which those values first appear.
Sometimes, however, a person wants values that occur exactly once and wants repeated values left out entirely. In that same example, the answer would be South and East, because North occurs twice. Excel supports both meanings, so decide which result you need before choosing the formula. This distinction matters for customer lists, inventory categories, transaction checks, and other tasks where “not duplicated” can mean either one representative per value or no repeated values at all.
The examples below use Microsoft Excel for Microsoft 365 on Windows. The UNIQUE function is also available in Excel 2021 and Excel 2024, and in supported versions of Excel for the web and Mac. The formula is the same across those versions, though the ribbon layout can differ slightly.
Basic formula and a worked example
With names in A2:A8, where the entries are Ava, Ben, Ava, Chen, Ben, Dee, and Chen, enter this formula in an empty cell such as C2:
With names in A2:A8, the formula returns Ava, Ben, Chen, and Dee in C2:C5, in first-appearance order.
=UNIQUE(A2:A8)
The formula’s argument is the range to examine. UNIQUE returns an array rather than placing a separate formula beside every result. The first result appears in the formula cell, and Excel fills the required cells underneath it. This behavior is called spilling. If the source list grows, the formula can include the additional rows when you expand its range or use a suitable table reference.
Place the formula outside the source range when possible. That makes the result easier to read and avoids accidentally putting the output where it could interfere with the source data. You can format the result range, use it in other formulas, or reference the whole spill with the spill-range operator, for example C2#, when C2 contains the dynamic-array formula.
Sort the extracted values
UNIQUE preserves the order of first appearance; it does not alphabetize the results. To return a sorted distinct list, nest UNIQUE inside SORT.
With names in A2:A20, enter this formula in an empty cell. It returns the distinct names in ascending order.
=SORT(UNIQUE(A2:A20))
SORT uses ascending order by default. For numbers, that means smallest to largest; for text, it generally means alphabetical order according to Excel’s sorting rules. To reverse the order, supply -1 as the sort-order argument.
With scores in A2:A20, this formula returns the distinct scores from largest to smallest.
=SORT(UNIQUE(A2:A20),1,-1)
Sorting is useful for reports and dropdown source lists, but it changes the original sequence. If the order in which items first appeared has meaning—for example, the sequence of stages in a process—use UNIQUE alone.
Return values that occur exactly once
To omit any item that appears more than once, set UNIQUE’s third argument to TRUE. This is different from the default distinct-list behavior.
With product codes in A2:A20, enter this formula to return only codes that occur once in that range.
=UNIQUE(A2:A20,,TRUE)
The second argument is left blank because it controls whether Excel compares rows or columns. The third argument, exactly_once, is TRUE here. For example, if the list is X, Y, X, and Z, the result is Y and Z—not X, Y, and Z. Use this version to find one-off entries, but be cautious when a one-time item is valid business data rather than an error.
Use UNIQUE with a table or multiple columns
A normal cell range is straightforward, but Excel tables can be more convenient when data is regularly added. If a table named Sales has a column named Region, use a structured reference so the formula refers to the whole table column.
With a table named Sales and a Region column, enter this formula outside the table to list its distinct regions.
=UNIQUE(Sales[Region])
Tables expand as new rows are added, so the formula can include new entries without editing a fixed endpoint such as A2:A20. Dynamic-array formulas should be placed outside the table: spilled results cannot spill inside an Excel table. If the table or column has a different name, use the name shown in the Table Design tab.
UNIQUE can also return distinct combinations across several columns. With department names in A2:A20 and job titles in B2:B20, this formula returns each distinct department-and-title row once:
With departments in A2:A20 and titles in B2:B20, place this formula in an empty area with two columns available for its output.
=UNIQUE(A2:B20)
Excel compares each row as a combination. If Sales appears with both Analyst and Manager, those are two different rows and both remain. This is useful for identifying unique pairs, but it is not the same as extracting unique values from just one of the columns.
Exclude blank cells
If the source range includes empty cells, UNIQUE may return an empty-looking item as part of the output. A filtered version can remove blank entries before extracting distinct values. FILTER is available in Microsoft 365, Excel 2021, and Excel 2024.
With entries in A2:A20, use this formula to ignore empty cells and return the remaining distinct values.
=UNIQUE(FILTER(A2:A20,A2:A20<>""))
The condition checks each cell against an empty string. It also excludes cells whose formulas return an empty string. If every cell is blank, FILTER has no matching rows and can return a #CALC! error because no if_empty result was supplied. If an all-blank range is possible, add an appropriate third argument to FILTER for the result you want in that situation.
Blank-looking cells are not always truly empty. A cell may contain a formula returning an empty string, spaces, or an invisible character. Those entries can behave differently from an actually empty cell. If a value that looks blank remains in the results, inspect the source cell’s formula and contents rather than assuming UNIQUE has failed.
Where to enter the formula and how to use its output
Click an empty cell with enough clear space below it, type the formula, and press Enter. Excel displays the first result in that cell and spills the rest into neighboring rows. On Windows, Enter is the normal way to confirm a dynamic-array formula; there is no need to press Ctrl+Shift+Enter. On Mac, enter the same formula and press Return. Excel for the web also accepts the formula in a cell.
To use the result as a dropdown list, first make sure the spill output is in a suitable location. In Data Validation, a direct reference to a spill range can be used as the list source, such as =C2# when C2 contains the UNIQUE formula. The # refers to the complete spilled range, including its current size. If the validation dialog rejects a reference in a particular workbook setup, define a name for the spill range and use that name as the source.
Do not type into the cells where the results need to spill. A value, merged cell, or other obstruction in the output area can cause a #SPILL! error. Clear the obstructing cells or move the formula to an area with room. If the source list is on another worksheet, refer to that sheet in the formula, for example =UNIQUE(Sheet2!A2:A20).
Common problems and what they mean
- #SPILL!: Excel cannot place the full result because something occupies part of the output area, or because the formula is in a location that does not support spilling. Clear the obstruction or move the formula.
- #NAME?: Excel may not recognize UNIQUE because the workbook is open in an older Excel version, the function name is misspelled, or the formula was entered in software that does not support it.
- Unexpected blanks: The range may include empty cells, formulas returning empty text, or cells containing spaces. Filter or clean the source as appropriate.
- Values that seem duplicated: Check for leading or trailing spaces, inconsistent punctuation, or values stored in different forms. For instance, a number stored as text may not behave like the same numeric value.
- Different capitalization: UNIQUE is not a case-sensitive way to distinguish text. Entries such as North and NORTH are generally treated as the same value for this purpose.
When cleaning data, avoid changing the original list unless that is intended. A helper column or a separate cleaned copy makes it easier to see whether differences come from spaces, data types, or other inconsistencies. UNIQUE identifies values according to Excel’s comparisons; it does not decide whether two differently formatted entries represent the same real-world item.
UNIQUE versus Remove Duplicates
UNIQUE creates a formula-driven result and leaves the source data intact. When the source changes, the spilled result recalculates. This is usually the better choice for a report, a live summary, or a list that should stay current.
Remove Duplicates is a data-cleaning command that changes the selected data rather than producing a linked result. In Excel for Windows, select the data and choose Data > Remove Duplicates. Review the selected columns before confirming: when multiple columns are selected, Excel identifies duplicate rows based on the values in those columns together. Keep a copy of the original data if removing rows would be difficult to reverse or verify.
Use UNIQUE when you need a separate, refreshable list. Use Remove Duplicates when you deliberately want to delete repeated records from a dataset. Neither method automatically determines whether two records are meaningfully duplicates; that depends on which fields define a match in your work.
Options when UNIQUE is unavailable
UNIQUE is not available in some older Excel releases. If you cannot upgrade or open the workbook in a supported version, use Excel’s Advanced Filter to copy distinct records to another location, or use Remove Duplicates on a copy of the data. Advanced Filter is available from the Data tab; choose the Advanced command in the Sort & Filter group, then select the option to copy results elsewhere and enable Unique records only. The exact ribbon arrangement can vary by version.
These tools are manual alternatives, not spill formulas. You may need to run the command again after the source changes. If the task must update automatically, a formula-based method requires a version that supports the functions used, or a different compatible approach designed for that Excel release. Avoid copying a modern UNIQUE formula into an older workbook and assuming the result will calculate there.
Frequently Asked Questions
What is the formula to get unique values in Excel?
With the list in A2:A20, enter =UNIQUE(A2:A20) in an empty cell. Excel returns each distinct value once and spills the results into nearby cells.
How do I extract unique values and sort them?
With the list in A2:A20, use =SORT(UNIQUE(A2:A20)). SORT arranges the distinct results in ascending order.
How do I return values that appear only once?
With the values in A2:A20, use =UNIQUE(A2:A20,,TRUE). The TRUE argument excludes every value that occurs more than once.
Why does UNIQUE show a #SPILL! error?
The output cells are not all available. Clear the cells in the spill area, unmerge obstructing cells, or move the formula to an open area.
Does UNIQUE work in older Excel versions?
UNIQUE is available in Microsoft 365, Excel 2021, and Excel 2024, but not in some older releases. Use Advanced Filter or Remove Duplicates as manual alternatives in those versions.
Related
- Excel Data Validation List Auto Update: How to Set It Up
- Excel Data Validation Lists: Add New Items in Excel
- Excel Data Bars: How to Add, Format, and Troubleshoot
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- DATEDIF in Excel: Calculate Days Between Dates CorrectlyTo get the number of days between a start date in A2 and an end date in B2,…
- Excel Filter Not Grouping Dates by Month? Causes and FixesIf Excel’s filter dropdown is not grouping dates by month, first check that every value in the date…
- How to Copy Formulas in Excel by Dragging the Fill HandleTo copy a formula in Excel by dragging, select the cell with the formula, point to the small…
- Excel INDEX Errors: Common Causes, Meanings, and FixesAn Excel INDEX error usually means the requested row or column falls outside the range (#REF!), an index…
- Excel Formula for Last Updated Date in a Specific CellFor a live current date in Excel, use =TODAY(); for the current date and time, use =NOW(). Neither…
- Excel Sum Dynamic Range: Formulas That Grow With DataTo make an Excel sum include new rows automatically, convert the data to an Excel Table and sum…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.