Excel UNIQUE Sort: List Distinct Values in Order

Formulas & functions · Updated November 18, 2026

To sort unique values in Excel, combine the UNIQUE and SORT functions: if the source values are in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. Excel returns each distinct value once, in ascending order, and spills the results into the cells below.

What a unique sort does

A unique sort answers two separate questions about a list: which values are distinct, and what order should they appear in? For example, a source list might contain repeated product names in the order Pear, Apple, Pear, Orange, Apple, Grape, Orange. A unique result removes repeated entries; sorting then puts the remaining names in order. The final list is Apple, Grape, Orange, Pear.

In Excel, “unique” usually means one copy of every different value, not only values that appeared once in the original data. That distinction matters. If Pear occurs three times, it still appears once in the result of UNIQUE. A separate option can return only values that occur exactly once.

This is useful for building a clean list of customers, categories, locations, or products; preparing dropdown choices; and checking which labels appear in a dataset. The combined formula is also dynamic: when eligible source data changes, Excel recalculates the sorted list automatically.

Use SORT and UNIQUE together

In Microsoft 365 for Windows, click an empty cell outside the source data and enter the formula shown above. Press Enter. The first result appears in the formula cell, and additional results fill the cells below it. This behavior is called spilling. Leave enough empty space for the output; existing content in its way can cause a #SPILL! error.

With values in A2:A8 entered as Pear, Apple, Pear, Orange, Apple, Grape, Orange, =SORT(UNIQUE(A2:A8)) returns Apple, Grape, Orange, Pear. It returns four values because there are four distinct names. The formula does not alter or rearrange the original list.

SORT uses ascending order by default. To reverse the order, with the same source values in A2:A8, enter =SORT(UNIQUE(A2:A8),1,-1). The result is Pear, Orange, Grape, Apple. The 1 identifies the first column of the array to sort by, and -1 requests descending order; 1 as the third argument requests ascending order.

UNIQUE and SORT are available in Microsoft 365 and Excel 2021 or later. Excel evaluates the nested functions from the inside out: UNIQUE creates the distinct list, then SORT orders that list. In older Excel versions, these functions may not be available, so use a built-in command or a PivotTable instead.

Sort unique rows, not just one column

Sometimes a record is made up of several fields, such as a product and its department. To identify distinct combinations, include all the relevant columns in the array. If product names are in A2:A100 and departments are in B2:B100, enter =SORT(UNIQUE(A2:B100)) in a clear area. Excel returns distinct product-and-department rows, sorted by the first column by default.

For instance, “Tea, Grocery” and “Tea, Cafe” are different rows because their department values differ. The formula will not collapse them into one Tea entry. If you want each product just once regardless of department, use only the product column as the input array, or decide which department value should represent that product before creating the list.

You can choose a different column in the returned array as the sort key. With product and department in A2:B100, =SORT(UNIQUE(A2:B100),2,1) sorts the distinct rows by the second column in ascending order. The whole row remains together; Excel does not sort the department values independently of their products.

Filter the source before making the unique list

You can combine FILTER, UNIQUE, and SORT when the list should include only records meeting a condition. Suppose product names are in A2:A100 and the corresponding region is in B2:B100. To list products sold in the West region, enter =SORT(UNIQUE(FILTER(A2:A100,B2:B100="West"))) in an empty cell. It filters to West rows first, removes repeated product names, and sorts what remains.

In that example, if West has Pear, Apple, Pear, and Orange, the result is Apple, Orange, Pear. The condition must match the values in the region column. Extra spaces or inconsistent labels such as “West ” and “West” can make records appear to be missing because they are not identical text.

FILTER, like UNIQUE and SORT, requires Microsoft 365 or Excel 2021 or later. If no records meet the condition, FILTER can return an error unless its optional empty-result argument is supplied. For a beginner’s first unique list, start with SORT(UNIQUE(...)); add filtering only when a real condition is needed.

Distinct values versus values that occur once

The phrase “unique values” is sometimes used to mean values that appear exactly one time. Excel’s default UNIQUE behavior is different: it returns one copy of every distinct value, even when that value occurs repeatedly. To keep only values occurring once, set the function’s third argument, exactly_once, to TRUE.

