formulastreak

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.

Monthly sales by region · edit G2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultG2 = Fern
ABCDEFG
1MonthRegionProductUnitsRevenueProducts
2Jan 2026NorthFern40$380.00Fern
3Jan 2026NorthMonstera12$408.00Monstera
4Jan 2026SouthFern35$332.50
5Jan 2026SouthMonstera9$306.00
6Feb 2026NorthFern44$418.00
7Feb 2026NorthMonstera15$510.00
8Feb 2026SouthFern31$294.50
9Feb 2026SouthMonstera11$374.00
10Mar 2026NorthFern52$494.00
11Mar 2026NorthMonstera14$476.00
12Mar 2026SouthFern38$361.00
13Mar 2026SouthMonstera13$442.00
14Apr 2026NorthFern47$446.50
15Apr 2026NorthMonstera18$612.00
16Apr 2026SouthFern42$399.00
17Apr 2026SouthMonstera16$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).

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = Lina Chen
ABCDEFGH
1RepRegionJulAugSepTargetBy September
2Amara OseiNorth$12,400$13,100$11,800$36,000Lina Chen
3Ben FischerSouth$9,800$10,250$12,100$30,000Hugo Martin
4Lina ChenEast$15,200$14,800$16,900$45,000Farid Haddad
5Diego AlvarezWest$8,700$9,100$8,400$28,000Eva Novak
6Eva NovakNorth$11,200$12,600$13,900$36,000Ben Fischer
7Farid HaddadSouth$13,300$12,900$14,100$39,000Amara Osei
8Grace KimEast$10,100$9,600$10,800$33,000Grace Kim
9Hugo MartinWest$14,500$15,800$15,100$42,000Diego 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.

Planning calendar · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Jan 2026
ABCDE
1Year2026Month
2Jan 2026
3HolidaysFeb 2026
41 Jan 2026Mar 2026
53 Apr 2026Apr 2026
66 Apr 2026May 2026
71 May 2026Jun 2026
825 Dec 2026Jul 2026
9Aug 2026
10Sep 2026
11Oct 2026
12Nov 2026
13Dec 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.

Planning calendar · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = #SPILL!
ABCDE
1Year2026
2#SPILL!
3Holidaysnote
41 Jan 2026
53 Apr 2026
66 Apr 2026
71 May 2026
825 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
Select G2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGH
1MonthRegionProductUnitsRevenueProducts
2Jan 2026NorthFern40$380.00
3Jan 2026NorthMonstera12$408.00
4Jan 2026SouthFern35$332.50
5Jan 2026SouthMonstera9$306.00
6Feb 2026NorthFern44$418.00
7Feb 2026NorthMonstera15$510.00
8Feb 2026SouthFern31$294.50
9Feb 2026SouthMonstera11$374.00
10Mar 2026NorthFern52$494.00
11Mar 2026NorthMonstera14$476.00
12Mar 2026SouthFern38$361.00
13Mar 2026SouthMonstera13$442.00
14Apr 2026NorthFern47$446.50
15Apr 2026NorthMonstera18$612.00
16Apr 2026SouthFern42$399.00
17Apr 2026SouthMonstera16$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.

Practise it now