Instructor: Let's talk about
what Dynamic Excel is all about.
In a nutshell,
Excel's calculation engine has fundamentally changed,
and as a result, formulas will never be the same.
Now, I know that might sound a little dramatic,
but trust me,
once you start working with these dynamic array functions
you'll realize just how game changing they are.
So up to this point,
throughout this course we've been conditioned to understand
that one formula is designed to produce one value.
So, looking at this screenshot here,
even if you've tried to reference cell ranges
within the formula bar,
when you click enter and treat it like a normal function,
Excel will only care about the first row, the first column,
and return a single result.
We're gonna call this "Legacy" Excel from here on out.
Now, with the introduction of dynamic array capabilities,
one formula can produce many values.
So that exact same function here,
B2 through D2 times E3 through E10
produces an entire array of results,
not just the single value that we saw with Legacy Excel.
So we're gonna call this Dynamic Excel from here on out.
So quick recap.
Again, a single formula in a single cell
can now return many, many results.
And what you'll see
is that the values spill across adjacent cells.
These results aren't hard coded
and they can change dynamically as your source data changes.
And with this new capability
comes a new set of dynamic array functions.
Functions like sort, filter, and unique
all take advantage of this new calculation engine
and allow us to do things that we've never been able to do
in Excel in the past.
And not only that,
but we'll show you examples of how you can combine
these dynamic array functions together
to unlock entirely new capabilities in Excel.
And like we talked about in the intro,
this dynamic array behavior
doesn't just apply to these new functions,
it can also be applied
to traditional Excel formulas as well.
And finally, array functions as a whole
are now much, much simpler and easier to learn.
So in the past, those control shift enter array functions
really typically were only used by Excel wizards
or experts, right?
They weren't really accessible to the general public.
Most people didn't understand exactly how they worked
or how to really harness their power.
Well, the great news
is that these new dynamic array formulas
are way more intuitive, much more user-friendly,
and in many ways much more powerful as well.
So whenever we talk about dynamic arrays,
we're talking about formulas
that can return arrays of variable size.
And so like we said,
these formulas can be entered into one single cell
and they can spill their results across an entire range.
Here's an example, right?
Consider you're looking at a simple table of data
containing products, sales figures, a margin percentage,
and you want to calculate profit.
Well, if you were to select the entire array
from B2 through B10,
try to multiply it by C2 through C10,
with dynamic array capabilities
that's gonna produce an entire array of profit values
populated in cells D2 through D10.
And that resulting range of cells
is known as the spill range.
Now, we're gonna talk all about spill range properties
in just a minute,
but the important thing to consider here
is that the formula itself
it only lives in the first cell of that spilled range.
So even though you see values populate
through all of those cells,
we're really only using one function here.
Now let's shift gears. Let's roll up our sleeves.
I wanna jump into the Excel workbook
and walk through a simple example
to show you exactly how this works.
Alright, so if you'd like to follow along,
open up the Section9 Dynamic Array Formulas file
from the course resources.
And we're gonna start in the first tab here,
Intro to Dynamic Arrays.
You'll notice this workbook
is gonna contain all of the demos,
all of the exercises,
that we're gonna be walking through
throughout this entire section,
including sort, sort by filter, unique, and so on.
So, here we've got a little sample of raw data.
It's sourced from that Glassdoor dataset
that we talked about in the project intro.
And what we're looking at here are 17 individual industries,
technology, sales, retail, real estate, and so on.
We've got the average salary in 2020 right here in column B.
And then we have the year-over-year percent growth
here in column C.
So, if our goal here
is to populate last year's average salary,
the 2019 average salary,
we're gonna do that here in column D.
Now, if we're in the legacy Excel mindset,
your first instinct might be to just reference,
you know, these individual cells, B3 and C3.
And in this case, if we know the 2020 salary,
we know that the salary grew 2% compared to last year,
we can calculate 2019 using a formula like this,
B3 divided by one or 100%, plus that growth rate in C3.
Now, the important thing here
is not to understand, necessarily,
the math behind this formula,
but more so the fact that we're referencing
those two individual cells, B3 and C3.
We're gonna press enter.
We'll get our average salary for 2019,
this case, 88,497.
And we would go ahead and double click
and shoot that down and get our results.
Now that works great, right?
We got the correct values, it worked properly,
but as I click through,
you'll see that we have 17 individual functions,
each occupying one of these cells
all the way from D3 down to D19.
So let's go ahead and undo that.
I'm gonna delete my formula.
And I'm gonna show you how it can do this exact same thing
with one single formula using dynamic array capabilities.
So, instead of referencing individual cells,
I'm gonna reference the entire range.
So let's take all of our 2020 salaries,
which live in B3 through B19.
Don't even need to worry about fixing the reference here.
I'm gonna divide it by one
plus all of our growth rates, C3 through C19.
Close the parenthesis, press enter and check this out.
Excel has just produced 17 formula results
using just that one formula that I wrote.
And remember, this whole range of values, D3 through D19,
is referred to as the spill range.
And there are some properties of these spill ranges
that are important to understand.
For instance, our formula really only lives
in this first cell, D3.
As I kind of arrow down to the other cells,
you can see it's the same formula referenced,
nothing's changing, but it's grayed out
because I can't edit that formula anywhere else
because it doesn't live anywhere else.
You'll also notice a blue box surrounding my spill range
and you may have noticed the fact that my formatting
only applied to that first cell.
It didn't automatically apply to the entire spill range.
So these are some of the properties
of spill ranges that are important to understand.
In the next lesson, we're gonna dig a little bit deeper
and talk about these spill ranges in depth.