In Excel for Microsoft 365, use =UNIQUE(A2:A20) to return a distinct list from the values in A2:A20, with each different value appearing once. The result spills into the cells below the formula; use =SORT(UNIQUE(A2:A20)) if you also want the list in ascending order.
What “distinct” means in Excel
A distinct list contains one copy of each different value, regardless of how many times that value occurs in the source. For example, if a column contains Apple, Pear, Apple, and Plum, a distinct list contains Apple, Pear, and Plum. The order is normally the order in which each value first appears in the source.
Excel’s UNIQUE function can also answer a related but different question: which values occur exactly once? Its third argument, exactly_once, switches from “show each different value once” to “show only values whose source frequency is one.” This distinction matters for inventory lists, customer names, and other data where repeated entries may be meaningful.
The basic UNIQUE formula
Suppose product names are in A2:A20, and you want the result to begin in C2. Select C2, enter the formula, and press Enter:
With product names in A2:A20: =UNIQUE(A2:A20)
If the source contains Apple, Pear, Apple, and Plum in that order, the result is Apple, Pear, Plum, spilling down from C2. Leave the cells below C2 clear so Excel can display the full result. You do not need to copy the formula down; the single formula produces a dynamic array that expands or contracts as its result changes.
UNIQUE is available in Microsoft 365 and Excel 2021 or later. It also works in Excel for the web and current Excel for Mac versions that support dynamic arrays. The formula syntax is the same across those platforms; the examples here use Excel for Microsoft 365 on Windows.
Sort the distinct results
UNIQUE preserves the first-seen order, which can be useful when the sequence reflects the source data. To present a tidy alphabetical list instead, nest UNIQUE inside SORT.
With product names in A2:A20: =SORT(UNIQUE(A2:A20))
For text, the default sort order is ascending; for numbers, it is smallest to largest. SORT is available in Microsoft 365 and Excel 2021 or later. Sorting the formula result does not rearrange or change the original cells.
Return values that occur exactly once
To exclude values that repeat and show only those that appear once in the source, set UNIQUE’s third argument to TRUE. The second argument is included as FALSE to retain the default row-by-row comparison.
With product names in A2:A20: =UNIQUE(A2:A20,FALSE,TRUE)
If the source is Apple, Pear, Apple, and Plum, the formula returns Pear and Plum: Apple is omitted because it appears twice. This is not the usual meaning of “get a distinct list,” so use the basic formula unless you specifically need values with a source frequency of one. The exactly_once option is also available in Microsoft 365 and Excel 2021 or later.
Get distinct rows or filter the list
UNIQUE can compare complete rows, not just one column. This helps when each record is defined by a combination of fields, such as a department and job title. With department in A2:A20 and job title in B2:B20, enter =UNIQUE(A2:B20) to return each distinct department-and-title pair. A repeated pair appears once, while the same department with a different title remains a separate row.
You can also filter records before asking for distinct values. For example, to list products sold in the West region, use FILTER to keep the matching product names, then UNIQUE to remove repeats.
With product names in A2:A20 and regions in B2:B20: =UNIQUE(FILTER(A2:A20,B2:B20="West"))
If West-region rows contain Apple, Pear, Apple, the result is Apple and Pear. The arrays supplied to FILTER must have matching dimensions: here both references span rows 2 through 20. FILTER, like UNIQUE, is available in Microsoft 365 and Excel 2021 or later.
Handle blank cells and messy text
A fixed range such as A2:A20 may contain empty cells. Depending on the source and how the cells were created, a blank entry can appear in the UNIQUE result, sometimes displayed as 0. If you want to exclude empty values, filter them out before passing the values to UNIQUE.
With product names in A2:A20: =UNIQUE(FILTER(A2:A20,A2:A20<>""))
This condition excludes genuinely empty cells and cells whose formulas return an empty string. It does not remove entries that contain spaces, such as a cell with one space. Extra spaces and hidden characters can make entries that look alike behave as different values. If the source has inconsistent spacing, clean it first or use a helper column to normalize the text; for example, TRIM can remove ordinary leading and trailing spaces. Be cautious with cleanup because it may change data that is intentionally formatted that way.
If every cell in the source is blank, FILTER has no matching value to return and normally produces a #CALC! error unless you provide its optional empty-result argument. If an all-blank range is possible in your workbook, decide what you want displayed in that case and build that case into the formula or keep the source range limited to populated rows.
What counts as the same value?
UNIQUE compares the underlying values, not just how they look on screen. Two dates that display in different formats may represent the same date value, while numbers stored as text may differ from numeric values. Likewise, values that appear identical can remain separate if one includes an extra space or an invisible character. Check the underlying cell contents and formats when a result seems unexpectedly repeated.
Text comparisons in Excel’s standard functions are generally not case-sensitive, so entries such as north and North are treated as the same for a typical distinct list. If the exact capitalization or another specialized comparison rule matters, UNIQUE alone may not meet that requirement; a case-sensitive formula requires a different approach. Also remember that UNIQUE removes repeated results, not duplicate records based on a chosen key unless you provide the appropriate column or combination of columns.
Where the results go and common errors
The formula cell is the top-left corner of a spill range. If any cell where Excel needs to place a result is occupied, the formula returns #SPILL!. Clear the blocking cells, move the formula, or make room for the full list. A spill formula cannot spill inside an Excel Table, so place it in the worksheet grid outside the table. The source data itself can be stored in a table; a structured reference can make the formula adjust as table rows are added.
- Only one result appears: check whether the source really contains different values and whether the formula refers to the intended range.
- A blank or zero appears: inspect the source for empty cells and use FILTER to exclude them if needed.
- The output does not update as expected: confirm that the source range includes the new rows. A fixed reference such as A2:A20 does not automatically include data entered in A21.
- You see #NAME?: the Excel version may not support UNIQUE, or the function name may be misspelled. Use a compatible version or an alternative method.
UNIQUE versus Remove Duplicates and older Excel
UNIQUE is usually the better choice for a live report because it leaves the source unchanged and recalculates when the referenced data changes. By contrast, Data > Remove Duplicates removes repeated rows from the selected data. It is useful when you intend to clean the source itself, but it is a destructive edit rather than a formula-generated view; make a copy first if you need to preserve the original records.
For a one-time distinct copy, Excel also offers Advanced Filter under Data > Sort & Filter > Advanced. It can copy unique records to another location without using a dynamic formula. Menu wording and placement can vary slightly by platform. In older Excel versions without UNIQUE, Advanced Filter or a carefully prepared helper-column method can provide a distinct list, but the result will not have the same automatic spill-and-update behavior. If you use a method that edits source data, keep an unchanged backup.
Frequently Asked Questions
How do I get a unique list in Excel?
With values in A2:A20, enter =UNIQUE(A2:A20) in an empty cell. It returns one copy of each different value and spills the results into adjacent cells.
How do I make the distinct list alphabetical?
With values in A2:A20, use =SORT(UNIQUE(A2:A20)). SORT orders the returned values without changing the source list.
What is the difference between UNIQUE and exactly once?=UNIQUE(A2:A20) shows each different value once, even if it repeats in the source. =UNIQUE(A2:A20,FALSE,TRUE) shows only values that occur exactly one time.
Why does UNIQUE show a blank or zero?
The source range may include empty cells. Filter out empty strings first with =UNIQUE(FILTER(A2:A20,A2:A20<>"")), adjusting the reference to match your data.
Does UNIQUE work in older Excel versions?
UNIQUE is supported in Microsoft 365 and Excel 2021 or later, but not in many older perpetual versions. For those versions, use Data > Sort & Filter > Advanced or another non-dynamic approach.
Related
- Excel Find Hidden Names: Locate, Review, and Manage Them
- Excel Find Max Value Based on Criteria in Your Data
- Excel Find Duplicates and Count Matching Values Easily
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel RIGHT Function Examples: Extract Text from the EndUse Excel’s RIGHT function to return characters from the end of a text value. For example, if A2…
- Power Query VLOOKUP: Match Data From Another TableYes. In Excel for Microsoft 365, Power Query can look up values in another table by merging the…
- Filter by Text Color in Excel: Steps, Limits, and TipsYes. In Excel for Microsoft 365 on Windows, you can filter a column by font color: open its…
- Excel Filter Command for Multiple Criteria ExplainedIn Excel for Microsoft 365 on Windows, use Data > Filter, then set a condition from each relevant…
- Filter Cells With Specific Text in Excel: A Quick GuideTo filter cells by text in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- Excel NPV Formula Year by Year: How It WorksIn Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.