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.
- 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.
- #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.