Summary
In this video, we go deeper looking at arrays.
EXAMPLE FILE: Modern Excel Formulas – Start.xlsx > Array & array functions
Array separators differ based on your regional settings. Make sure you use the characters for your settings.
Spilling occurs when a single formula returns values to multiple cells on the grid. The range created by the spill is known as the spill range.
Only the top left cell contains the formula. A blue box indicates the range occupied by the formula.
If another value occupies the range Excel returns the #SPILL! error in the top left cell.
To reference the spill range created by a formula use the spill operator ( # ) after the cell reference.
There are two types of arrays:
- Static / constant: Fixed values and size
- Dynamic: Resize to the required rows and columns at calculation