Explanation

Dynamic Arrays allow one formula to return multiple results that automatically spill into neighboring cells.

Instead of writing a formula separately in every row, one formula can generate an entire result set.

Example

=FILTER(A2:D20,D2:D20=”Sirsa”)

The result automatically spills into multiple rows and columns.

Key Concepts

  • Spill Range
  • Dynamic Array Formula
  • Spill Operator #
  • Automatic expansion
  • #SPILL! error

Example

If F2 contains:

=UNIQUE(C2:C100)

Then:

=COUNTA(F2#)

can work with the complete spilled result.

Practice

Create a dynamic list of all unique cities from a sales dataset.

PreviousNext