Yes. In Microsoft 365 for Windows, enter =UNIQUE(A2:A100) to return the distinct values in A2:A100, with repeated entries appearing only once. The result spills into cells below the formula; use the optional exactly-once setting instead if you want to keep only values that occur once in the original list.
What Excel UNIQUE does
UNIQUE compares items in a range or array and returns a new array containing the distinct items. It does not delete or edit the source data. That makes it useful for making a live list of product names, customers, categories, or other entries without repeated values.
For example, imagine A2:A7 contains Pear, Apple, Pear, Plum, Apple, and Grape. UNIQUE returns Pear, Apple, Plum, and Grape: each different value is represented once, and the first-seen order is preserved. If the source list changes, the result updates automatically, provided the formula has room to spill.
A common misunderstanding is that “unique” means “appears only once.” In the function’s default mode, it means “distinct”: a value that occurs several times still appears once in the result. Excel has a separate option for returning only values whose original count is exactly one.
How to enter the basic formula
With the list in A2:A7 as described above, enter this formula in an empty cell such as C2:
With the source values Pear, Apple, Pear, Plum, Apple, and Grape in A2:A7, use: =UNIQUE(A2:A7)
The result in C2:C5 is Pear, Apple, Plum, and Grape. You only enter the formula in C2; Excel fills the cells below it automatically. Leave the cells where the results will appear empty, and do not type into the middle of the spilled result.
In current Excel for Microsoft 365, you can enter the formula in the formula bar or directly in the cell and press Enter. The formula works similarly in current Excel for Mac and Excel for the web. The spill behavior depends on having a version with dynamic arrays; older editions such as Excel 2019 do not support the UNIQUE function.
Understand the function’s options
UNIQUE has three arguments: the range or array to examine, an optional setting for whether to compare columns rather than rows, and an optional setting for returning items that occur exactly once. If you omit the optional settings, Excel compares rows and returns each distinct row once.
For an ordinary one-column list, you generally need only the first argument. For a table with multiple columns, the default comparison is across whole rows: a row is considered a duplicate only when all of its values match the corresponding values in another row. This is useful when a record is a combination of fields, such as a customer and a region.
With names in A2:A8 and departments in B2:B8, enter the formula in D2 to return distinct name-and-department pairs:
=UNIQUE(A2:B8)
If the same name appears with two different departments, both rows remain because they are different pairs. If you need distinct names regardless of department, refer only to the names column, rather than passing both columns to UNIQUE.
The column-comparison option is for data arranged horizontally. With values across A1:F1, enter this formula in A3 to return distinct values as a horizontal result:
=UNIQUE(A1:F1,TRUE)
Return values that occur exactly once
Use the exactly-once option when repeated values should be excluded altogether. This is different from the default, which retains one copy of a repeated value. It can help identify one-off entries, such as a product recorded in just one row of a particular list.
With values in A2:A10, enter this formula in C2 to return only items appearing once in that range:
=UNIQUE(A2:A10,FALSE,TRUE)
For example, if A2:A7 contains Pear, Apple, Pear, Plum, Apple, and Grape, this version returns Plum and Grape. Pear and Apple are left out because each appears twice. FALSE keeps the usual row-based comparison; TRUE requests values that occur exactly once.
For a multi-column range, exactly-once applies to the rows being compared. If a particular name occurs in several rows but with different departments, those name-and-department combinations may each be distinct rows. Choose the input columns to match what you mean by a duplicate.
Sort the results and exclude blanks
UNIQUE preserves the order in which it first encounters distinct values. It does not alphabetize the results automatically. To sort a distinct list in ascending order, combine UNIQUE with SORT.
With values in A2:A100, enter this formula in C2 to return a sorted list of distinct entries:
=SORT(UNIQUE(A2:A100))
For a text list, the result is sorted alphabetically; for numbers, it is sorted numerically. SORT changes the display order of the results, not the source data. If the original order matters, use UNIQUE by itself.
Blank source cells can also affect the result. Depending on the source and how the blank is represented, a blank entry may appear as a zero in the spilled list. To ignore empty cells, filter them out before passing the values to UNIQUE.
With entries in A2:A100, enter this formula in C2 to return distinct nonblank values:
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
FILTER removes cells that are empty or evaluate to an empty string, and UNIQUE then removes repeated values from what remains. If no entries meet the filter condition, FILTER can return an error unless you supply its optional if-empty argument. Also check whether cells that look blank actually contain spaces; a space is text, not an empty cell.
Dynamic arrays and common problems
UNIQUE is a dynamic-array function. Its formula sits in one cell, called the anchor cell, while the other results occupy a spill range. Excel updates that range when the source data changes. Formulas that refer to the entire output can use the spill reference operator: with the UNIQUE formula in C2, =COUNTA(C2#) counts the spilled results. Here, C2# means the whole current spill range, not a fixed-size set of cells.
If Excel displays a #SPILL! error, another value or formula is blocking part of the area where the results need to go. Clear the obstructing cells, check for merged cells in the spill area, and ensure that the formula is not inside an Excel table. Dynamic-array formulas spill into the worksheet grid, but they cannot spill from within a table. You can place the formula outside the table and refer to the table’s data instead.
If the formula returns #NAME?, your Excel version may not include UNIQUE, or the function name may be misspelled. UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual releases such as Excel 2019 do not provide it. Opening a workbook containing UNIQUE in an unsupported version may prevent the formula from calculating there.
Unexpected duplicates often come from data that looks alike but is not identical. For example, “Mango” and “Mango ” have different text because the second entry contains a trailing space. Extra spaces, inconsistent punctuation, and different number formats can make a list look inconsistent; inspect the source values when the output surprises you. Excel’s ordinary text comparison for UNIQUE is not case-sensitive, so entries that differ only by capitalization are generally treated as the same value.
UNIQUE versus Remove Duplicates
Use UNIQUE when you want a separate result that stays connected to the source and recalculates as the source changes. It is a formula, so you can combine it with functions such as SORT and FILTER and use its spill range in other calculations.
Use Remove Duplicates when you want to remove repeated records from the selected data itself. On Windows, the command is on the Data tab: select the range or table, then choose Data > Remove Duplicates. Excel asks which columns to check. Removing duplicates changes the selected data, so keep a copy if you may need the original rows.
- Choose UNIQUE for a refreshed, non-destructive list of distinct values.
- Choose Remove Duplicates to clean a dataset in place.
- When checking several columns with Remove Duplicates, select the columns that define a duplicate record; the choice is similar in purpose to giving UNIQUE the corresponding multi-column range.
These tools answer slightly different needs. A formula result is a view of the distinct items, not a replacement for the underlying list. Conversely, Remove Duplicates is an edit to the data and will not automatically recreate removed rows when the source changes.
Frequently Asked Questions
Does UNIQUE remove duplicates from the original data?
No. It returns a separate spilled result and leaves the source range unchanged. Use Data > Remove Duplicates if you need to alter the source list.
How do I get one copy of each value?
With the values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. The result contains one copy of each distinct entry.
How do I return only values that appear once?
With the values in A2:A100, enter =UNIQUE(A2:A100,FALSE,TRUE). Repeated entries are excluded, rather than represented once.
Can I use UNIQUE in Excel 2019?
No. UNIQUE requires Microsoft 365 or Excel 2021 or later. In an older version, you can use Data > Remove Duplicates for a one-time cleanup, or use a more involved legacy formula approach.
Why am I seeing #SPILL!?
One or more cells in the output area are occupied, merged, or otherwise preventing the result from expanding. Clear the spill area or move the formula to a location with enough unobstructed space.
Choosing the right approach
For the usual request—show each item from a list once—start with =UNIQUE(range). Add SORT if the output needs ordering, FILTER if blanks or unwanted rows should be excluded, and the exactly-once option only when repeated values must disappear completely. If your Excel version does not support dynamic arrays, or you need to permanently edit the source, use an alternative such as Remove Duplicates. Matching the method to the task avoids the most common mistake: confusing a distinct list with a list of values that occur only once.
Related
- Excel Shortcut to Select to the Bottom of a Column
- Excel Shortcut to Select All Rows in a Column at Once
- Excel Shortcut to Change Lowercase to Uppercase Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sort Entire Row by One Column Without Splitting DataYes. To sort entire rows by one column in Excel for Microsoft 365 on Windows, select the full…
- Excel Spreadsheet Sort by Highest Number: Steps & TipsTo sort an Excel spreadsheet by its highest number, click a cell in the number column and choose…
- Create a Sequence of Dates in Excel: Formulas and FillTo create a sequence of dates in Excel, enter a starting date and use =SEQUENCE(10,1,DATE(2026,1,1),1) to spill ten…
- Excel Rank Using COUNTIFS: Formula, Ties, and GroupsTo rank numbers in Excel with COUNTIFS, count the values greater than the current value and add 1.…
- Excel COUNTIF Equal to a Number: Formula and ExamplesTo count cells equal to a number in Excel, use =COUNTIF(B2:B10,100), replacing the range and number with your…
- Median Formula Examples in Excel and by HandThe median is the middle value after a set of numbers is arranged from smallest to largest; with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.