In Microsoft 365, Excel 2021, or Excel 2024, use =UNIQUE(A2:A100) to return a list of distinct values from A2:A100. Enter it in an empty cell; Excel spills the results into the cells below, leaving one copy of each value in first-appearance order.
The basic UNIQUE formula
Suppose a worksheet has product names in A2:A8: Tea, Coffee, Tea, Juice, Coffee, Water, and Tea. With those names in A2:A8, enter this formula in C2:
=UNIQUE(A2:A8)
The spilled result in C2:C5 is Tea, Coffee, Juice, and Water. Each distinct entry appears once, and the original sequence of first appearances is preserved. The formula does not alter or remove anything in column A; it creates a separate result that updates when the source values change.
UNIQUE is a dynamic array function. You type the formula once, in the top-left cell where you want the result, and Excel fills the required cells automatically. A formula that returns multiple cells is called a spilled array; cells in the spill range are results of the single formula, not separate formulas to copy down.
How UNIQUE decides what counts as unique
UNIQUE compares the items in the supplied array and returns each distinct item once. For a single column, it checks values down that column. For a table with several columns, it can instead compare complete rows. The function preserves the order in which distinct items first occur; it does not sort them automatically.
For example, with city names in A2:A6 in the order Oslo, Lima, Oslo, Rome, and Lima, the result is Oslo, Lima, Rome. If you need alphabetical order, combine UNIQUE with SORT. With city names in A2:A6, place this formula in C2:
=SORT(UNIQUE(A2:A6))
The result is Lima, Oslo, Rome. Sorting is separate from removing duplicates, so use the simpler formula when retaining the source order matters, such as when the list represents the order in which customers or events first appeared.
Excel generally treats text comparisons in UNIQUE as case-insensitive. Text entries such as “North” and “north” are therefore not a dependable way to represent two separate categories. If capitalization carries meaning in your data, standardize the labels or use a deliberately designed case-sensitive approach rather than assuming UNIQUE distinguishes letter case.
Return values that appear exactly once
A distinct list and a list of one-time occurrences are not the same thing. The ordinary formula keeps one representative of every value, even when that value appeared many times. The optional third argument, exactly_once, changes the result so it includes only entries that occur once in the source.
With values in A2:A7 reading Red, Blue, Red, Green, Blue, and Gold, enter this formula in C2:
=UNIQUE(A2:A7,FALSE,TRUE)
The result is Green and Gold: each appears once in the source. Red and Blue are omitted because each occurs twice. The second argument, FALSE, tells Excel to compare down the column; the third argument, TRUE, requests values that occur exactly once. This option is useful for finding one-off codes or names, but it is not the setting to use when the goal is simply to remove repeated entries.
Unique rows and multiple columns
UNIQUE can compare complete records, not just individual cells. If a source contains first name in column A and department in column B, selecting both columns returns distinct name-and-department combinations. A person listed twice with the same department is one repeated row; the same person paired with a different department is a different row.
With first names in A2:A6 and departments in B2:B6, enter this formula in D2 to return distinct two-column records:
=UNIQUE(A2:B6)
For example, if the rows are Maya/Sales, Leon/Support, Maya/Sales, Maya/Finance, and Leon/Support, the result contains Maya/Sales, Leon/Support, and Maya/Finance. It does not return a separate unique list of names and departments; it deduplicates rows based on the combination of values across the selected columns.
The by-column option is for a different layout: it tells UNIQUE to compare columns rather than rows. Most ordinary lists are vertical, so the default row-oriented comparison is appropriate. Use the by-column setting only when the records you want to compare are arranged horizontally.
Ignore blank cells in the source
If the source range contains empty cells, a basic UNIQUE formula can include a blank in its result. To exclude blanks, filter the source to nonblank cells before passing it to UNIQUE. With entries in A2:A100, put this formula in C2:
=UNIQUE(FILTER(A2:A100,A2:A100<>"",""))
FILTER keeps only cells whose value is not an empty string, and UNIQUE removes duplicates from that filtered set. The final empty-string argument tells FILTER what to return if no nonblank cells match, avoiding its no-results error in that situation. In HTML, the less-than sign in the displayed formula is written as an entity; the formula to enter in Excel uses the ordinary less-than sign.
There is a distinction between a genuinely empty cell and a formula that returns an empty string. A visual blank may be produced by a formula, and it can behave differently from a truly empty cell in other calculations. If blank handling matters, check how the source values are created and test the formula on representative rows.
Where to enter the formula and how to use the result
Choose a destination cell with enough unused space below it, or below and to the right for a multi-column result. Type the formula in that cell and press Enter. Excel displays the formula in the top-left cell and marks the output range with a border when the formula cell is selected. The results can then feed charts, validation lists, summaries, or other calculations.
- For a dropdown list, use the spilled result as the source for Data Validation. In the source field, refer to the spill range with the top-left cell followed by the spill operator, for example
=C2#, if the UNIQUE formula is in C2. The spill operator is available in current dynamic-array Excel versions. - For a chart or another formula, refer to the spill range rather than guessing how many results will appear. The range expands or contracts as the source list changes.
- Keep the output area clear. Existing values, merged cells, or other obstacles in the needed spill area can prevent the array from expanding.
To make the source easier to maintain, consider converting the source data into an Excel Table with Insert > Table. A table expands as new rows are added. A formula referencing a table column can therefore be more convenient than repeatedly extending a fixed range; structured-reference details depend on the table and column names.
Version support and older Excel alternatives
UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Excel for the web also supports dynamic array formulas. If a workbook is opened in an older desktop version such as Excel 2019 or Excel 2016, UNIQUE may not calculate there and can appear as an unsupported function. Check the Excel version used by the people who need to open the file before building a workflow around it.
For a one-time list in Excel for Windows, use the built-in Advanced Filter instead of a formula. Select the source range, open Data > Advanced in the Sort & Filter area, choose whether to filter the list in place or copy it to another location, and select the option for unique records only. This creates a result at that moment; it does not provide the same automatically updating spill formula.
Another one-time option is Data > Remove Duplicates. Work on a copy if you need to retain every original row: this command removes repeated records from the selected data rather than generating a separate live list. In a multi-column selection, choose the columns that define a duplicate carefully, because the selected columns determine which rows Excel treats as repeated.
Common errors and fixes
A #SPILL! error usually means Excel cannot place the complete result in the required cells. Look for content in the spill area, merged cells, or a formula placed inside an Excel Table. Clear or move the obstruction, or put the formula in a suitable cell outside the table. A dynamic array formula cannot spill into occupied cells.
A #NAME? error can mean the installed Excel version does not recognize UNIQUE, or that the function name was typed incorrectly. Confirm version support and check the spelling. A #CALC! result can occur when an operation such as FILTER finds no matching values and no alternative result was supplied; the blank-filtering example above includes an if-empty value for that case.
Unexpected duplicates often come from values that look alike but are not actually identical: trailing spaces, nonprinting characters, different punctuation, or numbers stored as text can complicate a list. Inspect the source data before changing the formula. Cleaning or standardizing the source may be better than trying to hide inconsistent labels in the output.
If the formula appears correct but does not change after an edit, check that calculation is set to automatic under Formulas > Calculation Options > Automatic. Also verify that the edited cell is within the referenced range. A fixed source range such as A2:A100 will not include a new value entered in A101 unless the range is extended or the source is converted to a table and referenced appropriately.
Frequently Asked Questions
What is the formula for a unique list in Excel?
For a vertical list in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. Excel returns each distinct value once and spills the results into adjacent cells.
How do I get a unique list in alphabetical order?
With the source list in A2:A100, use =SORT(UNIQUE(A2:A100)). UNIQUE removes repetitions, and SORT arranges the remaining values in ascending order.
How do I return only values that occur once?
With the list in A2:A100, use =UNIQUE(A2:A100,FALSE,TRUE). The third argument requests values whose count in the source is exactly one, rather than one copy of every distinct value.
Why does UNIQUE show a #SPILL! error?
Excel needs an unobstructed area for all returned items. Clear cells in the spill range, unmerge cells that block it, or move the formula to a larger clear area. If the formula is inside a table, place it outside the table.
Can I use UNIQUE in Excel 2019?
UNIQUE is not supported in Excel 2019. For a static result, use Data > Advanced with the unique-records option or Data > Remove Duplicates. Those tools do not behave like an automatically updating UNIQUE formula.
Related
- Excel Number Format: How Many Digits Can It Really Show?
- Excel Formatting Keyboard Shortcuts: A Practical Guide
- Excel Format Numbers in Thousands (K)
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- SUMIF Excel Criteria: Syntax, Examples, and Common FixesIn Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with…
- XLOOKUP Formula: Real Examples in ExcelThe basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the…
- Excel Formula to Find All Tab Names in Your WorkbookExcel has no built-in worksheet formula that directly returns every tab name. In Microsoft 365 for Windows, you…
- Excel Date Plus 3 Months: Formula, Rules, and ExamplesTo add three calendar months to a date in Excel, use =EDATE(A2,3), assuming the starting date is in…
- Excel Formula for a List of Numbers: 10 Useful OptionsTo create a list from 1 to 10 in Excel, enter =SEQUENCE(10) in an empty cell; it spills…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.