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

Hey, sorry I'm running a few minutes late, everyone. Hope you're doing well. I was having some technical issues today so I had to switch computers real quick. If you can hear me, please let me know. Would love to know if you guys can hear me. So just throw it in the comments where you're coming from if you're out there and we will go ahead and get started here in just one minute. Please just go ahead and throw in the chat where you're coming from today. Just let me know you can hear me. All right, we got a from Pakistan. Welcome. Who else do we have out there? Australia. Monica. Great. I think it's early in the morning for you if I'm not mistaking. 76 7am Anyone else we have out there? All right, we have a LinkedIn user can hear me. We got Kathy from Virginia. Great. All right, I'm just pulling up the files here and we'll get started. I'm going to go ahead and share my screen here in just one moment. Everybody should be seeing PowerPoint at this point. If you're not, let me know. We have Saudi Arabia, another one from the US So great. We're going to talk about dynamic arrays today, so I'm going to jump right into it. As I know we got started a little late here. For anyone who didn't download the Excel file, you can get it here. You can also go to my website, the fpnaguy.com and go to Resources Excel files and it will be the first one at the top to download. Can I launch an FPA course for India? We'll see. I there's always a possibility. You never know. All right, so I'm going to tell you a little bit just real quick who I am for anyone who doesn't know. We'll briefly talk about what dynamic arrays are and then we're going to jump into several examples. I've covered dynamic arrays before so you can watch that webinar on the basics. Today we're going to talk about some specific functions that I think you'll find helpful and then we'll do a wrap up and key takeaways. So I think of dynamic arrays as a real game changer. In Excel they basically allow a single formula to spill or return multiple values values in a grid and they can automatically expand or contract based on inputs to that formula. So they can go into multiple rows and columns. It changes the way you work in Excel. Now here's some things people said about them. They're the biggest change to Excel formulas in Years maybe the biggest change ever, you know. Here's another one. They're one of the relatively must know game changing features of Excel. They can simplify the way you work and build. It really is amazing to see how it's changed the way people work in Excel. So we're going to cover several files today. Go ahead and open the student file, which is what you have. I have a master file, so it's going to look a little different. I'm going to enlarge my screen. I'm hoping everybody can see. Okay, so here's a list of most of the dynamic array formulas as well as some resources. The ones in green are ones that we will try to cover today. I don't know if we'll have time to get through all of them. We'll see how far we get. But these are some of what I'd say probably the lesser known dynamic array formulas. I think a lot of people at this point have heard of xlookup unique. Maybe filter text after or before. We're going to talk about sort sort by the stack formulas and then a couple choose formulas. So again we'll see how how far we get with this today. So please let me know. I'd love to know if anyone's used these formulas before. If you're familiar with them, let me know in the chat. And if you're not familiar, let me know in the chat. And if anyone has any good Excel jokes, you can also put those in the chat. So how this file is set up is there's two sheets for each of these functions. VStack is L33T. I'm not familiar with that one from the random guy, but I'm going to take it that it's awesome. Maybe I'm just getting old. What's going to happen is each sheet will have the function with description of what it does and the syntax along with each argument and a little bit of detail about that argument. They'll be in brackets if they're optional arguments. And so you'll see. Then there'll be an example sheet in your guys's case on the example sheet only the first one will have a formula. The other ones are ones for us to do and I can send out the master file afterwards. Ah, got it. Thank you random guy. I like the name. And that will be the same for each of these Sort by etc. If it's not big enough. If you're having a hard time seeing my screen, let me know. I've tried to enlarge it so you should Be able to see it. You know, as you can imagine, sort function is really simple. It is used to sort your data. Excel has always had that ability. I can highlight any data set and sort it. What's great about this is now is if my data is in something that's dynamic or in a table that can expand or contract, I can reference the table, add records to the table and my sort order will automatically change based on my criteria. That what I think is the exciting part. So what we're going to do here is the first example says use the sort function with unique function to return a unique sorted list. So basically what we're going to do here is we're going to do it off first names. So first I'm going to get a unique list. There's a formula called unique. I'm going to come over here and select first names. Notice I did that little drop down arrow that selects the entire column. You can see the the table is named data classes. The column is named first name in brackets. You're not familiar with tables? I do have a webinar where I cover Excel tables. I use Excel tables for most of my data. So you'll see that nomenclature a lot and that goes into quite a bit of detail. I offer also offer a free course on my thinkific site that's about an hour long for Excel tables available to anyone. All right, so all we need to do now for the unique is close this. I now have a unique list. What I don't have is a unique sorted list. So I can take this unique list and wrap it in a sort. So you'll see the sort has four arguments. The array which is just the data, that's this right here. This formula is that. Then it asks for a sort index. If there were multiple records, I could tell it which one to sort off. In this case there's only one. So I can just ignore that step. It's optional. So I'll go to the next one. Here's where I can select do I want it in ascending or descending order? It says if I want ascending I do a one. In this case I want it in ascending order. I'll go ahead and close it and you can now see I got the list of names. If I was to add a name. So let's just add the name Vicki because that should appear at the bottom. You can see it added that name Vicki. If I delete that from the table, it now removes the name Vicki. So the sort order would change. It added it to the bottom. If I did A name that needed to be at the top, it would add it to the top. And let's see, we have someone there from Mexico. Welcome. So Rio. And so then what we have here under last names, it's going to be the same idea. The only differences if we want to do a list of last name is, is two things. One, we're going to left reference the last name column. And two, when we wrap our unique in a sort, it helps if you type it right. What you're going to need to do is we want this in descending order. Descending order is negative one. So instead of one, we'll do negative one. And we can see Smith is first, Barnhurst is last. You'll see over time, here's the formulas. So, you know, feel free to take a minute, kind of practice that one if you want. And I'll give you about 30 seconds here. And then what we'll do is we'll try number two. And what I'm gonna ask, I'll. I'll go ahead and walk through this one. But on the next few, I'm gonna give you guys a minute to try it on your own, Ask questions if you have it, and then we'll keep going. So here on the sort one, we'll do this together. The difference is, is this one, return the full list of data sorted by last name in ascending order. You'll notice I did a unique, but because I'm selecting every single record, there is no duplicate. So I don't even have to actually do a unique. But what we can do here is we could say sort, and we're going to do the whole table this time. So I have the whole table, but I want to sort by last name in ascending order. So if I look at this table, index column G, first name is 1, this is 2, this is 3, this is 4. So here where it says sort index, I want last name. So in this case I'll enter two and then I want negative one for descending and then I'll close it and you can see it gave me last name in descending order. Now you'll notice something with sort. Sort has an index, but it doesn't. It's not. You can't do a sort by. So they created a sort function and a sort by function. Just real quick, I'll answer your question. Alexander. Yes, there will be a recording. You can get the working file on my website, the fpna guy.com go to resources, Excel files, it's the first file there. You can download it. This will be available on YouTube and you can also re watch it here on Streamyard as well for at least a couple weeks. But all of these are recorded to YouTube. And if you go to my website, I have a list of all my other webinars with the Excel files. You can download the file and watch the webinar. All right, so that's the sort function. Next we're going to do sort by. And you'll see here, here's the argument. I'm not, I'm gonna, I'm not gonna walk through the argument. I'm gonna walk through it as we do the exercise. But this is a resource for you to see how it works. So the basic idea is the sort by allows you to sort your data based on another kind of range or array. In fact, the data you're your sort, your data you're sorting and your sort by don't have to be the same. They need the same number of records, but they can be in different places. Great. We have someone there from Zimbabwe, it looks like. Welcome. Keep letting me know where you're coming from. Have to say is, what was Microsoft thinking when they took away his Excel mvp? But don't get me on a rant. Sorted by income, descending order, teacher's age, first name, last name. So basically we went through and sorted by everything in descending order. So if there was the same name, which there are in some cases, like Bill Smith, it, it would go all the way over to income, to settings we want to sort by. In this case, we sorted by sex in descending order. Then we sorted by income, descending order, teachers, age, first name, last name. So basically we went through and sorted by everything in descending order. So if there was the same name, which there are in some cases, like Bill Smith, it would go all the way over to income, to SEC segment. Which, which one comes first? As you can see here, this Bill down here is 267. So he's quite a bit lower than this bill because of income, because it's sorting them all with the sort by. So let's go ahead and see how that works. So we're going to go equals sort by. And is the first thing it says, oh, wait1t if I can spell, we'll do this. All right, so notice the first thing it has is array. So we're going to go ahead and select this whole area. Then we're going to go ahead and do our buy array. So what do we want to sort it by? And what I like to do is put each argument on a separate line to make it really easy to read the formula. So it says we're going to sort the list in ascending order by sex. So I'm going to come over here to sex. Oops. Come over to sex. What am I do. Hold on, why is it not wanting to. There we go. All right, so we're going to put sex in. I'm going to put that on a separate line. We're going to do it in descending order. Now I'm going to do the next one. The next one is income. So come over here, select income, negative one. I'm actually going to set that on a separate line just to make it easy to follow. We'll do alt, enter again, next one. So we've done sex, we've done income. Now we'll do age. All right, so sorry, give me one second. Don't know why it didn't want to select. There we go. So we got agent, negative one. And then we'll do first name again, negative one. And then I'll break them out on lines after I'm done because it works. Works better. And then last name, negative one. And then what I'm going to do is I'm going to take each of these and just break them out on a separate line so I can come in, drag this down and real easy, see. Okay, here's what my sorting on and here's all my criteria so I can see the order and that I'm doing all negative one. When I close this, I now get the list sorted in that order, starting with sex. You'll see all the males are listed first. Then it's going to go next by income, so that you can see the income the highest to lowest. If there's a tie for income, it would then go to age. If there's a tie for age, it would go to first name, then last name. So, for example, you know, Bill Smith here, there's different incomes. If I made them the same, they'd end up in the same spot. So you can assign as many as you want to a sort order. Look, is anyone else frozen? I'm not having any problems on my end, but I see a comment where someone says it froze up. If someone could let me know if they're still hearing everything, if it's working okay, that'd be great. I'm going to hope so. Hopefully we'll hear from somebody in a minute. I'll keep going, but please let me know if you're following along. All right. So the second example says return a unique, unique list of the full name sorted by sex and Age in ascending order. All right, good. Here. Fine. All good now. Great. So what I want to do is I want to give you guys a minute to see if you can figure out how that would work, and then I'll walk through the requirements on this one. So just maybe take a minute and see if you can figure out how to create a unique list of the full name sorted by sex and age in ascending order. I'll give you about a minute and then we'll walk through it. I want you guys to at least think about it on your own. Give it a try, because it's easy to to me to be up here and you to watch. But when you have to start thinking about it is when you learn. As much as I wish I could have learned it all by just watching Mike Girvin, it didn't quite work. I had to actually do some effort. All right, we'll give about another 30 seconds. While we're waiting, see if I can come up with a good Excel joke. It's budget season, so does anyone know why the spreadsheet was constipated? I kind of gave the answer. We'll see if anyone picked it up. Why was the spreadsheet constipated? Because it couldn't budget. All right, bad joke. I know. So we're going to go ahead and do this. So we're going to return a unique list of the full name sorted by sex and age. So what I'm going to do first here is we're going to do the sort by and we're going to do it by full name. So I'm going to come in here and select full name. And then my sort order is going to be sex. So we'll come to sex here and I'll go ahead and put one. If I put nothing, it defaults to ascending order. But we'll go ahead and add it. And then. Oops, I did. I meant to do sex first and age. So I'm just going to change this to sex. We'll come in, make this one age. All right, so that gave me a list notice. I got the spill error. And the reason for that is I haven't created a unique list yet. So the last thing I'm going to do is I'm just going to wrap this in a unique. So it returns each record only once. Now I have a unique list of everybody's name sorted by sex and age, from youngest to oldest. So we have Emily, Jacqueline, Jill, all the way down to the second Bill Smith. You can see, I think this Bill Smith has two spaces. This one has one. It looks like so slight difference between them. All right, so that's the sort by. Yeah. So the reason the the spill. For those who are wondering about the spill and Random Guy, I cover this in detail when I do the dynamic arrays1. But the reason for it is you get a spill error. All right, let me take this out. Whenever it can it something in its way. That formula right there is in its way. It will say spill and it will show you the range it needs. It needs to go all the way down to row 47. It's saying, hey, there's something in the way in 44. Therefore, I'm going to give a spill error till you remove it. If I delete this, notice it went all the way down. But notice the formatting doesn't follow. With dynamic arrays, the formatting does not adjust as they expand and contract. So if you want more on spill errors, definitely check out the dynamic arrays video. Or you can take my dynamic arrays course as well. I cover that in more detail. All right, so that's sort and sort by fairly straightforward. The next one is a function called drop. Love to know if anyone's heard of drop, but the idea is you can drop rows and columns from your data. So maybe it has totals, maybe it has headers, and you don't want those included. You can use drop to get rid of that first and last row or to get rid of a column. So we'll show a quick ex. Just an example of this. Where it becomes handy is when you start combining it with other formulas. But we'll show a couple examples. So the first one we'll do together. Then I'll give you guys a minute to try the second or third one for it. So we have here a table similar to the others, some people's names. And what I want to do is that to remove the header row from the table. So what I'm going to do here is I'm going to delete this and show you how it would work. You can go ahead and follow along here. So I've highlighted the entire table. Okay. Something. Just a minute. There we go. My mouse was being weird for a minute. Sorry about that. All right, so now you can see. Hey, do I want to drop rows or columns? I want to drop the first row. So if I just put a one and nothing for the column, notice it gave me everything but the headers. So we have one here. Looks like Luis has done basic drop. Great. Thanks for sharing. So that. That's a really simple example. Now I want you to try a second example where what you're going to do is use the drop function to remove the header row. So same as we did before and remove the first three columns. So you'll notice I got rid of this id, first name and last name, saying, hey, this is the only data I want the full name, the age, the state. So if everybody wants to take a minute and just give that a try, see how it would work and try that. One thing to keep in mind with this drop formula, one is for positive, negative one, it or one is for the first record, negative one is for the last record. So the last column you could do a negative one as well. So just so you know, as you're doing this, if you start from the top one, you start from the bottom negative one, you go to the right hand side, that's your negative one. This is your one for your columns. So that's the numbering that works. So go ahead and just try a minute to do number two. And then what we're going to do is we're going to do number three next. So you can see what that looks like what we call a nested drop. All right, some people are saying I froze again. I'm not having any problems. I everything seems to be working on my end. I'm a direct connection into my Internet. So apologies if I freeze for a minute. If some of you can hear me, let me know. But I know at least one person says I froze. All right, so it looks like some are good. I end, I'll just keep teaching and unless I get a lot of complaints, I won't worry about it. So here, what I want to show is this is a nested drop. So what I did is it says use a nested drop function to eliminate the headers, rows 10 and all, and all columns. So you get basically and all columns 1, 2, 3 and 6. So you can see there's different ways you can, you can do that. So what I did here is I first dropped the first the header. Then I dropped these first three. So I only get columns four, five and six. Then in the next one I went ahead and said, okay, okay, in the next one, let's go ahead and only keep the full name and the age. So we dropped the state and we dropped the last, we dropped the last two records in the state. So here we get it down to nine records. So I'll walk through that. So what I did here is we'll do the first part drop, highlight the whole thing, and I Got rid of the header. So I said, hey, I don't want the header and I want starting the last three, I believe. And you'll notice I get a spill error right now. So I get rid of everything else. So I'll delete that. So there you can see I have that. Then what I said is, in my second drop, I went ahead and said, okay, I want to get rid of the last two records because I only want nine. And I want to get rid of Arizona or the state. Not just Arizona, but any state. So now I have a unique list with just the last name and the age. So, see, you could start dropping things one by one. Hey, get just the. Get rid of the header, get just the columns I want. Then you could get rid of rows or additional columns and kind of nested inside of each other. There's other ways you could do it. Not saying it's necessarily the most effective, but just to kind of give you an example of what you can do. Yeah, you could. Yeah, you could do it that way. With the four drops, I think it's a little easier to do two than four, but either one, as long as it works for you. All right, so the next function is the take function. And in. In a minute here, we're going to start combining these to see some examples as well. So in this case, instead of dropping something, you're taking just what you want from the records. Same idea. You select your range, you take the rows you want, you take the columns where in the drop, you get rid of the rows you want, you get rid of the columns you don't want. So, same basic idea. And so let's take two examples here. This first one will be very simple. It just says use the take function to get only the industry column. So all I would do here is type take and reference this entire range and say, I want just column one with my header. Oops. And not row. Sorry. I want column one, all rows. So right there, that gives me just the industry very simple example. I think everybody should be able to follow that. Now, the second one is a little more complex, so I'll walk through this, but you're welcome to try it on your own as well. So it says use the take function with the sort to get the company name and revenue sorted by revenue in descending order and include the table headers and only the top three records. So we have it here. I'm going to go ahead and redo it over here. So what I did is I started by saying take, and then I did a sort so what I have here is I took the whole revenue table and I said sort it by revenue in descending order, right? So if I remove the take for just one minute, right now I have it sorted from largest to smallest in revenue. If I expand this out, the 8, the 87, the 81, all the way down to the 34. So that's the first step. Second step is to do the take. So in this case I want the header plus the top three records. So I'm going to take the first four columns, the header plus the top three. So I'm going to go four. But what it's going to take it from, it's going to take them from my sort. Not this here, but this virtual table I've created in the formula. If I hit F9, it's sorted in order. So I'm going to hit escape there. And now we'll do that take. So we got the top four and we only want the company and the revenue. So I'm going to do minus two. And so now from that table. Yes, you could put it in a pivot table and do something similar. I'm not sure why my numbers are not showing up. Ah, there we go. Sorted. Wrong. Okay, so what you'll see is I just created a table with the top three. If I made this dynamic, I could select, hey, I want top five, top 10, whatever. And every time something's added that's automatically going to update. You can see where you could use that with maybe a dashboard where you want someone to see the top numbers. You don't want them to have to refresh any pivot tables. Anytime stuff's added to the table, it's automatically going to refresh. Ah, we got the famous John in the house. Thanks for joining. John Michaludis Excel mvp. He has a lot of great content out there. So if you're not following them, I'm gonna give a shout out and tell you you should follow them. All right, so that's the take example. I'm gonna pause here for a minute and just see we're about halfway through if there's any questions so far from anybody. And if not, I'll probably tell a joke here in a minute. Well, I give you a second to ask questions. Link to his channel. I'll let John throw out his channel. I think it's my Excel online. No, it's not my. Remind me John. I know he has a podcast, but his website name is slipping my mind. So he'll add it. If he doesn't, I'll Grab it at the end and put it in the. In the comments if he doesn't add it himself. All right, so let's, let's go ahead and keep going, but first I'll do a joke. Does anyone know the difference between an accountant and an FPNA professional? All righty. Well, here goes. When an accountant gets creative, they go to jail. When an FPNA professional gets creative, they get promoted. That's why I'm in FP&A. So, Jeff, the question is, if there were multiple companies within a specific industry, can I they take the companies within an industry? No, no. The take only allows you to take rows or columns. But what you could do is you could filter the list first, then take the data you want from the filtered list. So similar to how I sorted this here, what you can do in Excel, and this is a concept that will really open up your opportunity to do so much more in Excel, is these are almost like virtual tables. You're using a. A formula to take the table and to shape it, then you're shaping that table again. So basically, this take is performing everything on this virtual table in here, not what you see here, because it's sorted in that order. So the way I would do a take with an industry is let's just say these were all the same industry, but one, I'll show an example. So we'll say. Well, three, what I would do here is, the way I would do this is I would start with a filter. I'm gonna have to think about the sort. But let's start with the filter here. So I'm going to take this. I got my revenue, I'm going to filter it by the industry. So that's going to equal software. You know, we can put that in a dynamic list. So we got that. So right now I'm going to sort this filter by column three in descending order. Right? So now I have this. Now I'm going to do a take and let's say I wanted just the top one in this case, and I don't want to show the industry. So what I'm going to do in my take is I want just the top two rows and I want. I think it's the last two. Let's see, negative two. I want the last two columns. So there you go. Ah, when I did my revenue, I did not include the header here. I should have done this to include everything. And what does it not like? I'd have to look at it for a minute. I got some. Oh, this here also should have Included the header. So it's all the same now there. Why am I not getting. Did I remove. I must have removed the header somewhere. I'd have to look at it for a minute. But that's the basic idea. I don't want to spend too much time. I'm sure I did something simple wrong of why I'm not getting the header. I'll have to play with it later. As soon as we hang up, I'll figure it out. But that's the basic idea is I filtered to get my data. I sorted it in the order I wanted. Then I did a take with the first one. I just did a sort with a take. So inside you can do whatever you want. You could do a lookup, you could do a filter, all kinds of different things, you know, perform your different operations and then at the end take just what you need from whatever you've done. All right, hope that was helpful. We're going to keep going here. Let's move to Choose Choose Calls. Let me enlarge this. So choose Columns is really simple formula. It has just two required arguments. Again, your array, and when I say array, that's your range, that's your set of data, your table, whatever data you're working on. Then you just tell it what column numbers you want. Do you want column 1, column 2, column 3, column 4, 1, 3 and 5? You can pick whatever columns you want. This can be really helpful if you start using an X match. Like if you have data where you need change what columns you're referencing. You could do things like matching it dynamically. And so we'll show one example of that here. So what we have here is we. I always like to start with just a real simple example that I'll walk through that has the information in it just so you can see the basics of how it works. So we have our data here. It says use the Choose Calls function to return the full name column. So what we're going to do here is I'm going to do Choose Calls. We're going to highlight all of the data. But in this case I'm going to ignore the header, just the data. And then full name is column 4. So I could type 4, or I could start from the back and do minus 3 and I'll get the same answer. So let's try both four. I get the list. If I add somebody, we'll add Jeff Gray. You can see he shows up at the bottom. What if I add John? Right there we have John Maludis. He's been added as well, so I can add them to it. So now try negative 3 and see what happens. Exact same list, so you can count from either direction. There's different reasons. May you want. May you might want to do that. Maybe you always want to return the last column. There's dates, you're doing some daily thing. And so then you could do negative one. You always want the first column, you could do one. Right, and so there's different reasons you do it. Now let's see it with an X match. Xmatch is basically the. Not basically, it is the new version of Match. If you've used Match, you've used X Match. X Match just has a few more arguments. You can do smaller lists, larger lists, but it's the same syntax, same idea. I've been trying to use X Match in all my data because it is a more complete formula and so I've been trying to get in the habit. So what we're going to do here is it says use choose calls with xmax to dynamically pull all data associated with column one. So you'll see there's a column name here. Notice if I change this to id, it updates to id. If I change it to last name, it updates to last name, age, etc. So what the formula is doing is it's very, very similar to last time in that we're starting with the entire table, but instead of just entering a number, we're dynamically telling it what number to choose. So we're basically saying, hey, Excel, I want you to pick the number. So we're saying find the age in the headers and it will return that number. So you'll notice if I highlight this right here, it's going to return. It's really tiny, you probably can't even see it. But right there where my arrow's pointing, it returned the number five. Yes, it will return an exact match by default. I put 0 here, but if I didn't put it, it would return that. So let's go ahead and type the X match so you can see how that works. So if we do X Match here, what I'm going to do is I'm going to select my value. So that's the first step. Look up value. My lookup array is my headers and then the last one is Exact match. Exact Match or next smaller item. Etc. If I select. If I don't do anything, it'll automatically do an exact match. I forgot to put under choose calls. So what I forgot to do there is. Let's see. Yeah, No, I forgot to put the extra parentheses. So there we go. That's an example with Choose Calls. Here's a second example. In the interest of time, I think you'll get more value of continuing on. But what I did here, I used Choose Calls with xmatch to dynamically pull the column one and then sort the names in ascending order based on column two. So I, I did use a match instead of an X match there. I'll let you follow that. But what it's doing is it's pulling the same list of people, but you can see it's sorting them by the state they're in. Notice Jeffrey Gray gets listed at the bottom of the zero because we didn't put in a state. He's in Canada, so we'll put him in Texas just because, you know, Texas and Canada are both big. And then you'll notice he moved up in the sort order. Yes, F9. John. That's a great way to also show a, a formula. It. So I saw him mention that. Right. If I want to see what a formula is doing, if I highlight it and press F9, it will show everything it's saving in the formula. But if you don't hit Escape and you hit Enter, you lose your formula. It only shows you the actual answer. The other option is you can highlight something and it will return the answer. But it's super small up here for you to see. There's not a way to expand that. And yes, 51st state. I often refer to Canada that way. It gets me in trouble sometimes. I'm going to skip choose rows because it's the exact same thing as choose columns, except for you're performing it on rows. This should say row number. That's a mistake there, but same idea. You can see a couple examples there. You can work on your own. I want to get to VStack because this is probably one of my favorite vstack and hstack. And I think you'll like some of the examples with these. So we'll, we'll run through these next. All right, so VStack, we're going to start with a really, really simple example. And then as we get the hstack, they'll get more complex. So the whole idea of a vertical stack is stacking data together. So it says combine the three tables starting in row four into one group with three columns with a header on top using vstack. So I want to get this. But from this one I only want to get this. This one I only got want to get this I could highlight the same thing for all of them and drop the first record. Instead, I'm going to do it a little bit different. We're not going to use the drop here. What I'm going to do is I'm going to type VStack, my first array. I'm going to include everything. So you'll notice it uses this hashtag all item identifier. That's to say to give me everything. But notice for the second one, it just calls it February underscore cells because I took just the data and so it gives it just a table name. Same for the third one, so it ignored the header. Now I just hit enter and I've just stacked those three tables. Now, on the outside I could sort them if I wanted to. Maybe I want to. So I could go sort and let's see, sort index. Why don't we do widgets and we'll do them in ascending order. So see, now I get widget one, widget two, widget three. So it's took the first one from each table. In this case, I could have sorted it by revenue, whatever I want. So you can start seeing how you can combine and shape your data. All right, so this next one we're going to use drop. I probably wouldn't actually use drop in this case, but I want to show you how you would use a drop. So we're going to get the same thing as before, but we, we have a total in here. We want to drop that. We want to drop the header. So what we're going to do is we're going to go ahead and write the formula using a couple drops just so you can see the combination together. And give me a second here, let me pull this down. Why is this not wanting to go down? It's weird. Something's freezing on my computer. But give me a minute here. We'll. We'll work through that. No biggie. All right, so what I would do here is VStack, and then I'm going to do a drop and I'm going to get my first table. And what I want to drop here is just the total. So we're not going to drop any, we're not going to drop any columns, but we are going to drop a row. So I'm going to do minus one and I'll close it. And so what you'll see here is I got the first one without the total. Now I need to add my second group. So I'm going to do a drop here. I'm going to highlight the whole thing. So we're saying in these cases, I want to highlight everything no matter what. And now what I'm going to do is I'm first going to drop the header. So that will get rid of that first row. And then I'm going to add a second drop. And in the second drop, I'm going ahead and get rid of negative one, which is my total at the bottom. And so now you can see we have the second group, and I can do the same for the third. So this right here, this drop, the only difference next time is we're going to reference the next table. So instead of this one, I'm just going to drag this over. If I can get it to highlight. Let's see. We can just do this. That should be all right. March cells. Yes. So Marchore cells 21 there. And voila. Jeff is talking politics. He's gonna get himself in trouble. I'm not touching that one. But yes, I voted today. Yes, you could. Random guy. I was just showing. If you wanted to reference both of them, you could. John, who will win the states? I'll give my opinion. Who I think is going to win. I won't say who I want to win. If I was a betting man, I put my money that it's very close. We don't find out tonight. And within a week or a day or two with all the right in ballots, close enough, it will eventually be Donald Trump. If I had to guess, total guess, not giving my preference on who I want to win. All right, moving on beyond politics so I don't get shot by somebody. We'll keep going. Right, next we're going to do hstack. Hstack. Very similar to Vstack, except for it's just horizontal. You're stacking things instead of vertically. So let's take a simple example. We're going to do this first one here. So all these companies, Apple, Nvidia, Google, Cloudflare, they all decided to merge, and we need to create some kind of horizontal stack of all their names. Why? I don't know, but bear with me. Just so you can see a simple example. Then we'll start to see how you start. Pun intended. Start stacking these formulas to do more interesting things. So I could highlight this first one, highlight the second one, and I can. I can do it by row or column. That will change the order it does it in. If I went this way, it would do Apple, then Nvidia, then Microsoft. Here it's going to do Apple, Google, Oracle, then Nvidia. Cloudflare SAP. So, right, so if I change this order, let's do it the other way. And I go like this, then like this, then like this. And what did I do wrong? Oh, it's. Yeah, it's going to stack them down. Yep. So let me take this out of here so you can see. So see, Yeah, I needed all three because it stacked them down there. If I do the hstack that way. So you can see the difference in how you can do that. All right, so I'm going to go back to what we originally had. Now the second one, what we're going to do and I should have wrapped this. It's longer than I thought. Once I increase, let me decrease my screen size a little bit. All right, so we're going to use hstack, sort, unique and sumifs to create. This is something I learned from the one and only Mike Girvin and how you can do that. All right, so I'm going to go back to what we originally had. Now the second one, what we're going to do and I should have wrapped this. It's longer than I thought. Once I increase, let me decrease my screen size a little bit. All right, so we're going to use hstack, sort, unique and sumifs to create. This is something I learned from the one and only Mike Girvin. Full credit there, this kind of idea to create a sorted summarized dynamic Full credit there, this kind of idea to create a sorted summarized dynamic list for each sales rep. So over here, notice list for each sales rep. So over here, notice we have salesperson, amount sold in month. What we want to do is I want to create a dynamic list with just the salesperson, the amount sold and their totals. So you can see here what we what we have and we're going to go ahead and do this formula. So what we're going to do is I'm going to type it over here. We're going to do a vstack. We have salesperson, amount sold in month. What we want to do is I want to create a dynamic list with just the salesperson, the amount sold and their totals. So you can see here what we what we have and we're going to go ahead and do this formula. So what we're going to do is I'm going to type it over here. We're going to do a vstack and the first thing we want to get is the two headers we want. So I'm going to come over here and the first thing we want to get is the two headers we want. So I'm going to come over here and I want these two headers. So if I close this right now I got salesperson and amount sold and that's it. Now what I need to do is I need to stack horizontally under that each of the sales people. So the first thing I'm going to do is get a unique list of my seller and I want these two headers. So if I close this right now I got salesperson and amount sold and that's it. Now what I need to do is I need to stack horizontally under that each of the sales people. So I'm going to come over here again to my table. We're going to highlight all this and all right, so we're going to close it for a minute and let's see what did I do wrong? All right, so give me a second here. We got our h sales people. So the first thing I'm going to do is get a unique list of of my cells. Let me just look at this hstack. Okay, so we got our vstack and this people. So I'm going to come over here again to my table. We're going to highlight all this. We have our hstack. We got our unique and then. Oh, we need one more. And see, right now I have salesperson, amount sold and the unique list of the sales reps. I don't want to end there, so I'm going to come to the end. And now we're going to do my second stack. My second stack is I want to do sumifs and all right, so we're going to close it for a minute and let's see what did I do wrong? All right, so give me a second here. We got our h. Let me just look at this hstack. Okay, so we got our vstack and then we have our hstack. We got our unique and then of the amount sold. So we're going to come in here and say amount sold. Our criteria range for that is going to be our sales reps. And what we want for our list is a unique list of each sales rep. Oh, we need one more. And see, right now I have salesperson, amount sold and the unique list of the sales reps. I don't want to end there. So I'm going to come to the end. And now we're going to do my second stack. My second stack. I added one too many it looks like. And oh, we got to move that out of the way. So Let me just. Let me just do this. Ah, I got it. I stacked it wrong. So let's see what did I. I did something wrong in my sumif there. I must. Oh, did I not Is I want to do sumifs of the amount sold. So that unique salesperson I don't think I did. So what I'm going to do is I'm going to cheat here for a minute and we're going to copy this over so you can see. And there we go. What I had failed to do is right here I done that unique wrong. So you can see I did the sales amount sold, the salesperson and the unique. So basically what this formula is doing is it's first stacking. To give me my two headers. We're going to come in here and say amount sold. Our criteria range for that is going to be our sales reps and what we want for our list is a unique list of each sales rep. I added one too many. It looks like salesperson amount sold. Then I'm getting a unique list of each salesperson. Then I'm summing each salesperson by the unique list. So this hstack the horizontal. Group one is the seven sales reps. Group two is the sum of the amount they sold. And by using this unique which I did here, it makes it the exact same list so it matches. And oh, we got to move that out of the way. So let me just. Let me just do this. Ah, I got it. I stacked it wrong. So let you probably could use rap calls in that example as well. I'd have to think about it for a minute. See what did I. I did something wrong in my sumif there I must. Oh, did I not Luis? But yeah. And yeah. Control alt. Thank you, John. So that. That's another example. We're near the end of time. There are a couple others in here that walk through wrap calls and wrap rows. What I will be doing is to. For everybody who attended who signed up through the stream yard I will. I have your email address if you signed up through stream unique salesperson. I don't think I did. So what I'm going to do is I'm going to. If you didn't you can email me but I'll send out the completed file so you have it. And the. And the PowerPoint before I wrap up I'll pause here. Any questions from anybody? Go ahead and close that. Any. Any questions from what we covered And Luis, there's a number of different ways that could be done Sheet here for a minute and we're going to copy this over so you can see. And there we go. What I had failed to do is right here, I done that unique wrong. So you can see I did the sales amount sold, the salesperson and the unique. So basically what this formula is doing is it's first stacking to give me my two headers, salesperson, amount sold. Then I'm getting a unique list of each salesperson. Then I'm summing each salesperson by the unique list. So this hstack the horizontal group one is the seven sales reps. Group two is the sum of the amount they sold. And by using this unique, which I did here, it makes it the exact same list so it matches. You probably could use wrap calls in that example as well. I'd have to think about it for a minute, Luis. But yeah, and yeah, control alt. Thank you, John. So that, that's another example. We're near the end of time. There are a couple others in here that walk through rap coals and wrap rows. What I will be doing is to. For everybody who attended who signed up through the Stream yard, I will. I have your email address if you signed up through stream yard. If you didn't, you can email me but I'll send out the completed file so you have it. And the. And the PowerPoint before I wrap up, I'll pause here. Any questions from anybody? Go ahead and close that. Any, any questions from what we cover. And Luis, there's a number of different ways that could be done. Some of these examples I showed today, what I did was not necessarily the most efficient way to do it. The idea is to just show you the art of the possible and get you to start thinking about. As you combine these formulas together, you can do a lot of really cool stuff when you start understanding them. That was really the goal versus, hey, this is the most efficient way to do it. Like you should use a choose coals in some cases, maybe choose calls. Maybe a filter makes more sense, you know, instead of a drop, not referencing it, whatever it may be. Right. There's a. The one thing about Excel that I think everybody who's spent any time with it has learned is there's 10 different ways to do the same thing in Excel. Part of it is just picking the way you want to do it. All right, so I'm going to go back to the deck here. Toward the end of the deck you'll find a couple resources for dynamic arrays. One is a book called up up an Array by Abbott Katz. I own that book. Scary enough, I Read it on a flight and helped me a lot with dynamic arrays. Here's a couple Microsoft resources as well. And then, you know, webinar wouldn't be a complete without a plug after all. I do like to earn a living, so I have my FPA ultimate course bundle. Normally that's $130. There's a coupon for 30% off if you use the code webinar. It includes business partnering, about two hours of detail there with Ron Moncaro and myself, modeling design principles, a data analysis course, and modern Excel. Altogether, it's over 10 hours of video content and his stuff is added to it. You'll get that as well. And then the last one is modern Excel. That's five hours. Same material used in the FPA certification by Wharton Online and Wall Street Prep. I was the instructor for that. It's the exact same videos for just that section. Obviously not the whole course, but again, 30 off. Regular price is 59.99 and it's going to cover Excel tables, power query, dynamic arrays. Just enough to wet your appetite. And John, I totally agree. As he said, there's so many ways to to skin in Excel. Yes, there are. So there are many ways to do things and there are many Franken models because of it, as I like to call them, which are Frankenstein models for those who didn't catch that. All right, we have about a minute left, so if anyone has any questions, I'll stay on for another minute or two and answer any questions. And if you want, you're welcome to drop. If anyone has a good joke, please put it in the chat. I could use one. I will. I'll email out the file to everybody. Oh man, Jeff is taking shots at my Utah Jazz team. That hurts. I can't believe you're doing that. All right, so I'm going to drop in the link here. For those who would like it, there's the podcast that John Maludis does. You can find that on Apple. Learn Microsoft Excel. Put that up on the screen. So shout out to him. You're welcome, Kathy. He also has his website as well, but check out his podcast. I've listened to a few episodes. I even even featured me once. I got lucky enough to be featured with him. All right, I'll give it another minute or two for any questions and then we'll end the call. This replay will be available, so it will be on YouTube. And if anyone has questions about the courses, feel free to reach out. I think on this note, I'll go ahead and drop off but thanks everybody. Wait, Paul. What? Jeff, you can ask one more question. I'm not sure if I'll give you an answer, but you can ask. It depends on what it is. Lewis, I haven't played a lot with checkboxes yet. You definitely you can do that to select and deselect columns. It's a great new tool. Maybe I'll cover that in the future. But checkboxes are fabulous. I highly recommend learning them. John, my best tip, you know, my best tip is if you're using Excel a lot, set aside 15 minutes every day and learn something new. Doing that will make you better in Excel versus telling you, hey, you should use this shortcut or learn that formula. What everybody needs to learn in Excel is different to do their job. There are similarities across many of us, but what I would recommend if I was to give one tip is just take some time and learn something new every day in Excel. You will be amazed how much you'll learn. Okay, let me see if I. I might have to see this example, Jeff, but I'm going to read that. Yeah, using it with the filter function. Agree, Louise. Filter is a fabulous function. I know Mark Proctor says it's his favorite function at least lookup function. He's a huge fan. I'm still looking for a way I'm going to put this up here to copy and paste multiple cells with formulas that are not locked so the relative reference instances without finding or replacing equal with pound then doing the opposite when pasting Are you pasting them into another workbook or a different and so you want to. Well, why wouldn't you make them. Why are they relative instead of absolute? If you're putting them in a different place but you want them to be absolute, which is sounds like kind of what you're doing by hey, putting a pound in front and then taking it back to an equal. If those formulas were absolute, they would copy over. So I'd have to think about the. Yeah, send me an email on this one. I would want to understand the reasoning because it feels like to me if you're putting an equals in front, I would have just made them absolute to start with. But I'm probably missing something. Yes, thank you, John. Any easy questions from anybody? Come on, don't put me at the spot. It's the end of the day and I'm tired. But yeah, send me an email, Jeff, and I'll take a look at it. Cynthia. So the courses contain files and videos where needed. There is PowerPoints as well. So there's decks there's files. There's lots of walkthrough exercises. So for an example, what you saw here, like, let's just say I was teaching VStack. I would walk through in detail the syntax. I would walk through a couple examples with you. Then I would give you time to practice exercises on your own. Then I would come back and answer those exercises in a second video. So each section is going to have that intro video and a second video where I go through the exercises that I've asked you to work on before, you know, you see the answer. So the idea of the course is, hey, let me show you a couple of examples, then let me have you work on your own. Then let me come back and show you how it worked to make sure you learn the concept. Because the best way you learn is by doing so hopefully that helps. Cynthia Alrighty. I'm gonna, I'm gonna drop. Gotta go pick the daughter up from school. I will get this material out either later today or in the morning. Everybody who joined on the via the webinar where I have your email, you'll get the master file and the PowerPoint. All right, thanks, everyone. Have a great night.