Skip to content

Dynamic arrays · Medium

What are dynamic array functions? Give examples.

Short answer

Dynamic array functions return multiple values that spill into neighbouring cells automatically. FILTER returns rows matching conditions, UNIQUE removes duplicates, SORT and SORTBY order results, and SEQUENCE generates numbers. They replace many helper columns and copy-down formulas.

Detailed answer

=SORT(UNIQUE(FILTER(Sales[product], Sales[region]="West")))

  • A #SPILL! error means something blocks the output range.
  • Refer to a whole spilled range with #, e.g. =COUNTA(E2#).