For a basic list of distinct entries in Microsoft 365, with source values in A2:A20, enter =UNIQUE(A2:A20) in an empty cell. The formula spills the distinct values into the cells below it; the options here show how to sort results, filter them, handle rows and blanks, and work in older Excel versions.
1. Return a basic list with UNIQUE
With the source list in A2:A20, enter =UNIQUE(A2:A20) in a blank cell outside the source data. Excel returns each different value once, in the order it first appears, so a list such as Red, Blue, Red, Green returns Red, Blue, Green. The result is a dynamic array: when you add or remove entries in the referenced range, Excel recalculates the output, provided the spill area is clear. This is a good starting point for category lists, customer names, or inventory types, but it does not sort the results or remove blank entries for you.
2. Sort the unique list alphabetically
With values in A2:A20, enter =SORT(UNIQUE(A2:A20)) in an empty cell. UNIQUE first removes repeated values, then SORT orders the remaining results in ascending order, which is usually alphabetical for text and numeric order for numbers. Sorting makes a report easier to scan and keeps a dropdown list from changing order just because a new category appeared near the top of the source data. SORT and UNIQUE are available in Microsoft 365 and Excel 2021 or later; if you need descending order, SORT accepts a sort-order argument, but the default ascending order is sufficient for most lookup lists.
3. Find unique records across several columns
With complete records in A2:C20, such as product, region, and status, enter =UNIQUE(A2:C20) in a blank area. By default, UNIQUE compares rows, so it returns one copy of each exact three-column combination rather than deduplicating each column independently. For example, two rows with the same product and region but different status values remain separate records, while an identical repeated row appears only once. This is useful for building a distinct list of combinations without deleting information from the original table; the columns should describe one record and have matching row lengths.
4. Keep values that occur exactly once
With entries in A2:A20, enter =UNIQUE(A2:A20,FALSE,TRUE) in an empty cell. The second argument tells Excel to compare items down the column, and the third asks for values that appear exactly one time, not values that merely have duplicates removed. If the source contains Red, Blue, Red, Green, the result is Blue and Green; Red is left out because it appears twice. Use this for finding one-off transactions, names that occur once in a roster, or exceptions in a list, and remember that a value repeated three times is excluded just like one repeated twice.
5. Exclude blank cells from the results
With possible blanks in A2:A20, enter =UNIQUE(FILTER(A2:A20,A2:A20<>"","")) in an empty cell. FILTER removes empty source cells before UNIQUE builds the distinct list, which avoids an apparently empty item at the top or bottom of a category list. The third FILTER argument returns an empty string if there are no nonblank values, instead of the #CALC! error that FILTER can return when nothing matches. This pattern is especially helpful when a source range is deliberately larger than the current dataset, though cells containing a formula that returns an empty string may look blank while still being formula cells.
6. Return distinct values for one condition
With names in A2:A20 and regions in B2:B20, enter =UNIQUE(FILTER(A2:A20,B2:B20="East","")) in a blank cell. FILTER first keeps names whose corresponding region is East, and UNIQUE then removes repeated names from that filtered set. For instance, if the same customer appears on several East-region rows, the result contains that customer once, while customers listed only in West are omitted. Replace East with another exact region label or refer to a cell containing the chosen region; using a cell reference makes it easier to reuse the output for different selections without editing the formula.
7. Apply two conditions at the same time
With customer names in A2:A20, regions in B2:B20, and order values in C2:C20, enter =UNIQUE(FILTER(A2:A20,(B2:B20="East")*(C2:C20>=100),"")) in an empty cell. The multiplication combines the two TRUE/FALSE tests as an AND condition, so a row qualifies only when its region is East and its value is at least 100. UNIQUE then returns each qualifying customer once, even if that customer has multiple orders meeting both tests. Keep all three ranges the same height; mismatched array dimensions can produce an error, and the empty-string fallback prevents a no-match error when no row meets both conditions.
8. Get unique entries from a row
With entries arranged horizontally in A2:J2, enter =UNIQUE(A2:J2,TRUE) in an empty cell. Setting the second argument to TRUE tells UNIQUE to compare columns instead of rows, so duplicate entries across the row are removed and the remaining values spill horizontally. This works well for headers, monthly labels, or a single row of tags gathered from different sources. The output direction follows the input orientation, so leave enough empty cells to the right for the spill; if you want to turn the horizontal result into a vertical list, use the transpose button or a separate TRANSPOSE formula.
9. Make the comparison case-sensitive
UNIQUE treats text values that differ only by letter case as duplicates, so Apple and apple do not reliably form separate entries with the basic pattern. With text in A2:A10, put =SUMPRODUCT(--EXACT(A2,$A$2:A2))=1 in B2 and fill it down through B10; EXACT checks each value against earlier entries with case sensitivity, and the helper returns TRUE only for the first occurrence of that exact spelling. With source values in A2:A10 and those TRUE/FALSE flags in B2:B10, enter =FILTER(A2:A10,B2:B10) in D2 to spill the first occurrence of each case-sensitive value. This two-step method is easier to audit than a single long array formula, and it preserves first-seen order; add a separate blank check to the helper if blank entries should not appear.
10. Build a unique list in older Excel
For Excel 2019 or Excel 2016, with source values in A2:A10 and the output starting in D2, enter =IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$10),0)),"") as a legacy array formula, then copy it down far enough to cover the possible results. In these versions, confirm the formula with Ctrl+Shift+Enter on Windows; on Mac, use Control+Shift+Return, with Return sometimes labeled Enter by the keyboard. The formula looks for the first source value not already listed above, and IFERROR leaves later cells blank after all unique values have been returned. This approach is less convenient than UNIQUE because it needs copied formulas and an output range, but it can keep a workbook usable for people without dynamic-array Excel.
How to Choose
Start with the shape of the data. For one column, the plain UNIQUE formula is usually enough; choose the row-comparison version for whole records, or set by-column comparison for a horizontal source. Then decide what “unique” means for the task: deduplicating means keeping one copy of every distinct entry, while exactly-once means excluding any value that repeats at all. Those are different questions, and choosing the wrong interpretation can change the result substantially.
Next, consider which entries belong in the output. Use FILTER inside UNIQUE when you need a region, status, date, or threshold condition, and remove blank cells with a nonblank test if the source range includes unused rows. For reports and dropdown sources, sorting often makes the output easier to use. If case matters, add the EXACT helper approach, because the standard UNIQUE comparison does not distinguish text solely by capitalization.
Check compatibility before sharing the workbook. UNIQUE, SORT, and FILTER are available in Microsoft 365 and Excel 2021 or later, but the dynamic formulas may show #NAME? in older releases. The legacy INDEX, MATCH, and COUNTIF pattern is a better fit when recipients use Excel 2019 or 2016, though it needs array entry and copied rows. On Windows and Mac, Microsoft 365 supports the same dynamic-array formulas; Excel for the web also supports many of them, but ribbon and keyboard details can differ. Finally, check for a clear spill area: a value or merged cell in the intended output range can cause #SPILL!, even when the formula itself is correct.
Frequently Asked Questions
Does UNIQUE change or delete my original data?
No. UNIQUE creates a calculated result in a separate spill range and leaves the source cells untouched. To replace the source data with a permanent deduplicated list, copy the results and use Paste Special > Values.
Will the list update when I add a new value?
It recalculates when a value changes inside the formula’s referenced range. If the source is a fixed range such as A2:A20 and you add data in A21, expand the formula range or convert the source to an Excel table and use its column reference.
Why do I see a #SPILL! error?
Excel cannot place the complete dynamic result because one or more cells in the spill area are occupied, merged, or otherwise blocking it. Clear the obstructing cells, or move the formula to an area with enough open rows or columns.
Can I use UNIQUE in Excel 2019 or Excel 2016?
No; those releases do not include the dynamic-array UNIQUE function. Use the legacy INDEX/MATCH/COUNTIF array formula above, or open the workbook in Microsoft 365 or Excel 2021 and later.
Does UNIQUE distinguish uppercase and lowercase text?
No, not when the only difference is capitalization. Use the EXACT helper-column method when Apple and apple must count as different values.
Final Thoughts
For most current Excel workbooks, =UNIQUE(A2:A20) is the clearest way to create a live list of distinct values. Add SORT for an ordered result, FILTER for conditions, or the case-sensitive helper when capitalization carries meaning. Match the formula to the data layout and Excel version, and leave space for the result to spill.
Related
- Excel Format Numbers in Thousands (K)
- Excel Format Cells Shortcut: Quick Keys for Windows and Mac
- Excel Format as Number: Steps and Common Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average Between Dates in Excel: Formula and ExamplesTo average values between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2). This averages the numbers in B2:B100 whose corresponding…
- Excel Formula Cell Reference Lock: Absolute ReferencesTo lock a cell reference in an Excel formula, add dollar signs before the column letter, row number,…
- How to Calculate ROI Percentage in Excel With a FormulaTo calculate return on investment (ROI) in Excel, subtract the investment cost from its final value, divide the…
- Excel Formula Keyboard Shortcuts and How to Use ThemThe most useful Excel formula shortcuts on Windows are Alt+= to insert AutoSum, F2 to edit a cell,…
- Excel Formula for File Name: Workbook, Sheet, and PathTo return the current workbook’s file name in Excel, use =TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"["),"]") in Microsoft 365 or Excel 2024. The…
- Round Excel Numbers to the Nearest Ten With a FormulaTo round a number to the nearest ten in Excel, use the ROUND function with -1 as its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.