· advanced
Dynamic Arrays and Modern Excel
One formula that fills a dozen cells. FILTER, SORT, UNIQUE, SEQUENCE and XLOOKUP replace whole techniques that used to need helper columns, and they change what a well-built sheet looks like.
Written in house, from the work rather than from a syllabus. Every example is one we would use ourselves, and every exercise is checked against the same engine that grades your answer.
What you’ll learn
- Explain what spilling is and why a formula can fail with #SPILL!
- Return only the rows meeting a condition, with FILTER
- Build a category list that maintains itself, with UNIQUE and SORT
- Generate a list of numbers or dates from nothing, with SEQUENCE
- Replace VLOOKUP and INDEX MATCH with XLOOKUP, and know what it still cannot do
- Compose these into a report that has no helper columns to fall out of step
Before you start
- · Summarising and Reporting in Excel, or fluency with SUMIFS and absolute references
- · Excel 365 or Excel 2021. These functions do not exist in older versions