These five are the daily bread of dynamic arrays. Learn their signatures cold.
How do FILTER, SORT and SORTBY work?
FILTER(array, include, [if_empty]) returns only the rows (or columns) of array where the include array is TRUE. The include argument is a Boolean array the same height as array. To return every column of the customer table A2:D9 where the status in C2:C9 equals "Active":
=FILTER(A2:D9, C2:C9="Active")Combine conditions with arithmetic on the Boolean arrays. * is AND and + is OR, because TRUE*TRUE = 1 and TRUE+FALSE = 1:
=FILTER(A2:D9, (C2:C9="Active") * (D2:D9>=3000000)) (Active AND revenue ≥ 30 lakh)
=FILTER(A2:D9, (B2:B9="North") + (B2:B9="South")) (North OR South)The third argument saves you from an ugly error. If nothing matches, a bare FILTER returns #CALC!. Supply if_empty, as in =FILTER(A2:D9, C2:C9="Active", "No active customers"), and a no-match returns a readable message instead. FILTER also keeps the source order. It picks rows and leaves their order alone.
Advertisement
SORT(array, [sort_index], [sort_order], [by_col]) returns array sorted. sort_index is which column to sort by, where 1 is the first. sort_order is 1 for ascending, which is the default, or -1 for descending. To sort the whole table by revenue, the 4th column, largest first:
=SORT(A2:D9, 4, -1)SORTBY(array, by_array1, [order1], …) sorts array by one or more separate arrays. The sort key doesn't have to be a column of the thing you're sorting. To return just the customer names, ordered by revenue descending:
=SORTBY(A2:A9, D2:D9, -1)Reach for SORT when you sort a block by one of its own columns. Reach for SORTBY when the key lives elsewhere, or when you want to sort by several keys at once. Region ascending, then revenue descending, looks like this: =SORTBY(A2:D9, B2:B9, 1, D2:D9, -1).
How do UNIQUE and SEQUENCE work?
UNIQUE(array, [by_col], [exactly_once]) returns the distinct values in first-appearance order. =UNIQUE(B2:B9) on the region column returns the three regions West, North, South. Set exactly_once to TRUE and it returns only values that appear a single time. That makes it a fast duplicate finder, because the values it doesn't return are the ones that repeat. Pair UNIQUE with SORT for a clean lookup list: =SORT(UNIQUE(B2:B9)). That's the self-maintaining source for a data-validation dropdown you'll build later.
SEQUENCE(rows, [cols], [start], [step]) generates an array of evenly spaced numbers. =SEQUENCE(5) spills 1,2,3,4,5 down a column. =SEQUENCE(1,6,2019,1) spills the six years 2019,2020,2021,2022,2023,2024 across a row, and =SEQUENCE(4,1,10,5) spills 10,15,20,25. SEQUENCE is the engine behind amortisation schedules, period headers and any "n evenly spaced steps" grid. You'll use it to lay out a projection's year axis in Worked example 2.
What does RANDARRAY do, and when should you avoid it?
RANDARRAY([rows],[cols],[min],[max],[whole]) fills a block with random numbers. =RANDARRAY(5,3,0,100,TRUE) gives a 5×3 grid of whole numbers from 0 to 100. It's useful for building test data and Monte-Carlo scaffolds. But it's volatile, recalculating on every worksheet change (there's more on this under the common mistakes), so reach for it on purpose and never by default.