Dynamic Arrays 2

This is the second post in the Dynamic Arrays series. In the first post, we talked about how formulas can return multiple values, the resulting spill range, and two dynamic array functions. In this post, we’ll talk about how to refer to the spill range with other formulas.

Video

Spill Range

The spill range includes the cells that store the results of a dynamic array formula. In other words, when you write a dynamic array formula in a cell, for example into cell B8:

A screenshot a formula in cell B2

And then hit Enter, the results spill out of the formula cell into the cells below:

A screenshot of the spill range from b2 to b10

The range that contains all of the formula results, B8:B10 in this case, is known as the Spill Range.

Now, the cool part about the spill range, besides the fact that it is dynamic, is that we can reference it from other formulas.

Spill Reference

Let’s say we’d like to populate the Total column with the sum of the accounts. The data table, Table1, looks like this:

A screenshot of an Excel table with Account and Amount columns

So, we figure we can use the SUMIFS function to generate the summary.

We write the following formula into C8:

=SUMIFS(Table1[Amount],Table1[Account],B8)

And press Enter:

An excel screenshot of the formula result

It computed the correct result, so that is good. But, now we would need to manually fill that formula down to compute the totals for Meals and Phone.

But, if we know how to use the Spill Reference Operator #, then Excel will fill the formula down for us … and continue doing so as the size of the spill range changes.

So, we update our reference from B8 (individual cell) to B8# (entire spill range) in our formula, like this:

=SUMIFS(Table1[Amount],Table1[Account],B8#)

We hit Enter and bam:

An excel screenshot of the sumifs formula being filled down through the spill range

It too spills down … nice!

And, the best part is that when our source data table has more rows and more accounts, the results will dynamically update.

If you are familiar with PivotTables, you’ll quickly realize we could build the example above using a PivotTable. The dynamic arrays approach is kinda like a cross between a traditional formula-based report and a PivotTable. It uses formulas, but has the dynamic element we love about PivotTables. So, depending on the context of your workbook, this may offer a nice option.

What do you think about this capability? I’d like to know … just post a quick comment below!

Sample File

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.