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

Hello, anyone out there? Let me know if you can hear me. We'll get started in another 2-3 minutes. But if you're out there, let me know where you're coming from. I just dropped in the notes the link. You'll find it in the chat to download the files today. I'm gonna go ahead and open up our deck, but just let me know where you're coming from, what your name is, and we'll start here in just a few minutes. Looks like I see about— this looks like there's at least 10 of you out there, so go ahead and let me know in the chat where you're coming from. I'd love to know where Where are you at today? Give me just a minute here. I'm just getting all the files pulled up for today. All right. All right, love. I'm not seeing anything from anyone. Let me know, put— go ahead and put in the chat where you're coming from so I know you can hear me. I'd appreciate it, and we'll start here in just 1 minute. Great. We got Nicholas from the bright and sunny UK this evening. Love it. Megan from Salt Lake City. Love it. Anyone who knows me knows I'm— who knows me well knows I'm from Utah. So love it, Megan. We got David from Tampa Bay. So Gabe Galveston, great. They're starting to come in. Sometimes it takes a minute, you get a little bit of a lag between LinkedIn and, and here. So appreciate you letting me know. Please keep letting me know where you're coming from. I always love to see the different locations. And we'll go ahead and get started. We're going to cover two things today. We're going to cover dynamic arrays and Copilot. So we'll spend the first half on Copilot, we'll spend the second half on dynamic arrays, but I'm gonna have a little fun. If anyone can guess what logo this shirt is— a vendor sent me this shirt— I will give you, uh, free access to my digital course. So you're welcome to try to guess, and at the end I'll tell who it is. So a little bit of fun as we go through things. All right, so it looks like we have Bolton, England, UK. 3 people from the, the UK. We got some night owls tonight. I mean, if I remember right, let's see, it's 10:30 there, I think. Let me know if I'm wrong, but I believe you're 7 hours ahead of Salt Lake. All right, so here's— you can download the Excel file here. You'll also see it here. There's also a link to it in the comments if you haven't downloaded. Great, we got Allentown, Pennsylvania, Cabrillo. So keep letting me know where you're coming from in, in the chat. So here's what we're going to cover today. We're going to cover two things. We're going to talk a little bit about what's new in Copilot. We're going to talk about how they move the button location a little bit around personalization and plan mode, and I'll show an example or 2 and share some of my, my thoughts on it and the changes we're seeing. I think it continues to get better. I still think it has work to do, but I'll be sharing a little bit there, and then we're going to run through what I'll call some shaping formulas and dynamic arrays, things like take, drop, HStack, VStack. We'll see how many we can get through in the time, but the workbook has, I think, 10. And they're really pairs, so there's kind of 5 groupings. Many of them operate very similar to each other. It's just one works for rows, one works for columns. Or one works vertically, one works horizontally. All right, so I'm gonna go ahead and jump into the file now. We'll keep this part to a minimum. And you should have seen— received a file called Workbook. So before I show some examples I built, where I want to start is— many of you may have noticed this, so I want to show— make this a little smaller. I want to show a few things first. And you can barely see it, it's very small, but down in the corner they moved where the Copilot button is. I don't know if anyone's noticed that Copilot used to sit on the Home menu. Now it sits down there. Kind of reminds me of Clippy in the way they— in the way they moved it. I don't know if anyone else feels that way, but that's kind of what it feels like to me. So what I want to show is if you right-click on it, it's going to give you a couple options. You can say show welcome, or you can dock it. So if you dock it, it comes over to the side there, and it's just— it stays in space. You can also, depending on your setup— this one doesn't allow me, my other computer does, and I have beta on that computer. So soon they're gonna allow you to return it back to the home ribbon. So that will be really nice. I prefer it up here on the home ribbon versus down here. Kind of gets in the way of other stuff. And so I, I don't know about you, but I'm not a big fan of the floating Copilot. So that's the first thing that's relatively new, really simple thing there. Next, we're gonna walk through the menu here, and there's two what I would call much bigger changes they've made. And the first— and I'm just going to ask before I go over them— has anyone had the opportunity to play with plan mode or personalization yet? Would love to know if anyone's kind of had an opportunity to play with that. If you had, let me know in the chat and let me know your thoughts. I'd love to hear them if you've had the opportunity to play with either. And I love your comment there, Gail. Oh, Clippy, yikes. Exactly. So far I'm seeing a lot of nos, that's good. So let me— this, this is the current setup for Copilot if you have the most recent version. So I'm going to walk through just kind of all the buttons briefly, and then we'll focus on the two so you can kind of see the latest layout. So you're playing with Claude? Yeah, that's a lot of people. So here you're going to see the model. You have GPT-5.4, 5.5, Claude Opus 4.7 and 4.8. So you can select whatever model you want. If I put this in chat-only mode, I can then come up here and bring up my regular 365 Copilot where I can use Researcher or Analyst to ask questions. I can also get all my connections. So you have to be in chat, not edit mode to do that. So you have that there. Okay. You have in chat mode, you have the ability for web or for work. I'm gonna go back down here and go into allow editing. And I am noticing something here, so I'm gonna talk to it. I'll explain here in a minute. The next thing— let me bring it back up. And I did not— okay, let me see if I can get Yeah, dang it. All right, so unfortunately what I'm seeing is I don't have beta on this computer, and a couple of the, uh, changes have not yet rolled out to this computer here. I think they're going through stages, and one of my computers gets them a little earlier. I thought we'd have both, so I'm going to talk more to them and I'll share some examples. So the two new features, in addition to allow editing— and I'll show you the results of it there's a new feature called Plan. And so what you do is you click on Plan and it works you through and agrees on a plan before it does any editing in Excel. And I have a file where I saved all the, um, back and forth with Copilot for a plan. So that's, that's the first one. The second one is there's a button— there's a new button, it's three dots. You click on it and it has a thing called Personalization. Let me show you what that looks like here. So I'm just gonna real quick pull up just a brief video so you can see what it is. It's about a minute long. So let me go to LinkedIn here. Bear with me for one minute. I did— I should have checked this ahead of time, so I apologize. I thought it was general release, and so I expected it to be on my computer, so I'm not sure what happened, but I'll show you a quick example of the personalization. So I'm going to come here. There's a really good post that covers it. And that's part of the challenge with this stuff happening so fast sometimes. There's the announcement that they had added Claude Fable. And then a day later it had gone away, as you know, I think everybody's seen. So what I want to show you is right here. Give me one minute. So I'm gonna make this full screen. We're gonna watch this video, then I'll talk to it. If you can't hear it, let me know, but you should be able to hear it. So I just saw the no sound, so let me see if I can figure that out because it says on my end sound's coming through. So thank you for letting me know. I didn't see that for a minute. Let me try one more time here, and if not, I will speak to it. Also share the tab audio. Yes. Hmm, let me try one other thing. Okay, I'm gonna try one more time. If you and let me know if it's coming through for you. If not, I'll speak to it. So let me kind of talk to what we're seeing since it sounds like— I'll see a list of all these things. So give me one minute here. What I'm going to do is just talk a little bit to it. So basically what they've created here— and come back— is, as I mentioned, there's three dots and there's the thing called personalization. And what's going to happen is you're going to be able to customize Copilot. So you can add details how you'd like Copilot to tailor its responses and work. So I tested it for a couple hours this morning as an example. And what I found— sorry about the sound there, that I appreciate letting me know. Unfortunately, there's a little bit of a lag, so I didn't see it till I've been running it for 30 seconds. So what I was going to say is with customized Copilot, What he's doing here is he hates that your, your tables don't automatically get named. So he put in, hey, all tables you create should contain short descriptive names and be prefixed with table. And then when he asks Copilot to create a table, it will always do that. So what's nice with these instructions is they're not workbook specific. They follow across your Excel account. So they're in Copilot. I tried it, I probably put 10 or 12 in. Some of the things I did was this one, table names. Another one I did, which I think many people will appreciate, is I said, use center across selection, don't use merge and center whenever you need to center something. I asked it to use color coding when it was building models. I asked it how to format currency, even some grid things. What I found, depending on the task, Some of them it did right every time. Some were a little hit and miss, and I'll show an example in a minute. So that's the first new thing that's coming. I thought that was rolled out in generally, but it looks like it may not have hit everybody's release yet. You know, with Microsoft, there's often a lot of different releases. I have it on my other computer. Unfortunately, I don't have it on this one, and I don't— I don't want to switch computers. That would take up a bunch of time. Naming them to prefix the So that's the first thing. And the second one I wanted to mention is historically we've had this edit mode and chat mode. They've added a plan mode and you get some really good results. So I want to show— I built this with plan. So I'm going to walk you through the prompt and then I'll show you what I built. I said first I asked it to put the Rob billing data into a table. It did that and it created a name. The name it gave it was Table Billing Data. So it did that automatically for me. The next thing is I said, and this had all my personalization in there, I need your help to plan out the building of a deferred revenue waterfall schedule model. Please analyze the data and help me build a plan for creating the schedule and accompanying key report metrics. I also want the plan to include documenting the model, and I misspelled it there, Pretty good at picking those up. Documenting the model you build. This report will be updated on a monthly basis, so make sure you think about that in your plan. And then it asked me 4 questions. Do you— you can see it down here. I will inspect the table structure, row counts, you know, blah blah blah, before I finalize the plan. How do you want the revenue recognition? Straight line, monthly, you know, do you— how do you want the waterfall horizon? Through last contract end, rolling 24 months, 36 months. What metrics do you want? How do you want to update it? Do you want to use append new data, replace raw data? What's interesting is one time it gave a Power Query option, one time it did not. Again, gets back to the probabilistic nature. I answer all those here, and then it gave its reasoning and went through and worked through things. This file here where they work through, you don't have access to, but I'm happy to send it out if people want it. But what I want to show— so here's the raw billing data. Everything to column K was the original. It added these new tables, but you'll notice I had put in my, um, instructions, I said use green links if you link to another sheet. It's linking in the same sheet, it's just linking to a table. And it put Greenlink. So example where it didn't exactly follow the directions, but I've gone through this and so far the formulas pretty much all seem to make sense. I didn't go through every single one, but here's what it built. And if anyone's seen the deferred revenue schedules I've done before, usually I could get some really— if I gave some really good detailed prompts, I'd get something. By using plan mode, I got better results than I was getting from detailed prompts. Another thing I had in my customization was, you know, don't hard-code numbers. So I kind of put in a lot of those assumptions right into the customization. And so first it built me a control sheet. It says, here's where you put the month end, tells me the purpose, forecast start month, forecast end month, and it got all these widths pretty much correct, which has usually been a big problem. This one's not quite there, but it did much better. Often I've found with Copilot and others you get really small column widths, so I again added that to my customization to say always autofit the columns. Didn't get it 100%, but it was much better. So put in a control sheet, it did a high-level waterfall schedule off the, off the other sheet here, so I can see You know, all my revenue, my billings, my ARR, my MRR. As far as I could tell, the formulas were all correct. Again, I didn't test every single one, but it passed that initial kind of sniff test. Then it gave me some reporting. Some was helpful, some not so much. Uh, you know, top 10, or, you know, product summary. I only have 3 products and maybe, you know, so there are some areas didn't quite widen all the columns, but I did ask it to use center across selection and it did. And it gave me a documentation sheet. So what I find with plan mode, what I like about it is it will help think of a lot of things you may not thought of. I find it does give better results, not perfect. You still need to check everything. Yeah. But combine that with personalization, and then in the future when they add skills, which I know are coming similar to Claude, you'll continue to get better results. On the whole, I still think Claude's the better Excel agent, but those are some of the things we're seeing that are causing some real improvements in, uh, in Copilot. I'll also share one other example here that I have in the workbook file, but I'll pause. Again, I apologize for not being able to actually show what I had built, but I'm going to pause. Any questions on the new things that are coming? The plan mode, the personalization, they both should help. The other thing they're doing now, which it doesn't do on this computer, is also with, with a plan mode and WorkIQ. If anyone's heard, they just launched WorkIQ. What it does is it scans your network for any documents it thinks is relevant to what you're doing. So for example, if I had a document in a folder that said modeling standards or a document that said deferred revenue schedule, or, you know, whatever, it's going to bring up all those documents and you get to click on or off if you want that, if you want it to consume that information before it builds your model. So it's getting smart enough now to scan your, your Microsoft network inside the network and say, hey, I'll look at all this. If you have it scanning outside the network, it's, it's, uh, consumption-based outside of Microsoft. They're going to start charging for it with WorkIQ. But that's the new thing they launched. Again, I don't think it's here because I don't have plan mode, unfortunately. I thought it would be on both, both computers. And I set all this up on my laptop this morning. So those, those are the things that are coming. And I do think you'll see a market improvement with those. As I've mentioned, you're going to have to keep in mind that's only for paying customers in Copilot. So I'll keep my eyes open to see if there's any questions on that. We're going to switch back to the other workbook, but before we do that, we'll tell an Excel joke. Or actually, we'll tell a Microsoft joke. I might get myself in trouble with this one, but that's okay. Does anyone know the last prod— the first product Microsoft made that when they release it, it doesn't suck? So you, you can, you can have it, you can have it do like— so let me, before I finish that joke, I'm going to come up to this one comment here and I'll leave the joke hanging. So if anyone knows the answer, they can give it. If not, I'll tell it at the end. So the joke was, what's the first product that Microsoft will make that doesn't suck when they first release it? So I don't understand the advantage of the plan mode. Can't you just tell it to walk you through the plan first before building? Wouldn't it be the same? So I think the plan mode does have some logic in it. Yes, you could ask it to do that, but they've designed it kind of like other agents. You generally get better results. Too many people don't do that, so it's not a huge lift. It's not like you couldn't do these things before. But having a deliberate plan mode versus an edit forces you to agree every time. So what it does is says, hey, should I proceed? Do you agree with this plan? Do you want to edit it? Instead of you have to type all that saying, hey, let's plan all this, don't start until I tell you. So it just, it's, it's not a huge lift, but it's one more thing to have a little more control and to get better results. For example, I, you know, like you may have a detailed prompt to do something and so you just run it. But if you do, hey, let me give it a basic prompt and open up plan mode and really go back and forth, you're going to end up with better results. Could you do that in a chat? You could, but the difference is chat is designed not to do edit. You could do it in an edit, but I have seen times where it starts working, so it's just a little better control. It's not a huge lift by any means. And JP Perry ruined my joke. Thanks, JP. Now, so the first product is a vacuum that Microsoft will release on day one that doesn't suck. All right, don't tell Microsoft I said that, I'll get myself in trouble. No, um, all right. So with the customization I was gonna do an example with the graph. But things I did in the personalization to give some ideas of things you could do is, I said, you know, remove grid lines, use monochrome colors, always do data labels, exclude the x-axis. And what I found is it was hit and miss. So the customization isn't perfect. It doesn't mean it's always going to follow it, but it definitely helps. You definitely get better results with it than without it. Like if I go back to that file, I had some other customizations I had to show examples is I always said start in B2. So I always leave, you know, row 1 blank. Always, I had it create a narrow on every one of the worksheets. I had to automatically get rid of grid lines. I don't like grid lines. So I'm going to bring this one on customization. You can tell it to remember you're working— not sure about the specific instructions. Well, yes, custom— that's customization, I think, for Copilot in general, to tell it your working style. This is personalization within the Excel agent, which is a little different. These are strictly instructions for Excel. Not your overall Copilot. Yeah, exactly. A little more, like I said, sounds like personalization with a good development, but more direct control. Yes, exactly. That's a good way to look at it. But yes, you can do some things with working style across, you know, Copilot as a whole. So thank you. Keep, you know, kind of the comments coming there. I'll get to them when I can. So that's what I wanted to share on, on Copilot. And if there's any questions, let them come. And then I want to spend some time going through some dynamic array formulas. So I'm going to go ahead and close this. We're going to come back to the workbook. And before I start going through these, I would love to know— the first functions we're going to cover is called DROP and TAKE. Has anyone ever used any of those functions? Are you familiar with DROP or TAKE? I'd love to know. So these functions we're going to go through today, I haven't really done this in a, in a session before. And so I thought it'd be kind of fun is I've talked about some of these functions, but these are really what I call the shaping functions for dynamic arrays. Anyone who's followed me knows dynamic arrays are basically spilled arrays. And so spilled arrays just means they don't live in one cell. They can live in multiple cells. So when you have something that lives in multiple cells, well, maybe you don't want all the data. Maybe you want to get rid of a row or a column. You can do those with DROP and TAKE. Basically. So you can see the syntax here. You put the array— that's your data, your range. You tell it the number of rows you want to drop. You tell it the number of columns to drop. So a good example is if you had a table, you don't want the total, you don't want the header. We'll see an example of that in a minute. You could also use DROP and TAKE to build automated, like, year-to-date, last 12 months, last quarter summaries, averages, sums, different things. So let's walk through how the function works. I kind of think of them in some ways as a pair. But what we're gonna do is we're gonna do a few examples. So I'm gonna have you delete this section here, and we're gonna do it together. So what we're gonna do is we're gonna type take here. And it is an example, and you could see it automatically tried to finish it for me. So I'm gonna do one thing here. I'm gonna go to File, Options, come down to— where is it? Copilot. And I'm gonna hide the formula completion suggestions so it doesn't try to do every formula for me as I show you how they work. So if we say equals TAKE, we just want to take the first 2 rows. So we take our array and we put in the number 2. We put nothing for column, so we close it, and it returns Jim, Sam, Jack, Sally, Susie, Paul. It returns just the first 2 rows. That's all take is. Now let's look at a second example of take. What if I wanted the bottom 2 rows? I wanted the negative number. I can do the same thing here. I can do take from the, from the bottom. So what we're gonna do in this case, it's just gonna be take this and we're going to say -2. And now I get the bottom 2 rows. So start thinking about this. If you're working with a table and you got 2 tables you want to combine, you can take the entire stable and you can drop the header on the second one, or you can drop the— you could drop the, uh, the footer, the total row, and combine them together. And so that's one example. You could also take, you know, the last 3 of something, or the first 3. So here's a few more examples, same idea. In this case, what would I type if I wanted to do it by column? Well, let's just do a quick example here. You can do it with me if you just delete it. It's just equal TAKE. And in this case, if we want column, the difference is we skip the row. And then we put in the column and we get the first 2 columns. You know, we can do row and column. In this case, they said take the first 2 rows, take the first 2 columns, so we get just Jim, Sam, Sally, and Susie. So that's basically high level for TAKE. I'm going to show an FP&A example in a minute. But DROP is the exact same thing. So I'm just going to walk through a couple examples. You can follow along if you want. But if I was to type equals DROP here, highlight all the data, and if I put in a 2, it drops the first 2 rows. So I just get Roger, Julie, Bill, Norman, Sean. If I put headers here, so maybe, you know, uh, teacher name, or, you know, first, last name, whatever I could drop. If I dropped just that row, Then it doesn't include my header. If I had a reason to do that, I'm going to go ahead and undo that. We'll go back to the name. -2 gives me the first 3 rows because it drops the last 2. So same kind of idea. Now I want to show an FP&A example. So let's just say I have, you know, the last 12 months. Of some sales data, whatever it might be, an account line, you know, some kind of— some number. I can put in the average, the number of months I wanted to do. In this case, I did -6, which would be give me the last 6 months. If I did -3, I'm going to get the last 3 months. So we'll go back to our 6 here. And what you'll notice is I did an average take. And so we'll write this formula together. And what it's doing is, is give me an average of the last 6 months. So what I did here is I just said equals average, and then I write a take. Here's my data set, which this is a dynamic data set. So it's going to give that spilled ARRAY. I only have one row, so there's nothing to do there. For columns, I'm going to set it equal to this and say yes. And you can see the number it gives me. If I take away the AVERAGE— so I'm going to move this over here so we don't get a spilled error— if I take AVERAGE out of this formula, you You can see this is exactly the last 6 months of data. You can see the '58 to '51. So I did a take and said -6 to give me the last 6 months. Now I could do the exact same thing. I just put a negative in front of it for the drop. That way it drops the first 6 months. It's the equivalent of a positive since I don't wanna create a separate one to select. I just did that. Probably would have made sense to make this a positive and flip the signs, but you get the idea. You could do, do it either way. You can do it with a drop, you can do it with a take. So all we did with the drop here is we said equals drop, and then I did negative C91, so it would drop the first 6 and leave me with just the last 6, where in the take I said just take the last 6. So I got the exact same numbers here, which I can put an average or a sum or whatever math I want around it. So that's one example of how drop and take would work. So now what I'm going to do is I'm— there's just one simple example. I want you to see if you can solve this exercise by yourself. So I'm going to give you 1 minute to try solving it. Then we'll look at the answer, and then we'll move on to HSTACK and VSTACK. So I'm going to put you on the clock for just a rough minute here to give that a try. Feel free to ask any questions if somebody wants, but go ahead and just practice writing that exercise. I give you about another 30, 45 seconds. Feel free to put any questions in the chat. All right, give you about another 20 seconds here or so to Try that one and then we'll look at the answer. So there's a few there. You can try all of them, but you'll also see the answers. So you could see, use drop or take to extract the letters. You could do the same thing just a different formula. This one we did -1 take, so we got just this row. Here we just dropped the first 2 rows. And in every single one of these, you're going to see you can answer it the same way. So drop and take are just different ways to approach it. Now let's look at a— let's look at HSTACK and VSTACK, and then we're going to show an example at the end of our workbook combining VStack and where you could use a drop or you could use a take if you wanted with it. Primarily a drop is what we're gonna do. So we'd love to know, has anyone ever used VStack or HStack? I find VStack and HStack can be incredibly helpful if you start writing let formulas where you declare variables. It can allow you to combine things together a lot of people— like, if you've ever— if any of you have used GroupBy or PivotBy, a lot of what people now use GroupBy or PivotBy with, they were doing with VStack and let and some of those things. So let me show you the basic idea. And at first you're kind of like, would I ever use this? So I will walk through a real-world example. So what they are is they're basically stackers, so you can stack data. So first I'm going to show you HStack here. So I have these 3 columns with names. Think, you know, think of how that could apply to FP&A. You could have, you know, 3 different months of data or different accounts or whatever. What I can do is I can stack each of these arrays together, and with the horizontal stack It just put it in one formula. So you know, there's no longer a space between them. That's all one dataset I could now work on, right? I could now apply a drop to this, and if I wanted to, you know, get rid of the bottom row or do whatever I want, that's the idea. So VStack Very similar. The only difference is it's much like the old VLOOKUP and HLOOKUP. VSTACK just stacks it vertically, just like VLOOKUP looked up things vertically, HLOOKUP looked up things horizontally. So now what happens if they're different size? How would you deal with that? So here's the next example. We've taken these 3 together. Notice what it does by default. What HStack does, and we'll, we'll just write it here. Equals— encourage you to write it as well. It's always good to get that practice. That's why I do that. Is the best way to learn formulas is practicing them. So right, I do this. And it says, no, I gotta create an array. So if your first one's 5, we're gonna make every one of them 5, and I'm just gonna put N/A. And so you get this N/A there. Well, what if I don't want that? You would have to wrap it in something like an IFERROR to get rid of it. So in this case, we've wrapped it here. And now you can see you no longer see the NA. All right, so same thing with VStack. Although these— not the same thing. What I meant is VStack doesn't suffer from the same issue. They don't have to be the same size because it's just stacking them on top of each other, so you don't need the same column length. So with VStack, it automatically is smart enough to say, okay, there's 5 here, there's 3 here, there's 2 here, I'm just going to stack them vertically. Where HStack says, oh, first one's 5, therefore I need 5 in every column no matter what. So it's kind of something to be aware of. Now where VStack does have the issue And let's look at this example is, what if I tried to stack VStack with 2 columns and then 1 column? In that case, you can see what it did. It stacked this one here with this and this, and in the second one it did the NA. So in that case, you could use the IFERROR. So this one struggles horizontally. It needs the same number of rows. This one here needs the same number of columns, or you'll get the NA. All right, so those are the examples of basically how HSTACK and VSTACK works. I'm going to give you a minute or two to just kind of try a couple exercises here. You can see the solutions. I'm going to give you a minute or two to work through that, see if you have any questions, and then I'm going to show what I would call, I think, an FP&A example. And we're going to show a couple different ways to do it, one with just VSTACK and one with VSTACK and DROP, so you can see them combined together. So I'll give you just a minute or two to do that. Let's see, I'll come up with a joke, and maybe JP knows the answer to this one. We'll see if he does. Does anyone know why the spreadsheet was constipated? I might get myself in trouble for this one, but that's okay, I'm used to that. Anybody know why the spreadsheet was constipated? Ah, good thing JP hasn't ruined it yet. I'll give him another 10 seconds to see if he does, and feel free to ask any questions. On this one. I'm not ruining this one. So he's been on a few of my webinars. I'm guessing the reason the spreadsheet was constipated was because it couldn't budget. So there's your second— there's your another FP&A joke for you. All right, so I'm not seeing any questions come in. Give you a minute to work on it. You can see the, the solutions below to these exercises. That it asks you to do, just practicing stacking. Now let's look at an FP&A example. The example I says— I have says combine the 12 months of data using VSTACK by itself or with DROP. Now this one will take a while to write manually, so I am going to turn on Copilot here in a minute. But here's what I would do. So I, I'm just going to do the first quarter. I take this first table here And right, you can see, okay, I— it took everything. Now the second one, I don't want the header again, so I have two options. The first one is I could just come in here and highlight everything but— and I could have typed that in— everything but the header. So we'll do that first. And then, you know, I do it for March as well, everything but the header. Now I could do this via Power Query, that'd be my preferred method, but maybe there's a reason I can't use Power Query. February. So I just combined the 3 months together. And what's great about this— now I need to clean up the, the, um, formatting doesn't come with it, so I should fix this column right here to be a date. So let's just do that, Ctrl+1, we'll make it a date. What I really like is what if I added an extra record here? So let's just say we had another day we sold the socks 6-pack, maybe on 1/27/26. We sold 500,000 at $10.68. All right, so that one should stand out. So let's make sure I change the quantity here again. I gotta fix that. We'll make it just a regular number. And when we scroll down, there it is. It got stuck right in place. So this just adjusted and said, okay, we're gonna start the next month one, one line down further. We'll make these a little bigger so we can read them all. And so you could see that. Now let's see how I could use drop to do this. And then I don't want to do all 12 months, because you can imagine writing each one's gonna take a while. So what I'm gonna do is each time I just want the entire table. I'm going to get all 3 real quick, and then I'll do the next part. So I'm going to take that one. I'm going to do that one. The problem if I do that, if I take everything, is the product ID repeats. I only want that once, right? So how do I fix that? Um, Well, I can come up in here and I can wrap this in a drop and say drop 1. I could come here and I could say drop 1. And what did I do wrong? Oh, did I not? Yeah, I got that outside. Let me fix that. Drop one, and then we'll close the whole thing. And now those go away. Let's say I wanted to add revenue to this. I'm going to show an example. This gets a little more complicated than I'd normally get, but I could do this. I could say stack this with an HStack. HStack. And here I'm going to take this times this. Now I'm going to get one error because I didn't have this quantity and price here. Ah, give me a circular reference because of that. I'd have to do it a little different, but I could have wrote a more complex formula there. But you could start adding stuff to it. So you kind of get that idea. When you start stacking things, you can build very customized datasets with these shaping formulas. Now what I want to show you is I'm going to turn back on Copilot for one minute. And I'm just going to start typing this manually. And so equals VStack. Let's do month 1. Let me do and see if it will— of course it's not going to do it. Let's see. Usually it will do all 12 months for me, but of course it doesn't want to do it. It should have generated the rest of the formula. because I didn't want to type all 12 months. But you could see how stack can be really useful, and you can— you could do, you know, VStack with HStack. Like, if I had another data set over here, maybe for whatever reason I had revenue here. And it's this right here times this right here. Right? I could, in theory, stack that. Ah, it's still gonna give me a circular reference. Dang it, I'd have to do it a different way. All right. I thought that one would work, but doesn't like it. But you get the idea. It did bring it in, it did stack it, it just wouldn't solve the math. So if these were hardcoded numbers, it would have brought it in. In fact, if I do that, just so you can see, so we don't have to deal with the circular reference I don't want to turn on, you could, you could stack those together horizontally. So you combine things together as well. All right. So we'll go to the next, next function here. And love— before I jump into it, this is the third set of shaping. So we've done drop and take, we've done horizontal and vertical stack. Now we'll do choose_cols and choose_rows. Has anyone used choose_cols or choose_rows? Let me know in the chat. I'd love to know if anyone's used either of them. I'm a big fan of choose_cols. I almost never use choose_rows. Choose Columns can be very helpful with— as another— instead of sometimes— instead of using an XLOOKUP, if you always need a certain position, it can be really helpful. So we'll see if that comes in. But the whole idea with Choose Columns is you highlight a data set, you tell it what column you want. So here I could type Choose Calls, and you can see Let me turn it off now, so I don't have it writing all my formulas for me again. Okay. Equals choose calls. I highlight the array. And I can tell it what column I want, what columns I want, and in what order I want. So let's say I could do something like this. I could do 3, 2, 1, and let me move these over so they're out of the way so you can just see how it works. It gave me all 3. Or I could say, hey, just give me column, you know, -1, which is going to give me the last one, or -3, or whatever. So that's the idea behind Choose Columns. So you could, you could use that with a VLOOKUP. Maybe you always wanted to match to the last column, or your lookup value is in column 3. So as an example, let's say I need to— if I needed to look up off the name Jack. And I'm just kind of ad-libbing a little bit here versus doing all the formulas, but I want you to see this. So let's say I need to look up, and I'm just gonna type the value Jack. And in my table array, I could do a, so I would do my table. And what I can do with this table is I could do a CHOOSE(CALLS), CHOOSE(CALLS), and flip the order because I know I need column 3 first. I could do 3, 2, 1, and now I'm looking up the first column or whatever. This isn't the best example, but you get the idea. ChooseCols allows you to manipulate the order of the table, pull out whatever you want. Well, we'll see some other examples here. Next is ChooseRows. Exact same idea, but it gives you the row. We can use it to extract multiple rows and columns. So in this example, we said, hey, I want the first 2 columns, and, you know, then I want rows. So it gives me— you got this here. So let's see. I asked it to just give me column 2 and 3. So in this case I get just these 2. Same thing with rows. I can say, give me row 2 and 4. So I'm gonna let you guys practice this one for a minute. It's a pretty, pretty simple formula. When you start combining it with lookups and other things, that's where its power starts to come in. But go ahead and take a minute and try to figure it out. I'm also gonna remind anyone, if anyone's able to guess what this logo is, I'll give you free access to my digital course bundle. It is a software vendor. It's one I've worked with before. I will give a hint. They are primarily a European vendor. So if anyone gets that right, I will, uh, give them free access to my digital course bundle. All right. So I'll give you a minute to try this exercise. And while we're waiting, I'll tell one more joke. And anyone who's attended a session has probably heard this one. But let's see, does anyone know what happens to a bar chart when it works out? Anyone know what happens to a bar chart when it works out? It becomes stacked. Looks like L7. I guess that works still. I like it. I can't say I remember that band. But I was high school in the '90s. So, you know, I'm getting up there in age. Half of you probably on here were not even born until then. All right. So next, I'm going to briefly show to call and to row. I will be— I'll be honest. And the reason I left these till last is I rarely use to call, to row, wrap calls, wrap rows. There are others that do. But I will use take, drop, the stacks to choose calls quite a bit. Those I think have a lot more use cases. Not that these can't be used, but let's see what they do. So let's look at the array here. What you're going to see is you put in your data, you have the settings to ignore blanks and errors, and then you tell it whether you want to scan by a column or not. So that looks a little weird, but that's the order And the function transforms it of array of data into a single column is what the column does. The row function transforms an array of data into a single row. And so what I mean by array— where vstack, if you remember, vstack was stacking separate arrays. So what that means is separate ranges of data, right? You're stacking this one, and then you're stacking this one. With, with VStack. What you're doing here is you're converting the entire range from columns to rows or from rows to columns. So all you have to do here is it's basically one argument. It's just toCall, and that's it. Unless you want to eliminate blanks or errors, which we'll show an example. That's all. Next, if you scroll down to Row, same idea except for it puts everything in a row. Teddy likes this one. Great, appreciate it, Teddy. All right, so now What they can't do is they can't deal with non-contiguous data. Remember how I said they all need to be in one array? Well, if I wanted to create a column of multiple— yes, I could have used a VStack here. That's how I would do it. But I could have also done equals to call and then just stack them together horizontally. So again, it kind of gets to that idea that once you start to understand how these formulas work, there's a lot of different ways you can solve things. Again, would I do it this way? No, I would have just done a VSTACK for that, which you see right here. Here's the VSTACK method. All right. So now let me show the, the other parameters that you have so you can see how those work. So right here at the end of to_call, you have 4 options. You can keep all values. So if I do 0, it keeps everything, including the NAs. You can see the blanks. It does all that. I'm going to move this down one, just a little closer than I want. So let me just drop that. All right, so that's the 0. Now let's see what happens if I do 1. One ignores blanks. So now we got rid of these two blanks. Two ignores errors, so got rid of the NAs, but it leaves the blanks. Three will ignore the blanks and the errors. So there are some use cases where that can be really handy. So that's basically to_call and to_row. Since we only have 6 minutes left, I'm going to go ahead and just show real quick wrap calls, and then we're gonna go ahead and wrap up. So wrap calls is different. What it allows you to do is convert your data into a set number of rows or columns that you want. So for example, here I have this big set of data. I could tell it that I want you to break the data out into 3 rows. Now, but what happens if my data doesn't naturally break into 3 rows? I get an N/A at the end. That's where you can pad it, and you'll see an example here where what I can do is at the end it says, what should I do with the pad? I could just put a blank. I could say, add the word, uh, not available. you know, research, research this one. I could put whatever I want there. And so you can wrap it, and you can do— you can wrap, wrap things, rows and columns. That's the whole idea of the wrap function. Again, not as useful as the others, in my opinion. But there it is. All right. So thank you for joining me. I do apologize that my setup, I thought it would be able to show the new things for Copilot, but hopefully you were able to get an appreciation for what they've added. I do think it continues to get better and better. I hope to show more in the future. I'll probably show some things on skills here over the summer. I'll be doing this every 2 weeks. And let me know in the chat, I'd love to know, do you want to see more AI stuff? Do you want to see more writing functions? We're in this phase where everybody's at a different point and things are changing quickly. So I'd love some feedback. Feel free to drop that in the comments. And I just want to let everybody know, anyone who attended this webinar, if you use the code WEBINAR, you'll get 30% off my ultimate course bundle. I have raised that price, it's now $150. Okay. Love that, Dale. AI skills, can you elaborate? What would you like to see in the way of AI when you say AI skills? I just love, you know, and you can also DM me separately, but if anyone— I think everybody's trying to figure out AI. I am freely willing to admit I'm figuring it out as well. So if there are specific things you want with AI, let me know. Skills to the AI agent. Okay, so writing skills and like Claude. I'm actually— so I'll share this real quick since you brought that up, Dale. I am preparing to take a course using Claude and using a bunch of skills, so I will get a pack of skills with that. I'll learn quite a bit, so there should be some things I can share there. Uh, you're welcome, Megan, appreciate that. All right, so there's this course here, Ultimate Course Bundle. You can see the QR code, and there's also a Modern Excel course as well for anyone that's interested. Thank you everybody for joining. I'll hang around for a few minutes in case there's any questions that you might have, so I'll hang on for a few minutes, but that's what we had covered today. Not surprised, Megan, to hear you say Claude. I think a lot of people are interested in Claude. What I will say on this, and I'll give my opinion here, one, Cowork has now been added across the board to Copilot. They are adding skills over time. I think they'll be more at parity. Right now, I do think Claude is the better tool, but you can get some pretty— you can get some really good results out of Copilot. So That's what I'd say. Totally understand that one. Love any other thoughts. I'll definitely— I did some Claude stuff, I think, last time or the time before, and I'll do some Claude again. It's, uh, it's always hard to know, so I appreciate that feedback. Uh, we'll end with this joke: what's the most dangerous cell in Excel? So the question is, what is the most dangerous cell in Excel? C4. Hahaha. See if you can figure that one out. You're welcome, Ben. You're welcome, Thomas and Megan, Linda. Thank you again. I'll hang on for just another minute or two in case there's any questions. All right, well, thank you everybody. I'm gonna go ahead and end the stream here, but I appreciate you attending, and feel free to DM me if you have any questions.