Dynamic arrays
Formulas that return whole lists: FILTER, SORT, UNIQUE, SEQUENCE, then LET and LAMBDA.
Lessons
In the order the Roadmap teaches them.
In the full course. Advanced · 9 min
What "spilling" means
Understand how a formula's list spills into the cells around it, keep its space clear, edit it from its first cell and use the whole list in another formula.
See the lessonIn the full course. Advanced · 11 min
FILTER rows by a condition
Pull the rows that match a condition into their own list with one FILTER formula, and fix a list that can't spill.
See the lessonIn the full course. Advanced · 11 min
FILTER with several conditions
Filter a list by two or more conditions at once, with * for AND and + for OR, and parentheses when you mix them.
See the lessonIn the full course. Advanced · 10 min
UNIQUE values
List each customer, product or combination once with UNIQUE, without touching the data, and find the values that appear only once.
See the lessonIn the full course. Advanced · 11 min
SORT and SORTBY
Show a sorted version of a list with a formula, by any column or by several, without moving the original.
See the lessonIn the full course. Advanced · 9 min
SEQUENCE
Number rows and list weekly or every-other-week dates with one SEQUENCE formula that grows or shrinks when a count changes.
See the lessonIn the full course. Advanced · 9 min
Count unique values
Count how many different customers, members or products a list has with COUNTA and UNIQUE, for the whole list or one group.
See the lessonIn the full course. Advanced · 11 min
Combine FILTER, SORT and UNIQUE
Build a sorted list of distinct values for one group in a single formula, SORT(UNIQUE(FILTER())), and drive it from a cell.
See the lessonIn the full course. Advanced · 10 min
CHOOSECOLS and VSTACK
Stack lists from two sheets into one with VSTACK, keep only the columns you need with CHOOSECOLS, and combine both in one live formula.
See the lessonIn the full course. Analyst · 9 min
LET: name the parts of a formula
Give a repeated calculation a name inside a formula with LET, so you write it once, change it in one place and read the formula like a sentence.
See the lessonIn the full course. Analyst · 11 min
LAMBDA: build your own function
Turn a business rule into your own function with LAMBDA, test it on one row, and run it down a whole column with MAP or across each row with BYROW.
See the lessonGet the full course
Every lesson in the Excel course, with new lessons added as they're written.
- All 217 Excel lessons open today, and every new one as it's written.
- The Roadmap, Skills and all eight paths.
- A cheat sheet for every lesson you finish.
- At checkout, add the Google Sheets course for US$49.