Instructor: Let's dig a little bit deeper
into spill ranges and their properties.
Now, like we've talked about,
the spill range is the array or range of cells
which contain the results of a single dynamic array formula.
So looking at this familiar example here,
we've got a dynamic array formula in cell E2.
It's producing profit values
that are based on the sales figures in column C,
multiplied by the margin percentages in column D.
Now, a couple things to note here.
Like we saw, only that first cell in the range, cell E2,
where that dynamic array formula was initially entered,
only that first cell is editable.
As you click on the other cells in that spill range,
you'll see that the formula gets grayed out
because you can no longer edit it.
A couple other things to call out here.
Like we talked about,
cell formatting won't automatically carry over
to the entire spill range.
You'll see formatting applied to the first cell
where you enter the formula, but all other values
in the range will inherit general formatting.
You can also easily see the bounds
of your spill range by this blue border.
And as you update the values in the referenced cells,
that spill range will update automatically
and it will resize to fit the resulting array.
That's why they call it dynamic.
And last thing to call out right here
is that you can use a pound symbol
to reference an entire spilled range.
So if we take the sum of E2 with a pound,
that's telling Excel to look at the spilled range
that begins in cell E2
and sum up all the values of that range,
regardless of how far it goes or how many rows it contains.
And that's a really handy trick
that we'll use time and time again
in the demos throughout this section.
Now, as you start to work with these dynamic array formulas,
you'll start to see a brand new error.
It's the spill error.
And luckily, this is actually a pretty simple one.
It occurs when something is blocking the spill range.
And this is a good thing, right?
Because Excel won't automatically
overwrite values by default, you know, which is great.
It's gonna tell you,
"Hey, I want to spill values to this range,
but there's something already there.
I can't spill those values."
That's what's happening when you see that spill error.
The good news is that it's very easy to fix.
All you need to do is clear anything
out of that spill range, whether it's existing text,
whether it's values, or even something like a merged cell
can produce this spill error.
So looking at a simple example here,
again we've got that profit dynamic array formula
in column D, and if we had some text there in D8,
you know, we're saying, "Hey, great margins
on the Blu-Ray Players," that text is gonna block Excel
from spilling those profit values
and produce the spill error that we see here in cell D2.
So important note, the other time you may see spill errors
is if you try to use dynamic array formulas
inside of a table.
And let me be clear here, I'm not talking
about a range of cells that's formatted in a tabular way,
like the cells that we see here,
I'm talking about an actual table object in Excel.
You can reference values in tables
using dynamic array formulas,
but those formulas themselves cannot live
as a column inside of that table.
So just make a mental note for now.
We're gonna talk a little bit more about table behavior
in the next lesson when we talk about growing source data.
But just a heads up, that's the other time
where you're likely to see these types of spill errors.
So with that, let's jump back
into our Excel workbook and do another quick demo.
All right, here we are
in the Section9_Dynamic_Array_Formulas workbook.
We're still in that first tab, Intro to Dynamic Arrays.
And you can see we've got our 2019 average salaries
that we produced using an array formula here in D3.
Now, if our goal is to populate these average salaries
for both 2020 and 2019,
let's talk about how we might do that.
Well, for 2020, we would just use, you know,
a simple AVERAGE function, right?
And we'd say, all right,
what are all the 2020 salaries that we wanna average?
Well, we wanna take the average
across all of the industries which live in B3 through B19,
close it out, press Enter.
Great, so average salary for 2020 was 67,238.
Now, same approach here for 2019, =AVERAGE.
This time, I'm gonna reference the values
that were produced by my dynamic array,
in other words, my entire spill range.
And watch what happens as I make this selection.
D3 through D16 through D17, D18,
boom, look what changed.
Instead of going to D3 through D19,
Excel has automatically recognized that
as the end of our spill range
and replace the reference with that hash or pound sign.
And now if I close the parenthesis, press Enter,
it's gonna calculate properly, 65,586.
I can verify that by making the selection,
seeing right here in my footer that it matches up.
And what's great is that as this spill range changes,
we'll never have to update that reference
because the pound sign tells Excel,
"I want all of the results in that spill range
regardless of how many rows it contains."
Now again, remember that if we were to change values
inside of our referenced cells,
our spill range will update automatically, that makes sense.
If we remove values, it will also update accordingly.
But one place where things get a little bit more complicated
is if we add rows or add records to our source data,
the data that we're referencing in the formula.
So let's say there were another industry here,
let's call it Basket Weaving,
with an average salary of $250,000, that grew by 4%,
notice how our spill range doesn't recognize
that we've added new values here, right?
Because in this case,
our formula is still only referencing values down to row 19.
So we're gonna have to get creative here.
Good news is that there are a few different ways
that you can deal with growing source data.
And in the next lesson, I'm gonna talk you
through three different solutions to solve this problem.
Let's go ahead and clear these out,
jump into the next lesson,
and I'll show you how we can tackle this problem.