Referencing Spilled Arrays in Excel (Using the # Operator)

If you’ve started using modern Excel functions like VSTACK or FILTER, you’ve probably noticed something new — formulas that return multiple results that spill into surrounding cells.

But then the next question comes up:

How do you reference those spilled results in other formulas or sheets?

The answer is the # spill reference operator.

When a formula spills, you can reference the entire result using just the first cell plus #.

Example:

=A2#

That means: use the whole spilled array starting in A2

If the spill grows or shrinks, your reference updates automatically.

Real Example from the Video

In the tutorial, I show a practical workflow:

Use VSTACK to combine multiple sheets into one master list

Reference that list on another sheet using #

Build formulas like FILTER, SUM, AVERAGE, and ROW counts from it

EXERCISE FILE:

ExcelMonthlySales-VSTACK-HASH-02.xlsx

Download XLSX • 20KB