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

Right. Welcome. If you're out there, we'll get started in a few minutes. We start at the top of the hour, but I thought I'd jump on. So any of you that are out there, welcome. Would love to know where you're coming from. If you just want to share that in the chat, the comments as we wait to get started and you'll notice here on the screen there is a link to download the Excel file. Hey Wayne, you're welcome. Thanks for joining us. I'm glad you're here. So again, if you want to download the Excel file, you can click on the QR code or you can go to my website, the fpna guy.com go to the resources link and click on Excel. And the first link will have today's file. Now the student file, the master file will be sent out yesterday, which is the file I will be using today. Welcome Bri from Arizona. Welcome Wayne from San Diego. Really excited to have you here. One other thing I just want to share with everybody is for those who are interested, I have a 100 free FPA community. It's called the FPA Hub and you can join it at the fpahub.com we have nearly 1500 members. We have a lot of free events. More and more events that I host will be moving to that platform over time. Still be free, but they'll just be available there. So would love for you to join us there. Really easy to register. Just takes a few minutes and you can meet people all over the world in that community and ask questions, see videos, post comments, chats. A lot, a lot of different free resources. So just want to let people know about that and really excited to have you here. As we're waiting to get started, would love for you to just keep letting me know where you're coming from. Looks like we have about 10 people out there right now is what I'm showing. We may have a few more on YouTube and LinkedIn, but in the webinar at the moment and we'll start in another minute or two. Just want to give it a few minutes for people to join. So please let me know where you're coming from in the chat. Great. I see we have Joanna from Lincoln, we have Brie from Arizona, we have Wayne from San Diego. So thank you, all of you for joining me. Excited to see you out there. Always fun to see the different location and places we get. Michael from Virginia, thanks for joining. As I mentioned, we'll get started another minute or two. If anyone has a good joke, please Feel free to put it in the comments and if you've listened to a prior session of mine, all my jokes are probably old, so I'm not sure they'll be good. But we'll, we'll tell a few. What did the. What did the mathematician. Actually, no, until that one. Let's see what, why, we'll go with this one. Why was the spreadsheet constipated? See if anyone knows the answer to that one. It couldn't budget. All right, there you go. There, there, there's my joke to get us started. We will keep going here but you know, feel free to put your comments in the chat. We're going to try something, there's a new feature to just kind of put comments to the stage. So from time to time we'll do that and bring them up. But please let me know what questions you have throughout the event, where you're coming from. You can see we have Wayne, we have Bri, Johanna, Michael. We'll see who else joins us. All right, so this session today, this is one I posted before. Few little differences, but I did it about a year ago. We're going to focus on dynamic arrays and I'd love to know if anybody has used dynamic arrays. It's on the call, you know, please let me know. And I see LinkedIn user likes the humor. Glad you enjoy it. You'll see I'm trying the show comments. You'll see those kind of come up on the screen. I may take them off. It's a new feature, just kind of experimenting. So what we're going to talk about, as I mentioned, our dynamic race. So let's jump into it. I'd love to know in the chat if anyone's using dynamic arrays. Here's a little bit about me. Always like to start with this. So you can see here I have a beautiful picture of my wife catching a fish, my daughter Salt Lake City where I live. Me out hiking on a 50 mile hike probably 20 years ago. So I was young then. I love hiking and I love running. I try to run every day. Podcast. You'll see. I have the 3fpa tomorrow future finance Financial Modelers corner. If you're not listening to them, give them a listen. If you like them and you haven't left a rating or a review on Apple or Spotify, please do so. It really helps me grow the shows. It makes me know that people are enjoying listening and that they like it. So would love it. Bri says I have not used dynamic arrays yet. I see Wayne not often Totally understand for co workers there are. You have to find balance. We'll talk a little bit about that. And I see someone else who said yes, so looks like we have a mixed audience. Some have not used them, some are familiar with them but don't use them much and that's going to vary. Another one love sequence, unique sort. So right, we got a, we got a wide range out there of different people saying different things which are great. I'm going to just remind people real quick. I'm going to put this up for a few minutes. Banner. Anyone who wants to join the community, feel free. All right, so here's kind of the, the agenda. I just want to share a few slides but what I really see is the abilities of dynamic arrays. They can change the way you work. They really are quite amazing. But you know, you just have to know when, where, how to use them. My key thing is even if you can't use them in your models like Wayne here mentioned, it's important to understand them because somebody will use them and if you don't understand them, you'll be locked and over time they will become more and more the default. It may take. It's going to take years, just like anything in Microsoft. You know, if I ask how many people know Power Query, the majority still don't know Power Query yet. It's been out for well over a decade, I think 13 years now. 12. 13 years. So it's sometimes very slow. We'll do a brief overview of them and we're not going to build the sensitivity today. Today I changed it up a little bit. We're going to do some additional formulas. I forgot to update the agenda. And then we'll do a wrap up in key takeaways. So here's a couple just kind of brief quotes I want to share and you guys will all get a copy of the deck after. For anyone that attended through streamyard, I have your email address. If you want the deck and you are on LinkedIn you can email me for it and I'll send it to you. So Microsoft Excel dynamic arrays are an Excel 365 feature that allows a singular if I could read a single formula to return multiple values in a grid, that really is a huge upgrade to everything with single value outside of the occasional. If some people remember, I'll date myself the curly brackets when you hit Control Shift enter. That was the only way you could do some arrays and they weren't dynamic. These ones can automatically expand or contract based on the inputs that are put into them. Dynamic arrays are the biggest change to Excel formulas in years, maybe the biggest change ever, said Dave Bruns of Excel Jet. Absolutely love his website. If you want a great website, if you ever need to find a formula, his website's fabulous. You know, obviously LLMs and AI is really, really good now too, but I really like the work he's done there. All right, so I'm just going to run through a list of some of the benefits, just to talk about the pros and cons. Then we'll jump into Excel for pretty much the rest of the time. So they do make writing formulas simpler. Great example is unique being able to easily create a unique list. That wasn't so easy in Excel prior to dynamic rays. It could be done. It's not hard, but it wasn't an easy formula. So they're simpler to understand. They dynamically update, much like an Excel table. Ironically enough, they don't work in Excel tables. There are some workarounds, but they don't really work in Excel tables. They improve your overall calculation speed because the formula lives in one cell. So if you have a vlookup where you input 100,000 values into your lookup, that's all one formula versus the old way of hey, I did one, I dragged it down. I got 100,000 formul. That's going to improve the speed. New functions this is one of the things I love most, constantly coming up with new functions. This morning when I was updating the file, I put together a new list of all the dynamic array formulas slash functions. You could argue I left a few out that I don't know they're dynamic. They. They are dynamic, but I didn't necessarily include them in the dynamic ray list. I'll show you that here in a minute. And then they can reduce errors. Just because you have less, you have less formulas to maintain. Now here's the. Here's the downside challenges, lest we think it's all rainbows and unicorns, as I like to say, compatibility issues. If you still have people using Excel 2016, Excel 2013, they're not on 365 or 2021, you're going to have backward compatibility issues. Tables, as I mentioned earlier, they don't by default work in tables. There are some workarounds to use some formulas there. We won't get into any of that. Financial models. Corkscrews. Corkscrews are really hard to build dynamically with lambda, basically programming or somebody else's lambda, which you could use and not program. You can get around it but I don't think they're there yet to use for full financial models. And that's what most my guests say. Everybody has a different opinion on that one. That that's my take, for whatever it's worth. Learning curve, right? There is definitely a learning curve. It, it really changes the way you think about and work in Excel. And so it takes time, it takes a little time to get used to. And then there's a challenge with formatting and I'll show you what I mean when we get into a workbook. But basically the formatting from the first cell where you wrote the formula does not spill with the formula. So if you're adjusting your size, you're going to have to go through and reformat it every time, which is one one of the pains of dynamic arrays. There are some ways around that, but again, using conditional formatting, it doesn't on its own do that. All right, so now I'm going to open up the Excel file. Everybody should see that. Like I said, if you don't have it, you can go to my website, the fpnaguy.com the resources tab. Excel is the resource. And there the first one you'll see a link to drop to, not a link to download the student file. All righty. So I'm going to show some simple examples. And funny thing is I built this example, I don't know why I accidentally created a sheet. Let me delete that real quick. I built this example here. When dynamic arrays first come out, I would, I really need to go through and kind of rebuild it. It would probably be much cleaner and easier now. But here's the basic idea. Very, very simple model that we have with the general assumptions, some years of investment, we have revenue, ongoing expenses, etc, cumulative gross profit, cumulative present value. And then down at the bottom we have some key metrics like net present value, internal rate of return, etc, right? Nothing surprising. Very, very simple model. But what I can do here is just to show the power of dynamic rays, I change that to five, the model by default change to five. I, you know, I change this, the model adjusts, I change this. And so I can change the years and anything in the model, which many of these we could do before, but it's really the fact it's dynamic and if I change years, I don't have to drag any formulas. It's 100% dynamic there. So that's what I mean. You know that by being dynamic, by being able to adjust and have your, your cells spill. Let me show you two other examples I have here a list of 11 sales reps right here. I'm going to put this one as month two one 2022 and what I've done here is I've written one formula. Now the formula is complex so we're not going to go over it today, but it's basically using three sorry if I could speak three dynamic formulas. A couple others it's using what's called vstack which vertically stacks stuff hstack hor horizontally stacks it a unique a sumifs another h stack here. And I'm able to take this table right here and recreate it here. Sure. Could I put it in a pivot table or there are other ways I could do that. I could now do a pivot pivot by this was think even created before then. But what's, what's cool with this these type of formulas and this type of thing is oh, I added cells rep 12 I put in an amount and it automatically updated. Notice as I mentioned the formatting didn't follow because I didn't use a conditional formatting. What I should have done is format a little further down and then have a conditional formatting that says hey, when you find the word total, format it accordingly. That's the usually the way around that. Alrighty, another one. This is one of my favorite. I've taught this several times in different sessions. I have it out on LinkedIn. This is a more simplified I think version than the latest one I'm using. But here's basically the idea. I have the sensitivity analysis and I want to adjust the units sold. Fine, that's normal, it all updates. But let's say I want to look at what happens over 15 price increments. Now I can see okay from 8:30 to 11:10 what happens. What if I only want 5 cost of goods sold increments or maybe I want 20 it all dynamically adjusts. Conditional formatting is there and so it's a great way to build sensitivity tables. There's some cases where you could use it in place of a data table with with it. And the reality is this is basically built. This whole thing can be built with I think three, three functions. Every one of these drop downs can be built with the sequence. This is built with the sequence. This is built with the sequence. So basically 1, 1 new function and then a function where we're subtracting our groups from each other. We're. We're going to get into several formula examples later but just want to show some of the things you can do. And you can get much more complex than this. You know, if you want some great people to follow that, just show amazing stuff you can do that. I my mind is always blown dim early. Mark Proctor does some really good stuff and there are others. Then what I've given you here is this worksheet has a list of all the formulas. Shruy, you're the first one that says you can't find the file. It's on my website. You should be able to find it under resources. It's the fpna guy.com and then just click on the first button under Excel under the resources. If somebody has the exact link, if they just want to throw it in the comments as I present be appreciated. But it should be out there. If anyone else can't find it, let me know. But I'm I put it out there this morning. All right, so there's a number of different links where you can go some Microsoft resources, Excel jet. Here's a book that has a lot of the earlier formulas and there's here's a basic list of the formulas that are available for dynamic arrays. Hey, thanks Brie, for putting that out there. So you should be able to get it from right there. Shruti. All right, so that's a list. There's ones like choose columns to choose certain columns, choosing rows, dropping something group by which is a great new function really love group by pivot by xlookup and so many others. All right, I'm just going to pause real quick here. Let me just show people where you can get the file. Give me one minute here. I've heard a couple people saying they can't find it. And just as a reminder, this is why I always encourage people to join early. Generally I won't, I won't do this just to so I don't slow it down for those who have it. But I will show here real quick. Go to my website. Come over here to Resources. Click on Excel files right there or today click on it and download it. Simple as that. Okay, we're gonna go back. Alrighty. So I when I talk about high how dynamic arrays work, I'm really giving a very basic I think it makes it easier for people to understand we're not going to get into some tech technical things. There's broadcasting there's what's called pairwise lifting. Another a number of other things that if you're really going to go deep with dynamic arrays and start to use let and lambda you want to learn about I would recommend for that Mark Proctor, he has a great course off Excel, off the grid that goes way beyond anything you'll see today or anything I talk about in my dynamic arrays course. If you're looking for a great intro, getting started, some basics, these, these type of things will get you there. But just, just be aware there are some things where technically the definitions I'm giving are more to make it easy to understand. They may not always be technically right, but let's start here with working on this. So over here I have a number range. I have the numbers 1, 2, 3, 4, 5 and 6. Here I've highlighted that entire range and you can see it's dynamic in the grid. Previously it would not have highlighted all this. So what you're going to see is you see this little blue box around it as I highlight these cells here, the 1, the 2, the 3, 4, 5, 6. Previously, how it would have worked if, you know, right here, here's non dynamic. I've referenced that whole range, but I put an at symbol at the front which will make it work how it used to. And you see I only get the number one. It doesn't spill. So that's the first thing to be aware of is the spilling feature, which we'll talk quite a bit about. Another thing that, that, that's cool is it can operate on multiple cells. So let's, let's take an example what I mean by that. So right here, right now, I'm operating on one cell. I've said hey, text before B22, put it in quotes and it drags down many formulas. The input value is supposed to be a scalar value, an individual value. But with dynamic arrays, the way the engine works, it will now do what it calls broadcasting. But basically it allows you to put in multiple values within a, within your lookup value. So we're going to put all those in. I'm going to delete the ones below and notice. All right, I didn't quite get the whole list. I forgot the first one. So let me just fix that. I'm going to Change this to B22, but notice what it did there. Again, it spilled all the way down. And the formula lives in the one cell. So you'll notice it has five rows that it spilled down, but it gives an NA in all but the first one. All right, so now let's come down here. Dynamic array formulas can adjust size based on inputs. So here I have this array, you can see five rows by six columns and we'll walk through the function later. I just want to show the examples you'll get to practice here in a few minutes. Let's say I only want two rows. It just changed to two rows. I want three rows and four columns. So I've dynamically changed that by changing the inputs that go into the function. And then the last thing I want to show is we can reference dynamic array functions. So let me delete this here. Notice if I reference this right here, which was dynamic, and I only reference that first cell, that's all I get. If I reference this cell here, that's all I get. If I reference three of them, it's going to spill. You can see it's three cells, but it doesn't give me the whole range. I could come up here and highlight the entire range. But notice if I do that, what it does, it automatically puts this hashtag symbol at the end. That tells it to reference the entire array. Whatever that prior array is, it's going to be referenced down below. So it makes it really easy to reference dynamic formulas. I could do that wherever, right? I could reference this whole range here. And again, you'll notice the pound. So that's just a little bit of how they work at a very simplified level. All right, next one, there's a new error you get. Notice right here, it says hashtag spill. I'm going to make this a little larger. So I have this spill error. And notice I have these little dancing ants, kind of the blue around it, saying, hey, I should be able to go all the way down to row 23, but I can't. And the reason for that is there's data in the way. I. If I change, there's two things I could do. I could change the spill to only reference three rows. Now it works. Or if I wanted to reference more, we'll just say I want to reference 30. I would have to move this. I can delete it or drag it out of the way. And now it works. So that's another thing you have to get used to with dynamic arrays is making sure you have enough room on your workbook for them to expand and contract. Otherwise, you'll end up with spill errors, which could break your entire model downstream. So I'm going to pause for one second here. Any questions from anybody so far, just let me know in the chat if anyone has any questions. All right, not seeing any questions yet. If they come through, I'll address them, but we'll just keep going here. So let's take another example. Let's do a math operation, and we could do multiple different ones, but I have this first row with five numbers, second row with some numbers. These are all just random numbers, you know, Here I have a formula down here. So actually I have six here, 13, whatever. I'm going to delete these ones for a moment. And what I want you to see is how we can do math operations. We're going to do adding, but subtract. We'll also do a subtracting. So if I did something like this and I said I want to add this plus this plus this, I get the answer. What I can't do though is I can't say, hey, sum, row one, row two, row three. And it gives me the total sum. It's saying, hey, you want everything in that range. So I could also, you know, I could subtract this if I wanted. And it's going to work because I'm referencing each of them individually. And actually, no, sorry, it takes the first. I did something wrong there. So you can see issue. But let me show you. Basically, what Microsoft's done when you run into those type issues is it's particularly for summing things if you don't want to do something like that. There's a simple formula called by call. There's one also called by row. And what they allow you to do is they allow you to perform an operation on each grouping. So I just have to highlight this whole section. I say sum and I close it and it now sum the totals. I could also say things like give me the average of, you know, each row. And now it's giving me the average of the 12, 0 and the 1. I could say max, whatever. So there's a by call. And what it does, it takes each column or each row in your arrays and sums them or performs whatever math operation you give it. So that gets a little more complex than it used to be. But that's the easiest way to, to do things like that. Let me show just one or two more examples. Then we'll jump into some formulas and walk through them. So first one here, let's look at a multiplication example. So I have these two ranges. I use the sequence which we will walk through that formula right now, if you're not familiar with it, you're probably like, what's the sequence? But I have these two, two ranges and I just multiply them. I do B6 pound times C6 pound. So now if I add a value here, I add value 16, you can see it added it, but it gave me an NA here because it's saying 30 times nothing is NA. I can't answer that because the arrays are not the same size. So until I fix this one, it's not going to work. Formula lives in one cell. It just multiplied all of them. It will automatically adjust as I adjust this. Now here's another simple example. What if I want to multiply kind of different size arrays? I have one here with five and I have this one in the rows. This one column I have here with three across the columns, I have five. I take each of these and I times them by each other. And what it's going to do here is it's automatically going to assume, hey, this is zero, this is zero. Since I don't have a number, you notice to put all zeros at the bottom because it needs, in theory, it needs arrays of the same size, right? For in order to do a 5x5 multiplication, you need five values in each of your arrays. So it's making assumptions. Nathan, when you say, can you reference the array length? What. What do you mean by that? You can reference an entire array. But is so I could do this. Are you saying can I count the length of an array? If I wanted to count, like say if I wanted to count an array, I would come here and do this, and now I get a count. If I change this, I Change this to 12, the count changes to 12. So I'm not sure if that's what you mean, Nathan, but yes, I could figure out what the, the, the number of columns or rows or whatever it's spilling into by doing a simple count of those cells and having it be with the pound signal. It will give me the number of cells as far as the length. You can count the length of any cell, correct, Craig? Yeah, it's just exactly. It's matrix multiplication. You need the same numbers in 1 and 2. Otherwise you're either going to get an NA or you're going to get a 0, depending on the situation. Yes, and we'll show an example of that. Nathan. The first reference, for example, in the sequence. I do this all the time. I'll just show a quick example. I do a count a right here. So I count some kind of list that's dynamic so that my rows will be dynamic. I use that all the time. So I could reference this one here, saying I always want it to be the same size as this one. Any of any single one of these. In a dynamic, you can reference another formula function, whatever you want in every single argument, just like you can with any other argument in Excel. All right, so let's just look at one More example, I'm gonna do a simple vlookup. I prefer xlookup, but what I want you to just see is a couple things. So right here I can now reference multiple columns in my column index number. So here I had this simple table. I want to get all three scores. I could put an array in curly brackets if I want and get all three. I could also do some choose calls. There's some other ways around that, but I don't have to limit it to just bringing back one call value from one column. I can reference multiple. The other thing I can do is notice here, if I want right now I have one cell referenced, I drag it down again. Older functions now with the new calculation engine, even though they're designed to only take one value in your lookup, right? It says lookup value, not values. You can give it multiple values and it will adjust and give you the full list. So that's the basics of how dynamic arrays work at a very high level. What will work for 90 of what you're doing, obviously you can go much, much deeper than that. So let's run in and look. Let's go ahead and look at a few functions here. One of my favorite dynamic functions is the unique function. Your, your sheet should be empty. I'm going to go ahead and delete these and we'll do them together. And I may let you try one or two on your own. But the old way, most people, and I'm guess some of you are probably still doing this. I need to get a unique list. I could highlight this and throw it into a pivot table. I could copy it here, come over here, paste it so I don't mess with my original list. Come up, the data come up to remove duplicates. And now I have my unique list. I can easily count it and say, oh, there's seven. But right if I come down here and I add truthy to this list, now all of a sudden that's no longer updated. So let's see how it works. I'm going to go ahead and delete this for now. Let's see how it works with the unique function. Here you'll see the argument for the unique function. It basically has three arguments. Let me make this just a little bit bigger. Let me fix one thing. All right, so it has the array, the by column, and what's called exactly once. These are the three array by call and exactly once are the three arguments. So let's go ahead and come in here. Going to type unique. And if I can spell, I Will actually type unique. So we're going to type unique. I want to get first names. I can do that. N notice it has a table name called classes 26. Probably because I've used this table a number of times and every time it appends a different number onto it. But I could just highlight the whole thing or I can do shift control down, or if I'm a mouse person, I can highlight the mouse. I get the same thing. It's going to give me the name of the table and the column. And that is all I have to do. I can hit enter. And I now have a unique list with Peter, Ron, Paul, Anders, Jill, Mike, and Bill. What if I wanted that unique list to be first name, last name? Go ahead and do it with me. We highlight both of them. And now I have a unique list of first and last name. So you'll notice Bill shows up twice now because there's a Bill Johnson and there's a Bill Smith, where if I do just first names, Bill shows up once because it's not looking at another column. It doesn't care that there's bills with different last names. All right, so that's the very basics. Now let's see what those optional arguments are all about. Let's go ahead and highlight this, the first optional argument we're not going to do anything with right now. I'll show it to you later. But it says true, return unique columns. False. You turn return unique rows. So you can return it by columns or rows. We'll show an example of that later. And then the second one, which is question two, I'm just going to go ahead and answer it right here is it. You'll notice question two says return the unique. Use the function to return the names that appear once and only once. So that is going to be true Returns items that appear exactly once versus a list of every distinct item. So if I type true here, let's see what happens. I get a calc error. The reason I get a calc error is every single name in this list is in the list more than once. So let's change that. Let's add Bree. Brie's with us today. We'll add Brie Martin, and she's taking dynamic arrays course. Right. All right, now she shows up as the only one because she's the only one in the list once. So you can do either of those. All right, I'm gonna sh. We'll keep going on this one and I'll do three. And then I'm going to let you do four on your own. I'll give you a minute. You can ask questions. So next, I like to use a unique with account A count A counts, text or values. The reason I don't use the count is I'm counting lists with text, so I need to use count A versus just a regular count. So we're going to do count A. We're going to wrap it in a unique. And so we're going to come here, we're going to do unique list of first names. But I could do the entire table and you know, it would give me the entire table because everybody's different. I could do just class, offerings, whatever. So we want just this right here and now. I'm going to close it. And it gave me a count of eight. If I take out Bri, it would give me a count of seven that I find that incredibly helpful when I'm trying to understand data and just get a quick idea how many unique values do I have if I wanted. Now try doing the same, but see if you can count where the. The. The items that appear only once. So you should be able to use this formula, add the optional argument and come up with the number one. Because we only have breathe. So I just want everybody to practice that on their own. We'll give you just a minute. Shouldn't take long. Just a minute here. Let me know in the chat if you're able to accomplish it if you had any problems. And after a minute, I'll just walk you through it real quick. But I want you guys to practice some things on your own because it's the only way you learn this stuff. Nobody wants to listen to me blab for an hour at least. I don't. All right, if you could just throw in the chat once you finished it, just let me know, put it in the comments, say you got it. Or if you have a question, I'll go about another 30 seconds and then I'll walk you through it. All right, I'm going to go ahead and walk through it. All you need to do here is it's count A unique. Glad you got it, Amy. Perfect. I start to see people coming in, let me know they got it, and then I'm going to highlight this. And I want to get just the table. So don't want the entire column. I want just the values I'm looking at. Don't need to worry about that first argument. So we'll come over to the second argument and oops, I put a period. That's not what I wanted. I want A comma, second argument. Return items that appear exactly once. Make this true. And you got to put close both functions, the count A and the unique. And you should get the count of one. Keep in mind that if it's an optional argument, it will have a default value. So this one here, the default value is false. So if I, you know, just took this out and did nothing, I get the full list. Or if I type the word false, I get the full list. So just one thing to keep in mind. Pretty much all these functions have default arguments like Vlookup. If you don't add, I think it's false. You get an approximate match and you often get answers returned you don't want. So that's one thing keep in mind. Good, I got a few people that got it. Perfect. Let me show one other thing here. Notice this list since it's dynamic. What if I come up here and say I want to sort this list. So let's go to data. Let me click on sort here and see what happens. Continue with the current selection. You can't change part of an array, so you can't use the sort function on that list. So what are you going to do? Well, that's why Microsoft was nice enough to create a sort function. You can throw sort on the front of it. And now I've sorted the list alphabetically. Sort. We, we won't go over in detail here on sort, but it also has its own optional arguments. So you can sort things by different columns and you can change the sort order. So if I wanted this to be in descending order, I could add a negative one. And now it goes from Ron to Anders. So unique is great on its own. Unique becomes really powerful when you start using it with other functions. Account a a sort, what's called sort by filter, etc. There are a lot of different functions you can use it with to increase the power of of unique. Last example I want to show is number five here. So let's come here. Go unique. Let's say I have this list right here. Notice by default the spills across columns. I could come in here, say faults and it does that. So that's your kind of your by call. If you say true, it's going to do the same, but you can also spill it by. But you'll notice here. So you can, you can spill it as well by columns or rows. So let's say I spill it by columns, but I really want it in rows. You can use something as simple as transpose and it's going to take your Unique list of columns, spill them down the rows. All right. Curious. Has anyone ever used the text before the text after functions? Let me know in. In the chat. So what, what there is, there is a text before, a text after and a text split. We won't get into text split today just in the interest of time. But those three functions, in most use cases, almost all can replace left, mid, right and Wayne's used those. Great. And so here I have a full name here I wrote this formula that said, hey, look at left H4, which means start at the beginning, find the first space and you know, do all this and give me the first name where it's different length. And so that formula, not hard, but it's a little bit of work. But now see here I have this simple one formula and it did the text before. So we're going to delete. I think you have the stuff deleted here. I'm going to walk you through how you can do what this formula is doing much easier. I'm going to highlight all of these. I'm going to do a comma and I'm going to do this. So I'm going to do the first two arguments. Notice if you look at text before, it has 1, 2, 3, 4, 5, 6, 6 arguments, two are required, the rest are optional. So I'm just going to give it the first two. I'm going to give it my text range and my delimiter. My delimiter is where it finds a space. Since that's text, you need to put it in double quotes. We'll close it. And now I got the first names. You know, if this first name changed to Pauline update and look how easy that function was. Now you're going to notice there are a bunch of others. There's instance number, there's match mode, match to end if not found. I'll walk through a couple of those at a high level and then we'll go through a couple more examples. So match or instance number is. What if we had a middle, you know, what if we had a middle name and we wanted everything after the middle name. So we want, just let's say we want. You know, you can do different things like tell it what instance. So here I said one. So it's going to give me the first and middle name. And why did. Oh, oops, I copied that. That was a mistake by me. Sorry about that. So what I'm doing by saying negative one, I'm saying give me the everything before the last instance of the space. What that does is that now Gives me Pauline Wesley here because the last space is right before Barnhurst. On these other ones, it gives me just the first name. So I'm getting both the first and middle name by saying the instance number. Negative starts from the right, positive starts from the left. So now if I do negative one here, like I said, I don't get the exact same thing as I do with one. If I went back and changed this, took out this middle name, I'm going to get the exact same thing. Because there's only one instance here. So that's your instance number. Your match mode is just to make it case sensitive. So if you have something where you want just a capital X or a lower X, because you could put anything in the delimiter, you could put a formula in the delimiter, you could put multiple items in the delimiter. You could actually do something like this for multiple arguments and say, hey, if there's a space or a dash or period, give me all of them. And so if I change this to be a period, notice it still worked. If I change this one to be a dash, it still worked. That's not something you could as easy to do with left and mid. And fine, those get to be some really ugly formulas. You know, here, here's another example where I use all the arguments. So let's just kind of walk through this and then I'll do it once. So I've highlighted my lookup values right here. I said, hey, split it by the space. I've said, go ahead and give me the first instance of it. So if there's more than one, this next one is the match mode. So I've said, hey, be case insensitive, which it really doesn't matter if it's 0 or a 1. When I'm looking at a space, there is no case to a space, right? There's not an upper case and a lowercase space. You have just a space and then you have. You could treat the end of the text as a delimiter or not. So what that means is if. What that means by that is let's add a. Say I add a bunch of spaces here. I'm telling it to ignore them. If I change this to a zero, it would. It could split them out depending on how it's set up. But you could do that match all the way to the end. And then here, which is one of my favorite things, is the if error. Basically if not found instead of having to wrap it in in an if error. And let's show an example of what I mean by that. So let's leave our formula here. This one right here has no space. Has a space at the end. Let's see if I do just one. Oh, that's right, it's going to give me that. Let's do this. Let's do. Let me change it so you can see what I mean. Sorry, I don't. Space is a bad example. Let's do an X here. Let's come over here and let's have this look at X's. So what you'll notice here, it's giving me the whole thing. But if I take around that match to the end, then it will say not found. So that's what the match the end does. It will treat the end of the text as a delimiter by default. Which means, hey, if it doesn't find what you want, it gives you the whole statement. If you turn that off now, you can see, you know, here found the X. The rest of them is saying, hey, I didn't find anything. So you could have it return the full value if you wanted. Or by turning off that match end, it's now going to give you. Hey, it didn't find it. So that's basically the text before function. Now what I want you guys to try doing right here, I want you to see if you can split out just the ad.com using text before. So you want to try to get the word just this ad dot com. So how would you do that? So I'll give you a minute to try that and then we can walk through how you would get that answer. So I'll give you about one more minute and then I'll walk through the answer. All right, so what I did here and we'll just do it over here is feel free to follow along with me. We do text before. We highlight this. I wanted to get all the text before the last slash. So I do a minus one here. I don't need anything else. Or did I do. What did I do? Oh yeah, I think I did a text after. So I should have used the text before, which we'll show you next. So there's another way I could do this. I could have used a text after to do the last one. So if I want to get the text before, I would need to get the text before the last slash. So like there's a slash here, I could do that. Or I could get everything after the first one. So I think I add the wrong formula there. But what, what I would do in this situation, which we'll do next is I would do a text after here. But I can also use text before and get a text before any of you know any of these different, different things as well. So I want all the text, but I could do everything before the AD.com and we'll do the text after next. So let's say I want everything before the last one here. If I do negative one, you can see I get everything before. We'll walk through the text after next. Sorry about that. I didn't realize that was the wrong. That was not the one I wanted. So I can get everything before here. Since there's nothing before the dash, it's going to be blank and let's go to the text after and I jot it down and note. Let me draw a note to make sure I fix that. I didn't realize that was wrong on that one. This was an old workbook that I missed that when I updated it. So I apologize there. But the only difference between text before and text acts, for which you probably figured out from what we did before is text before gives you everything before your delimiter. Text after gives you everything after your delimiter. So you could see here, I use this to get the last names. So only thing I changed here was before versus after. Exact same formulas. Before gives me last names. If I wanted to do that with a function, there's. There's the formula I would be writing. Right. That. That's not very pretty. It's doable. You're using a mid, a find, a length, another find, and you're dynamically having it figure out where is the space, how many characters is it, and it's giving you everything after that. I much prefer writing this than writing that. And so that's why I say it makes things much easier. Text after here. Same thing as before, but it gave me everything after the space. So in the interest of time, I want to show you how you can use the two together. And there's step by step instructions down below. I encourage you not to use them right now and follow along. So we're gonna. What we're gonna do is I want to get a list of the web addresses out of this. So I'm gonna say text. See, how did I do it? I just want to see. I think I did text after first. I can do it either way. But let me look real quick. All right. Yeah, so I want to copy the same way is the example here. Because the step by step instructions will follow that. So we're going to do text after first. So I'm going to say text after, going to highlight this range and we're going to do all the text after and we're going to go ahead and do it after the first period. So now I got Google, I got all the dos. Now I want to get rid of the document. Bri no, you don't designate dynamic somewhere. So this is the important thing. Microsoft Excel, when they rebuilt the calculation engine, the formulas by nature are dynamic. There are some exceptions, the N formula, the no formula, a couple others. But Vlookup, almost every formula will take a dynamic input. Assuming you're using 365, they re completely redid the calculation engine. So by default things are dynamic versus single cell. So you don't need to do anything special. Excel's work this way for five years now by default. And what's impressive, and I just want to emphasize this, what's really impressive is a lot of people don't even know they continue to use Excel the old way they did. So they built it in such a way that it's fully compatible with however people are using it. So they did a really good job there. So we have text after. Now what I'm going to do is I'm going to type the word text before and my first text is now going to be this, which is virtually stored in the grid. All these, right. You don't see them here anywhere. So I'm referencing that range in the grid and now I'm saying give me all the text before the dot com. So now I have a unique. Do not unique. I have the domain name list. Let's take it one step further. I'm kind of, you know, a grammar. Grammar nerd. I want it to be proper. I could throw the proper formula around it. Now it capitalized everything and I could just keep going here. Let's say I really cared about a unique list. I could throw unique on the front of this. I gotta do is make sure I close it. Now I have a unique list. Yeah, that's not enough for me. I want my list to be sorted. And so I just did a sort unique list of just the web addresses, the names from the web addresses. Right. That would have that. That formula. There would have been a real pain without dynamic arrays. So I just used 1, 2, 3, 4 dynamic arrays with two of them. All I did is just a simple wrap. I didn't have to do anything else. Right. There's not. Once you learn how arguments work in combining formulas. This really is not that complex of a formula. If you don't know those basics. It can look really ugly. And I get that the last function is the sequence, and I'll just take three minutes and walk through a couple examples just real quick. And then we're going to go back to the Excel file and wrap up because we only have five minutes left. So the sequence function, you can see the arguments here. There's four arguments, rows, column, start, and step. It's just like you think it would be. It's a simple sequence. So here on number two, it says, return a vertical list of the next 12 periods, starting with the number zero and incrementing it by five each time. So I want a vertical list. I want one row with 12 row. I want 12 rows, one column. We're going to start with zero, which is the third argument. We're going to increase it by five every time I. So here on number two, it says return a vertical list of the next 12 periods. So starting with the number zero and incrementing it by five each time. So I want a vertical list. I want one row with 12 row. I want 12 rows, one column. We're going to start with zero, which is the third argument. We're going to increase it by five every time. I've created a list. Number two, we want a horizontal list. So I'm going to do one row this time. And here you can see I'm inputting it from the grid. Previously, I inputted it all into the formula. I don't recommend hard coding as a general rule, but for illustration purposes. There we go. A lot of you are like, okay, big deal. Here's where sequence can be really helpful. Problem 3, I just created 13 months by using a date. So let me show a simple example. Let's say I wanted a sequence formula for all 30 days. I want 30 rows, one column. Going to start with day one, go with day two. We're going to write the date function and you guys can copy this with me. I'm going to say 20, 25. We're going to go the month of June. And now for the days, I'm going to put a sequence in here because I want to put multiple days. I don't want just one day. So now I'm going to say give me 30 rows, one column, start with the first day of the month, end with the last day of the month. I have a video out on YouTube if anyone's. And oh, I forgot to put the second. There we go. And so now I have all the dates didn't show up in a date Format. I can come up here real quick and just. I could use a shortcut key, but we'll just do this. And Now I have 30 dates. If I change this to 10, I have 10 dates. So you can increment weeks, months, end of month. You can create dynamic date lists. If anyone's really, really interested in the sequence formula, there's actually a full book on it. I cover a number of examples from this book called Mastering Sequence. Since I think I'm blurry, it's probably hard to see, but it's an entire book on just the sequence function. All right, so we're going to wrap up. I might go a minute or two over. You're more than welcome to drop if you need to, but I'm going to go back to the, to the power PowerPoint for a minute. All right, let me bring it up. Switch my screens. Right. So we talked about the pros and cons here. In the file you'll see some use cases when not to use it, you know when to use it. That kind of gets to the advantages and disadvantages. You'll see a little bit of an example here. Some of the common terminology. We talked about the spill range. Spill range operator, spill errors, expanding formulas, etc. I really like this quote. Excel Dynamic dynamic arrays have changed the way we use all functions and formulas in Excel for the better. We should be able to work more efficiently with dynamic arrays. The sooner everybody learns it, the better our, our, our files can be. And you know, doesn't mean we use it in every situation. Working more than one cell at a time. You know, like I said, they can change the way remember where they're only available. And here's some resources. There's a book, I have it over on my shelf called up up an array that's strictly about dynamic arrays. Here there's some online resources from Microsoft. When I send out the PowerPoint you can find, including going over XLOOKUP which we didn't cover today. And then last but not least, I do run a business. I do like revenue. I'm not gonna lie. I love sharing. I love helping others. I also like paying my bills. So for those of you that would like to go a little bit further, I have a course called Modern Excel. It covers Excel tables, power query and dynamic rays. It's an intro course. It's not going to take you deep. There's probably about five hours worth of videos and you can go ahead and click on the QR code and take it. You can use the code webinar to get 30% off. I also have another one here called the Ultimate Course Bundle, which also includes business partnering model, deny design, data analysis, and other things beyond just the Modern Excel. That's $130. So if any of those interest you'd love for you to sign up. Always helpful to me. If not, you're always welcome to just attend the free webinars. I have a podcast recording that starts started a minute ago, so I'm going to go ahead and end now. Thank you so much for joining me. Feel free to message me with questions. I will send out the master file. I will send out this PowerPoint in the next couple days to everybody who registered through Streamyard. So if you join virtue of LinkedIn or YouTube, you're going to have to message me to get the list. Thank you everyone. Have a great rest of your day.