UNIQUE, SORT and spill ranges
Updated · Works in: Excel 2021+, Microsoft 365 · not Google Sheets (no SORTBY) · Every example checked before release
Short answer
=UNIQUE(range) lists each value once and SEQUENCE(n) makes a list of numbers. In Excel, =SORT(array, column, -1) sorts descending, SORTBY sorts by another range, D2# refers to everything D2 spills and #SPILL! means something blocks it. Google Sheets writes SORT(range, column, FALSE) and has no SORTBY or # operator.
Dynamic-array functions return whole lists that spill into neighbouring cells. Four of them, UNIQUE, SORT, SORTBY and SEQUENCE, handle most list-building jobs.
UNIQUE: each value once
UNIQUE(range) keeps the first appearance of each value. Wrap it in SORT for an alphabetical list, or in COUNTA to count distinct values.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Product | Units | Revenue | Products | |
| 2 | Jan 2026 | North | Fern | 40 | $380.00 | Fern | |
| 3 | Jan 2026 | North | Monstera | 12 | $408.00 | Monstera | |
| 4 | Jan 2026 | South | Fern | 35 | $332.50 | ||
| 5 | Jan 2026 | South | Monstera | 9 | $306.00 | ||
| 6 | Feb 2026 | North | Fern | 44 | $418.00 | ||
| 7 | Feb 2026 | North | Monstera | 15 | $510.00 | ||
| 8 | Feb 2026 | South | Fern | 31 | $294.50 | ||
| 9 | Feb 2026 | South | Monstera | 11 | $374.00 | ||
| 10 | Mar 2026 | North | Fern | 52 | $494.00 | ||
| 11 | Mar 2026 | North | Monstera | 14 | $476.00 | ||
| 12 | Mar 2026 | South | Fern | 38 | $361.00 | ||
| 13 | Mar 2026 | South | Monstera | 13 | $442.00 | ||
| 14 | Apr 2026 | North | Fern | 47 | $446.50 | ||
| 15 | Apr 2026 | North | Monstera | 18 | $612.00 | ||
| 16 | Apr 2026 | South | Fern | 42 | $399.00 | ||
| 17 | Apr 2026 | South | Monstera | 16 | $544.00 |
SORT and SORTBY
SORT(array, column, -1) sorts descending by a column of the array. SORTBY(array, by_range, -1) sorts by another range. In Google Sheets write SORT(range, by_range, FALSE). For a top three, TAKE(SORTBY(A2:A9, E2:E9, -1), 3) keeps the first 3 rows (Excel 2024 and Microsoft 365; Google Sheets has no TAKE).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | By September | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Lina Chen | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Hugo Martin | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | Farid Haddad | |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | Eva Novak | |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | Ben Fischer | |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | Amara Osei | |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | Grace Kim | |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 | Diego Alvarez |
SEQUENCE and the # reference
SEQUENCE(12) is the numbers 1 to 12, and DATE(year, SEQUENCE(12), 1) every month's first day. In Excel, refer to a whole spill with #: D2#+6 shifts every date in it. Google Sheets has no # operator.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | 2026 | Month | ||
| 2 | Jan 2026 | ||||
| 3 | Holidays | Feb 2026 | |||
| 4 | 1 Jan 2026 | Mar 2026 | |||
| 5 | 3 Apr 2026 | Apr 2026 | |||
| 6 | 6 Apr 2026 | May 2026 | |||
| 7 | 1 May 2026 | Jun 2026 | |||
| 8 | 25 Dec 2026 | Jul 2026 | |||
| 9 | Aug 2026 | ||||
| 10 | Sep 2026 | ||||
| 11 | Oct 2026 | ||||
| 12 | Nov 2026 | ||||
| 13 | Dec 2026 |
#SPILL!: something is in the way
In Excel, a spilled result needs empty cells. Here a note in E3 blocks it; delete the note (or move the formula) and it spills. Google Sheets also refuses to overwrite data, with its own error message.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | 2026 | |||
| 2 | #SPILL! | ||||
| 3 | Holidays | note | |||
| 4 | 1 Jan 2026 | ||||
| 5 | 3 Apr 2026 | ||||
| 6 | 6 Apr 2026 | ||||
| 7 | 1 May 2026 | ||||
| 8 | 25 Dec 2026 | ||||
| 9 | |||||
| 10 | |||||
| 11 | |||||
| 12 | |||||
| 13 |
Check yourself
One real exercise from the “Dynamic arrays” unit. Checked on our server; no account needed.List each product once, in G2.
- Use UNIQUE
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Product | Units | Revenue | Products | ||
| 2 | Jan 2026 | North | Fern | 40 | $380.00 | |||
| 3 | Jan 2026 | North | Monstera | 12 | $408.00 | |||
| 4 | Jan 2026 | South | Fern | 35 | $332.50 | |||
| 5 | Jan 2026 | South | Monstera | 9 | $306.00 | |||
| 6 | Feb 2026 | North | Fern | 44 | $418.00 | |||
| 7 | Feb 2026 | North | Monstera | 15 | $510.00 | |||
| 8 | Feb 2026 | South | Fern | 31 | $294.50 | |||
| 9 | Feb 2026 | South | Monstera | 11 | $374.00 | |||
| 10 | Mar 2026 | North | Fern | 52 | $494.00 | |||
| 11 | Mar 2026 | North | Monstera | 14 | $476.00 | |||
| 12 | Mar 2026 | South | Fern | 38 | $361.00 | |||
| 13 | Mar 2026 | South | Monstera | 13 | $442.00 | |||
| 14 | Apr 2026 | North | Fern | 47 | $446.50 | |||
| 15 | Apr 2026 | North | Monstera | 18 | $612.00 | |||
| 16 | Apr 2026 | South | Fern | 42 | $399.00 | |||
| 17 | Apr 2026 | South | Monstera | 16 | $544.00 |
Common mistakes
Typing into a spill range: the whole result turns into #SPILL!.
Referring to D2:D13 instead of D2#, so the reference breaks when the list grows.
Using Excel's -1 for descending in Google Sheets, whose SORT takes TRUE/FALSE for the direction.
Questions
Does Google Sheets have SORTBY?+
No. Use SORT with a range to sort by: SORT(A2:A9, E2:E9, FALSE).
Can I sort by two columns?+
In Excel, SORTBY(array, by1, order1, by2, order2). In Google Sheets, SORT(range, column1, is_ascending1, column2, is_ascending2).
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.