Excel Hour - Learn to Excel with The FP&A Guy

And we're starting a few minutes early. If you're out there, hello, if not, we'll chat with you soon. But I just wanted to jump on here. If you're out there, please feel free to comment. Looks like I see a few people out waiting for us to get started. We have about five minutes before we'll actually get started. But you can download the Excel file there. So if you can hear me, if you're out there, let me know where you're coming from, let me know your name. Would love to learn a little bit about you and we'll get started here in a few minutes. Sa Foreign we got a few more minutes before we get started. So if you're out there, if you can hear me, let me know where you're calling in from. Would love to hear. I'm looks like I'm seeing a few people out there, so just confirm you can hear me. Hello, it great. Looks like people can hear me. Welcome Grant, Roxana Prism. Looks like I may have said that wrong but ah, what are we learning today? Great question. We are going to learn about the choose calls and group by function. We got a few minutes before we start here. You can download the Excel file using the QR code there. You can go to my website. The FPNA guy resources Excel files. It's the first download everybody. Let me know where you're calling in from, where we have people from in the world today. I'm gonna go grab me some water before we get started and I'll be back in just one minute. Welcome from Virginia, Grant. I'm in just outside Salt Lake City in Utah. Got my FPNA guy hat on today, repping my brand. Foreign just got about another minute or two here before we'll really get started. See who else joins us. Yeah, as we're waiting let me know where you're coming in from and would love to know why you joined today. If there's, you know, anything in particular that interested you about the subject or just like to join. I know some ask what the subject will be. My mic is cutting in and out. That is really weird. Is that better now Grant, you mentioned it cutting out. Is it cutting out for others? Just let me know. Are you guys able to hear me clearly now? I'm going to adjust it a little bit, see if that's better. Okay for you. Let me just double check my settings. It is the right. Let me know if this is any better. Clear on your end. Perfect. All right. Well it looks like for the most part it's clear. So we'll keep going and if it does cut off, just let me know. Hopefully we don't have any more problems. All righty, so let's go ahead and get started. As I mentioned, we're going to talk about Choose Calls and Group by today. I thought it'd be fun to talk about both of these. Some of you on the Choose Calls may have seen some of the examples if you downloaded the file from my last newsletter. We're going to go through several of them and then Group by is a fun new function. It gave me an opportunity to play with it, to put together a file. So it was fun for me. I'm still learning the function and what you can do if you've used either of those, you know, let me know in the chat. I'd love to know if any of you are using Choose Calls are Group by today. Again, if you need to download the file, the fpnaguy.com go to resources. Excel Files will be the first one to download or scan the QR code. All right, so we got three areas we're going to do very little in in this PowerPoint today, but I'll share a little bit about who I am. Then we're going to jump into Excel and talk about Choose Calls and Group by and we'll have a wrap up. So many of you, if you've attended before, has seen this, but I'll I'll run through it anyway. So you know, my name is Paul Barnhurst. Many people know me as the FPA guy or the dude that likes to grow his beard long. Any of them work? I have three podcasts. You can see the name of each of them down below. I'm a trainer, Microsoft MVP. I share a lot of content on LinkedIn. I think I just like to hear myself talk. So I have an MBA. I spent about 12 years in FPA. That's a little bit about me. And let's jump into Excel. All right, I'm gonna close PowerPoint here for now and you should be seeing an Excel file. If you're not seeing it, you know, let me know. All right, I did. Thanks, Jared, for letting me know. You haven't used those yet. So Choose Calls has been around probably two years. If I had a guess, Group by is one of the newest functions. Came out the same time as Pivot by and you know, maybe we'll talk about Pivot by some more another time. So the Choose Calls function at first, some of your prob might wonder, you know, if it really is very Helpful. But let me just explain how Choose Calls work. Basically, you're selecting a table or an array of data. So you know, could be from column G over to J. You know, from 2 to 8, you have some data here. Choose Calls allows you to pull certain columns out of the data using a number. So you could pull one, you could pull all if you wanted. Though I don't know why you do that with Choose Calls. There's other ways to do it, but you could. You can pull them from front to back. From back to front, you could pull just column one and column three. It's the same as Choose Rows. I don't see as many applications for Choose Rows, so I chose not to include it here. Plus, it works pretty much the exact same, except for in pulling a column, you're pulling a certain row in your data. So the arguments are quite simple. There's the array. The array is just your table, your range, your data, whatever you want to call it. Then you have the first column number you want. You have to pull at least one column. Beyond that, every other column is optional. So you could have column 2, 3, 4, 5, etc. One thing you'll want to know is positive numbers go from left to right. So if you wanted column one, you would do one. If you wanted the last column, and you always wanted it to be the last column, no matter how many rows or columns were inserted, you could do negative one. Negative one always starts from the end. Positive one always starts from the beginning. You could, you could even mix them together so you could do one and negative one. So that's the basic function. We're going to run through several examples and really, you know, what I'm hoping to show you here is kind of just some unique use cases. I don't even necessarily say unique, but some fun use cases for choose columns. The reality is what I'm showing you can be done other ways in Excel, which is true of pretty much every function, right, is people like to say there's 10 different ways to do something in Excel. So what I have is there's two sheets, there's the questions, and then there's the answers to those questions. We're going to go through and do these questions off this data set right here together. So hopefully everybody can see my screen. Well, we have here, you know, employee ID to title. It's a table. If you look at it, it's called Employees. So if I come down here in yellow, I've highlighted what area each of these formulas should return. So the what you're going to notice if I was to count this, there's 10 columns. Go 1, 2, 3, you know, 4, 5, 6, 7, 8. I think there's nine. Actually, there is nine. So this number that says 10 should say nine here. So what we're going to do is we're going to pull column 4, 4 1, 2, 3, 4, full name, title, cost, center and salary. And we want to pull it and return it in that order. So 4,956. So all we have to do is come in here and type, choose calls. In this case, the array is going to be a table. So you will see table syntax. I'm going to select the entire table. The table is named employees. And then I got this identifier which goes in brackets, hashtag all, which means give me everything headers. If there was totals, data, give me everything in the table. So that's my array. You can see if I, if I highlight it, it's going to show up here, the entire data table. Next we're just going to do a comma and we're going to say, give me column four, give me column nine. Give me column five, give me column six. Close it. Real simple example. Notice we got the headers with the data, right? If we didn't want the headers, we could just go here and select just the employees without the headers. Notice now, this doesn't look right from a formatting standpoint. You'll notice this down here is blank because we lost our headers. So there, there's different ways you could, you know, you can pull it. The other thing to remember is with dynamic arrays, whenever you adjust the size of your array so you pull more or less rows or columns, it any formatting you have doesn't automatically carry over, you will have to go in and either use conditional formatting or manually adjust your formatting. We're not going to get into conditional formatting for these today. Who I recommend to have some great videos on YouTube around, you know, using conditional formatting with dynamic arrays is Mike Girvin. Excel is fun. He has a number of great videos. Some, some great ones on group by. All right, so that's the first example. Now we'll walk through the second. This time we're just going to pull the last three columns in order. So I want title, benefits, bonus. So same idea again. We're just going to type in, choose calls. We're going to come up here, we're going to highlight the entire table and then we're going to do negative 1, negative 2, negative 3. We'll pull it and you can see it. Pulled it. I can clean up the formatting if I want. Here you'll also notice let's say I add a new column. Let's say new column is company. So maybe Apple, Google, Microsoft, and we're just going to repeat those, make it really simple here. Just do something like that. Now notice I get Apple because I said give me 1, minus 2 and minus 3. If I delete this column, it will go back to title. So much like index. If anyone's used index, match, match, we use the match to make the number dynamic. We can do similar things and we'll show some examples. We can do similar things with choose columns. There's different ways we can make it dynamic. So let's keep walking through a few more examples of how we could use it. And you could see in a report how it could be helpful to, you know, be able to have people choose columns that they want to see. So here what we're going to do is we're going to use choose columns with the sequence formula, which I've talked about before. If you're not familiar with it, I'm not going to go into detail on the sequence formula today or the sequence function. But you can find videos I've done before that go over that function. So right here I have this little drop down. I've put in anywhere from negative 9 to 9. And what I want to do is choose columns with sequence to return the N columns in order. So what I'm going to do here is I'm going to type choose calls once again. Same as before. You'll get tired of me doing this. Highlight the table. I could have just typed it in as well if I wanted. I could just go here and type employee tab, open bracket, bracket and then select all close the bracket. So if I don't want to use my mouse, I could do it that way. So I have that. Now what I'm going to do is I'm going to use sequence here and we're going to say yeah, one and then I want to. What I want to do for my column, I'm going to do the absolute value of this number right here. My start is going to be this and to put a one at the end and why, what did I do wrong? That should have worked. There we go. Did I delete a column? Yeah, it's working now. So you can see here it's returning column one. Here it would return minus seven columns. So it's spilling. Let me move this a little bit. So as you can see, this one here, I have this in the way. I, that's. I, I meant to move this. There we go. So see, now I'm returning seven columns. And so if I just wanted to return a certain number of columns, I could do that. Starting here with column three, it's going to give me last name, full name, cost center, last name, full name, cost Center. So what it's doing is this one is giving me n number of columns in order. So if I say give me four, it's going to go to column four and give me the next four. 1, 2, 1, 2, 3, 4. So I got full name, cost center, salary bonus. And so that's a way using sequence, that I can select dynamically how many columns I want. Now what I can't do is pick, pick different ones. Like if I wanted 4 and 2 and 6, there's different ways I can do that. But here just shows using sequence, I can return any list of formulas. So what the formula is doing is we're saying choose all columns. Then we're using a sequence here to we just put one in the rows and it's going to spill whatever it needs then in the columns. In fact, we could even just take this out and it would work. And then in the columns, what I'm doing is saying, give me the absolute value of this many columns. So I say nine. If I said negative nine, it's going to give me nine. If I say positive nine, it's going to give me nine. Starting with either negative nine, positive nine, whatever number this is and going one each time. So if it's negative nine, it's gonna go negative eight, negative seven, negative six. If it's nine, it's gonna go eight, seven, six. If it's one, you're just gonna get the one. And it allows you to sequence whatever data you want from the table. Yeah, there'll be a recorded video of this. It will be available afterwards on LinkedIn. It's also available to watch on Streamyard, which is the webinar platform. And it will be on YouTube. So, yes, you can get this. I know being in the office, it can be hard to focus. So that's choose calls with sequence. And that's really all about just returning a certain number of columns in order. So now take another example here. Let's take this one right here. Use filter with choose calls and sort to return the Cost center data for the three columns below dynamically by largest salary. So what that means is, you know, if I change this to a different cost Center, I will get that data. What I want to do is I want to just kind of show you the answer here and then we'll walk through it. So if we come here, you'll see the formula is using three things and I'm going to copy this and then we'll walk through it versus spending a bunch of time retyping it. So if I come here and I'll walk each of you through it and you can do it. So let, what we're going to start with is we have a sort and a filter. So we'll write the formula here in a minute. So let's change this back to cells. And so we got sort, filter, choose columns. For choose columns, we're going to select the employee table. And the reason it says underscore two is tables have to have unique names. So all the answers are underscore two and all the ones on the question sheet are just straight employee with no underscore two. So I'm going to fix it here. So what we've done is we said, hey, choose columns from employees then. And let me just walk through this. So let's actually type the whole thing together. I'm just going to copy this over here in case I forget and any fun step. So give me one second because it's a long formula and it will save us time if I forget something just in case I need to reference it. But if you copy here with me. So what we have is we do, we start with a sort, we do a filter, we do choose columns. And so for choose columns like we did before, we're going to come up here, we're going to highlight the whole table. Next we need to tell it we want three columns. I want full name, title and salary. But I want them dynamically. I, if I change the full name to something else, I want it to pull that. If I pull the title, if I change the title, I wanted to pull that. So what we're going to do is we're now going to add a match. Most people think of index and match together, but it's really important to understand match is a standalone function that all it's doing is returning the number from some kind of range. So from a list, like if you have a list with the names Paul, Joe and Mary, if you looking up Mary, it's going to return the number three because it's the third in the row. If you're going columns and you have maybe name student, university and you want to return university, it's going to return the number Three. So instead of having an index here, we're just using a choose columns. That's our table or array and then we're using a match to tell it what columns we want. So it's dynamic, very much like index match. Same idea. So here we're now going to match it. So what I'm going to do is I want to match all three of these and where I want to match them is my employee headers. So I'm going to select headers and then it's going to ask me what kind of match I want. I want an exact match. So I'm going to do that. And let's just take out filter and sort for a minute and notice it, it's. It would return that. It's giving me a spill error because there's something in the way because I didn't do the filter. So now we have it. So let's say the next thing we want to do is we want to do the sort, because that's easy. So we'll add the sort next because we're going to break this apart. We got our sort index and now we need to tell it order. We're going to do negative one for descending order. Let's see. No, what do they do wrong? Hold on one second. What did I. Oh, so sort. Right, yeah, there we go. So we want. I forgot to put the column we want to sort by. We want to sort by salary in descending order. So now we can see it sorts that in descending order. Notice here, if I change this, I get bonus first name. Let's say here I don't want the headers because I have them above. I can take out all and I get that. So the last thing I want is. Now how would I filter this to give me only cells? Well, I can wrap the choose calls in a filter. So that's my array. And then we want to come back here and we want to include the cost center. So we'll come up here, highlight the cost center where it is equal to cells, we'll close that and then we have our sort afterwards. And so that's an example where we can stack our functions and use choose calls to dynamically pull the data for certain columns and filter it. And these columns can be in any order you want. Right. I can have cost center here, employee ID here, and salary here. And so it's a really easy dynamic way to pull filter any of your data out of a table and sort it in whatever order you want. So we've been going for a while now. I'm going to pause and let people ask any questions they have so far on what. On what they've seen or just in general. And I will see if I can come up with an Excel joke. Unfortunately, if you've attended one of my sessions before, you've probably heard all my jokes. So we'll do a non Excel joke. Does anyone know why you take an extra pair of socks to the golf course? Anyone know why you take an extra pair of socks to the golf course? Yes, Andrew. Great, great question. And then I'll tell the. I'll tell the answer to the joke first. So the answer is in case you get a hole in one. Ah, there we go. Jake's heard it. Well done, Jake. See some people. Got it. Okay, so, Andrew, yes, this will be available on YouTube. It will be available on LinkedIn to watch. And as long as you've registered or you register, you can watch it through Streamyard. So yeah, it will. It will be available after all these are available because I know often you want to go back and re watch things and you'll notice a few mistakes. I just barely created this file and I realized after we were almost done with this exercise, this exercise right here should have came first so you could see kind of, you know, just the match right here. So there's a few things that are a little off in this file. I'll have to fix it and resend it out. But this one said from the above table, pull back the columns listed below using Choose Calls and Match, which is what I showed you above. And then I added to it, also filter and Sort. So if I wanted to do just this, we'll walk through it one more time. Because repetition is definitely the way to learn things in Excel. We're going to ignore that so much like index. Here's my table. Now with Match, I'm going to tell it what columns I want. I want these three columns, I want to return them from here, and I want to do an exact match. So there we go. Only difference here is I've added more things to it. Same basic idea. All right, last one is kind of an interesting one. Has anyone ever heard of the by call function by chance? So by call is a function designed with dynamic arrays to allow you to sum multiple columns individually. Typically, if you use a sum on a dynamic range, it's going to sum all the columns by color. By row allows you to sum them by column or by row. And so what we're going to do here is from the choose calls I want to get just the salary and the bonus, and I want to return the sum of them. So let's kind of first start by doing choose calls for those two columns. So I'm going to do choose calls. I'm going to come up here, highlight this and I wanted salary. We'll do salary and benefits. So that's 6 and 8. So I'm going to type 6 and 8 here and we can receive. See, it returned all the numbers. Right. If I highlight all this together, it's $2.3 million. Now, many of you are probably thinking, well, can I just sum that? Well, let's see what happens if we sum sums it into one column. That's not what I want. I want to know what my total salary is. I want to know what my total bonus is. And maybe I want to know what my total benefits are. We'll do that one next. So instead what I do is I say equals by call, which asks for an array which is our choose columns is in our array here. And then it says what function do we want to use in this case I want to get a sum. So I'm going to do sum. Now let's have a little bit of fun here. Notice I could also do average. And what is interesting is because this is a special type of function in Excel and lambda is notice when I type average here, I'm not putting parentheses after it. I'm not putting a range. It knows that it's working off this array. And so all I have to do is put the name in this formula. Let's say I wanted to try to do both. Maybe I want sum and average. Well, let's try typing it and see what happens. It says too many arguments. Well, there's fun ways around that. Try typing this and say sum, average and let's see if this works. I'm hoping it will work here. Yep. So now I didn't do it. I just gave the first one. I'll show you with group by. We can do it. Ah, yeah, I gave the last one. It gave me the average. Here, I'll show you some ways. I, I. You can use this right here to show multiple elements when we get to group by. But you can see it accepted both of them. I'll explain a little bit more on that in a minute. But let's go back to some or, you know, average or min or whatever. There are 16 options here that I can group it by. You could even use a lambda and write your own custom formula. There's some a number of different things. So let's say maybe I wanted the min, so, you know, I got the minimum salary if I come up here, what's the lowest salary? Well, it's $100,000. What's the lowest bonus? 25. So it's returned that. Okay, so that's everything on Choose Calls. That last example where I tried to do multiple, I shouldn't have done that here. I've done it with Group By. I thought it would work with Buy Call, But I'll show you how we do it in Group by here in a minute. It'll be the same principle. So I'm going to pause any questions on Choose Calls or Choose Columns. I'll give you a minute. Let's see if I can think of an Excel joke. In the meantime, what's the most dangerous cell in Excel? So that's my question for you. What's the most dangerous Cell in Excel? C4. Well done, Grant. Everybody's probably heard my cheesy jokes. All right, so let's go on to Group by. Group by at first looks like a really scary function. And so if anyone's ever done SQL, you've probably done a group by, maybe an order by a lot of things like that. If you used other languages, a group by just allows you to group data into a column by rows and aggregate some kind of value with it. So the group by function has like eight different elements. So that feels long. You're right. I can't think of many functions that have eight different parameters that you can enter dat, you can enter something for. But the good news is you only need three of them. These first three what I'll call the row fields, the values, the function. The row field is the column or columns of data you want to group your data by. So let's say an example. We go back to the last one we had. I want to group it by salary. That would be my row field. So I would just select from that table salary. Now, there's the values. That's what you want to return? Well, no. So let's say I not group by salary. Let's say I group it by cost center. Sorry, not by numbers, but I group it by cost center. So I have my different cost centers. My values will be my salary. Then I can, you know, ask it. To sum it average, I got these 16 different options. That's what we saw earlier with By Call. So all these are available as a function. And I'll show you how that works. Then you can tell it, I want filled headers or I don't want filled Headers. So yes, include filled headers. No, you could tell it to do grand totals, grand totals and subtotals. So you're starting to see this is probably a lot like a pivot table. Pivot by allows you to do rows and columns. Group by allows you to do one, you're not doing both. So it is very similar to the pivot table for subtotals. You must have at least two columns if you want to do subtotals. And then you can do a sort order instead of having to wrap it in a filter. They included the filter right in the function. So you can filter the group by. And then there's this filled relationship. I have not used this yet. So we're not going to walk through an example of this. But you can see the information here. So if we come over here, let's look at a couple. So first you'll see our data. Our data we have is some tool data for tti. They're kind of like Black and Decker. They own a number of brands. Milwaukee, Ryobi, etc. So we're going to do several examples. So the first one says let's group it by product category and return the quantity stumbling. So the function we want is the sum. So what we'll do is we'll type group by and it says oh, what row do you want? Well, I'm going to come over here and I'm going to highlight product category. I'm going to do control shift down and you'll notice it highlights it. Or I could just drag it here or whatever, something. There we go. So now I have the whole table. Something weird. Give me one second here. I think this table got duplicated. If you can delete everything after row 70, I'm going to delete those. I'm going to highlight everything. Hit control minus. I think when I copied some of the formulas down I added more than I want. I don't want this that much information. So we're going to shorten that a little bit. We're going to come back here, we're going to type group by. We'll come back over here, we'll select product category. So that's my first step in here you can see that's my row fields. Now it says where are your values? So I'm going to come over here and select quantity and I'm going to include the header and then I'm just going to say sum and I'm going to close the formula. There we go. And what you'll notice and I'll So here you can see it sum the data and it gave me the total. So I have floor care, hand tools, outdoor products. 371,000. If I came over here and checked my quantity going down to just row 69 there. 371,000. So now let's say I want the header included. I'm just going to go ahead and do it here. There's a later example, but I think it will be easy to show. So now I'm just going to go ahead and say, yes, I do have a header and show it. So if I hit 3, it now gives me the product category and quantity. But you'll notice I lose my formatting, so I'd have to clean that up. So next let's say I want to do group by category. Return the quantity sum in descending order. So it's going to be the same function except for I need to go to sort order. So I'm going to skip this step because this is optional. I'm going to skip total depth. And now I'm at sort order and I'm going to select the sort order I want. So let's say I want in descending order is one. So you can. You can see it did that in descending order. So you have that ability to do a sort order. I could also say one, and now it sorted it alphabetically. If I wanted to sort by a different column other than the first one, you could probably use a sort by to sort by values. There's different things you could do, but for now it's just going to sort by that first one there. All right, so we're going to keep going. This is the one with the filled headers in descending order. So we kind of covered that above. As I mentioned. We were going to do it, but we'll walk through it one more time just so everybody gets the hang of it. So let's say group by. Again, we're going to select the field. We want product category, we want quantity. So we'll come over here and select that. Then we're going to go in, say sum. Yes, and show. You could say yes and don't show no, but generate. I actually haven't tried that. I'm assuming it does something like column one, column two. We'll say we don't. We won't worry about this. And then we'll do it in descending order. So you see, now I have it in descending order. Next I could do the same thing but exclude the totals. So if I wanted to exclude totals, I would need to come back to this step right here and say no totals. So really, basically you have the first three arguments. What's my data? You know, what's my row? Or not row, what's my columns I want, what's my values I want, how do I want to aggregate it? Then everything after that is just allowing you to play with the data, sort it in a certain order, include subtotals, include headers, filter it. So instead of having to add a bunch of extra functions, all those optional steps are allowing you to do it all in one function. And that's what you see with xlookup having the kind of if error inside of it. As Excel started building a lot of these newer formulas, they said, why should we make you stack, combine multiple functions together? Let's just give you all these optional arguments that you can choose to use or not. All right, so I'm going to pause here before we keep going and see, you know, see if there's any, any questions so far. And as I'm waiting to see if there's any questions, let's see if I can think of a good joke. What does a statistician look for in a spouse? See if anyone knows that one. Grant. Jake, what does the statistician look for in a spouse? A normal distribution. All right, so now we're going to do. We're going to fill. We're going to group it by two categories. So we're going to do group by and we're going to select product category and product type. So they're next to each other. So we just selected both of them. Now we're going to do values and we're going to do quantity and we'll do sum. We'll stop there for the moment. And you can see now it did both of those and you could see there's a total at the bottom. Grant doesn't think he can use the statistician one at work. Well, I'll try to come up with another one you can use before we're done, Grant. So there's that group by product category type and return the sum of the quantity. Now, I did say with subtotals and headers, so let's go ahead and modify this to include that. So now we're going to go to our optional arguments. First one is going to say yes and show here we're going to say give me grand and subtotals. I could also say give them to me at the top with negative two. Let's go ahead and give them at the top and just see what it looks like. So you could see grand total is now at the top. Floor care total is at the top. Hand totals is at the top. Outdoor products is at the top. And let's say you wanted these to be bolded differently. You could write a formula to say hey, if it's not blank use condition. You could use conditional formatting to say make it, you know, make it bold or make it a, a light gray color or put a box around it or whatever you want. That's how you would do that. So there's the example of that. So let's keep going. Now we're going to get a little. Going to get a little tricky here. This is probably about as complex as we're going to get. So now I want to do group by product category and product type and return some average percent of max and and min of the quantity with subtotals and headers. So I'm going to copy this formula up here and notice right now I just have totals, so I have the sum right here. I need a way to return multiple different aggregations. I could use a lambda. I'm not a lambda guy yet. It's still one I need to learn. So we're going to use a formula called hstack which what hstack does is it has horizontally stacks something. And so in this case we're going to stack the aggregations we want. So, you know, it seems kind of weird, but I can actually just type the word sum average like I was trying to do earlier. I mentioned I'd show you this average percent of max min. Close that and check that out. How cool is that? So I can do a group by and I could get, you know, 10 different categories. If I wanted to stack them in a a row, I could use a vertical stack there. And instead of them going this way, it would show them this way it would show some average. I think I'll get a spill error because I have something below it. But let's just see what happens if I type vstack. Yep, that's what I figured. You can see it would go way down here. So let's just show you what it looks like. Let me copy this formula. Let's come down here outside of that range and notice some average percent of max min. What it's doing is it's stacking those and it's showing them for every different category. This will take some getting used to because it. It was really weird for me the first time. I'm like, I'm writing a function and then I'm writing a bunch of functions inside of it without any reference. It has to do with what's called, I think it's reduced eta lambda. So basically it's a lambda, but they've removed having to type anything but the function and it will work. Don't understand all of it technically behind it, but it's a really cool feature. So that's how you can do mo multiple. So if I come back here, I'm going to change that back to hstack and I could do as few or as many as I want. I could do all 16 here. I think a few of them wouldn't make sense and so we might get an error, but you get the idea. And I can change the order. If I want average first, I type the word average first if I want max first and do it in whatever order I want. So I guess, you know, there's a lot. I could probably wrap this in a sort and get a different, different order if I want. Haven't tried it, won't try it. Now let's go to the next example. Let's group by two columns, not next to each other. So now we're going to use the choose columns first formula we did with group by. I was asked this last Excel session when I did ask me anything, somebody's like, how do you work with non adjacent columns in a group by? That's where choose columns comes in handy. So what we're going to do is we're going to do group by and then we're going to wrap it into choose calls. And what that allows us to do now is choose two columns that aren't next to each other. So I want product, category and power source. I can't just type in, you know, M2 to M4 and P2 to P4 because that comma will treat it like the next step in the function. So by wrapping it in a choose calls, I'm making it one step in the function. So what the choose calls is doing is it's basically allowing me to get multiple non adjacent columns by treating it all as one argument. So then Excel understands it. That's the basic idea. So now you can see we've done that. So now it wants our values filled. So we'll go ahead and select quantity and then we can, you know, add whatever we want here. I think in this case we had the same as before, some average. So I would just do that H stack. Maybe we'll mix it up. Min, max, average, sum, you know, whatever percent of. Maybe I'll do it in that order. So we got it. Now we'll say yes and show grand totals and subtotals and we'll sort it there. And you can see we now did columns that aren't next to each other. All right, so here's another one where it says group by product category with the following values. So now I want multiple values. I can use choose calls for multiple values. So we're gonna do group by and we want product category. I'll come up here, select product category. Going to say my values here is going to be a choose calls. So I'll highlight all of this. And I want. Maybe I want revenue. So we'll say negative 1, because that's the end. I want cost, negative 3. I want price, negative 4, and I want quantity, negative 5. I could have counted the other way and said, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. I want 11, 9, 8, and 7. Can put those in whatever order I want. We'll close it. You've entered too few. All right, so what did I do wrong? Oh, I didn't tell it what value I want. And we'll just say I want a sum. So see, now I have the sum of all those. You know, same type of thing. I could say, oh, do I want a header? Yes. And show now I get a header. I want no totals. And you can see all those things, you know, the totals, the no totals. You could put all of those in a drop down and create a report where somebody can go and look at the data saying, I don't need headers. Or using, you know, match, using choose columns. You could make it all dynamic. So there's some pretty cool things you could do. Yes. Could you do these with the pivot table? Sure you could, but every time your data changes, you have to hit refresh, you know, and so this gives you a dynamic group by function. And, you know, I'm still trying to think through all the different use cases for it. Just like anything in Excel, I'm still getting familiar with it, but I wanted to introduce it to you because I think there's a lot of value in this function. So I'm going to stop here for a minute before I go back to the PowerPoint and we wrap up questions from anybody. We got a quiet group today. All right, so I'll tell one more joke before we go back to PowerPoint. Does anyone know why the spreadsheet was constipated? And Grant, you probably can't tell this one at work either. Jk that you can think of? Yeah. The, the Excel is available. I said at the beginning, it's on my, it's on my website. You can download it from the fpna guy.com. so, yeah, the Excel file is available. I'll also send out the PowerPoint and the Excel in the next day or two here to everybody who registered. So, Jake, very close because it couldn't budget. Kind of backed up, but not exactly. So the spreadsheet was constipated because it couldn't budget. There's my FPNA joke for the day. All right, so I'm going to go ahead and close this. Going to bring back up the PowerPoint and first you're going to find a couple resources here. These are just Microsoft resources. Obviously you can go out to YouTube and there's tons of great resources out there. But here's one. Group by group by and pivot by choose calls. Next you get a minute of selling from me. So I have two digital courses that I'm going to make available here. If you use the code webinar, you can get 30 off. The first is those of you who are an fpa. You'll get my Excel course bundle, my data analysis course, my one around modeling, design and business partnering. That usually is $129. You'll get 30% off. So less than a hundred. That's over 10 hours of digital content. You could see what the student said. Now, if you're looking to boost your FPA skills, I highly recommend their courses. They're truly worth it. So great question, Jake. You need 365 for these functions. Dynamic arrays require 365. So if you're on an older version upgrade or if your company won't go upgrade, find a new company. I don't know. Or just be sad not I'm not very helpful there. But that is something you have to consider with all of these functions. If you're seriously working with somebody that's using 2016 or 2010 or whatever version, these will not be backward compatible. Because in 2019 they rebuilt the calculation engine for Excel. Because they rebuilt it, they can't be backwards compatible. Excel used to work on only one cell at a time. Why you had to use Control Shift, Enter if you did any arrays. When they overhauled the engine, they made it by default to be arrays and to spill and to allow stuff to go into multiple columns. That was a huge upgrade. That's why we have data types, that's why we have Python, why we have Lambda. It allowed them to do a ton of things correct. JK, it's. I don't think group buy is even in 2021. Not even sure if Choose Calls is Dynamic arrays in general are. But I'm pretty sure Group Buy and Choose calls are only in Office 365. All right, so if you want to take my modern Excel course, that's going to cover Excel tables, Power Query, dynamic arrays, an introduction. It's the same material that's used in the Wharton Online program. They licensed that material for that FPNA certificate. You could take it. It's about five hours of content, $60. Use the code webinar, you'll get 30% off. And you can see kind of a testimonial there from somebody. Grant, you are correct. Group Buy was just in the last two months or so. It came out of beta. It's very new. As far as live course offerings. I haven't done any virtual live course offerings. Jane. On Excel, it's just a digital. At the moment. I do corporate trainings. I might at some point look at some virtual. But at the moment, I. Outside of these webinars, I don't. I may offer Power Query later this year, but it depends on my partner. So at the moment, I don't have any virtual Excel courses. Giles, Mel has some great live ones. I highly recommend them. They're on Maven. If you need someone that's really good, check them out. You need an introduction, happy to do it. But at the moment, it's just digital or these webinars, so I'm gonna pause there. We're just about out of time. We're near the top of the hour. Any other questions from anybody before we let you go? Anyone got a good joke for me? I need new material, so put it in the chat. Grant, Jake, I'm looking at you guys. All right, not. Not. Not seeing any jokes. Let's see. Heard most of the good ones in Vegas. Got it, Grant. Well, I know what happens in Vegas stays in Vegas, so I get it. All right, thank you, everybody, for attending. Appreciate you joining me today, and good luck with Excel, and you're always welcome to reach out. Ah. Where does the Excel team go to drink after a long day? Yes, I have heard this one at the Formula Bar. And on that note, goodbye, folks. Good night. I'll be back again soon. Yeah. Thanks, everyone. Thanks for the Happy New Year. I'm glad you found it really helpful. Thank you, Sharmila. I try to provide value where I can, so thanks, everybody.