What it does
Modifies the active cell formula to make it a spillable array by converting non-array cell references to array-based reference, or toggling back to non-spilling cell references. This is a great, quick way to make formulas dynamic and automatically update as your data changes or grows.

When to use it
When you want to quickly change your formula to automatically spill when you rows are added.  Often used with lookups, conditional formulas, and others within a data set and you don’t want to have to extend the formula range.

Why to use it
It’s a quick and easy way to make a formula spillable and back to non-spillable.

Default shortcut
None

Other Details

• Category: Formulas / General
• Difficulty: 2/5
• Usage/frequency: 3/5
• Automation factor: 4/5 (estimated 180 seconds saved each time used)
• Type: Shortcut
• Date added: 2/23/2024
• Tags: Formulas, toggle, dynamic, spill

Related Macros
Formula Picker

Other Articles
None

None

Prerequisites
Select the cell containing the formula you’d like to update.  If the active cell does not contain a formula with one or more cell references, an error message will be displayed.

Instructions
With the desired cell range selected, run the Toggle Spilled Formula macro.  The cell references will be updated to include OFFSET/MAX/COUNTA functions to return a dynamic range based on data in the referenced cells.  As the referenced data set grows to more rows, the formula in the selected cell will automatically spill into the new rows.

With a converted formula selected (containing the OFFSET/MAX/COUNTA functions), run the Toggle Spilled Formula macro again to convert the cell references back to non-spilling.

None