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

Foreign. If you can hear me, let me know. We'll get started in about five minutes. Just getting things set up here. So let me know in the comments if you can hear me. We're really excited for this session to get started here in a minute. And for anyone who needs the file, you can find it on my website. It got loaded shortly after noon today, so it was a little up there, a little later than normal. I know a number of people went to get it and it wasn't quite there. So if you need it, you can go to the fpnaguide.com click on Resources Excel files and you'll find it right there. Hey, glad you hear. Glad you hear me loud and clear, William. We'll get started here in a few minutes. We're a little early. We got about another five minutes. I just wanted to get everything tested and up and running as I'm using a different computer than I normally do today. So I can show some stuff in beta. I'm on my, my laptop with beta set up, so it just took me a little while to get everything set up this morning. So this afternoon we'll get started here in a few minutes. In the meantime, if you can hear me, let me know what you're, you're, where you're coming from. I'd love to know if you've ever used any of these formulas before, what you're hoping to get out of today. So if you hear me and you're out there, feel free to let me know. We got a few minutes before we'll get started. So looks like we have seven, eight people out there. So you can go to the fpna guy.com resources excel files and download today's file so that you have it for our training. It should look something like this. This is the master file, so yours will be grayed out. You'll get that after the training. But you should see three things in that file. Group by, pivot by and copilot, which we'll briefly show. All right, so I'm going to go ahead and bring up the deck. You can also click that QR code and download the file. I'll put the address as well in the comments. So anyone needs it, they can do it. And I think there's looks like there's at least seven of you out there. So let me know where you're coming from. If you're out there, I'd love to know where you're coming from. I know we have some people coming to us on LinkedIn, some coming through Streamyard directly. So we have different people from different sources. But go ahead and let us know where you're coming from and we'll get started here in a few minutes. Where are you coming from, William? Bay Area. Perfect. I used to live out in the San Jose in the Bay Area for a couple years back in the 90s. Date myself. I feel old now. That was 30, almost 30 years ago. But how life goes. Perfect. Welcome, Rochelle. And where, where are you located? Rochelle would love to know where you're calling in from. Alrighty. Got about two, three more minutes before we get started. If any of you have ever used Group Buy or Pivot by, let me know in the chat. I'd love to know. All right, we got another minute or two here before we get started. All right, so it looks like we got Columbia, we got the Bay Area, some other locations. We'll get started here in a minute or two. All right, so just a reminder, you can download the file. I put it in the chat. I'll also just put it across the top of the bottom of the screen here. You can download the file here. So if you just go to the fpna guy.com click on resources, go to Excel files. All right, we'll go ahead and get started. I know more will be joining, but if you're out there, you know, please let me know where you're coming from. I always love to know the different places we have throughout the world. Looks like I see we have William in the Bay Area. We have Leonardo or not Leonardo, Fidel in Colombia. We also have others who have joined us, Relle, Leonardo and I'm sure we'll get more as we get going here, so why don't we go ahead and jump into things. So as I mentioned, we're going to cover Group by Pivot by and copilot functions so you can download the Excel file here. You should have saw that on my screen as well. If you haven't got it, there it is. I will send out the master file when we're all done. Just a little bit about me you can see here on the screen. You know, my name is Paul Barnhurst. I go by the FPA guy for my business. I love running. I've always been a big runner. I love podcasts. Everybody knows I have three of them. Future Finance, Financial Modelers, Corner FP and a Tomorrow. And then I'm also a Microsoft mvp. This is a picture of my wife, my daughter, very old picture of my daughter. She's 12 now with is crazy to think here's me out hiking many years ago doing a 50 mile hiking trip. I love to hike and so that's just a little bit about me. Oh, I feel intimidated. Now we have Joe McDade out there. All right. So if anyone who doesn't know him, he works full time on the program for Microsoft, so we have him to thank for many of these functions and things that go on in Excel. So today we're going to talk briefly about Group by, pivot by and I'm just going to highlight I'm not going to spend more than a few minutes on it probably toward the end unless there's an overwhelming desire to do earlier. I just want to show a few things about the copilot function. It's definitely generated a lot of buzz on LinkedIn. You know, some people loving it, super excited seeing it as a game changer, others concerned about it. And so we'll talk a little bit about that. I'll also be talking about it more in my newsletter tomorrow, so if you haven't signed up for that, please go ahead and do that. All right. Then we'll do wrap up and key takeaways. So why don't we go ahead and get started In Excel everybody should have received the student file. So where I want to start and let me stop sharing for a second, it's not showing the right screen. Give me one second to reshare and William Murphy asked about a recording. So one, this is on LinkedIn. LinkedIn will record it so you can watch it there. Two you can go to Streamyard. As long as you register you have access to watch it. It's also being streamed live to YouTube so you can go to YouTube and find it and let me just show so that might be helpful for people to see. Let me bring up my website on all my prior sessions are here with the download files so you'll see whatever it says down here that's what that session was. I've probably done 20 of these now and so there's the sequence function, pivot tables, new features in Excel dynamic arrays I've done a couple times power query. If that's the subject you're interested in, you can go download the file. If you have any problems, email me and I'll check why it's not there because I have all of them and so you can see all kind of my different Excel videos with the files here. There's a few at the bottom that are just general files may have been tied to a newsletter or something else. But you'll be able to See all the different videos there. So hopefully that's helpful. William. And we see Abbasi from Nigeria. Welcome. Right, we're going to jump back to the Excel file. So let me just enlarge Excel here just a little bit, make it a little bit bigger. All right, so here we have the group by function. And I'm not going to go through this in detail, but you're going to have in the file the syntax. The whole idea is, it sounds like of a group by function is it allows you to create groupings of your data. You can group it by rows if you want to group by both rows and columns. That's when we'll show a couple examples of the pivot by function. So, as you can see, these are incredible functions. I love them. But what you're going to notice is they have a lot of requ of fields arguments. There's 1, 2, 3, 4, 5, 6, 7, 8 arguments and I believe there's either 9 or 10 by pivot by. So there are some of the most arguments you're going to see in any function I can think of in Excel. The good news is most of them of them are optional and group by and pivot by are fairly similar in many of their arguments. So it's not as difficult as you would think to learn. Ten years ago, if you had told me how to learn a function with 8, 9, 10 arguments, I would have looked at you like you were crazy. Today I'm like, hey, let's just go figure it out. It's just go through it one by one. So you'll see here you have your row fields, your values, and then you have multiple different functions you can select from. You can do sum percent of average array to text, you could do a product, you could even do lambda and write your own custom functions to group things so you can group it numerically almost any way you want. There are field headers, what we call total depth. You want to see grand totals, grand totals and subtotals. There's a sort order. You can use a filter array to filter out specific rows similar to the filter function. And then there's a filled relationship. If you have multiple columns with different possible values, you can do hierarchies and tables. We're not going to walk through all nine of these in the hour. We're going to give you a high level overview, as you can imagine, just from a time perspective. To go through these three in the time we have, we're not going to go real deep on all three of them and allow you to do examples. So as normal, how this file is structured is we have the function, then we have some examples in my file. You'll see the examples there in your file. They're blanked out and we're going to do them together and they're going to get progressively harder. We have roughly seven examples that we'll do for Group by and then I think we have four for pivot by. Then we'll go to copilot. And if I get going too quick, if you need me to go slower, if you have questions, stop me at any time if you see my head turning back and forth because I have two monitors and one's off to the right to see the comments and Excel's in front of me. So if I move around a little bit, that's why I'm trying to keep my eyes on both. All right, so I'm curious just by not necessarily show of hands, but just in the comments, who's familiar with the group by function? Is anyone using it today? I'd love to know. So if you are, just go ahead and throw it in the chat. And this is one I've covered before. So the group buys a little bit of a repeat. When I did it previously, we did it with choose columns. Today I thought it would be good to do it with pivot by and then also, like I said, show a little bit of copilot. All right, so we here's our data. This is some data I use to train a customer. There's a bunch of brands here, Milwaukee, Ryobi. You can see it was a parts company, Hoover, Oric. They owned all these different brands. So we have some data. You can see there's a brand name, a product category, a type, a power source product name, and then some numerical values. We have quantity, we have price, cost, the month and then revenue. Here. That's a formula. So all of this is available in our group by and so what. What I like about this is it's dynamic. So Evelyn, you've never used it. I think you'll really like it. I mean, some of you are thinking, hey, I could do this with a pivot table. And yes, you could do a lot of this with the pivot table or, you know, sumifs or a complicated let formula. There's different ways you could do a lot of this. I find the group by makes it easy. It's a great, it is a great formula for quickly summarizing data. It's something you can use in reports and I really like it. So we're going to start with a very simple example. So what you're going to see here, and I'm going to delete it in a minute is example one says group by by product category and return the quantity sum. So if we come over here, we have product category and we're going to want to do the revenue, so. Oh, quantity, Sorry, not revenue, but quantity. So we're going to go ahead and write this together. And so what we're going to do here is everybody follow along with me is we're going to type equals group by and as soon as you hit the open parentheses you're going to see all these different options, which feels like a lot. The first one was row fields. So that's what do we want to be in the rows. And you're pulling that from a column and. And it's going to summarize it in the rows. So we're going to come over here to product category. I have this as a table. So I'm just going to get the drop down arrow by hovering over until I get it and it's going to write the name of the table and the column. So this is the equivalent. If I was to do this manually, it's the equivalent of me dropping it down there. But if I go one short, what you'll notice is I could write M4 to M69 if you know, if I was doing this not in the table, that's the section it's highlighting because that's the entire column. So we've done our row field. Yeah, I can zoom it in. It's a little hard to read. Yeah, let me zoom in. Thank you. Give me one second here. Let me exit out of the formula and let me know how. How's that? Is not that big. Not 400. Sorry. What about. Is that. How's that look? Is that a little easier to see? Hopefully that's better for you. All right, so equals group by and then we'll scroll over here, we'll come up to the top and select our product category. So that's step one. Now it's asking what are the values you want? So if you think of like the sumifs, that's where you put your whatever you want to summarize. In this case we wanted quantity. So I'm going to select quantity here and then the next thing it asks, once I put the comma, you'll see number three is the function and it gives me the list so I can select any of these. We're going to keep it simple and just do sum. Now, notice everything else is optional at this point. So I'm going to close this and so now you're going to see that it's given me floor care, hand tools, outdoor products, power tools. By default it gives me a total. We'll show how to turn that off later and then the quantity for each of these. So if I came over here and filtered on one of these product categories, say floor care, and I came over and summed up the quantity, I'm going to get 67, 000, which is what I have here. Once I unfilter this because it's kind of hiding some of it, you'll see it matches, right? So 67, 000. Then we get 73 for hand and each of them, you can see down here what the full formula looks like in this case. This formula down here, I included the a little longer name because I included the headers here. I'm not including the headers at the moment, but I could. So if I wanted to, I could also. And we'll do this later, I could have, instead of just selecting the data, I could have selected everything including the header. But if I do do that here, I have to also do it over here. Otherwise it will be confused. It will get confused because it will say the data set is not the same size, it doesn't have the same number of columns, basically, so exact same data here. I included the header. Later on we'll show how would we add the header if we want it. So that's the first example and I'm glad, Joe, you also forget to zoom in on your demos because I have a big huge 30 something inch screen and I'm like, that looks good to me and I start going. So thank you for the reminder. So here we want to group by product category and return the quantity sum in descending order. So I'm going to delete this right now. It is, you can see it's in descending order. Right, so. Oh, I did revenue on this one the second time. We want quantity. So I'm gonna go back and change that to quantity. And why did it not like me? Now we'll just leave it as revenue for the moment. We'll do the next one, but we could change it to quantity. This one we will do quantity. So what I'm going to do here is I'm going to delete this and we're going to do the same thing as before. But this time I'm going to show you how do you sort it in the order you want so we're going to do group by. We'll come over here and again I'm going to include the header. Normally I don't with most formulas, but with group by generally it makes sense, especially if you want to include the headers in your group by function, then you're going to need to do that. So we're going to do that. We've done those first two things now, just like last time, we're going to sum. But now we want to figure out how do we do descending order? So we'll see the filled headers. Right now we can leave that blank because it's optional. So we're going to leave it blank. We'll come to it in the next example. So we're going to hit the comma again. Total depth. We're going to ignore. We're going to move to the next one, which brings us to the sort order. And so we could put a one here and then we can close it. And now it's sorted it in descending order. You can sort it in descending or ascending order. Right? And I believe it's a negative one, if I remember right, for ascending order. And let's see. Yes, it is. So you can see now it sorted it from largest to smallest. Previously, it sorted it from smallest to largest. So one of the big things you'll remember, you need to remember with this function is you. All those optional arguments, you don't need to put them in. You can end whenever you want. You can put one optional, you can put every optional. You can put commas for them or not include them and it defaults. So I'm going to pause here for a second. Any comments from anybody, any. Any questions anyone has. I'll give it one minute and then we'll move on to number three. If I'm going too fast or if you have questions, just let me know. All right? And while we're waiting to see if there's any questions, we'll see if I can come up with an Excel joke. Does anyone know what Excel and gold diggers have in common? The question is, what does Excel and gold diggers have in common? If you want them to stay in place, you have to throw some money at them. So we'll see who got that one. All right, this one here we're going to do in descending order. So largest to smallest. And we're going to put the category headers. So it's going to be very similar to before. In fact, you could even copy what was done before and just add in the new one. We're going to do it from scratch and I encourage you to do the same because I'm always a believer that practicing something multiple times, it sticks in your mind a lot better. So we're gonna. Once again, we're gonna do product category here. We'll start doing some others shortly. I'm going to highlight the entire product category. Then I'm going to come over to quantity again. So I'm going to come up here and I'm going to highlight the entire quantity. So we've done that. Now I'm going to go back up to the question so I can read what it says. So we got quantity again. It wants sum. So we're just going to type sum in here. And now we want the filled headers. So let's go and see what we have Right here you have zero. There's no field headers. There's one. Yes, but don't show two. No, but generate. And then there's three. Yes, and show. In this case we want three because we want to show them. And then we're going to skip totals for the moment. Let just that be the default. And we're going to go to sort order and we're going to do negative one and we're going to close our function. So now you can see it's the same as before, except for we've now added the row headers. And so that's example three. As a general rule, I always like the row headers. Some people don't. Now, some people don't want totals. That's why there's so many optional arguments, is to give you flexibility to turn on and off what you want. You know, what depth do you want? Do you want subtotals, do you want grand totals? All those different type of things. All right, so let's go to example four. And so notice in this case, we're going to show you how to exclude totals. So this is going to be very similar to the one above. You can see the only difference between these two is this one does not have totals. So I could copy from what is above and just go change that optional argument, which is, you know what I want for totals. Instead we're going to rewrite it again. I know it's a little boring, but like I said, the more practice you get, the more it sticks. So we're going to do what we did before and then the next few examples will we will be including more information, not the exact same stuff every time. We're going to try different ones here in a minute. So we've done our product category, we're going to do our quantity, we're going to do same as before and do sum. And then I want to come back over here to number four and see what else did we want. So example four. So we want the filled headers. So yes. And show we're going to do three. Then we're going to say no totals for grand totals and we're going to do a sort order of negative one that will do the descending order again. So we can see. Now we have headers with no total. Here we have headers with total. Here we have no headers and we have it sorted. And here we just kind of have. We have total. So by default it gives us that, but it doesn't give us the headers. So we've done four different examples, all with the product and the quantity. Now just in this example, before we move on, I want to show just a few other things. Notice here with sum we can change it to any of these. So let's say I want average. I could change it to average. Oh, it didn't quite. Let's see, what did I do wrong? I'll just type it here. We'll type average. And you can see now I have average quantity. Hey, thanks for joining for a minute, Joe. Glad you you joined us for a few minutes. He has to drop for his next meeting, but hopefully I'll get to. We'll get to see you again in the future, Joe. So thanks for jumping in for a minute. So right, I did average there, but I could do whatever I wanted. I could do median and I will show you. Sorry, you have to type it in. I like to. I forget when you select it, you have to hit tab. So if you come down here and hit tab, it will then add it. So sorry about that. So there I've now done median. And I can change those to whatever I want. So I can just keep working through them. All right, so now let's go to example five. So here we're going to do two categories. Notice group by product category and product type and return some of the quantity with subtotals and headers. So now we're saying I want to group by more than one thing. So we're going to type group by. And I'm just going to hit tab here because I can't type. And we're going to do product category and product type. And so what I'm going to do is I'm going to come up here, I'm going to select product Category over to product type. And so now I'm selecting a continuous range. And so it shows the call and saying, hey, I'm going from here to here. I could add the next one if I wanted, but we've done that now. This time, let's go ahead and add revenue. I don't remember if we said to add revenue, but we're going to do revenue anyway because I want to mix it up. We'll do revenue, and I'm going to say sum and close it and we'll bring it back and add the rest. So here we go. We did say quantity, but I decided to kind of mix it up and do revenue. We want a sum. We are going to say yes and show our headers. We're going to do grand totals and subtotals. And notice we have two options. We can just do them normally or we can put them at the top. So let's do them normally, and then let's go ahead and sort this again in descending order. So now we see Power Tools. Chainsaw is first. You can see Drill Grinder and ignore the. The bolding. I had bolded this based on what we would originally do. I didn't do conditional formatting, so we'll just take those bolds off. But you can see the different categories. You. You can see right there at the bottom, there's the total for all the power tools. $27 million. So if we come back up here, notice under the total, we selected two. But what's really cool is I can select negative two, and I can start with all my totals. So you can see grand total Power Tools. If I wanted to bold these columns, I could use conditional formatting and say, hey, bold everything. That this category is blank, right? And so then it would bold that entire row. There's different tricks to do the bolding. One thing to always remember with dynamic arrays is is it does not by default format your data. That's something you have to do yourself. So that's example number five. We have a couple more that I want to show, but I'm going to pause here and see any questions from anybody at this point. I just want to give it a minute or two. I'll tell another Excel joke while we're waiting, but I want to see if there's any. Any questions from anybody on the group by function so far. All right, as we're waiting, does anyone know what the best cell is to store your Excel error messages in? There's a cell that's best to store them in an Excel any guesses on what that would be. So the one that's best to store your Excel error messages in is. And I'm going to go to it right now. We're going to go to right here. It's column FU, row two. FU2 is the best place to store your Excel error messages. All right, foreign, let's come back. And we're on, I believe, example 6. So this one's going to be a little tricky, but I think you guys will get the hang of it once I show it. So notice what I have here. I have the product category, the product type. But then I've added multiple aggregations, some average percent of max and min. Again, we're going to do quantity with subtotals and headers. So now we're starting to see the real power of it as we combine things. So we want both product category and product type. So we'll come over here. Ah, great question, Evelyn. The answer is yes, you can, you can select two non contiguous or not next to each other columns. In the next example after this one, I'll show you how. So that's a great, great question. So they do not have to be next to each other, but it is going to require another function to be nested within group by. So I will show that in a minute. I first want to show how to select multiple aggregations. So again we're going to come to quantity. We're going to select that. So now everybody should have the table name with all data. That's the item identifier, product category to product type. Here we have just quantity. So we've selected those columns. Now if we want to select multiple elements, we have to do an hstack, which seems a little weird, but we'll expand, you'll see it here in a minute. So let's come down here. We want average. So I'm going to type in average. So that's my first. We want some first. So we'll do average there, we'll do some here. And what a HSTACK is doing is it's creating separate columns for each of these functions. Without it, you can't put multiple. If I just type sum comma average. As soon as I put that comma, it thinks I'm moving to the next argument. By adding an hstack, all those commas are staying within that one function and it's treating that all as one argument within the overall formula. So let's keep going here and you'll, you'll see it in a minute. Next I'm going To type percent of, we'll come down here and we'll select that. We don't need the open parentheses. That's only true with certain functions. I won't, I won't go into the idea behind it. It's called an EBITDA reduction lambda EBITDA reduction. It, it's smart enough to know by just the words within this formula. So it only works in this group by and some others that you can do that. So now we're going to close it and we've added some average percent of max and min. And then it said we want subtotals and headers. So here we'll say yes, show headers. Here we'll say give us grand and subtotals and we'll do negative. We'll do one this time. Do it in ascending order and check out. How cool is that? I just got the sum, the average, the percent of the max and the min of my data. And so now we're starting to create something that's, you know, pretty big. It has a lot of different information. You can, you can notice here I'm going to remove, because I changed this a little bit from what it was before. I'm just going to go ahead and remove all the bolding and you can see it here and I can make whatever changes I want to it. What, what's cool is, is you could even do something as simple as have a drop down that has, you know, these different options. So you could do a drop down that has the number, you know, number three here and a sec, let me get my number lock working. Let me do that again. Just so you can kind of get an idea is if maybe you wanted to be able to, hey, turn headers on and off or change, you know, your sort orders, you could link all of these to a cell and then just change them based on what you want, right? So maybe somebody doesn't want grand totals and subtotals. Well, you could come in and change that by changing, you know, your number here. See, they go away. Now I don't want headers. You could change that to zero and it gives me no headers. I want it in descending order. So what you're seeing where group by gets its real power is by starting to use other formulas and functions with it such as hstack, such as linking out instead of hard coding. And now we're going to add the last example I want to show of group by before we move on to pivot by. And copilot is the example somebody asked for earlier, which is the non contiguous, you know, selecting columns that are not next to each other. So now we have an example. See, is this the one? Yeah. All right, so what we're going to do is group by product category with the following and then following values. And I'm going to correct my spelling here. So what we're going to do in this case is we're going to bring in multiple values. We could also bring in multiple columns. Maybe I'll show that one next. So what we're going to do is we're going to do group by. It's the same principle. In this case we're going to have just one row field. So we're going to come up and select product category again. Next we want our values. And this is where we're going to use a function called choose columns, which tells us what columns we want. So I'm going to type choose calls. You can see it there. Just type in choose and you can select choose calls. And it's going to ask what's the array? So I want to select any, any columns from this entire table. So I'm going to select the entire table. Now I'm going to tell it what columns I want. And I wanted, if you remember, I said I want revenue, then I want price, I want revenue, quantity, price and cost. So I could start from the left and move to the right with positive numbers, or I can start from the right and move to the left with negative numbers. Since I know I want revenue first, it's much easier for me to go negative one than count 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 and put 10. So I'm going to do negative one. I don't want the month, so I'm going to skip that one. So that would be negative two. Cost would be negative three. I want quantity next. So I'm going to go negative five. Then I want price. So I'm going to go negative four. Then I want cost. I'm going to do negative three. And let's just say I want just some. We'll close it. And I did want to do headers and in descending order. So let's add those. So we'll do yes, and show headers and we'll go ahead and include grand totals. And we want it in descending order. So now you can see that I have it here. You can see I can. I'm going to copy this formatting over one so it kind of matches. I got revenue, I got quantity, I got price, I got cost. Now let's do choose calls here as well. So what I can do is I can do the same thing for my fields. I can select multiple fields. So let's come up here to choose calls. Let's come up. We're going to select this entire group again. And this time I'm gonna do product category, product type, and power source. So in this choose calls. I'm going to start from the left side. So I want product category first. So I'm going to go two. And then actually, let's say I want power source first. I'm going to do four. So it's out of order. Then I want product category. Then I want product type. And I get a spill error because this right here is in the way. So I'm just going to delete this and I'm going to get rid of my formatting that I had. We'll just take the. We'll take this off so it's easier to see and we'll get rid of the bold. I could fix the rest, but you can see I have Power source, product category, product type, and I have totals at the bottom. Well, maybe I want a different type of total. So I can come back here and say I want grand totals and subtotals. Subtotals only apply when you have multiple categories. So you can see I got a subtotal for power Source. And you know, you. I can get all the different subtotals as it works its way down. Subtotal for cordless, subtotal for corded. And so that. I'm sorry, Adam, you said no idea. Is that a question? I'm not sure what Adam Anderson was asking, but if you have a question, let me know. But that's group by. I see an lol. So I'm gonna guess that wasn't supposed to come through. All right, so that's the group by function. Now let's look at pivot by. You know the biggest thing I hear with pivot by. And there is. There is some truth to it. Pivot by allows you to build a pivot table using a function. Some people are like, why would I ever want to do that? Historically, Pivot by didn't update when you added new data, but the pivot by function did. So that would have been one reason, but they just changed that. I'm not sure if that's in general release or not, but there is now an option with pivot tables to have it automatically refresh. So there's a setting where it will automatically refresh when you add additional rows to a TABLE but also a pivot by allows you to write lambdas. You don't have to either go through the data model to write complex DAX or try to write complex calculations. In that little tiny window, you have a lot of function options. So pivot by has its place. It can be great for reports. It allows you to format it however you want, which can be a pro and a con, because then you have to go to the all the work of formatting it. But you also have more flexibility. So the pivot by function, some people will be like, why do I ever need it? Others will be like, oh, that's awesome. I like it. You know, it's for you to decide how useful it is. I, I think it definitely has its place. And so let's walk through the pivot by function. And so the pivot by function, the main difference is it has even more arguments, but it's very similar. So you can see this one has 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 arguments. Most arguments I know of kind of in a function, except for those that can have the same. Like a Sumif can have 255 different criteria. Okay, so technically it can have two, you know, those, all those arguments, but unique arguments. So the pivot by the first thing it has instead of fields is you can pivot by both rows and columns. So you have to tell it the row field, the column field values, that's the same as before function, same as before and same options, filled headers, same as before. But then instead of just total depth, you have row total depth, you have column total depth, you have row sort order, you have column sort order. Then you have the filter array where you can filter out certain things very similar to your pivot table. Right? You can, you can filter stuff, same idea. And then you have a relative to. So this is typically used with percent of. Hey, do you want it to be relative to the column totals? The row totals the grand totals. You have different options if you've ever used a percent of in a pivot table, Same idea, you have to tell it. Do you want the percentage of the row of the column of the grand total? So it this only matters the relative to if the function requires two arguments. All right, so let's walk through a couple examples. We're not going to do as many with pivot by partly because it's very similar to group by. There's a lot of similarities between the two. All right, so let's go ahead and we're going to create a pivot, a pivot table with pro or pivot by function, I should say, but we're creating a pivot table with product categories and rows, product types and columns. And we're going to sum the data. And since it didn't tell us what it wants, we're going to assume revenue here. So if everybody copy follows along with me, we're going to do pivot by. And this time we have to tell it the row. So I want the product category for my row. So I'm going to come up here and I'm gonna. In this case, I'm going to select everything. Actually, I'm not. I want you to. We're going to do this a little different this time. We're going to not select the header. So you can see something here. So when we select this, go ahead and do product category. So there's my row for my column fields, we're going to select the column product type. Now we're going to select our values. In this case, I want revenue and to stay consistent, we're not including that header. Now we have our function sum. Those are only required arguments. You'll notice those are the. The four that do not have brackets around them to signify they're optional. So we'll close it. And I got a spill error. What did I do wrong? Thought I said, did I do this backwards? Row field should be product category, then row type. We're gonna. We're going to leave that there and just see what I screwed up by moving this over a little bit. So I'm just going to copy this, move it over. All right. Wants me to highlight just a table. We'll highlight it and we'll just drag it. So let's just drag it over. So it looks. Oh, I selected product type. And I think I wanted power source here because. Yeah, so that works for product type, but let's change it to power source so it's a little smaller, it's easier to see. So I'm going to delete that and I'm going to come in and I'm going to select power source. There we go. So now I can see floor care, hand tools, outdoor products, Power Tools, corded cordless, not applicable because it's a hand tool and the totals foreign. So notice here if I. I want to show a couple other things. If we go to these optional arguments, I can say no, but generate and it will try again. I'm going to need to move this down one. So I'm just gonna insert a roll here. And what I want you to see Insert one more, make sure we have plenty of room. There we go. Notice it tried to create its own column field called column field one. Not very helpful. You can see value one. If you say no, I don't have them but generate them. It creates those default names. So I just wanted you to see that. Not very helpful. It's why I always recommend having headers to your data if you can. It makes it much easier to work with. All right, so here we're going to create a pivot table with product categories and rows, product type and columns and do a percent of the grand total. So I'm going to just copy from above because I'm a little lazy this time. Now what we want here is we're going to go and we're going to go into sum and we're going to change it to percent of. And let's go ahead and show the headers. So we'll say yes and show we're gonna, we're gonna say grand total here. We'll ignore the sort order, we'll say grand total here and then we're gonna ignore the order. We're gonna know our filter array and now we need to do relative to, so we want the percentile to be relative to the grand total. And again we're going to get a spill error because I added a little bit. So we'll just add, we'll insert a couple rows here and you can see it. I, I, I would need to clean it up. So if I took these out then it will, I'll take out my totals and let's take out my grand totals and then it will show here you could see these percentages. So I can just clean this up wherever I don't like it. Let's fix this so it's percentages. We'll fix the bottom down here so it's percentages as well. And what we've done here and you'll notice there's this zero. The reason there's a zero is when we inserted those rows we accidentally inserted some rows into our table. And so it's trying to bring that in. So I'm going to delete that out and you'll see that goes away now. So now we have corded, cordless, na and total and we have the percentage of each individual item. So floor care for revenue was 8% basically. Outdoor products was 75% of our revenue. And that's because we have riding lawnmowers going to be much more expensive. There's like a twenty thousand dollar lawnmower and so you know, if we look at this by quantity, it would look very different. So if I wanted quantity I could just come here, delete revenue, select quantity and notice how much it changes now. Now outdoor products are 23% instead of 75% and power tools are now the biggest we can see. Power tools in total are 38. So that's example number two of a the pivot tables. There's two more here. You'll get the master file with these answers. So I will let you guys go through and practice these last two. I want to take the last I want to take about five to 10 minutes show copilot and then I want to briefly show just wrap up basically. So I'm curious how many have seen any videos or heard about or familiar with this new co pilot function? Let me know in the chat, I'd love to know. So if you're familiar with it, please let me know or if you've even tried it. Today is the first time I've tried it. I've watched several videos. I haven't had time to go deep so I'm going to show some pretty basic examples. But what we're seeing is more and more tools trying to not not trying. Bringing AI into the Spreadsheet the workflow of the future is a combination of talking, writing, natural language functions, sometimes writing functions, often having them write it for us is the workflow of the future right in our spreadsheet, in Excel or Google Sheets or if you use some other AI spreadsheet. I talked a little bit about that two weeks ago as I shared several of the different tools that are out there. So what Microsoft did is they released a copilot function. So how this function works is you can in natural language type your prompt, then select the data you want the prompt to act on. You don't even have to have data in your spreadsheet for the prompt to act. Couple things I'm going to warn you about for this function should never be used for anything that's legal regulatory compliance because you're pulling AI, you don't want that in your function. The data only is updated currently through like middle of 2024. It's only available in beta and you have to have a subscription to the co pilot in Excel. So not just the general free copilot, you have to have the the paid subscription for this function to work. But there are some really cool things you can do. And my newsletter tomorrow, if you haven't subscribed to it, you know, please do and you'll see That I talk several examples, share several videos that others have done on co pilot. So let's look at two examples. So the first one here, and I'm gonna, I'm gonna show you what I did is you'll notice I have date, transaction and amount. And so I have airline travel, Facebook ad spend, Google Ad spend, company snacks. And what I asked it to do, what I asked co pilot to do is we're going to do it here is I can say equals co pilot. And the prompt part, please categorize the data into grouping. Please categorize the transactions. We'll just say like that and see how it does. And then I give it the list of transactions, which is this right here. It takes a minute, it thinks. And there you can see. Oh, I missed one. I should have went down one more to 37 and I think I had written the function wrong last time, but here we go, you can see. Okay. Airline travel gets grouped to travel. All this gets grouped to advertising selected office supplies. Here said, hey, this is a software subscription company dinner. Book that to travel book. Book this to utilities. It is 336. So let me fix that. You can see, it rethinks. I don't know why it's. That's weird, huh? I'm not sure. So you can see there's some quirks. It's in beta. If I type 36 it doesn't pick up the last one, but If I type 37 it does. So I think we found a little bit of a quirk, but pretty cool, right? I can use copilot and just have it categorize my data. You're not supposed to use it to calculate numerical values. They all always recommend you use the function. And let me show another example here of something similar. So over here I have a drop down. It says we will use Copilot to return a list of the largest cities in a country by land mass. So I said provide a list of the 10 largest cities by land mass and the approximate size of the city in the descending order. It didn't quite get the descending order the way I wanted, but it did get the rest of it right. Again. What you're going to see here is it's going to refresh every time we calculate. So it's a volatile function, but it's given me the top 10 by land mass. I could also change this and say provide the top 10 cities by population for the country referenced and I could probably even get it to work without that. So part of it is prompt engineering. The Better you write your prompt there. You could see Sao Paulo, biggest one. Well, what if I come in here and select Canada? Thanks. For a minute. And I forgot to ask it and and provide the population. So you notice I put them in order by population, but I didn't tell it to give me the population. And so let's try it again. Now I have it by population, but notice the XLFN means you don't have that formula available. It's currently only in beta. If you're not part of the beta Channel. Channel William, it's not going to work. So this is really a demonstration. You have to join the insider program which you can do under account. If you Google you have that option, you can come in and change which one you're set up to within your account. So in order for this one to work, you have to do that and you have to have a paid subscription to Copilot. This is really more just to show you what's coming and how quickly the way we work in Excel is changing. So I personally, I'm really excited for this, but I caution people because, you know, if you're trying to tie out every penny, you want things to be very specific. You don't want to be using Copilot because it's generative AI. The answers could change. You know, it's not great at math. All the things we know that apply to our LLMs. It's just making the LLM available straight in a function versus having to come here, go to Copilot and trying to chat to get it, or going out to an LLM, typing what you want and bringing it back in. It's now bringing it in as a function. So that's Copilot in a nutshell. In a nutshell, there are some great videos, some really good use cases if you want to see them. I probably recommend. My favorite is John Ankampora right now. I really like his. There are several others. They show a number of other use cases. All right, so we're going to go ahead and wrap up. I'm going to go ahead and close this and bring back up PowerPoint. Give it just one second here. Sorry for the wait. All right, let's get PowerPoint up. All right, so as mentioned, we went through Group by, Pivot by and we showed a little bit of the new co pilot function. My takeaway for Group by and Pivot by is there they are. You know, there are other ways you can do most of what they do, you know, the pivot table, other functions, but they make it easier. I think they're well worth learning. I'm not sure Muhammad, what your question is, what the name is again. The function name is co pilot if that's what you're asking. I'm not exactly sure if you want to add more detail. All right, so last I I'm going to go ahead and pitch and if you don't want to hear it, you're welcome to drop off at this point. I will open it up to questions after but I have a Modern Excel course. Over time I definitely want to add to it But I have three modules right now, Excel tables. The reality is the LLMs work best when your data is stored in tables is what I've found in general. So I think it's a really good practice. I call it the gateway to Modern Excel. You need to know it for Power Query. Module two is Power Query. You get a couple hours. Session three or module three is Dynamic Arrays and it's just an intro. This is used in the Wharton FPA program. Oh the co pilot video. John Ankompora is the name. So let me just throw it in the comments and if we have time after I go through this I'll show you a couple videos that I recommend. So that's the name. Thank you Muhammad. So you can see here a testimonial to Excel. The course is 59.99. No, normally you're getting five hours of content. If you use webinar you get 30% off. Next there's the ultimate course bundle that includes for business partnering which has three different modules. Financial modeling, Design, Data analysis, Modern Excel, about 11 hours worth of videos. So you're paying a little over $10 an hour. You'll get $30 off using the code webinar we get. We've got really good feedback on people who've taken the different courses. So you can get that if you want. And you know, if you're interested you could also sign up for my community. It's absolutely free. Going forward we may there's a good chance we'll move some of the training into that community and I'm going to share where you can sign up for that and then I'll show a couple of those videos. So if you're not already part of that community, like I said, it is free. We do lots of webinars and stuff. You can just go to www.the FPA hub.com we have 1700 plus members in there. We have conversations daily going on. We have events regularly. So please come join the FPA Hub we'd love to have you. And then last, for anyone who's interested, I'll recommend a couple of YouTube videos for Copilot. So if you come in here, you type Excel co pilot function. So I scroll down. I like this one right here is a really short one. I like Bill Jelen. He gives a very unfiltered view of it. I like Minda Tracy. I think hers is really good. And then if you also type in John Ankora co pilot, right here, he has 17 ways to use Excel's new co pilot function. I think he gives a lot of good examples. They start from very simple to quite complex where you're using Filter and a bunch of other functions with Copilot. So. So those are a couple of those for anyone who registered via the webinar and you still can. If you signed up in the webinar, you'll get an email with the master file and the PowerPoint and so that that will be sent out there. You can always download the student file and watch this again on YouTube, on Streamyard, on LinkedIn. So I'm going to stop there. I see a few comments. I'll address those and we'll just kind of go from there. So, William, I will email out the master file to everybody who signed up through Streamyard for the event, though I have the emails there. So, yes, I see you came through Stream Yard. You'll get that master file. It may not go out today, but it will go out by the end of the week. So you'll see that you're welcome for the training. Glad you enjoyed it, Kimberly. William, I'll. I'll hang on for another minute or two here to see if there's any questions from anybody. All right, I'm just going to give it one or two more minutes. If anyone has questions, feel free to put them in the chat. If I don't see anything, we'll go ahead and call it a. Call it a meeting. You're welcome, Tammy. We'll hang on for one more minute, see if there's any questions. All right, I'm not seeing any questions. I'll give it another 30 seconds and we'll call it a. Call it a session if I don't get any questions. Alrighty. Don't see any more questions. So I'm going to hang up here, but thank you, everybody. You're always welcome to email me if you have questions and keep building awesome Excel files. Thanks, everybody. Bye.