 The five is master the new dynamic arrays and new Excel calculation engine Hmm new Excel dynamic arrays. What do I mean by dynamic arrays, right? So, so this is future. I call this future Excel, right? So this is is future Excel Excel for the future and I can tell you it's super super super amazing. Yeah future future Excel Because Excel has fundamentally changed how how it works how Excel works has really been Fundamentally changed by including certain things called dynamic arrays. So I'd advise that you go learn what they are Google it Check it out. I mean, there are many people have done stuff already on on dynamic arrays You can see how they work and stuff. So it's really it's really cool. Yeah, so check it out let me Let me show you a little bit just a very little bit. We don't have much time So I'll just show you a little bit of what I mean by dynamic arrays and the new calculation engine Okay, so dynamic arrays remember what I said about reporting, right? So I said reporting I said when you use an Excel, they're really How will I put it there really? Three two things you need you need you need past data You either doing something in the path with past data. You're doing something for the future They're trying to forecast the future. Those are the two main things we do in Excel So if I'm using dynamic array is really I'm building More things I think to do with the past but again, it's just a technique for working which is so efficient Take it that what Excel has done with this new calculation engine, which really has fully been released in August So if you have the August version of office 365 if you have August office 365 You will have these dynamic arrays working, right? So what exactly are they? Well, Excel has completely fundamentally changed how it works. Yeah, how just take it that maybe Excel is like I always say 30-story building and Someone has just held that building up gone to the foundations Removed the foundations and put in a new foundation while the building is still standing That's exactly what has happened to Excel the foundations have been ripped apart and put back and the new foundation is Smoking hot excellent, right? So I'll give you a simple task very simple I'll just do one something that we do a lot. So for example, I have a report and I want people to be able to pick Maybe these markets, right? I want people to be able to automatically pick markets from a list So the first thing I will do is I'll go to my control sheet and I'll create a list of Markets, right? I wanted a list of unique list of markets. So in old Excel, I would do this I come to be a copy I Go to control I come and I paste maybe a paste special values here or something Right, then I paste once I paste it. I will now remove duplicates. So I go to data. I then go to This button here, which is removed duplicates and I say, okay, and guess what says remove duplicates. Perfect. Look at them so it's a nice list of Unique values. Yes, but these unique values are static if you look at the formula bar, they are static So look at what the new dynamic arrays help us to so there are if I'm not mistaken six formulas I think there are six new formulas. I must have written them somewhere Let me think I think okay. They're not here. So anyway, I think there are six new formulas So one of them one of the key one six or seven. I'm not too sure. So unique is One of the new formulas. So these are dynamic array So returns the unique values from a range or array unique is one of the new Functions, so if I tab what does unique require it says array by column occurs once So you see these are optional. So I'm just gonna do the simple one. It's just array, right? So what do you mean unique? I'm writing a formula in a cell guys So I go to data now since this is a table I can just highlights it like this so you can see this is unique sales market I close my bracket and remember this is a formula in a cell and I enter Guess what? Look at what it did. It just drops all the values. It just spills it. Look at it So I don't need to do that copy paste and remove duplicates and blah blah blah And I did that in a single cell these cells here. You can see it's kind of grayed out This is the formula. So this is an array formula that spills So if I come here and kind of write something to kind of disturb the spill enter you see his haze complaining There's an error message. I can't spill Right. So the thing is with this engine is even your normal Excel functions are now array functions Okay, so if I do equals to right? I mean typically I do equals on highlight this This doesn't make sense really doesn't make sense in old Excel to give me an error But look at that. I wrote a formula I said equals to and then I just highlighted certain things in old Excel You will never you get an error message, right? Yeah, but there's something called implicit There's there's Well in old Excel, you get an error message if it's not in the same line So let's say enter and I see it spills. So everything kind of spills in older in the new Excel If it sees hey, this is a list that this guy wants to return it was spill And this opens up so many doors so so many doors for example I could do a data validation for market here So for example, I want to do a data validation for market, right? Right. So in here, I want a drop-down for data validation for market. All I need to do is go into data. I Go to data validation Right. So when I go to data validation, I say list and in old Excel I will need to make this a dynamic list and you know, I do all sorts of stuff to make a dynamic list All I need to do is click on this first cell which I know has Right now it's in it's in market, right? So so I don't know why it's in market. I think I named it market So let me I don't want to name it market. I want to show you something else without it being called market Let me just let me just delete all of this All of these names in here is delete delete select all Okay, let's just go to market. Just delete What do you delete them some of them are not deleted. Yeah, I know that so let me just delete what I want to delete So yeah, everything's deleted except that close Great. So what I want to show you is this I come here. I remember the formula was in here I'd not anywhere else the formula was in here. So I come to this cell. I say data. I go to data validation and Then I go to list Here I say this cell that contains my array and then there's a new thing you put at the end is the pound key or the hash Button, so you just put a hash there and say, okay Now when you do that what that hash does it makes it it tells it Hey, this is an array and fill is fills it out So if I if I come to a cell here, right come to a cell and say equals to right I come to this cell and I enter you see it's just this Whatever say equals turn I come to this cell and then I put the hash sign, right and I enter What does it do? It spills So because it knows that this is an array this formula here is an array that hash Which is just a new way of working, right? It's what spills it is what says hey, give me the full array I know this cell returns an array. Give me the full array. That's what hash does So you need to learn The way this works and there are many other functions the sort I would just give you one more for example I can add another new function here and what it does is it sorts So if I say sort and I open my bracket, I say is hey, I'm going to sort using a function So I enter guess what it has sorted automatically sorted the list And if I come to my data and add a new market, let's say I'm adding David as the market, right? I'm adding Gupta as another market Gupta. Yeah, so Gupta So I put David and Gupta right now so David and Gupta is in my list makes no sense I know but let's go to control and guess what Can you see David, right? See David in the list automatically added? Of course, you don't see David in this list This list is not dynamic. Where's Gupta see Gupta, right? So these are dynamic arrays super super stuff in new Excel. Let's jump to our next step