Dynamic Table Formulas in Excel are currently (2022) available to Microsoft 365 users. 2019 and earlier versions do not have access to this functionality.

Dynamic array formulas – Excel formulas that can return arrays of variable size based on a single formula entered in one cell. Unlike Array Formula, is selected in one cell and confirm with ENTER only.

Spill – refers to a behaviour where dynamic array formulas that return multiple results “spill” these results into multiple cells automatically.

Spill array – refers to the range of values returned by a dynamic array formula that spills results onto a worksheet. Once you enter a spilled array formula, when you select any cell within the spill area, Excel will place a highlighted in blue border around the range. The border will disappear when you select a cell outside of the area.

Functions:

  • FILTER  – Return the array based on the criteria you define.
  • UNIQUE  – Return unique items from a range if cells.
  • SORT  – Sort a table (or range) by column.
  • SORTBY  – Sort a table (or range) by another list.
  • SEQUENCE – Generates a list of sequential numbers.
  • RANDARRAY  – Generates an array of random numbers.

Errors:

  • #SPILL!– An error resulting from the fact that the dynamic array cannot spill because there is a non-empty cell in the area.
  • #CALC! – An error occur when Excel’s calculation engine encounters a scenario it does not currently support.