With the example list in A2:A8, enter =SORT(UNIQUE(A2:A8,,TRUE)) in an empty cell. The result is Grape, because Grape appears once, while Pear, Apple, and Orange each repeat. The two commas are intentional: the second argument is left blank so TRUE is supplied as the third argument. This option is useful for finding one-off entries, but it is not the normal way to create a deduplicated category list.

Use Excel’s Sort command for existing data

A formula is not always the right tool. If you want to rearrange the original records rather than create a separate list, use Excel’s sorting command. Select a cell in the data, then choose Data > Sort A to Z or Data > Sort Z to A. For more control, choose Data > Sort & Filter > Sort, select the column and order, and confirm whether the data has headers.

When the data has multiple columns, sort the complete table or range so that each row stays intact. If Excel asks whether to expand the selection, choose to expand it when the columns belong together. Sorting only a single column beside related data can detach names from their amounts, dates, or categories. Excel tables are convenient because sorting a table column keeps the records together.

Sorting does not remove duplicates. To remove duplicate records from a selected range, choose Data > Remove Duplicates and specify which columns define a duplicate. This command changes the selected data, unlike the formula approach, which produces a separate result. Make a copy first if you need to preserve the original list. On Mac, the same formula works; ribbon placement can differ by Excel release. Excel for the web also supports dynamic-array formulas in current Microsoft 365 accounts.

Blanks, errors, and other common problems

  • Unexpected blank-looking entries: A range may contain truly empty cells, formulas that return an empty string, or cells with spaces. These can behave differently. Check the source for stray spaces and decide whether blank entries belong in the output before applying a more complex formula.
  • #SPILL!: Clear cells in the intended output area, including cells that look empty but contain spaces or formulas. A dynamic-array result needs unobstructed cells to display all its values.
  • Results that seem duplicated: Text with extra spaces, punctuation differences, or other invisible characters may not be identical. For example, “Pear” and “Pear ” can be treated as different entries. Clean the source data when these variations are accidental.
  • Formula name not recognized: Check the Excel version. Dynamic-array functions such as UNIQUE and SORT are not available in every older release. If you use a version that does not support them, try Remove Duplicates or a PivotTable.
  • Wrong sort direction or field: The default is ascending order by the first column. Specify the sort index and order when sorting a multi-column result or when you need descending output.

Excel’s text comparisons in UNIQUE are generally not case-sensitive, so values differing only in letter case may be treated as duplicates. If capitalization itself carries meaning, preserve and inspect the source carefully rather than assuming this function will distinguish entries such as “east” and “East.”

Choose the method that matches the job

Use SORT(UNIQUE(...)) when you need a separate, automatically updating list of distinct entries in a chosen order. It is particularly effective for reports and helper lists that should reflect changes to source data. Use the exactly_once option only when the question is specifically which entries occur one time.

Use the Sort command when the aim is to reorder existing rows, and use Remove Duplicates when you intentionally want to delete repeated records from a selected range. A PivotTable is another useful option for summarizing categories or counting occurrences, especially when you need more than a simple list. These methods solve related but different problems: sorting changes order, deduplication changes which records remain, and a formula creates a calculated output.

For a reusable workbook, consider converting source data into an Excel table and keeping the formula output in a separate area. That makes the source easier to manage and reduces the chance that the formula’s spill range will collide with new data. Always verify a few results against the source when the list informs a report, since inconsistent labels can create separate entries even when they look almost alike.

Frequently Asked Questions

What is the formula to sort unique values in Excel?
If the source values are in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. It returns one copy of each distinct value in ascending order.

How do I sort unique values in descending order?
With the source values in A2:A100, use =SORT(UNIQUE(A2:A100),1,-1). The final argument of -1 specifies descending order.

Does UNIQUE return values that appear only once?
Not by default. It returns one copy of each distinct value, including values repeated in the source. Set its exactly_once argument to TRUE when you want only values that occur a single time.

Can I sort unique rows across multiple columns?
Yes. Supply the full set of columns, such as A2:B100, to UNIQUE, then wrap it in SORT. Excel treats each complete row as a record and keeps its fields together.

Why does my Excel formula show #SPILL!?
One or more cells needed for the results are occupied. Clear the obstructing cells or move the formula to a location with enough empty space for the entire list.

Can I do this in older Excel versions?
If your version lacks dynamic-array functions, use Data > Remove Duplicates to change a copy of the data, or create a PivotTable to display distinct categories. These alternatives do not update in the same way as a live SORT(UNIQUE(...)) formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.