- What you're about to see
is gonna change everything. After this, it's never
going to be the same again. Let me explain. If you learn how to work
with super advanced formulas, like, control shift enter
formulas, array formulas, like this one, or this one. You're going to be titled the formula expert in your company. Now let's be honest. Not many people like to spend time to write this from scratch, or even bother to look for a file that included this formula. It's time consuming. Here's the problem. They need it for their work. That's why these two videos are popular videos on my channel, even though they include
these crazy formulas. After this event, it's
Microsoft Ignite 2018, everything changed. Dynamic arrays were introduced. Now here's the thing. This isn't just a bunch of new formulas that were added to Excel. This is a change in
Excel's calculation engine. It makes everything easier, also the way existing formulas work. Now I want to show you something. I'm going to take these two videos. Their total time is over 30 minutes, and I'm going to bring up the timer. I'm going to solve for these
both videos using Dynamic Arrays, just to give you some context. Here, I have a list for division. You can see the names
are not grouped together, they're scattered. In front of it, I have the app that belongs to division. Now what I did was to first create a unique drop down for division. This has a list of three names. In front of it is a
dependent drop down list. So these are the apps that
belong to the Game division. What you see down here is
the data preparation table to get these to work. This gives us a unique list, this gives us the app for the divisions. But check out this formula, it's crazy! It even goes to the next line. And what's also crazy
is the offset formula that I have in here, let's just take a quick look at that under Data Validation, check this out. Now I created this to
make sure my app list stops at the end of the last app. So it doesn't give me
any empty cells in there. No wonder this took me so long to do. Just a note here that at
the time of filming this, dynamic arrays aren't available
for the general public. They will be available in the future. When? It's not defined yet. Now let's go and do this
the dynamic array way. Bring up the timer, start now. I'm still going to create
a data preparation table, and I'm going to use one of
the new dynamic array formulas, it's called the Unique Function. It requires an array argument. Notice the next two
arguments are optional. I'm not even going to bother with them. I'm going to close the
bracket, press enter. Now watch this. The formula spills into the next cells. I didn't need to do control
shift enter, just enter. Now what's even more exciting is to create the drop down list. Let's go back to Data
Validation, select list, but for source, I'm not
going to highlight this because my array might end up expanding. I want to reference the
entire spilled array, and there is a new syntax for that. Right after the cell reference, you put the hashtag sign. This means you're
referencing the entire array. So watch this. It has them all in there. Now, you're probably asking, "What happens if I add
something new to this? Will it update?" I formatted this as an Excel table, so yes, it will update. If you don't have an Excel table, you just might want to take extra cells into your unique formula, and it's going to take them into account once they get filled. Let's just add something here. The new division shows up here and it shows up right here. Let's do the more difficult part. That was the Dependent Dropdown List. I'm going to use another new
dynamic array function called the Filter Function. It's actually filtering in the formula. No need to manually refresh
a filter, it's dynamic. What do I want filtered? I want this one filtered. What's the parameter it should filter on? We should filter if the
division equals this cell, close the bracket, and press enter. And that's that. Let's create our second drop down, go back to Data Validation, list. What was the reference for the spill? Hashtag. We have our dynamic dropdown. Let's switch this to new. Check this out. Our list is restricted to the
members of the new division. Stop the timer. Wait a minute! What about getting this sorted? In traditional Excel, this
was super difficult to do. Not anymore. All I have to do is use the
new dynamic array sort formula. This is my array. I can define the sort index, sort order, by which column I want to sort, all of those are optional. I'm going to go with the default. Sorts it perfectly well. I'm going to do the same
to my filter function here. That sorted, too. Check this out. I have game, new,
productivity, and utility. I get the new fields in
here alphabetically sorted. Let's compare these two formulas. (funky music) Isn't that crazy? A lot of you left nice comments
under those long videos, and here are some that
also tell the truth. Not easy to digest. It's really hard. I find it too complicated. Yes, it is complicated. Here's another one. Very useful, I would have thought
Excel would have something like this already since it's
likely to be used quite often. Isn't that great that we're finally going to get it built in? Everyone who uses Excel for data analysis is going to have it easier, because it's not just these formulas. It's thousands of formula combinations that are going to make
difficult tasks super simple. A big thanks to the Microsoft Excel team for bringing this to us. If you're excited about this, and you must be if you're
still watching until now, comment below, let me know what you think. (upbeat music)
