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

Welcome. If anyone's out there, let me know where you're coming from. If you can hear, if you're out there. Looking forward to getting started here in a few minutes. Just go ahead and let us know where you're coming from in the chat. Like, I see about eight of you out there so far. All right, we got about five minutes before we get started here. Looks like I see nine of you out there at the moment. We have a few early. So if you're out there, just let me know where you're coming from. I'd love to know in the chat and why you decided to join today. Also, the file for today's meeting, I realized I hadn't uploaded the correct file until about 15 minutes ago. So if you download the file early today, I recommend you redownload it. Is. It is a different file. So just to make sure you have the right file, go ahead and download that and we will get started here in a few minutes. I know we got a few of you out there in on LinkedIn as well, potentially YouTube. So please just let me know where you're coming from. I'd love to know. We got about three more minutes before we get started here. Hey, Dust, welcome from. Welcome from Turkey. Thanks for joining us. I. I think everybody knows, but if not, just you're aware, we're going to cover dynamic arrays. This is a subject I've covered before, so the material will be pretty similar to what we've done before on this one. Just so everybody's aware, if you attended that one before, it will be very similar. Still doesn't mean it may not be beneficial, but just so you're aware, welcome from South Africa, Cindy Sue. Alrighty. Just another minute or two here and we'll get started. Daniel, welcome from Houston. As I mentioned before, if you download the file earlier today, not within the last 15 minutes, please go ahead and download it. I did update the file and you'll want that version. Sasso, welcome from Serbia. We got Brian from Knoxville. Great. We got a good group out there. We'll give it another minute here. We're just still a little early, not quite two o', clock, and we'll get started here in just a minute. All righty. It's two o' clock again. I'll remind everybody to download the file, we'll tell an Excel joke and then we'll get started here. So if anyone has a good Excel joke, feel free to put it in the chat. Otherwise you might be tortured with mine. So let's see if I can find A good one today. Why did the Excel file go on a diet? It wanted to reduce its sense cellulite. And the next one is why did the Excel function? Why did the Excel spreadsheet go on vacation? Because it was tired of all the functions. Welcome, Olivia. I believe that's Olivia. I used to work at ven, if I remember right. Tejas. Welcome, Valerie. All righty. Well, let's jump into things. I want to start by just introducing myself. My name is Paul Barnhurst. Many of you know me by the nickname of the FPNA guy. I for a living today, I've been about two and a half years now running my own business. Before that I worked in FP&A for over a decade with companies such as American Express, Solera and Digicer. Today I create content. I do a lot of training. Lucky to work with a lot of Fortune 500 companies and individuals. Recently was awarded the Microsoft MVP. So those the file you should download is the first one that, the very first one on that page, that's for this week. It's always the one at the top. So if anyone wonders what file it's going to be the first one around. Dynamic arrays. It should be called, I think student dynamic array file or something like that. And then I'm also a podcast host. I just love to hear myself talk that or I do enjoy hearing stories from others. So I have a podcast called FPA Tomorrow, which is all about FPA that replaced my podcast FP&A Today. That's my version of that. There's Financial Modelers Corner, all about financial modeling. And then I'm lucky enough to host Future Finance, AI and Technology and Finance with Glenn Hopper who is fabulous if you want to learn AI. Alrighty. It's a little bit about me. Let's go, let's jump into the subject. So the focus is to talk about dynamic arrays, which I believe they have game changing abilities. I'd love to know in the chat if any of you are using dynamic arrays today. If you are, go ahead and let me know. I'd love to know. Let me know how or if you have a favorite formula. We'll do an overview of dynamic arrays. We're going to build a sensitivity table with dynamic arrays. We'll cover a couple formulas and they'll have a wrap up and key takeaways. So I'm just going to take a minute in the deck here and talk about dynamic arrays before we jump into a file. So dynamic arrays are part of Excel 365. So if you have an earlier version, dynamic arrays will not work. You'll only be able to watch. And, and the reason for that is in 2019, Microsoft Excel rebuilt the calculation engine. They completely overhauled it so that dynamic arrays would work. Prior to that, if you wanted to do arrays, you use Control Shift Enter. There was a book called Control Shift Enter by Mike Girvin. And so what it does is it allows a singular formula to return multiple values in a grid. They'll automatically expand or contract. So it allows for what's called spilling. But Jonathan, a little bit. Never, always avoid them. We'll see if we can change that. Olivia. Brian. Not yet. All right, so it looks like not a lot. So I love this quote from Dave. Dave Runs, who runs Excel Jack, one of the best websites out there. If you ever need to understand a formula, highly recommend it. I love the site. So dynamic arrays are the biggest change to Excel formulas in years, maybe the biggest change ever. This is because dynamic arrays let you easily work with multiple values at the same time in a formula. All right, so here's a list of formulas. You'll also see that in file. I'm not going to go the to these in much detail other than to say we have a few beta formulas that are being tested, which is really exciting. There's a pivot by coming that allows you to basically create a tivot sorry to a pivot table with formulas not even having to put it into a pivot table. You're going to also have a group by and there's a new percent of as well as there's other new formulas coming around. What they call reg expressions are not necessarily. I imagine they're dynamic in nature, but I think of them as formulas to help you with lookups. So what are the benefits of dynamic array functions? They can simplify your formulas. I know somebody that went from something like 50,000 formulas to a couple hundred. Using dynamic arrays, you can substantially reduce the number and many formulas become much easier. They can dynamically update so when input changes, the array can adjust, it can resize. You can get a quicker calculation speed. The formula lives in one cell, so there's versus many cells. You get many new functions, which is really exciting. You know that list I showed earlier? Xlookup sequence unique filter to call vstack list goes on and on. Those are all dynamic array formulas. And then it reduces errors since you're not copying over multiple cells, but rather it's automatically spilling. It can help reduce errors. It's not guaranteed to reduce errors, but I think that's a benefit now the challenges, lest you think it's all up and up and a good thing and there's no issues, compatibility first is hey, if somebody's using an earlier version of Excel, I believe 2021 does have a dynamic raise, but 2019 and earlier does not. And they're not backward compatible. They do not work within Excel tables, something I wish they did. So this is one challenge and I'm a big fan of tables financial models without using lambda. And if you want to see some uses of Lambda create, Craig Hatmaker is who I recommend following beyond Excel 5G I believe is his website. He's created a lot of lambdas that solve the weaknesses of dynamic arrays so you can create a fully functioning three statement model. There's one on my website. One of the files there was loaded by Cameron and Nicholas Hay. If you get a chance you should listen to the podcast. They have built fully dynamic models using lambdas combined with dynamic arrays. But Corkshrew is not something you can solve with dynamic arrays by themselves. So you have to start writing custom formulas which can get really complex or use somebody else's. There's a learning curve, takes a while to get used to them. And then there are some formatting challenges which we'll talk about when we get into them. So why don't we go ahead and jump into it. I'll send the deck out so you can see the rest of it, but I want to jump into the file so we have as much time there as possible. So let me open the file here. Everybody should see a file. Yours should be id, dynamic array, student file. Mine's called Master, but they're basically the same. First I want to show just a couple examples of what can potentially be done with dynamic arrays. So here I have this investment model. You'll see I have a number of assumptions here. Model year is a discount rate, year one revenue. And notice here I have periods, you know, 1 through 14. I'm going to what I want to show you is let's just say I Change this to 20 periods. My model automatically adjusted to 20 periods. If I change this to 10 periods, every single formula adjusted. I did not have to drag a single formula. I didn't have to come over here and drag it. That's what I mean by dynamic arrays. And off these drop down lists I could modify this however I want. So that. That's one example. Second, here is notice I have this list. I have sales rep amount sold, all 10 sales reps and a total. This is one formula in Excel. And notice let's say I add a new sales rep. Let's say I add sales rep 11. Notice it automatically added it here. The one thing it didn't do is it didn't move the formatting. That's what I mean by the formatting doesn't automatically update. I could have done some conditional formatting on the word total if I wanted. I didn't in this case. But now once I add a new number, let's add 150,000. Notice it automatically updated. So that's just one of many examples of dynamic arrays where it spills and adjusts. The last one which we're going to build at the end of this exercise is a sensitive, a sensitivity table. This is a simple one. We're going to build a little more complex one than this at the end. But notice here, right Now I have five, five prices from 8:30 to 9:10 COGS, from 550 to 650. And I have all my inputs, my units sold. Etc. Let's say I want 10 price increments. There it spilled. Let's say I want 15 cost of goods sold. I'm really concerned. We've been very inflationary. Maybe we think it might go back down to 550, but it could also go all the way up to 830. What happens to my profit margins? And you can see in the sensitivity table, I can adjust any of these and it spills automatically and everything updates. That's what you can do with dynamic arrays. So I'll pause for one second and just see. Any questions so far from anyone, please put them in the chat if you have a question and sometimes it does take a minute for me to get them. I know sometimes there's a little bit of a lag, but please put them in there. Next you're gonna see I have here on this page some resources. Here's some website resources to learn about dynamic arrays. Here's a book, it's one I have on my shelf over here. You can't see it, but I do have it. And then here is the different dynamic array functions and what they do. So let's get into how dynamic array arrays work. I want to do an overview, so go with me to the page called How Dynamic Arrays Work and you can, you could copy with me. So I'm going to delete what I have right here. Notice over here I have the numbers 1 through 6. If I highlight this range, I'm going to highlight the whole range and hit enter. Notice, two things. Well, three things. First we have the numbers 1 through 6. It's not in one cell. Jonathan, if I had to choose between dynamic arrays or power query, which would I choose? Oh, that's a great question. I would choose power query probably, but I, I want both. But if I had to choose just because of how much I used it at work, I would probably go power query, but definitely want both. All right, so first thing you noticed is it spilled all the numbers it didn't live in one cell. Second, highlighted in blue, what it spilled. So this is an array that goes through one through six. This array doesn't adjust. We'll show you what I mean by dynamic, where they automatically adjust based on an input later. Next, notice the formula text. See, I reference B8 pound, which references this entire range. But you'll notice the formula only lives in the first cell that is grayed out in the second set. So it only lives there and it has an N A in all in all the others. Now, prior to 365 how it worked. If you come over to column F, you're going to see how it used to work. If you put an at symbol at the front of this range, it only gives me the first number. The @ symbol is telling it to just provide the first number, which is how it used to work. If I had highlighted that range, I would have got the. It would return the one. Or if you did in the middle, it would have done implicit intersection and returned like the three if, you know, depending on how you set it up. But it would have only returned one value. So next, let's see how dynamic arrays can operate on multiple cells at once, as mentioned. So the first thing, and this is the. This is again not dynamic, but the new engine allows you to enter multiple values in places where it used to require a single value. So here I have this formula where I said hey, left and instead of entering one number and dragging it down, I've entered the entire range B22 to B26. And then I'm saying, hey, find the space and then do B22 to B26 and I'm getting the first name. Now what's cool as well is I'm going to show you one other thing. Let's say I do text before as a formula, which is a new formula in Excel equals. Make sure I'm spelling it right before. Notice I can highlight all these at once. I can say my delimiter is the first space and again I get the same thing. So you can input into the new dynamic formulas as well as many of the older formulas. Multiple values versus half and enter one and drag it down right before you would do B22 and then go drag it down. Now you can. So if this is, if this list adjusted for some reason and you highlighted the whole list, this could adjust. And so let's see that next. Next what I want you to see is this is example 3. Dynamic ray formulas can adjust size based on the inputs. So here I have a formula called sequence. We're not going to get into the how the formula works right now. I'll go over it a little later. But notice I have rows, columns, start and stop. Those are the four elements that go into sequence. You'll see rows, columns, start and stop. And so C34 says three rows, two columns. Start with the number two stop. All right, start and step, not stop. Sorry. This should be step. How made it increase it by thought. Something didn't look right. So what you're seeing is it goes 2, 5, 8, 11, 14, 7. What if I make it four rows? So just change this number to four. It now automatically adjusted to four rows. If I change it to one row, it adjusts to one. If I change it to five columns, it will adjust. If I change it to five rows, it will adjust. And so that's what I mean by dynamic is it can dynamically resize smaller or bigger columns and rows in a minute. I'll show you what happens when it doesn't have room to spin. We'll get to that in a minute. But before we do that, what I want to show here is how do you reference a dynamic formula? So let's say I want to reference an entire dynamic range in another formula. Well, let's come in here and look what happens. Let's start. If I, if I reference just that, I only get the one cell. So it doesn't, it doesn't spill. If I reference the entire range, let's see what happens. Well, you can see it now spilled. But notice what it did. It automatically put the pound at the end. If I had only referenced half of would not put the pound at the end. It would cover all those cells. But if I was to change this to, you know, six columns, it wouldn't update. It would only do that. So I could reference the first cell and put this symbol, the pound symbol. The pound symbol says I'm referencing a dynamic range. Anything that uses that's dynamic in nature, you can use the pound symbol. All right, so that's the basics. Now I'm going to show a couple other examples. So spill errors. So what happens if there's not enough room for the formula to spill, notice right now it goes down to row 13. You see, in 22 I have the word Joe. So what happens if I change this formula to go down to 20, which would be row four to 23? It didn't work. It returned a spill error. You can see that there. And so two things I want you to notice. First, it gives the words hashtag spill. Second, instead of being a blue outline, it gives you a dab. It's no longer solid. And so you can see, hey, we want to go all the way down to here. So now what happens if I remove the word Joe, it will now go down. And so it now works. You know, if I put Peter here now I. What I can't do though is I can't overwrite this. Actually, I can. See, I overwrote it and now it gives me a spill error. So let's say I write John here and then the whole thing gets messed up. And so you have to be real careful with working with dynamic arrays to make sure you have enough space for your potential ranges so you don't end up with spill errors. All right, so next let's just look at a couple adding examples. And again, feel free if you have any questions to, to ask them in the chat. All right, so here I have month 1, 2, 3, 4 and 5. And notice what I did here to add them all together instead of doing a sum, because I can't. If I do sum of each row, it's going to total everything. So what I did is I said, hey, give me the first row plus the second, plus the third. And you'll notice it has the box around it, but not ideal. You know, if I had another number over here, I would have to adjust it. So summing columns or rows, what they've done is they've created a by call formula which I'm not going to go into detail here, but notice how I was able to highlight that whole range and it worked. Let's say I change these because these are. Let's say I add an additional one here, 13, I don't know, and seven. Now I could, you know, come here and that's automatically going to spill. See, this didn't spill. I'd have to go in and adjust each of them. So if this formula was dynamic, like if I use the named range or something to make this dynamic, by call would allow you to sum the totals. So it's a little weird how you have to do some, some things, but here you can Use it to sum those totals. Now, let's look at multiplication, which I think is a little more straightforward. So here I have quantity, price. I've multiplied the two together, and you can see if these were dynamic, then I could do this as a dynamic formula. And what I mean by that is right now these are just individual cells. And because of dynamic arrays, I can multiply them together. But I could also make this a dynamic formula. So if I came in here and said, sequence, we'll do 10 rows 1, see? Oops. I can type 10 rows, 1 column. We'll start with 0, we'll increase by 2. So see, I get that error. So I'm going to delete those and then I'm going to come over here and I'm going to do the same type of thing here. But in this case I'm going to say I'll start with 30 and negative 2 not. And so I get different numbers. And now what I can do instead of this being B6 times B11, since my ranges are dynamic, I can do B6 pound times C6 pound. And notice it now automatically did all did that math for me. And if I change these ranges, so let's say I change this one to 15 and I. You can see a change. But right now it gave an NA because it can't answer those. And I change this one to 15. It multiplied it together. Notice the formatting didn't copy down. That's what I mentioned earlier. I would have to manually copy the formatting. All right, any. Any questions at this point that anyone has and. Well, I'll give you a minute to ask some questions. Let's see if we can find an Excel joke. Why did the Excel file become a detective? See if anyone knows why the Excel file became a detective. Because it wanted to solve the mystery of the missing formulas. Didn't say they were good jokes. Just said they were jokes. All right, not seeing any questions, so we'll keep going. One last example, and this is with an old formula, Vlookup. Notice here I've entered the entire range for vlookup and it's returned all three answers. That's what I mean by you can input multiple values. This isn't necessarily dynamic. It's not a dynamic array in that this range is going to spill. But previously you had to enter. You can do it with me. Enter one at a time and drag it down less than ideal. Right. And I would want to take away that so it dra. So I don't have the. And then we'll just drag that down and there we go. Furthermore, I can also reference multiple columns if I want Notice here. I. I went ahead and just put it in brackets, which you can do the squirrely brackets and said give me column 2, 3, and 4. And it returned all three with one lookup instead of dragging it across. All right, next we're going to go to unique function. And I'd love to know in the comments if you do it this way, please just go ahead and let me know. I'm going to remove the ticker down here at the bottom. We're going to hide that. If you've ever got rid of duplicates this way, let me know. So I need to get a unique list of all these names. So I copy the first names here. I come over here, I don't know column V. I paste it, I come here, I go to data and I click remove duplicates. And in this case, I'm going say my data doesn't have headers and I get a unique list. Let me know if you've ever done it that way. Just throw a yes in the chat or. Yep, still do. All right, There's a few problems with that method. One, it wouldn't update if my list updated. I'd have to do it again. Two, I had to copy and paste data. Now, kind of a little timely. So let's see with using the unique function, how we can do it where it's automated. So the unique function has three arguments, and that's it. All right. As the array, which is the range, which is your data, it has this ability to do it by column or row, and then it has what's called exactly once. Only the first part is required. So this is one of the easiest formulas you'll get. So I can see Daniel put a yes. So, Daniel, I'm going to show you a cool way to get this list. So everybody type with me. And it was there before, but we're going to type over. So just Delete what's In G7, type equals unique, and then come over here to your array. And what I want you to do is highlight the first column. There's a couple ways you can do it. You could highlight the first one, hit control, shift down arrow. You could use your mouse and drag down. What I like to do is I like to come here until I get this little black arrow. Click on it, and it highlights the entire range. And you can tell because it has what I call the dancing ants. And so you'll see it referenced my table, which is called table classes 26 first name. So I had it in a table. The reason it says 26 is because there's been a lot of tables in here and I didn't clean up the naming. So that table has been used a lot of times. So now it's all I have to do. I highlighted my range and noticed I now have the unique list of names. Same as the list would be if I had done remove duplicates. But a couple things that are really cool now, now that I have it here and I have it in a table, this is why I use tables. Let's come down and add somebody's name. So let's see. Let's add Olivia. So we added Olivia here. Now let's see. Did Olivia get added? She did. You can see down here, Olivia has now been added. The formatting didn't update, but Olivia was added. Now let's try somebody else. Let's add Mohammed and maybe Valerie. So now we've added a few names and notice they've all been added so that a unique is as simple as that. That is the easiest way to get a unique list. Now I'm going to show you one other thing. There is a dynamic formula called sort and I can wrap it here. And now I got a unique list sorted in alphabetical order. Going to undo the sort for the moment. Now what I want to show you is you'll notice there were two other arguments. Return unique columns, you return unique rows. I generally am always working with columns and tables. So I leave that blank. And then there's this third one. Return items that appear exactly once are return every distinct item. So I'm going to type false. And you notice I get the same list as before here. If I type true, I'm going to get those names that only appear once. You'll notice I just added Olivia. If I add Olivia again a second time, it now goes away from the list here because it's twice, it remains on the first list because this is distinct, right? If I added Matthew down here at the bottom, it now gets removed from the list. If I delete. Let's just say I delete these last five names. So I'm going to highlight them all and hit control minus delete them from the table. Notice I now get a calc error. The calc error is saying we found nothing. It returns hashtag, hash tag or pound, calculator, exclamation point when it finds no values. Now one thing I wish Excel had that Google Sheets has is Google Sheets has account unique and I believe they have account unique ifs. But we can do a count quite easily. There's a function called count a if any of you have ever used it. So if you do count a unique and then just highlight the range. Oh, sorry, you got to put parenthes two parentheses, one for each formula. What count A does is it counts how many times there's something in the cell, whether it's text or a number. So you can see it returned seven unique names. I could also, I could also do do it this way. I could do count A and come up here, highlight this and it would do G7 pound and it gives a count of seven. And if I was to change this, I'll add Olivia again. It's going to automatically update the count. Right. And so that's much easier than writing a formula like this that says hey, one over the count, doing a countif so saying hey, Peter's in here 10 times. So that equals 0.1. I add all those up, I get 10, I can count all these cells and it's going to equal eight, which is what I got here. Right. A lot easier formula than writing this one over and dragging it all the way down and totaling to eight to be able to get account. So unique is one of my favorite formulas. Very easy to use. Any questions on unique before I show you sequence and then we walk through the sensitivity analysis, how to build one. You know, funny enough, when I first first learned about sequence, I didn't think it was a very useful formula. I've since learned it's amazing. There is a Microsoft mvp, Excel mvp. He wrote an entire book on just mastering sequence that has hundreds of examples of how you can use it with mid and text before and left and right to do all kinds of really cool stuff. Amortization tables become easier working with dates. I'm going to show a couple basic examples and then we're going to use it as it's the main formula we're going to use to build a sensitivity table. So let me make this a little bit smaller. So sequence has four arguments. Only the first argument is required. You tell it how many rows you want, how many columns you want, what number to start with and what your step increase is going to be. And these could be positive or negative. So let's do problem one. I'm going to delete problem one and the goal is to return a horizontal list. One through twelve is our goal. So we're going to type sequence and I already have what I need right here. So I'm going to do rows, columns, start Step and close it. And I got the numbers 1 through 12. If I change this to 0, I get the number 112 times. If I change this to negative 1, it's going to go 1, 0, negative 1, negative 2, etc. I could change this to 0. So I can adjust these however I want. So that's the first example of sequence. Another one that's pretty cool. I'll show just real quick. If you do sequence, 256 rows, column. And if you start with. You could do. I'm trying to. Let's see if this will work. Char 1. I think this is right. Nope, you can create all. I'll have to do it later. I'll send it. But you can create all the characters, like the Unicode characters, a complete list of them with sequence. And so I'd write that a little different. I think you have to do the char on the outside, but in the interest of time, I won't show that right now, but you can create that. But let's see some date examples, because this is where I use it all the time. So we're going to use two functions here. We're going to use the date function and the sequence function to dynamically return the next 12 months, starting with January 2020, using sequence and date. So if anyone has an idea how to do it, go ahead and give it a try. I'll walk through it here. So we're going to type date as a formula. It's going to ask for the year. So our year, you can see, is 2022. Now we need to get the numbers 1 through 12 in the month. The easiest way to do that is to write a sequence where we say one row, 12 columns. We start with one, we increase by one. By default, if I leave these blank, it's automatically going to start by one and increase by one. And then we'll put the day we want. I want the first day of the month. So now you can see I just created a dynamic list of every month in 2022. Maybe I really want this to be 2024. You know, maybe I want 13 months. You could see, hey, maybe I want end of month for each of these. So I could come in here and say zero. And if I showed you the actual day now. So if I change the formatting, let's just show long date, going to have to expand them, but you'll see end of month, I'm going to undo that real quick. And I could do control one as well and just come in and change the date. Let's see here, Right. And you can see each of these is now end of month. And so that's where you can use Jonathan. Oh, Mastering sequence is the name of the book. Email me and I can get you the details on it. All right, I'm going to keep going for now. So I want to show one other example. Problem four, return a vertical list of the date ranges from cell U5 or sorry, that should be W5. I apologize, I made a changing here from W5 to W6 starting in cell Wi Fi. All right, so you can see right here, I got 1, 1, 2021, 12, 31, 2022. That's 730 days. So I want to create a formula with 730 days. So how would I do that? Well, I want this to be my number of rows. I want one column and I want my start date to be this date right here. I want it to increase by one. Notice I now have 730 days. You can see the count right there. Now I could change this, you know, to 2023 and then I need to change this. So let's just Change this to 2023. Now I have 365 days, but I can create a dynamic calendar there. And every time I change the starter end date, it will automatically adjust. All right, so here's the part I'm guessing most people want to see. We're going to create this sensitivity table right here. So let me show you what's involved in the sensitivity table before we before we build it. So what you're going to notice is these are all data validation lists right here. These two are formulas. So each one of these has a drop down. I can select number of units sold, the base case price, the base gross margin, what I want my price increments to be how many price increments I want. What's my worst case price as a percentage of base case. So I know what my worst case price is, what my base case cogs is. So basically all this allows me to build this table and make it all dynamic. So if I adjust any number here, this all adjusts any of these as we saw before. Now let's look at the formulas real quick. Notice one formula sequence, two formulas, same sequence but a different formula and a third formula. That's it. This entire sensitivity table is three formulas. This one down here is referencing above. So it's really not even a new formula, it's referencing what's above. And this is the new formula. And this formula is mostly referencing other stuff. So surprisingly very few formulas to create a dynamic sensitivity table. So the first thing I did is I created my lists I wanted and I used the drop down. So every one of these lists I'm just going to expand, make them a little bit bigger. I don't know why that didn't adjust. I wanted to make those a little bigger. Let me double click again. All right, so what you'll notice is every single one of these is a sequence. And so in this case I went ahead and just entered it. But if I was getting fancy, I could have created a start value how much I wanted to increase by for each of these. But they're all dynamic. I could change any of these ranges. So these are all my starting places. So the first thing I did with that is I came in here and we'll do it here. So I want units sold. So I came to data data validation. You create a list, My source is going to be this right here. And notice when you put it in data validation, it doesn't recognize it as dynamic. You have to backspace and add it after the B4. So once I add it, I've now created this list and if I make changes here to this, like if I change this to be 20 now, it only goes to 435. This now only goes to 435. So instead of 22, it's now 20. What I'm going to do in the interest of time is I'm going to copy the rest of these over so that I don't have to manually do all of them and then we'll create the formulas. All right, so this formula here, just so you know what it's doing is it's calculating the worst case price and saying, hey, base case price is 43,000. The worst case price is the percentage of the base case price is 99%. It could go all the way down to 80%. And so calculates this number and it does something similar for cogs. It says, hey, base gross margin is 15%. If the gross margin is 15%, then there's my base case cogs. So now what we need to do is we need to create our sequence formulas. So the first sequence formula, let's just look at this is price. So you'll notice I put one in the rows. The columns is going to be my price increments. I'm going to start with my worst case price and then I'm going to tell it how much it should increase by. So let's go ahead and do that. So here we're going to Say, I'm going to come over here and do this with me. Type sequence 1. Your columns is going to be your price increments, your starting price is going to be your worst case price, and your increments is going to be your price increments. So I'm going to close that. That's now dynamic. If I adjust this to five, it adjusts. All right, now let's do the same with cogs. So we're going to come here. You're going to type sequence again. In this case, you. Your rows is going to be your cogs increments, your columns are going to be one. Your start is going to be your. We're going to start with our base case cogs and then we're going to do our cogs increments. So in this case, we're selling the car at a loss. You know, obviously we wouldn't want that. So we'll adjust that, say to 25% so we can see our cogs there. There are some points where we may be selling. We can see our price there. Now we got to do the inside formula, right? Which if I want to know my margin, it's going to be this minus this times my unit sold. So if I do that, I get the one answer. I need the answer for the entire range. So let's go back and look what I did. So you can see it is all I need to do is add a pound to each of those formulas. Pound, pound. And there you go. The. I already had the conditional formatting, but you could always add that. If you come in here to conditional formatting and say manage rules, you'll see this rule I have C18 to AF42, so it goes all the way over and down. So as I resize this, the conditional formatting will go with it, right? So when I size this to, I don't know, 25, you can see the conditional formatting worked all the way out. What didn't work is that this didn't resize right? So I can make this larger. And we got one error. Let's see. Something didn't. Let me see what's going on. Something didn't. You'll notice the formula is working here, but as we go across, it's not working. So let me just try something. See what I did wrong here. There we go. I had done it backwards. I wanted C17 minus B18. I did B, I did COGS minus price. So there we go. And now we just need to get rid of that one, one rule there. So I'M going to undo this and we'll just flip this. See, that looks okay. So what I'm going to do is we'll just copy the formula from here and fix it. All right, there we go. You can see the table now makes more sense. But you'll notice this. It did conditional formatting for just that one cell. So we'll come in and we'll delete this one rule here. Okay. And now we'll highlight the entire. Now we'll go to manage rules and we'll fix that. So if you just go to conditional formatting, we want this worksheet. You'll see we want this right here to start, you know, not C19. We want it to start in C18. So I would just change it and be good to go. I could change this to be C18. Didn't like when I copied it because I should have copied just the formula and not all the values. So there we go. You've created the dynamic array. Hey, thanks for joining, Olivia. Enjoy your next call. All right, so the idea would. I'm going to show you how you would build the bottom here. You can see it is all it would be now is taking C18, which is our gross margin divided by our price times the units sold, and that's going to give us our margin percentage. And again, that's going to be dynamic as well. So as you can see, only a couple formulas to create that entire dynamic array. So I'm going to go ahead and close the workbook. I'm going to pause here before I go back to the PowerPoint and just see any questions on what we covered today? Anyone see some use cases of where they could use dynamic arrays? I'll share a couple examples. Do you ever have to do an amortization table, a loan schedule, dates? So those sensitivities you ever need to do an FX rate sensitivity? Yes. There's other ways you can do it. You don't have to do it dynamically, but I do think it can be really helpful and quick when you can just adjust all your inputs and everything adjusts for a manager. All right, so I'm going to bring back up the PowerPoint and just cover the. I want to cover a couple slides here. So there's a quote by Claudia Buckley where she says Excel dynamic arrays have changed the way we all. We use all functions and formulas in Excel for the better. And I would agree with that. So what are the key takeaways? You can work in more than one cell at a time. They have the potential to change, as I said, the way you work in Excel, reduce your formulas. Jonathan no, I don't and I'll tell you why. So Jonathan asked. Let me put his question up here and I'll answer this real quick, he said. With Pivot by upcoming and dynamic arrays being dynamic, do you think Pivot tables and power Pivot data models will eventually go away? The reason I There's a couple reasons I say no. 1 pivot by allows you to create pivot tables, but it doesn't. The formatting doesn't update. So if you wanted total rows or banded rows, it gets really tough every time your data changes. 2. The data model. You have cube formulas. You can write off the data model and you can use some dynamic arrays and I find there's some really cool stuff you can do with that. So even with Pivot By I would still want my data model. Also, the data model comes from Power bi, so I would say no, they won't go away, but it will change things. Some people won't use them at all. Some people didn't previously. One of the great things with the Pivot buy is when you make changes you don't have to press refresh because everything's dynamic. Next, here's the book I recommend for up called up an Array by Abbott Cats. Here are some online resources from Microsoft. One that kind of covers xlookup preview of dynamic arrays and some of the formulas and behaviors. And then I'm going to put a plug in for for my own course here you can see there's QR code there. Two courses. One's called the FPA Ultimate Course Bundle. I think it'd be good for people in finance in general, not just fpa. It covers business partnering. There's three modules Modeling Design so how to think about modeling a financial model. What are good design principles? It's not going to teach you to build a three statement model. There are a lot of courses out there for that data analysis you get about three hours, some statistical modeling, a little bit around AI, the different types of analysis we do in finance, and then modern Excel which covers dynamic arrays, power query and Excel tables. So if you use the code webinar you'll save 30% off the regular price of this bundle has about 10 hours of content. So with that 30% discount you're paying less than $10 an hour. And here's kind of a testimonial you'll see from somebody just saying huge thanks to Ron and Paul for their amazing experience. If you're looking to boost your FPA skills. I highly recommend their. Their courses, they're truly worth it. Ron and I did that one together. And then you have my modern Excel course. If you just want Excel, you got Excel tables, power query, dynamic arrays. You will get lifetime access to that. And I will be adding more modules. It will be a little while, but I will be adding more to it. And so you'll get that access if you sign up. 30 off and that one's $60. Again, use the code webinar. There's the QR code for it. You can scan that. You can also go to my website and find it. And here's another quote from Deepak Bandari. He's the. I guess he's the former. Now he. He was the interim CFO at Highliner Foods, and I had the opportunity to train his entire team. So I'll leave that up for a minute and I'm just going to open it up to other questions anyone may have at this point about anything we covered anything Excel related. And while I'm waiting for anything to come in, we'll see if I can find another Excel joke. All right, here we go. You know what happens when Excel crashes? I mean, I absolutely hate it. I hate that it gives me a spread sweat. All right, that one was bad. Let's see. Do you know what a statistician looks for in a spouse? Does anyone know what a statistician looks for in a spouse? A normal distribution. And I'm not seeing any questions coming in, so I'll give one or two more jokes and we may close a little early, but feel free if you have any questions to ask them. What did the Vlookup say on its online dating profile? So what did the Vlookup say on its online dating profile? Seeking an approximate match. It's true. And I'll leave with this one. This one gets me in trouble with some people, but we'll go for it. Why was the spreadsheet constipated? Why was the spreadsheet constipated? It couldn't budget. There's my FPA joke. All right, we got another question from Jonathan. Is sequence the most powerful dynamic array function? It's one of them. It's up there. I mean, it depends on who you ask, but it definitely probably has some of the most use cases. In fact, I'll show right now. Let me grab it since we have a minute. So this is the book I was referencing earlier called Mastering Sequence. It has over 200 examples of all kinds of different things you can do with sequence. So it's definitely very, very powerful. No, sorry, Jonathan. I paid for it myself. Don't have any free copies, but you can buy it if you want. Manet Po is the author. Any other questions? All right, if there's no more questions, we'll go ahead and let you go. Hopefully you'll join me again next time. If anyone wants to let me know of anything they'd like covered in the next session, just go ahead and throw it in the chat. I'll hang on for one more minute, but we're. We're pretty much done. So thanks again for joining me. You are welcome, Jonathan. You're welcome. Lewis, welcome. Daniel, welcome, LinkedIn user. Since it doesn't show your name, I like this one. All right, thanks, everyone. We're going to go ahead and close it now.