Instructor: All right, it's pro tip time.
We're gonna talk about how to deal with growing source data,
very common challenge that people deal with in Excel.
So here's the thing, when you make cell references inside
of a dynamic array formula,
those references won't automatically resize
as you add new data.
So for instance, you drop in new records
or new rows of data beneath the original reference.
Those formulas will have no idea that you just added
that data, and they won't accommodate that data by default.
Good news is that there are a few different ways to
accommodate dynamic source data
and I'm gonna show you three approaches right now.
Approach number one is to format your raw data as a table,
an actual table object in Excel.
And when you do that,
it's gonna allow your formulas to use structured references
like pointing to an entire column within the table
as opposed to those traditional A1 style cell
references that we're used to.
And the beauty of structured references is
that they will automatically include all
of the values within that table field, or column.
So this is a great way to deal
with growing or dynamic source data.
Second approach is to select extra rows to
accommodate any new records that you expect to add.
This approach is a little bit of a hack.
I don't love it, but it does work
and it can be a very quick way to solve this problem.
In this case, instead of just referencing the category names
in B2 through B9,
we're actually referencing B2 through B16
in this case because we expect additional products
and categories to be added to this raw data in the future.
That way the unique function
that we're using here in cell F2 won't need to
be updated every single time we add a new row.
And then the third approach here, which works very well
but is a little bit tougher to set up,
is creating dynamic named ranges using formulas
like OFFSET and COUNTA.
Just like we talked about in the scrolling chart demo
earlier in the course.
So let's jump back into Excel.
We'll stick with the same demo we've been working with
and I'm gonna show you how we could apply each
of these three techniques if we needed to accommodate
for growing records in our source data.
All right, so if you wanna follow along with me,
open up your section nine workbook.
We're gonna stick with that first tab,
Intro to Dynamic Arrays
and I'm gonna walk you through each of those three
techniques I just showed you.
Now, it's important to keep in mind that in many cases
you may be dealing with static source data.
You know, for instance, this may be the data that you'll
always use for your analysis, and that will never change.
If that's the case, these techniques may not
be even relevant.
But if you consider a case where you may have
additional industries or additional records or rows of data
that are being added over time,
that's where these techniques
can come really, really in handy.
So let's walk through these.
The first one that we talked about is the table reference.
So what I'm gonna do here is delete
our dynamic array formula in D3,
and I'm gonna select our raw data
which currently is just a normal cell range
including the headers.
And I'm gonna use the control T shortcut to turn it
into a table object.
Make sure you've got the box checked that says we
have headers here.
We press okay, and Excel has turned it into a table.
And note, you can do the same thing
from the home menu using this format as table button.
And if you don't like the automatic formatting
that gets applied to the cells, just jump into table design,
expand the table styles options
and click this light option here.
That's just gonna preserve your original formatting.
And now even though it looks the same,
Excel is treating this as a table.
You could see it added filters to our headers.
And this little icon here, this arrow
in the bottom right corner is a nice little clue
that we're dealing with a table here
and not a normal cell range.
So let's go ahead and rewrite our dynamic array formula here
and watch what happens this time.
Instead of referencing cells like B3 through B17, 18,
when we select all of the values
it turns into a structured reference.
In other words, we're telling Excel point to all the values
in the 2020 average salary column inside of that table.
We're gonna divide that by one plus our growth rates.
Same story here, it's referencing the growth rate column
as a structured reference.
Close the parenthesis, press enter
and we'll see the exact same output that we saw before.
So nothing really new there, but here's the kicker.
If we added a new industry,
this time let's say analytics, average salary,
90,000, growth rate 5%,
check it out, our spill range has automatically updated
to incorporate that new record, that new row of data.
And in our 2019 average salary calculation here in G4
because we used the hash sign
that reference has expanded to include all
of our values as well.
So this is a really great,
really handy approach to solve this type of problem.
So that's technique number one,
converting a range to a table.
Let's go ahead and delete that formula.
We're gonna basically get back to our starting point.
And a little pro tip to show you how to
get there is to select the table or just a cell within it.
Go into table design and click this
convert to range button, that's gonna convert it back
to a normal cell range.
Just like where we started.
You note that little arrow in the lower right is gone.
That means we're just dealing with a range again.
So onto technique number two, which is adding rows
to our initial selection to accommodate new records.
This one's pretty easy.
Again, it's a little bit hacky
so it won't always be the best solution
but we could do something like this.
Instead of just selecting B3 to B20,
if we know that a handful of industries
are gonna be added to this source data
over the next period of time,
we could just extend that out to B30, right?
Divided by one plus our growth rates,
which will also potentially expand down to row 30,
close it off, press enter, and that's gonna work.
Except the problem is we've got these zeros now.
So if we add another field,
let's do basket weaving again
with a new salary 50,000 and a new growth rate like 3%.
We are going to get those values here
but we're also just gonna get these zeros to account
for the extra rows that we added.
Now, one other thing you might be wondering is
do you have to select an arbitrary range
in this case out to 30 rows?
Or could you do something just
like this entire reference to column B divided
by one plus an entire reference to column C.
In this case, unfortunately you can't.
You're gonna get a spill error.
So you do need to define specific ranges there.
Now let's delete that formula.
Let's delete these two new rows that we added.
And let's add one more example here,
which is probably the trickiest of the three.
It's the named range approach.
And to do this, what I want to do is create a
named range that basically tells Excel
that that range will contain any values here in column B
and it's gonna grow if new values are added.
Same thing here with column C.
So to do this, we're gonna use the OFFSET
and COUNTA functions,
just like we practiced earlier in the course.
I'm gonna do this quickly
and you can follow along if you'd like.
Otherwise you can kind of just watch here.
So I'll define a name, let's call it salaries first.
And this is where we're gonna type our OFFSET function.
So we're gonna offset, starting with the first cell
containing data here in B3.
We're not gonna offset any rows,
we're not gonna offset any columns.
So I'm gonna put zeros as those next two arguments.
The next argument
of the function is the height of the resulting array.
This is the kicker because the height needs to be dynamic.
To make it dynamic, I'm gonna use a COUNTA function
and what we'll do is point
to the entire column B, close the parenthesis,
subtract one to account for the header
and that's the dynamic height of our resulting array.
Final argument is the width
which is just one column wide and close the parenthesis.
So I'm gonna pause there.
It's a complex formula made even harder by the fact
that when you're here in the name manager dialogue box
you don't get those helpful IntelliSense tips to guide you
through the formula.
So this one's a pretty tricky one to write from scratch
but that should do the trick for our salaries range.
So press okay, and we're gonna do the same thing
for our growth rate range.
So let's call it growth rates, and it's gonna
be an offset this time we're gonna start at C3.
We aren't gonna move any rows,
we aren't gonna move any columns.
The height is going to be the count
of non blank values in column C.
Close it off, subtract one
for the header, comma to our final argument
which is the width of the resulting array.
One column wide, close the parenthesis, press okay.
All right, so that was the hard part.
We defined our dynamic named ranges
for salaries and for growth rates.
And now instead of referencing cell ranges,
we can reference those named arrays
in our dynamic formula here.
So equals salaries.
You can see that named range appear right there divided
by one plus growth rates.
I'm gonna tab to enter that in,
close the parenthesis, press enter,
boom same exact result that we got, whether we used a table
a cell range, or dynamic named ranges.
And just like we saw with our table,
now if we add new fields, analytics, 90000, 5%.
Check it out, we get an additional value in our spill range,
our dynamic formula automatically updates.
And same with our 2019 average salary calculation here.
So there you have it, three different techniques
for accommodating growing dynamic source data.