In Microsoft 365 for Windows, use =UNIQUE(A2:A100) to list distinct values from cells A2:A100; Excel spills the results into the cells below. To omit blank cells and sort the list, use =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))).
The Basic UNIQUE Formula
UNIQUE returns one copy of each distinct entry in a range or array. With names in A2:A100, enter this in an empty cell outside the source data:
With names in A2:A100: =UNIQUE(A2:A100)
Excel returns a vertical list in the order each value first appears in the source. For example, if A2:A8 contains Pear, Apple, Pear, Banana, a blank, Apple, and Mango, the result is Pear, Apple, Banana, and Mango. Repeated entries are not repeated in the output.
The returned list is a dynamic array: one formula in one cell produces results in multiple cells. You do not need to copy the formula down or select a group of cells first. If the source values change, Excel updates the results automatically.
How to Enter the Formula
To create a unique list in a worksheet:
- Find a clear area with enough room below the formula cell for the results.
- Click the top cell where the list should begin, such as C2.
- Type the formula using the source range, for example
=UNIQUE(A2:A100). - Press Enter. Excel displays the first result in the formula cell and spills the remaining values below it.
The example formula assumes the source list is in A2:A100. If your data starts in a different column or row, replace that range. Avoid putting the formula inside the source range: a formula that refers to its own output can cause a circular reference.
In current Microsoft 365 versions for Windows, Mac, and Excel for the web, the formula is entered in the same basic way. The surrounding interface differs slightly by platform, but the spill behavior is the same. Some regional settings use semicolons instead of commas between function arguments; if Excel rejects a formula with commas, check the list separator used by your Excel installation.
Exclude Blanks and Sort the Results
A source range may contain empty cells, especially when it is larger than the current list. Depending on the source and how blank cells are represented, UNIQUE can return a blank-looking result or a zero for an empty cell. FILTER removes empty entries before UNIQUE builds the list.
With values in A2:A100 and the goal of excluding blank cells: =UNIQUE(FILTER(A2:A100,A2:A100<>""))
In this formula, FILTER keeps entries for which the cell is not equal to an empty string. UNIQUE then removes duplicates from the filtered array. This is useful when the range includes unused rows or formulas that return an empty string.
UNIQUE preserves the order of first appearance; it does not alphabetize the results. To sort the unique nonblank values in ascending order, combine it with SORT:
With values in A2:A100 and a sorted, blank-free result desired: =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
For the example list Pear, Apple, Pear, Banana, blank, Apple, Mango, this produces Apple, Banana, Mango, Pear. SORT uses the normal ordering rules for the values involved. If the source contains a mix of data types or custom sorting needs, inspect the output rather than assuming it will follow a business-specific order.
What Counts as a Duplicate?
UNIQUE returns distinct values, not necessarily text that looks different on screen. Excel compares underlying cell values, so number formats can make different values appear similar, or make the same stored value look different. For example, a date is stored as a number with a date format. A cell formatted to display only a month may conceal differences in the underlying dates.
Before relying on the result, consider what “unique” should mean for your task. Should “North” and “north” count as the same label? Should a numeric 12 and the text entry “12” be grouped together? Should two dates in the same month count as one value? UNIQUE is designed to identify distinct entries in the input, not to apply custom cleanup or grouping rules. Standardize the source first if your definition requires trimming spaces, changing capitalization, converting text to numbers, or grouping dates.
Leading or trailing spaces are a common source of apparent duplicates: “Maple” and “Maple ” may look alike but contain different text. Similarly, a nonprinting character copied from another system can make two labels differ. If a unique list seems to contain duplicates, inspect the source entries and clean inconsistent data rather than assuming the formula is broken.
Dynamic Arrays, Spill Ranges, and Updates
The formula occupies only its first cell, while Excel reserves the cells needed for its spill results. A blue outline may appear around the spill range when you select the formula cell. Click the formula cell to edit the formula; editing a cell within the resulting list is not the way to change the source values.
If a cell in the required output area is occupied, Excel may show a #SPILL! error. Clear the obstructing cells, remove merged cells in the spill area, or move the formula to a place with sufficient space. The number of results can grow as new distinct source values are added, so leave more room than the current list needs.
A normal range such as A2:A100 does not automatically expand beyond row 100. If new records may be added below that row, use a suitably larger range or convert the source data to an Excel table and refer to its column. For example, if an Excel table is named Sales and its column heading is Region, this formula returns the unique entries in that table column:
With an Excel table named Sales and a column named Region: =UNIQUE(Sales[Region])
Structured table references expand as rows are added to the table, making them convenient for ongoing lists. The formula itself still needs an unobstructed spill area, usually outside the table; dynamic array formulas cannot spill inside an Excel table.
Version and Compatibility Requirements
UNIQUE, FILTER, and SORT are available in Microsoft 365 and Excel 2021 or later. They are also supported in current Excel for Mac and Excel for the web. A workbook containing these formulas may not calculate correctly in older perpetual versions such as Excel 2019 or 2016, which do not include these dynamic-array functions.
When someone opens a workbook in an older Excel version, they may see an unsupported-function error or a value that does not update as expected. If the file will be shared with users on older releases, either provide a static copy of the resulting list or use an older-version method that suits the workflow. Confirm compatibility with the recipients’ Excel versions before building a process that depends on dynamic arrays.
Formula or Excel’s Remove Duplicates Tool?
A formula is usually the better choice when the list should refresh as the source changes. It leaves the original data intact and recalculates the output. It is also easy to maintain: update the source range or add rows to a table, and the formula returns the current distinct values.
Use Excel’s Remove Duplicates command when you want to change a dataset by deleting repeated rows, not create a separate live list. Select the data, then choose Data > Remove Duplicates. This edits the selected data and is not a substitute for a formula when the original records must be preserved. Keep a copy before using a destructive cleanup command.
For a one-time list that needs no ongoing update, you can also copy and paste formula results as values after checking them. In Microsoft 365 for Windows, copy the spill results and use Paste Special > Values from the Home tab or the paste options. This separates the final list from its source formula, but later source changes will not update the pasted values.
Common Problems and Fixes
- #SPILL! appears: Check whether cells below or beside the formula are occupied, merged, or otherwise blocking the result. Clear the spill area or move the formula.
- A blank or zero appears: Filter out empty cells with FILTER, and check whether the source contains formulas returning empty strings or actual zero values.
- Duplicates still seem to appear: Look for extra spaces, nonprinting characters, inconsistent spelling, or values that only look identical because of number formatting.
- The formula name is not recognized: Check the Excel version. UNIQUE requires Microsoft 365 or Excel 2021 and later; older Excel versions do not support it.
- The output stops including new data: The formula may refer to a fixed range that ends before the new rows. Expand the range or use a structured table reference.
- Excel rejects the separators: Some regional settings use semicolons in formulas. Use the separator configured for your copy of Excel.
Frequently Asked Questions
How do I get unique values from a column in Excel?
With the list in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. Excel spills one copy of each distinct value into the cells below.
How do I list unique values without blanks?
With source values in A2:A100, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")). FILTER excludes empty entries before UNIQUE removes duplicates.
How do I sort a unique list alphabetically?
With values in A2:A100, enter =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))). For text entries, the result is sorted in ascending order and blanks are excluded.
Can I use UNIQUE in Excel 2019 or 2016?
No. UNIQUE is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or 2016. Use a compatible one-time tool such as Advanced Filter, or produce and save a static unique list.
Why does my unique list show #SPILL!?
Excel cannot place all results because one or more cells in the spill range are blocked, or the range contains a layout obstacle such as merged cells. Clear the output area or move the formula to an open part of the sheet.
Related
- Excel Find Shortcut Key: Search Cells Fast
- Excel Find Minimum Value Based on Criteria in a Range
- Excel: Find Names in Two Worksheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIFS Not Blank: Multiple Criteria GuideTo count non-blank cells with multiple criteria in Excel, use the COUNTIFS function with the criteria "" (meaning…
- IFERROR Function in Excel: Syntax, Examples, and TipsIn Excel, use IFERROR to return a chosen result when a formula produces an error: the syntax is…
- How to Concatenate Names in Excel With a Space EasilyTo concatenate first and last names in Excel with a space, put the first name in A2 and…
- C# String Functions: A Practical GuideC# string functions are methods and properties on the built-in string type, also called System.String. Common choices include…
- DAX SUMPRODUCT: Multiply and Sum Values in Power BIDAX does not have a built-in SUMPRODUCT function. To multiply values row by row and add the results,…
- Excel Yield Analysis: Formulas for Bonds and ProductionExcel yield analysis depends on what you mean by yield: for a bond’s yield to maturity, use Excel’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.