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

Foreign. Hello, everyone. Welcome to Excel hour. We'll get started here in a few minutes. I know people are just joining, so go ahead and let us know where you're coming from in the chat. Would love to know where you're located this evening. Hey, Veronica. Good to see you. So we got a few more minutes here. Texas. Everything's bigger in Texas, I've heard. West Africa. Thank you, Aziz. And I apologize if I pronounce that wrong. Yeah, that's what I've heard, Veronica. Everything's supposed to be bigger there. So, you know, I got a new. A new mixer and I've been telling everybody I'm going to use this voice from now on. We'll see how annoying you find this as we're waiting. Hello, my name is. What do you think? Should I use this voice? Good. I'm glad I got the pronunciation correct. If you're just joining us, go ahead and put in the chat where you're coming from. We're going to give it a couple more minutes before it gets started. Yeah, it is bad. It drives my training partner crazy because I'll play it. I. I have this new mixer and it has all these voice things it can do, you know, in addition to. This is what I play. Whenever I tell a joke and nobody claps, I just click the applause. It's called Road. It's. I. I hook my podcast through it. So it's like a production mixer where you can do a lot of things. I can run it separate from my computer, so it was my big upgrade as I do all these podcasts and speaking, but it has a few really annoying features. Thank you for joining us, Eric. I see you in there from Montreal. Alrighty. We'll give it just another. Another minute or two here. We're still a little early and then we'll go ahead and get started. So for those who want to see, I'll show you. Veronica. That's the. That's the mixer there. This right here. I wanted to pretend like I was big time with my podcast, so I. I splurged. Now let's see if I can get my camera back in the right position. There we go. Yep, it's equipment. Alrighty, if you're just joining us, go ahead and let us know where you're coming from in the chat. I imagine we'll have a pretty small group tonight. We'll see how many we get, but I'm guessing anywhere from five to 20 people will join us, so. Yeah, no, definitely not just some software, but I'm sure you could find some software to do it. All right, well, I think we'll kick it off with a couple Excel jokes before I share my deck, because that's what I like to do. So if anyone has a good Excel joke, throw it in the chat. I'll go ahead and share mine. Does anyone know. Hey, Laon, thanks for joining. Does anyone know why cell references in Excel are like gold diggers? So the question is, does anyone know why cell references in Excel are like gold diggers? All righty, here we go. If you want them to stay in the same place, you have to throw some money at them, right? Absolute references. You throw money at gold diggers, you got to throw money at. I never. They added an advanced formula editor. Now we have Python coming, and one I'm super excited about is the group by and pivot by functions. All right, so we're going to jump into things and again, you know, just throw comments in the chat. Where I thought we'd start today is some of the new features in Excel, and I would love to know if any of you are familiar with these future features. So if you are, comment in the chat. So here's the ones I want to cover today. The Analyze data function, where you could automatically have it analyze your data and ask questions of your data. This is not co pilot. This is native to Excel. How many of you have maybe used analyzed data, are familiar with it? Streaming custom functions for the win? Yeah, that's, that's, that's a great one. Thanks, David. So I'm curious how many maybe are familiar with Analyze data or the new data types in Excel? Anything you see on this list. These are ones we're going to talk about briefly. Some others we'll talk about. The new image function, very similar to the Google function. We'll show you how that works. There's import data from picture, which is new to the desktop. It was in mobile. There's a navigation pane which is like a table of contents. And then one of my favorites is this value preview tooltip. If you haven't seen it, will save you a lot of time. All right, so Veronica's familiar with analyze data, but she, she hasn't played with data types. Maybe we'll start with data types and then we'll come back Analyze data. I will send out this file to everybody who registered for the event, so you'll have this for yourself. The first sheet lists roughly 20 changes over the last couple years. 18 in desktop. We're not going to go through all of them, but like automate data which is Office scripts. You can now show changes. Have you ever wondered who made that change? In your Excel file you can now see that search drop down list, one of my favorites and others. So let's jump into a few. So data types. In Excel there are basically four data types. So what you do is if you come up here to your data ribbon, you'll see four here. Organization, stocks, currencies and geography. All right, so I have these four stocks here. If I highlight them and come up here and click on convert to stocks, it just said, oh, that one's Microsoft, that one's Forward Industries for Ford. I would have liked it to find Ford, but I guess there's a different company there, Tesla. And then it said, hey, we couldn't find these two. And so I come over and at the moment it didn't find any results for Coinbase. Not sure why, but we'll put a different name in there. Maybe I'll try Toyota. I tried Toyota. It finds, finds Toyota here on this data selector. If I click on it and say, hey, select Toyota for New York Stock Exchange. I have it now I have Nvidia. It's going to be like, which one do you want? Nvidia is listed on all these different exchanges. I'm going to say New York Stock Exchange. And so now I've brought in the stocks. Nothing fancy so far, right? You can see I brought it into a table and I'll explain why in a minute. So this here, you can see the table design. So I have these stocks, well, what if I want the price of these stocks? Notice here in this table, there's this little drop down right here. So these are all things we can add to this data type. It's almost like it's a card. It's embedded in. If I click here, it will show a card with all of the information that's available. So you can see, I can see the price, the extended hours, their name, you know, what exchange they're on, what their change has been, the high and low, the 52 week high and low volume, you know, on and on, a description of the company, its PE ratio, you know, how many shares it has outstanding, etc, and Give me one second here, I thought my daughter might come in. All right, say hi to everybody. Hi. All right, head on out. All right, close the door behind you. Thank you. So what we can do with data types is we can add information to the grid because it's all stored in the data type. So let's say I want price, it's going to add it add it as a new column there. So I'm just going to unhide. I think there's a couple columns here. We'll just delete them and you'll notice it. Did price notice the formula? The formula says hey equals stocks dot price. So if I came in here and went back, I can see after the.it gives me everything that's in the card. So I can say wait, I wanted volume or you know, whatever I can change that. So I'm going to name this price. I can come over here, add the high, maybe add the headquarters, whatever else I want. That is a data type. So there are three built in data types to Excel. Next one. Anyone have a favorite city? Who has a favorite travel destination? Throw it in the comments. What test this one. I know somebody has to have one. Lan, you got a favorite travel there? There we go. London from Christina. All right, so I'm going to type London here. I'm going to go up to data types. So we'll go to data and I'm going to say geography. All right, we got a few. We'll, we'll add those two. Notice now it says hey, what London do you want? You want London London, Ontario, Greater London or London, Kentucky? I'm going to assume you wanted Greater London, England. So I'm going to select the first one. So notice this one shows a map. Now I'm going to put it in a table. So what I'm going to do is I'm going to insert a row here, make this a city. And the reason I'm doing a table is just because it's easy to automate, you don't have to put it in a table for this to work. So now we're going to do the other cities. We add Hawaii, Japan and now I'm going to highlight both those. We'll come back to data geography and see it automatically found those. It didn't even ask me. You know there's not multiple London or Hawaiis and Japans to choose from. So now I get a bunch of options. What's the area? What's the consumer price index? What's the currency code? You could see for those two for some reason I got an error that occasionally happens. I don't know why the data wouldn't have that. That's one you would expect. But I could do like what's the capital? Again there is no capital for London and I think the reason the currency is not there is this isn't a country, it's a city. So if I put a Like if I put the United States right here, see it says USD. So then it gets the currency, you know, since the it gives the capital here for London, there really isn't a capital. So. And you get more and more. I. You can ask it to give you an image and it automatically brings in an image related to the city. For some reason it doesn't find an image for London. Not sure why again, but you can start to see data types are pretty cool in that you could store whatever kind of data you want and it lives on this card and you can pull that data into Excel, you can work formulas on it and do a number of different things. So there's these two data types, there's also currency. And then the last one is really Power Bi and Power Query. You can see it says powered by Power bi. You can choose your own data types, you can build them like your organizational data types and so that will give you those cards and you can pull them into Excel any way you want. So I really like data types. Unfortunately, originally they were going to have like 300 data types and somehow they decided not to do it. They had like food things, calorie counts. They actually had one for. They're going to do one for Pokemon, like all kinds of different things. And for whatever reason it never made it out of beta. But there's data types. So the next one I'm going to come back to analyze data last. I think the next one I want to show you is the navigation pane. So let me know in the comments if you've used the navigation pane. I'd love to know. And also you know, if, if no, if you haven't shared where you're calling in from, please let us know. I know we've had a few people join the call. So I'm going to come here and go to the view window and what you should see is there's a new thing here under show called Navigation Pane. So check out this. I love Navigation Pane. Check out what just happened. I clicked on the navigation pane on the right hand side. I now have this table of contents. So it starts with each of my sheets, new Excel futures, new items reviewed, analyze data, data type, etc. I got a LinkedIn user from Canada and I can search for things. So before I search for something, when I say is, you can see each of those. So now let's look at the sheet I'm on notice. It's telling me there's two tables here. There's a one named Stock table and something named table 8. If I click here on the image function, it's showing me, hey, there's something in B2. You could see it right there. There's something in B4 and C4. B7 through C10 has data. If I click on it, it will take me to that part of the workbook. Also, I can rename the sheets from here. I could also delete sheets. Yeah, it's great. Really good to find stuff. Veronica and she says, wow, this could be cool to find stuff. That's exactly it. If we keep going, if you have something that's a picture, right? It tells me, oh, that's a picture. So I can go click on that picture. If something's an object, it tells you it's an object. If it's a table. The one thing I wish you could do, which you can't do yet, is I wish you could put comment here, like a description. Like, hey, this sheet is used for such and such. You know, this sheet here is used for such and such. So if you need to comment on your sheet, you're still going to need to have a table of contents. But in many places, depending on the workbook, this could replace your table of contents. So big fan of it. Encourage you to use it, especially when you're researching a worksheet. It's just one more way you can better understand what the prior person did. Speaking of the prior person and the new and a new employee, does anyone know what we call a broken spreadsheet? The new guy's problem. All right, so I'm going to show one other thing. Under analyze data, we've done the navigation pane, and then we'll run through a few more of these. The other thing I wanted to show on the. On the pane here is if you rightclick, you'll notice there's refresh settings. Could we move. That's a good question. I don't think we can move tab order, but let's try. No, you can't. That's what I thought. I didn't think you could, but I don't think I tried it. So Eric asked, could we move the tab order in it? And the answer, unfortunately, is nope, doesn't allow that. Wish it did. So the two things I would like is the ability to comment, move tab order. I can rename the sheet so I could change this to be, you know, course one, whatever. Right. And it renames it down here. All right, so the last thing I wanted to show on data types that I should have shown earlier and I'd missed is this refresh setting. This is the only place I know of in Excel where you have and it's only works for stocks. I can set it to automatically refresh every five minutes on file open or manually when I refresh it. So there's nowhere else where you could have something refreshing every five minutes. So that's, you know, similar. Google has a similar function and I think you could refresh it whenever you want. So they've added that. So right now on these new items, we've covered these first two and we've covered. Not the first two, sorry, we covered the navigation pane. We'll do analyze data last. Next we're going to go to import data from a picture. How many of you have ever used this function? If you have, let me know in the chat and let me just see. It's giving. There we go. Right. So I have this image right here. What I'm going to do is I'm going to highlight this image and I'm going to copy the image and I'm going to come up here to data and you'll notice there's an option to say, hey, I want to load from a picture. There's two options for a picture. You can do a picture from a file or picture from a clipboard. So I'm going to just see what happens if I take this picture here. If I click picture from clipboard, notice what happens on the right hand side. Right on the right hand side. It brought the picture in and it tried to interpret everything. It added some spaces. The reason is, is it. It doesn't know what to do with these little boxes. The filter, if I turn those filters off, off and took a picture, this would work better. It wouldn't be perfect. But you know what I can do is I can now go through and review it. It asks me, hey, is that right? Is that right? And I'm just gonna say yes, yes, yes. There's one I've noticed is wrong. So I'll scroll through them. Looks right. Power tools. I want this to be lowercase. We'll say so I'll do that. Maybe I want this one to be N slash A instead of nia. So I accept that. All right, so I'm going to call that good enough. I'm going to close it and I'm going to insert the data. We are unsure. There were still some items I didn't review. So you could see here pretty close. Right. It didn't get everything quite right. I got to bring the product type over Delete this. Now delete this row here. But pretty useful feature. Veronica saying she recently had to use this. I'm not surprised. All right, so that's import data from picture. The next one is. And again, this is something that Google used to have. How many of you have ever wanted to add an image? So let's see. We had someone joining us from Canada. So I'm just going to go find a picture on the web, going to search Canada on my other screen in Google, and I'll show you what I'm going to do. Let me bring it over. So are you guys seeing my. Are you guys seeing Google right now? Hopefully you are. I have this image here, so I'm going to click on it under images. And if you right click on an image, you can copy an image link. So what I'm going to do now is I'm going to come back into Excel and this is the image link I copied. I'm just going to put it right here for a minute. That's the image link. So if I come down here and type equals image, open parentheses, put a quote around it, quote at the end. That's a really long link. Oh, didn't know there was more than 8,000 characters in that link. So it's not liking that link. So we'll pick a different link. Yes, I get it. You don't like that. All right, let's just delete this one. Okay, there we go. Delete that. I'll. I'll show you here. Here you can see it. So you see image. And then it just needs the web page and it will automatically pull the image into Excel. So there's an image there, and you can read, you can readjust the size of the image however you want. Right here under the. The list of new items that I have, I've pulled in images for each of the different new things from different websites. So you can, you can see there are different images. These are all formulas. Yes, exactly. It's a function called Image. If you come here, how the function works is it has one required argument, the source. That's the U URL link you want to import. Then it has three optional arguments you can put in an alternate text if you want. You can add, you can tell it what you want the height and width to be in pixels. All right, so that's relatively new. So if we come back here, we've now covered image function. And in a minute here, we're going to show a couple new formulas we've talked about. Import, picture. So the last Two I want to do. I'm going to do value preview, T tip and then I'll briefly show analyze data. All right, so how many of you ever use F9 to help you audit a formula? How many have ever done this right here where you come in, you highlight a section, you hit F9 and it tells you it's false. And then you forget to hit escape and it gets hard coded into your formula. I don't know if anyone's ever done that. I do that all the time. So I'm using this if example. Not that you should write this formula because but I think it works well for the example. So notice if I hover here, it gives me the answer right there. It's now a preview. I don't have to hit F9. It tells me it's false if I hover this whole thing. See, I think I need to go to there. Let's see. So see I can highlight each of these and the, the right now it's going to tell me false. And so for each one of these it would tell me oh hey, that one's true. It is greater than you know that that number. And so you can see if something's true or false just by going through it. And what it does is it allows you to audit it. See, if I highlight the whole thing, it tells me oh hey, the answer's A right? If I highlight this whole thing, it's going to tell me it's B. She tell me it's B minus. And so what that allows you to do is take sections of it. Now you can see it's really tiny here. I wish I had a way to enlarge it. But you can highlight any section of a function and have it give you the preview so you don't have to hit F9 and deal with the forgetting to hit escape. And then you're like oh crap, I didn't have that formula in another cell. I got to rewrite that section. That took me a while to rewrite. So that's preview tool tip. The last one I want to show is analyze data. So you know, many of you have probably analyzed your data with generative AI. Love to know in the chat if any of you have used either a cloud AI or chat GPT to analyze data. But you know, we also have co pilot now but in Microsoft there's a built in function to analyze your data. So let me sec. I was accidentally on two sheets. Let me get on just one. Come to home here. Hey. Oh, it's still okay. Let me why is it doing that? I don't know. That's really weird. Hold on one second. Oh, that's why I'm still in the formula. There we go. So if I come to analyze data, there's this part right here called analyze data. Right. So let me click analyze data and it automatically looks at the data and discovers some insights. So it's showing an insight price by product category and product name. We think that makes sense. If I agree, I click right here. Voila. Automatic pivot table. Come down here. Oh, I. I don't really like that one. I love pie charts. So I'll click on that one. And so now it did the second suggestion notice with the pie chart. It gave me the data that it used for that pie chart. And so you'll see this analyzed data is its own section. I can also ask it questions. So I can maybe ask the data, what is the max and min price for any product category? See how it does with being cap locks and things. So there it goes. It gave me each category and gave me the max and the min price. So big eye Yasher and so you can ask it questions. I encourage you to use it as maybe a place to start. Sometimes when you got a new set of data, it even gives you some suggested questions and you can give it feedback and it will learn from your feedback. So it's using AI. It's been there for years. This will get better. So it's just one more way to analyze your data. All right, so now we have about another 20, 25 minutes left. I'm going to tell an Excel joke and see if anyone has any questions on anything we've covered so far. So let's see. Does anyone know what the. What. What did the Vlookup say on his online dating profile? Any ideas? What did the Vlookup say on his online dating profile? Seeking an approximate match. It's true. All right, so next file here I want to go over something in dynamic arrays. Just a couple basic formulas. So how many have used the text after text before our text split function? If you've used it, let me know in the comments. I'd love to know. And I haven't any comments for a while, so I'm assuming everybody's still awake. Veronica has. All right, so Veronica's familiar with them. How about anyone else not getting a response, so I'm going to assume no. So text before, text after and text split. Basically replace needing to use left mid right in most situations, not all. So we're going to walk through how textafter works. I'm going to enlarge this a little bit and we'll walk through an example. Actually, I'm going to leave the formula there. I'm going to delete this. So this first one here in text after, I want to get the last name. I want to get everything after the space before the formula. Text after Paulo. Good. I'm glad to hear this is new to you. The old way I would have wrote this formula is something like this, with this like a mid and a find and a length. So I'm taking. I'm finding the first space, counting how long that is, subtracting that from the total length so I can get the last name. Well, now all I have to do is use this text after formula. So this is what the text after looks like. Equals text after. You have your text, a delimiter, the instance number, a match mode, what to do if it's. If not found in a match end. So only the first two are required. So let's walk through an example. Let's say text after. We'll type that here, open parentheses. The first thing you can see is right here is text. So next is going to be delimiter. So I'm going to put a comma in this case. I want my delimiter to be a space and I'm just going to close it and it gave me the last name. One other thing I can do. If you're not familiar with how dynamic arrays work, let's say I want to highlight the whole range. It just did all of them at once. All right, good. New to. New to Christina. So. So we're learning something here. All right, so that is at its simplest form, that's text after. But let's say for some reason I had a space in the front of. We'll just say, you know, say it's on all of these. We're just going to do it for the first three for right now. So question would be, what do I do? Well, that obviously didn't work. It gave me the full name. I didn't want the full name. Okay. If I know a space is there now we can use another function. So now I. The. The next one is instance number. Well, I don't want the first space because I know somebody put a space in front of this. So I'm going to tell it to go after the second space. Or I could say do negative one. So count backwards and do. The first space gives me the last name. So that's what instance number does. Then next I can make something case sensitive if I want. So Apple our airlines capitalized would be difference than airlines lowercase right? All those examples here I'm just going to say case insensitive because I don't care about that. Match mode is hey, do you want it to match all the way to the end? So at the very end you want to count if there's a space at the end, yes or no, we'll just say don't match to the end. And then one of my favorite things is this, if not found, this is very common now is instead of have to wrap something in an if error if it's not found, it's just the last argument in the formula I can just say not found. So I'm going to real quick just kind of undo all this and you can see here's another example where in this case what we did. So what I want you to notice is notice how there's spaces at the end. So what I did with this one is I said hey, text after H19. Then I said, hey, give me this first space said instance number one match mode there which is, you know, case insensitive. And then I said hey, match it all the way to the end. So I did each of these different arguments, right, match to the end. And even though there's a bunch of spaces at the end and there's not one there, it's smart enough to say, oh, you only want the word in there. Good. I'm seeing this is new to most people. That's what I. That's. I was hoping this would be something that would be new for at least some of you. So let's go through one more example here. Next we have text before. You could probably guess I'll be pretty quick on text before. The only difference between text after and text before is you're telling it. What do you want before the delimiter? So in this case I'm saying hey, I want everything before the last space or if I Change this to A1, the first space. Now I get the same answer either way here in this case, you know, I wrote text after I got the population text before I got the city. Now what I want to show is let's bring those together because this is text before and text after the exact same thing with the only difference being in the name Tom and the fact that one takes everything before the delimiter one takes everything after. They have all the same arguments. So let's take an example here and just let me enlarge this so everybody can See it? Let's say I want to get just the FP and a guy from the website here. So we're going to start. And I'm going to start with text after foreign. We're going to highlight our text. We're going to put in a period, going to close it. And so we can see. I got text after. But let's say I don't want the dot, right? I just want the middle part. Well, I can wrap this in a text before. And so here's my first part, which right now all I have is the FPNA guy dot com. So I can say, now give me everything before the period. And what did. Oh, let's see. What did I do wrong? Why is this not working? That's my text. And then this should be my delimiter. And then I need a double quote. Yeah, there we go. All right. So now you see, I got the FPNA guy. If I drag this down. Oops not. Copy it down. Drag it down. You can see, I get Tesla, I get cnn. So that's how you can use text before and text after together. Want to show one more here called text split. So the idea between text split is you can now split things by both row and column. So what I can do. And let me just. Let me actually I'm going to use this example number two, because I. I really like this example. So right here I have Boston, Fredericksburg, Ranchera, Toronto. Notice I just split it all out with one formula into all those cells. So this is the whole idea of dynamic. But I want to show the formula. So let's see how that works. So text split. What you do is you highlight your text and then notice there's a column delimiter and there's a row delimiter. If I scroll up, scroll up, you'll see it. So text split has column delimiter, row delimiter. Then you have this ignore empty match mode and what's called pad width, which we won't really talk about. Well, so what we have is if you want things to go into a column, you tell it one delimiter. If you want it to go to a row, you tell it a different delimiter. If it's empty, you tell it that. And then you can add a match mode. So let's watch what happens here. So I want to split Boston and its population with the space. So I put quotes around it and it's going to split by space. I could close it right now, but it's not going to work because it doesn't know how to split the rest of it. So it split the first. I mean, it gives me something, but it didn't give me what I want. So the second thing I'm going to see is Fredericksburg has a comma. So I want it to go to a new row every time it has a comma. So that's my second delimiter. So we see comma after the text, quotes with a space comma. And then we have the quotes again here with this comma. And now it spilled that all out. So if I come down. But what if there's a big, huge space in between? How would it work? So we'll walk through how it would work. But first, just see what happens if I put a big space in here. See, it tried to add something for all those spaces. So that got put all the way down there because it added one column for every space. So how do we fix that? Well, you'll notice there's the argument of what it should do when it's empty, which, what's that Means if there's, if there's more than one blank. Because my delimiter is one blank. So if there's more than one blank, it's going to ignore all this. So by adding true right here, it's going to be smart enough to go, hey, that blank, that, those spaces, I'm going to ignore everything but the first space. And so it will split it into rows and columns, Lots of different areas you can use that. But those are the three new text functions. They're part of what's called dynamic arrays. So the idea behind dynamic arrays is the idea that they spill. So I'll just write a formula here. Don't know if anyone's used sequence, but we'll say I need 10 row. I need 12 rows of data, one column wide. Gonna start with the number one and increase it by one and watch what happens. Right. That formula is in 12 cells. If I change this to two, it's in two cells that's dynamic. It can automatically resize. And so Microsoft now has about 25 dynamic arrays. If you don't count lambda and let. And so here's a list of some of them. So there's quite a few. And I'll show a couple examples of some work I've done with them. So you can see them here. There's one called VStack. Unique text before text after sort, sort by sequence. There's one for filtering. There's ones where you can drop certain records, do all kinds of things. Two Cool examples of what you can do in dynamic arrays that we won't walk through how to do them today. But if you haven't been using dynamic arrays, I want you to get an appreciation for how powerful they are. And then I'm going to open it up and you guys can put any questions you have in the chat here. So let me show you two examples. First one, I have this sensitivity table here, just a simple sensitivity that says, hey, if the price is $8, the COGS is 550. So that's $3 or 250. My volume is 6,000. My gross profit is $15 higher. My gross profit is the conditional formatting. The the bigger it gets, right? So 980 and 550 would be my biggest grand gross profit here. I would lose money. My cost is 830 and I'm at $8. So now what's cool is I've built this all dynamic. Let's say I really want to see what it is for, I don't know, 20 price increments. Everything automatically adjusted with one formula. It's what I like to call Excel magic. You'll notice there's now 20 of these. The formatting changed. Well, may the file really only want five. I have five. Maybe I want this to be 10, you know, and I could change any of the other inputs. This isn't really anything new, but I could change the units sold. I could change my worst case price, I could change, you know, my base cogs. I have these all linked to tables. I could change my increments. Maybe I wanted every 25 cents and cogs every 5 cents. And I can start seeing the impact. The reason this works is these formulas. They're dynamic formulas. Putting a pound at the end of something makes them dynamic. So if you learn how dynamic arrays work, you can change the way you work in Excel. You'll no longer work on one cell. You can have formulas, one formula that spills into thousands of cells. The last one here, I wouldn't recommend writing this one. This is more fun to show off what can be done. I had to learn this from somebody else. I would have never in a million years figured out how to do this. Here's the formula and you can see there's like, you know, these vstacks and H stacks, which are new, they stack things. What I want to show is what it does. So notice here I have a table, sales rep, amount sold and month notice. I have one formula, column headers every single record and the total. You can see right here, the total matches 594. 594. That's one formula. Now if I come over here and delete this. Still one formula. Unfortunately, the conditional for the formatting didn't stay with it, but you could see it eliminated. Sales rep 12. I can eliminate sales rep 13. Now I could add sales rep one here and say, oh, maybe it's their new month. They sold $450,000. You can see the numbers adjusted. That is the power of dynamic arrays. And there is a book if you really want to learn dynamic arrays right now. This is the book I would encourage people to read. Called up up an array. Yes, an entire book on arrays. I am a total nerd if that one doesn't interest you. Mike Girvin Excel is fun will be releasing one in the coming year on dynamic arrays. So I'm going to stop here, stop sharing my screen for a minute and just open it up to a couple things please put in the chat. You know, if there's something you liked from today, a question you have something you'd like me to do in a future session and I think we'll do in the future is we'll do it on a different. We'll change platforms. We'll either use my teams or something else. We'll figure it out so that you guys can ask questions as we go through this because I think that makes more sense for Excel. But please, any questions you have for me and I'll leave it here for a few minutes for anyone to ask questions. I'm not getting any questions, which makes me think there isn't any. But we'll give it another minute or so just in case. What array formula do I use most? That's a good question. I am pro. It's a top tossup. I probably use Unique the most. That's probably my favorite sequence. I use a lot as well. Xlookup. Those are probably the three I use the most. Understand Veronica I totally get wanting to play with them. So Eric, my question for you. Have you used any array formulas yet or are you familiar with them? David is a big transpose fan. Yeah, I use Transpose a fair amount. David, that's a. That's a nice one to transpose data. So there's some. There's some really fun ones with some of the like let me just show one other that I think you guys will like since we have a minute. Eric likes Unique. So one other that's pretty cool is things like to call it doesn't make sense in this case but let's just Say I want to do this. Notice it just took all those. Call. Took all those columns and put them in one column. I could also. I could accomplish the same thing. Let's say I want to stack, for whatever reason, you know, multiple columns together. I can do that now. I can horizontally stack them. You know, other things. Let's say I wanted to stack it, but for whatever reason, there is a total. So I'm going to drop the last row. So the last row just got dropped. Maybe I want to drop the last. You know, doesn't make sense here. But 30 rows. So those are all different things with dynamic. And there's a number of other new formulas. Yes, I'll. I'll send out the file. Ooh. For what processes do you use Power Query and Power Pivot for? So if anyone's used SQL to transform, load, clean data, get it out of a database, that's what Power Query does. You use Power Query to help fix your data, basically is the best way to put. To clean up your data, to combine your data together. And it automates those tasks so you can run them again and again. So it's great for loading things from the web, from text files, from SharePoint folders. Power pivot. You're really using that to build a data model so that you can report multiple different things in one pivot table. Most people now just use Power bi. It has DAX in it. So that's the formula language of Power bi for Power Pivot. All right, I see one asking for the file. Yeah, we'll. We'll get a file out to those that attended. We're. We're basically at the top of the hour, so if there's not any more questions, I will thank everybody for joining. We really appreciate you carving out a few minutes from your. Your day, your morning, your evening, depending on where you're at, to join us. So thank you and please let me know what you'd like to see in future Excel hours, because we'll cover different material and so just let me know what would be helpful. Thank you, everyone. Thank you, Veronica. Appreciate the comment. Thank you, Lauren. Yeah, just a reminder, ESG tomorrow, if anyone's available, we'll have an event on that. So. So you're welcome, Eric. Bye, Paulo. All righty. We'll get the file out to you maybe in a day or two. If you don't get it, feel free to email me. But again, thanks everyone and have a great night or day, depending on where you're at.