In Excel, a spilled array refers to a set of values returned by a dynamic array formula that automatically populates neighboring cells. Let’s break it down:
Dynamic Array Formulas:
These are formulas that return a set of values (an array) and spill those values into adjacent cells.
Unlike older array formulas (legacy array formulas), dynamic array formulas can vary in size and adapt to the data they generate.
When you enter a dynamic array formula, Excel dynamically sizes the output range for you, filling in the results across the spill area.
Spill Behavior:
When you create a dynamic array formula, Excel automatically places the results in the appropriate cells.
For example, if you use the SORT function to sort an array in descending order, the resulting array will spill down to neighboring cells.
You only need to enter the formula in the top-left cell, and Excel handles the rest.
Spilled Array Formulas:
Formulas that successfully spill their results are referred to as spilled array formulas.
Once you enter such a formula, selecting any cell within the spill area highlights the entire range.
Only the first cell in the spill area is editable; other cells show the formula but are “ghosted” and cannot be changed directly.
To update the formula, modify the top-left cell, and Excel will automatically adjust the rest of the spill area.
Excel Tables and Spilled Arrays:
While dynamic arrays work well outside Excel tables, spilled array formulas are not supported directly within tables.
Consider placing dynamic array formulas in the grid outside the table.
Excel tables are better suited for independent data rows and columns.
Remember, spilled arrays make working with multiple values more efficient, and they’re a powerful feature for handling data in Excel