User FAQs

1) What is the difference between a Formula, an Array Formula and a Dynamic Array Formula ?
Formula - lets you perform a mathematical calculation in a single cell.
Array Formula - lets you perform complicated mathematical calculations in a single cell. These must be entered using (Ctrl + Shift + Enter).
Array Formulas spill to a fixed size result
Dynamic Array Formulas spill to a dynamic size result

2) When are Array Formulas converted to Dynamic Array Formulas ?
The curly brackets are automatically removed

3) How many functions use Dynamic Array Formulas ?
All the functions in Excel 2021 (new and old) use dynamic array formulas.
Dynamic formulas have been integrated into all the built-in worksheet functions, even those that were only designed to return a single value.

4) What is the Spill Range Operator ?

5) How do you clear a spill range ?
To resolve a #SPILL error that occurs because spill range isn't blank, either remove the blocking data from the spilt area or move the formula to a new location where there are enough empty cells to output all the results.
To remove all values in the spilt area, delete the formula in the first cell.

6) How do you change a spill range ?
As the spill range is the result of a formula, it cannot be changed manually.

7) How do you fix a spill range if it's too big ?
The size of a spill range is determined by Excel and changes automatically as the source data or the formula gets updated.

8) How do you get rid of a spill range ?
There is no option for switching off the spill range feature.
To remove a particular spill range, delete a formula in the first cell.
To prevent a formula from spilling into multiple cells, use the @ operator which reduces multiple values to a single value.