Instructor: Hey, everyone.
Welcome to our section on Dynamic Arrays.
Now for students who have been in this course
for quite a while,
you may have realized that there was an older array section
in this course that has since been replaced.
Microsoft and Excel have evolved quite a bit
over the past few years,
and part of that evolution involves
creating a brand new type of function
called a dynamic array function.
And those make these older
traditional array functions much more powerful
and much more user friendly.
So that's where we're gonna be focusing for this section.
So let's talk about what we're in for.
In this section, we're gonna introduce dynamic arrays.
We're gonna explore how array calculations actually work,
because they're a little bit different
from normal Excel functions.
And then we're gonna apply some powerful brand new functions
like Filter, like Sort, like Unique
to explore and analyze some really
fun project data right here in Excel.
So the topics we're gonna cover.
We're gonna introduce this idea
of Dynamic Excel, what's new, what's different,
what are any compatibility limitations, things like that.
Then we're gonna talk about
something called the spill range.
And from there we'll dive into each of these new functions,
sort, sortby, filter, unique, sequence, and RANDARRAY.
And finally share some really cool pro tips
about how you can apply dynamic array functionality
to existing legacy functions in Excel as well.
So goals for the section.
Again, we're gonna understand how dynamic array calculations
and spill ranges actually work.
We're gonna learn the basics of these new functions
like filter, sort, sequence, and unique.
We'll practice applying dynamic calculation behavior
to legacy functions in Excel.
And then finally, we'll analyze our project data
by combining and nesting
these dynamic array formulas together.
Now, before we get into the good stuff
let's talk about versions and compatibility.
So important to know dynamic array or DA formulas
are only available for Office 365 subscribers.
That means if you have a standalone version of Excel
like Excel 2019, Excel 2016, whatever it may be,
you likely will not be able to use these functions.
The good news is that with the introduction
of these dynamic array formulas, those traditional arrays,
those Control + Shift + Enter formulas
that we've talked about in the past are no longer needed.
These replace those old array functions.
And again, they make them much, much more user-friendly.
So with the functions we're gonna be talking about
in this section, don't worry about Control + Shift + Enter.
All you need to do is press Enter
just like any other function.
And just a note here, for compatibility purposes,
those older CSE or Control + Shift + Enter formulas
will still work.
Last but not least, these new DA functions
and these spilled ranges that we're gonna talk about,
they won't work in older Excel versions.
So that backwards compatibility is limited
and it's gonna lead to workbook errors.
So that's important to keep in mind.
If you're building workbooks,
if you're building reports and using these new DA functions,
make sure you're not sharing those workbooks
with users who don't have a compatible version of Excel.
So you might be wondering, hey,
does my version of Excel have these functions?
One quick way to verify whether or not you have access
to dynamic arrays, just type one of these new functions,
like equals filter or equals unique into a worksheet cell
and see if Excel recognizes it as a valid function.
So with that, let's shift gears.
Introduce the project intro,
and then roll our sleeves up,
and start working with these functions.