Creating dynamic functionality with array functions, part 1 - Microsoft Excel Tutorial

- [Instructor] Welcome to what is most assuredly going to be the most exciting and impactful steps in our entire dashboard creation process. This video is part one of a two-part section where we are going to be introducing dynamic array functions, starting with, what I consider, Excel's most powerful function, which is the filter function. And what we're going to do is use the filter function to dynamically control which data are going to be displayed and calculated depending on a selected date range. So if we jump into our spreadsheet, remember, we already took care of our lead sources by creating these on/off toggles and linking them up with our data tabs, but we didn't do anything yet with our report parameters by date. We're going to do that now. And so, before we get started, I'm actually going to go back over, grab our Index tab and bring it right back up next to the Data Rollup tab once again because we are going to be referencing this a little bit and I want to have it accessible. And all the magic is going to happen here on our Data Rollup tab because this tab is about to go from a four-banger engine to a big, incredible turbocharged V8. I'm going to stop talking before you realize I don't actually know anything about cars. But anyway, you get the point. This is going to be an incredible calculation engine for us and, ultimately, for our dashboard. And the way that we're going to make that happen is through the power of filter. Now, here's how it works. I'm going to go ahead and insert a column here and I'm going to highlight all of this, go Control + Shift, right, down, copy. And I'm just going to bring this down a couple of rows. Let's go right here, paste. Notice, because all of our references up here were data tables, pretty much, and then our calculations over here were relative references to those data table-based calculations, all of this down here should be consistent with what was above. So we didn't lose anything by copying and pasting. But what I want to do right here, I'm going to actually copy this cell, C, and paste it here. And I'm going to write Dates because I wanted to just grab that formatting. This is where we're going to drop our filter function and it is going to dynamically retrieve the dates within the range that we set on the dashboard controls. So the filter function, as you might imagine, filters data. And I'm going to show you how it works right now. So we hit =FILTER, we're going to grab this guy, I'm going to hit Tab. And you can see there's only a few arguments. It wants to know what is the array that we're going to filter. What do we want to include in that filtered array? And then, there's a optional rule, if there's an empty, what to do with it. We're not even going to deal with that. We're just going to use the array and what to include. So let's get into it. If I want to be able to give our report date and a 24-month lookback, I want to represent this by month. So I'm going to go back to our Index tab, and this is where all of that information is housed. Remember, this is dynamic. This links to the report start, which links to the dashboard parameter, and then each subsequent row is going one month back in time, up to 24 months back. So this range is going to be consistent and always representative of what we want to filter. So I'm going to hit F4. That feels good. And now, I just have to tell it what do you want me to include. And because of the way that this is set up where the top value is always going to be the start date, all we need to do is say anything that is after or, in Excel speak, greater than the report end date is what we need to include. So let's do that. All we need to do now is say this, F4, anything in that range that is greater than or equal to the report end. I'm going to hit F4 just to lock that. I'm going to close my parenthesis and I'm going to hit Enter. And look at that. It just spilled all the way down a bunch of numbers. We need to format these numbers. So drum roll, please. What do you think it did? Short Date. It just brought back all of the dates in that range that matched the criteria. So what do you think happens now, if I go back to my dashboard, change my lookback to, I don't know, six months? What do you think happens? Go to our Data Rollup. We've got six months. It just rolled back six months because on the Index tab, because we wired this up properly, it tells us that the report end date is being retrieved as six months back, right here, from the start date. Boom. I think that one deserves a boom. So what we just did is we added the filter function to the dates that we have and we made it dynamic and tied to our report parameters so that it's going to explode down here. What we're going to do in the next video is actually now link this dynamic array function that spills to each of these calculations so that each of these calculations also spill down dynamically, and that is where the magic really happens. See you in the next video.