Advanced Excel · Beginner level

Beginner level · Module 3 of 5 · 10 guided hours

Dynamic Array Formulas

Use modern dynamic array functions to build reports that filter, sort and summarise themselves.

Learning objectives

  • Filter, sort and de-duplicate data with FILTER, SORT and UNIQUE
  • Build calculations with SEQUENCE and LET
  • Create self-updating report blocks

Topics

  • spill ranges
  • FILTER
  • SORT and SORTBY
  • UNIQUE
  • SEQUENCE
  • LET

Lessons

  1. Lesson 1 FILTER, SORT and UNIQUE

    Return whole result lists with one formula that spills into neighbouring cells.

    spill range#SPILL!FILTERSORT and UNIQUE

  2. Lesson 2 SEQUENCE and LET

    Generate series with SEQUENCE and make long formulas readable with LET.

    SEQUENCEdate seriesLETreadable formulas

  3. Lesson 3 Building Self-Updating Reports

    Combine drop-downs and dynamic arrays into report blocks that need no manual updates.

    selection cellreport layoutdynamic summariescharts from spill ranges

Practical activity

Build a branch report sheet where choosing a branch from a drop-down shows its sales sorted by amount, its unique customers and totals.

Expected outcomes

  • Reports that update without copying formulas
  • Readable, maintainable formulas

Assessment

Practical: build a dynamic report block from a specification; marked on results after the data changes.

Free taster lesson Lookup Functions — VLOOKUP & INDEX/MATCH Read a free self-study lesson on this topic in the Free Course Library.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.