Guides

UNIQUE, SORT and SORTBY: Lists That Update Themselves

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 6 October 2026 · Last updated: 6 October 2026

Shahrukh needs a list of cities from a 20-row sales table, sorted A to Z, for a drop-down. Last month he copied the column, removed duplicates and sorted it by hand. Then two new rows came in and the list was out of date. One formula, =SORT(UNIQUE(Sales[City])), now keeps it current.

UNIQUE returns each value once. SORT returns a range sorted by one of its own columns. SORTBY sorts a range by another range, such as reps by amount. All three are dynamic array functions: you type the formula in one cell and the answer spills into the cells below.

Download the practice file (unique-sort-sortby.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

=SORT(UNIQUE(Sales[City]))
=SORTBY(Sales[Rep],Sales[Amount],-1)

UNIQUE

=UNIQUE(array, [by_col], [exactly_once])

• =UNIQUE(Sales[City]) gives the 6 cities, each once.
• =UNIQUE(Sales[[Rep]:[City]]) gives each Rep + City pair once.
• =UNIQUE(Sales[City],,TRUE) gives only cities that appear exactly once.

Count distinct values with =ROWS(UNIQUE(Sales[City])). In the practice file it returns 6.

SORT

=SORT(array, [sort_index], [sort_order], [by_col])

• =SORT(UNIQUE(Sales[City])) sorts the city list A to Z. The first one is Kolhapur.
• =SORT(Sales,3,-1) sorts the whole table by its 3rd column (Amount), largest first.
• =INDEX(SORT(Sales[Amount],1,-1),1) returns the biggest amount: 9,100.

SORTBY

=SORTBY(array, by_array1, [sort_order1], ...)

=SORTBY(Sales[Rep],Sales[Amount],-1) lists reps in order of their amounts, highest first. The first name is Rani, who has the biggest single sale. SORTBY can sort by a column that you do not show, and by several columns: =SORTBY(Sales,Sales[City],1,Sales[Amount],-1).

Fixing #SPILL! and other surprises

• #SPILL!: something is in the way. Select the formula cell; Excel draws a dashed border around the spill area. Clear those cells.
• Spilled formulas do not work inside an Excel Table. Put them on a normal range next to the table.
• Refer to the whole spilled list with the # sign: if the list starts in H2, use =H2# in a drop-down source or in another formula.
• Excel 2019 and older do not have these functions. Use Remove Duplicates and Data > Sort instead.