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

You're out there and you can hear me. Let me know where you're coming in from. We'll get started in a few minutes. You're out there and you can hear me. Let me know where you're coming from. We'll get started in a few minutes. I think I see at least a person out there too. If you're out there and you can hear me, let me know. Hey Ricky. Hello. How are you doing? Where are you coming in from? So the plan is a very relaxed Excel hour today. Don't know how many people are out there, but what we have going on is just a. Basically you're able to ask me questions about a subject and I'll. I'll talk about it. Kind of a little bit of an ask me anything. So I think we have pretty low attendance. I had to move the time on this kind of last minute yesterday, so I don't know how many people will have. But this is an open form for you that are out there to ask me questions about Excel and I will do my best to answer them. So if there's a function you've always been curious about, anything like that, I'm going to share my screen and just do a quick overview of myself. If there's anyone out there that doesn't know be thinking of your questions. If there's no questions, it will be a very short meeting today. But ask me anything, a little bit about me. Let me just switch my screen here. So if you don't know who I am, it's Paul Barnhurst, FPNA guy. I'm a Microsoft mvp, a trainer podcast host. I did my FPAC certification, Advanced Financial Modeler, do several popular podcasts and a lot of training. And today the plan here is I thought of putting something together but I decided I'd give the opportunity for you who are out there to ask me questions on anything related to Excel and I will provide my thoughts. Work in Excel. To show you the example, if there's a function formula, something you'd like me to touch if I'm able to, I will. If I can't, I'll give you the name of a resource you probably can. So very, like I said, very lowkey. Today, floor is yours. I'll open it up for some questions. While I'm waiting for questions to come through, I'll show a couple recent updates in Excel. So this is a fairly new one. Let's say I have a list of some data. I'm just making up some numbers here. I have what's called A focus cell now. And so you can highlight certain cells and it puts a crosshair on them. Hello from Vietnam. Cindy. As I just mentioned, this week for Excel hour, I'm just opening it up for you guys to ask questions and I'll do my best to cover that. In the meantime, I was just showing a relatively new feature in Excel which is focus cells. Some of you may not even have it yet, but you can focus on the column row, turn it off. That's one relatively new feature. Another one, if you haven't used it, is the ability to insert checkboxes in Excel. So let's just say maybe my list is me fruit grains. I can set this up by using. I could use a conditional formatting. We'll go to more rules. If the cell. Let's see, let's do a formula and you can set it equal to true. Do a strike through. Could copy that same down. And so what happens is it should. Why is it not quite doing what I want? Manage rules should only do the strike through when it's true. I'll have to look at what I did wrong, but you get the basic idea there. But you now have checkboxes. And what is nice is you if you write a formula here, let me get rid of the strike through. It did not do that for the conditional formatting. But what you're going to see is it's automatically going to say true. Let me just get rid of. And here if I drag it down, it's going to be false. So you could use it in formulas. If it's true, do the following. If it's false, do the following. And so checkboxes can be really handy as a new Excel feature. Welcome John from Kenya. So it looks like we have John, Cindy, Rogelio. There may be some others out there. As I mentioned earlier, you weren't here when we first started. Today is really an open form for you to ask questions. If there's a formula you'd like me to demonstrate. If there's, you know, some feature you want me to show some things in co pilot, feel free to ask and I'll do my best to cover that. In the meantime, I was just showing a couple recent new features. There's the check boxes which I've hardly played with at all yet, but they're now in cell. There's the focus cells, recent formulas pivot by group by or some of the newest others that are coming that are still in beta is regular expressions. There's one called Translate that's coming. So let me know What? What question? Is there a formula feature you'd like me to talk about? Put it in the chat. I'm going to give you guys a couple minutes here to ask a question on something you'd like to learn. I thought I'd try something different today versus coming with an hour prepared. So put it in the comments. What do you want to know? This is your chance. Jason C. What's the most underrated formula function that doesn't get enough talk? Who? That is a tough one. I think one is the I don't know if it's underrated but I definitely say it doesn't get much talk. Is the ifs function Instead of having to do a nested if you could, you could use ifs. I think filter is highly underutilized. Underrated some product used to be but with dynamic arrays you can do a lot of that. But I would say I think the most underrated formula function is until you realize all the use cases is sequence and you know I'll give an example. Let me pull up a file here. I built three sensitivity analysis here so I'm just going to share this. First one is evaluation where I can say I want to see let's just say 14 EITA multip I started at an EBITDA multiple of 14 increment it by 12 and I could adjust all these scenarios and see what my revenue would be. So let's say I start with a multiple of four all of this sequence sequence. Each one of these here is built using a sequence. So basically a sequence, a drop down list and one other formula and a couple simple formulas here and I've built an an entire sensitivity analysis and ignore the red I didn't quite set the conditional formatting right but the idea is here so let's just say you know I want to look at it at you know, 10 different margin spots. I can quickly start to see what happens at different margin what happens at different multiples and it's all based off sequence. Okay. On the Excel web one I will have to admit Abigail, I don't use Excel Web enough to know if there's a workaround so I'm just going to be totally honest on that one. Is there a workaround and refreshing the data from lists if I'm using Excel web I don't know I would put that out. If you send me a message on that I can check with some other people and see if I can get you an answer. Just message me on LinkedIn with that question. I don't know the answer I'm going to go back to the sensitivity one more time. Here's another one I built where it's taking the prior year's working capital and it's allowing me to look at different revenue, different margins, different tom, different percentages of inventory on hand. And I can see at all these different levels what my daily working capital would be, what would happen to my cash conversion cycle. I don't know that this is the way I'd necessarily build it, but was me just playing around and it's all pretty much built off. Sequence, sequence, sequence, sequence, sequence. And it's using other things that were built off sequence. So yes, there's a few formulas but you can see almost every one of these is either using a sequence or was built off one of these sequences. Each of these ranges are sequences. And what's so what's so great about sequence? It is allows you to create any range dynamically. Let's take an example. Let me just expand this down a little bit. Right. And with sequence. Oh, I want 25 records. Well it automatically adjusted. I want 15 records. I want to start at 4 million or 4,000. I want to increment it by a thousand. And you can quickly adjust any list. So dynamic drop down lists become even more dynamic in that. Now when you filter them here, you could adjust them here and then you got the drop down and the drop down will automatically adjust if you add more. If I go to 30, ah, I have something in the way at the bottom so we'll do 25. This list will automatically have 25 characters. If I change this to 5, I now have 5. So their sequence. Highly recommend it. I've done, I did a whole webinar on it a couple weeks ago. You can find it on my website or YouTube. So I think that's one that doesn't get enough talk. What's your thoughts Jason? I'd love to know what do you think is a formula that nobody speaks about. Alex, thanks for the being a fan of my content and joining. I appreciate it. I'm in curious about embed Python in Excel. Yeah. So let's take a minute and, and show a little bit on Python. If you're. If you're not familiar with Python. I. I'm not. I'm trying to get more familiar. There is a get started with Python in Excel tutorial. I recommend checking it out. We're going to check it out right now. I'm going to click Create and it will go through and show you the very basics. So the first thing you're going to notice is there's a new Python box. Insert Python diagnostics editor initialization. If I want to use Python, here's how you do it. Py and that's Python. Now notice it's going to put Excel B5 there. So Python, when you want to close Python, you hit Control enter. See, it didn't like that one there. I wasn't really using a formula. So it's like, hey, you don't know what you're doing, but we can see the examples. So here it starts to show how do we edit Python. So I can do something as simple as one plus one Control enter and now let me make this a little bigger. I got. I could reference this. I could go equals py and I could click on this runs the calculation. 6:15am you are crazy. Filter and unique. Yeah, I definitely think those are underutilized. Those are really cool. Happy to show a little bit on that. And so what this is going to continue to do is it's going to walk through and explain the basics of Python. A couple other things you'll notice with Python you have two types Python object Excel value. So if I convert this to an Excel value, it's not going to happen. And it shows some, some more examples how to reference cells. I can reference this range says headers equals true and there it gives me a data frame. This is really cool. You can actually reference. You can import data with power query and then you can reference that connection with. So now I want to paste in this link right here. Is it not liking that? All right. Odata feed. If you go to file under new Let me show you here. First I'm just going to connect to this so this, this file here. All right. So I want to select categories. Let me do this real quick. Load 2 Only create a connection. So right here, if you come down to new if you don't see it right here, search in the templates Python and Excel tutorial. No problem. Aristotle, you're fine. I we're doing an ask me anything. So I don't have a file but what I'm showing is a little bit of Python because people ask. So Python and Excel tutorial. We got it here. Now this is the cool part. After loading the data, the import only create connection. You can see the connection and queries pane. I can reference this. So I can come up here equals py Excel categories Control enter is to process it. Enter takes you to a next line. So that's something you have to get a little used to. I hit Enter a lot. So I now have a data frame that is this Power Query link. I haven't even brought it in now. I could even make it an Excel value if I wanted. And I got to delete that. Let's see, let me reprocess it. That's the other thing is it calculates in the cloud so you got to hit Control enter again. And so that's the data that was in that database. And what's interesting to note is that's from Power query without a table, you know. And so you can start playing with these things and there's quite a bit you can do. Like I said, I'm still very early. This has some errors and diagnostics. This teaches you about calculation order this. Start talking about the different libraries. You can add additional libraries to it. You can import them. This is kind of cool here. This is a plot using matplot and watch this. So let me just change it to Area Control enter. And now I have an area chart box. Control enter to refresh it under plot 1. Taking a minute to recalculate this time. And now it did a box. Well, maybe I want a histogram. And so what it's doing is it's saying, hey, take this data frame in cell H5, take the data passengers from the data frame and plot it according to the type of plot that is in this cell right here in H7. And so it's plotting just the passengers according to whatever plot type I select. Aristotle There is you said original file you're working on. There isn't one with samples. I just downloaded the template and started from it. This is all a Microsoft template. I don't have a a regular working file today. Little different, little different session than normal. Then it talks you about Python editor gives you some useful shortcuts and resources for learning more. There's a LinkedIn learning course, Python in Excel. I recommend George Mount is a great resource if you want to learn Python. I've started taking a few little courses, but I am still a total amateur when it comes to Python. But this would help get you at least started and familiar a little bit because as you start to learn it, there's a ton you can do. You could use co pilot to help you. Like I could try something on. I gotta turn on auto save. Fine. Or co pilot, but let's just try create a graph of NBA champions. We'll see what it does. See if it can even figure it out for the last. Or create a chart with a list of NBA champions since 2010. Using Python and the web. Let's see what it does. My guess is it can't quite figure this out yet, but I'm just having fun. So it says, hey, I can do that with advanced analysis. So let's see what it does. I'm asking it for a data set. It's saying, hey, provide me the data set and then I can do it. So, all right, if I went out and pulled the data set, it would then create the chart. Yeah, not a whole lot of savings there, but you get the idea. You can start playing with this and it can help you write code. All right, so I think Jason mentioned filter and Unique. I am curious who's who uses Unique. Throw it in the chat. If you're familiar with unique, I'd love to know. All right, so let's say I have a list, I have a bunch of duplicates, Peter, Alex, Jason, whatever, right? So I used to be able to say copy, paste it somewhere else. Come up here to data remove duplicates. I now have a unique list, right? But if I add Cindy down here at the bottom, I no longer have a unique list over here unless I manually added or redo it. That's where unique comes in. So what I'm going to do here is I'm going to put this in a table, I'm going to reduce this down a little bit. We're just going to call this name and instead of having to remove duplicates, I'm just going to write a unique formula, highlight my data. I'm going to skip name and I'm going to skip that one and say return every distinct item or everything that appears exactly once. Well, I want every distinct item because I want a unique list. I now have a unique list. Well, what if I want to sort it? Well, I'll wrap it in a sort function. Well, what if I don't want to sort it, but maybe I want to count it? Super easy. Wrap it in account A and I now get a unique count of nine if I add another name. Who else we got out there? We'll do LinkedIn user have a little bit of fun. Now goes up to 10 if I delete half of these. Now changes to three. So that's why unique is a lot more valuable than remove duplicates. Is it? You link it to a table and it's going to be dynamic. You can use unique in a lot of different ways. So now let's show a simple example of filter. Let's say I have this list of names. This case we're going to remove Duplicates. So I'm going to data. So we have a name and we're just going to do student ID and we're going to keep it really easy, plus one and drag it all the way down. Yep. I hope the Python was helpful. I hope in the future to do more on Python, I just have to have time to learn it. So if we go filter here, I'm going to say filter this right here. Or actually, no, we're going to do. I want to return the student and I want to do it where the student ID equals. We'll just say 1000 and I think I want to put that in parentheses, if I remember right. Nope. All right. Yeah, because it's a number. Sorry about that. So there we go. I've filtered the name based off the student id. Now let's say we had a gpa and what I'm going to do is I'm just going to randomly create a number here, show you a formula. I'm going to go Rand array. How many rows do I want? Well, I'm lazy and I don't want to count it, so I'm just going to do a count of my table, that's how many rows I need. I want one column and we're doing gpa, so I want the minimum GPA to be two, the N max to be four, and I'm going to allow decimals. So, oops. So now I have these. I can copy them into my table because you can't do dynamic arrays in your table. So now what I could do is I could come back here and I could do a. A filter of this times GPA greater than three. All right, what did I do wrong? Oh, there we go. So you'll notice I get nobody because this GPA is 2.8, so it gives a cal. What if I Change this to 2? Gives, you know, just this one. So what I could do is I could change this to greater than a thousand. Oh, not less than. I want greater than. So and a GPA greater than 3.5. So now I get Joe, Jack and Cindy. So that, that's an example of filter. And how filter works is if you have basically single criteria, which is one element, multi criteria, your multiple criteria, a star is for an end. So I want it to be greater than a thousand and a GPA of 3.5. If I change this to a plus, it's going to do a student ID of greater than a thousand or a GPA of 3.5. So I'm going to get everybody, because everybody Has a student ID greater than a thousand. So, you know, not really doing a whole lot of filtering there. But, oh, no, it's. There is one. This one's equal to a thousand. So if I did greater than or equal to a thousand, I'd now get everybody. And you could add as many filters as you want. You can get really creative with filters. So that's a. That's a function I really like. Other. Other questions today. A little bit of an unusual day. Like I said, feel free to ask if there's a formula or something you want me to show. I'll do it. I'll give it a couple minutes here, see if any questions come through. If not, we'll close a little early and I'll run through, make sure I didn't miss anything. I don't think I have so much to return. Two columns with values. It's a good question. I haven't done a lot with group by. So let's take a look and see what we can figure out. So I'm going to group by cells region here. I'm going to change this sales region. We'll just do east, west, north, south. And now I'm gonna have cells. We'll call this region and we'll call this units sold. So I'm just gonna. We're gonna do another rand array over here because I can't do one in a table. I'm going to do a count A to get my rows. We're gonna do a one. We'll say our minimum unit sold is 10. Our max is 100. And true. We don't want. So I'm going to throw this in here. Next we'll do our cells. We'll say our cells is between a hundred, a thousand, and ten thousand. Ideally, I should probably tie the cells to the units sold. But first. Now we're not going to. Now let's do our group by. Group by. All right, so our row fields, we'll do region, our values. We're going to enter both and we'll see what happens. Here. I want to sum both of them. So we'll type in sum and we're going to exclude filled headers for the moment. And there you go. Work just fine. So east is 4795. Units sold is 118. So yes, you could do multiple values. Now what we could do, let's modify this. And here we're going to include our header. Here we're going to include our header this time. So now we've done our sum, and then it Adds, asks about build headers. Yes. And show. And then do we want grand totals? Sure, we'll do grand totals. Sort order. Well, we'll sort by column 1. Do we want to do any filtering? We'll say no. And I'm not sure what the filled relationship is. I haven't had a chance to play with it. But now I have my headers and I've sorted it and I've added a total. If they're separated, I'd have to play with it. Aristotle. I mean, I think there's some things you could do to. Here's one you could do. Let's say they're separated. I have an idea here, let me try something. So let's just say I got these columns in between and I don't want them. So let's see what happens. I'm going to try two different things first. Let's see. I select this. I think what happens is it moves on to the next function. So I can't select it that way. So let's do choose columns. All right, so my array is going to be this whole thing, and I want my columns to be column two and column six. There you go. It works. Just choose columns so it sticks them together. Choose your columns for your group by. Choose your columns for your sum. They're not next to each other. Just had to think about it for a minute. I. I've used Group by like once or twice so far. So there you go. Aristotle. Other questions. I love choose columns. Incredibly helpful. Someone asked me a question the other day, and it was. I used it with an xlookup and a filter and a Choose columns to dynamically be able to look at like a hundred different tables and select the right answer based on multiple criteria. It was pretty cool. Ah, can we walk through xlookup? You. When you say with the new features. The only thing I'm aware of that's coming for xlookup, but it's not there yet, is what we call Regex. But let me walk through xlookup. So we're going to do one where we have employee id. We'll just do five. We'll keep it simple. We have bonus rating. We have somebody who scored a five, a four, a three, a two, and a one. And then we have a table over here that has payout bonus rating. So we're going to say the payout is 50,000, 40,000, 30, 20, 10. I'm going to fix that first one. All right, I'm gonna. All right, now we'll make these all the same Data type. I'll just do that for now. Bonus is 5, 4, 3, 2, 1. We're going to change this 1 to 0 right now. And let's walk through how VLO XLOOKUP works. So I want to return the payout. So I'm going to say equals xlookup. Let's walk through the steps of xlookup. First thing is our lookup value. This is a dynamic array. I can enter the entire range and look up all five at once. So I'm going to first do that. Then it says, what's my lookup array? Well, I want to match bonus rating to bonus rating. So I'm going to select that. What I want to return is the payout. So I'll select this right here. If it doesn't find anything, say no. Probably get myself in trouble here. No bonus for you. Maybe I should say Chris. All right, so no bonus for you. That's the basics of xlookup. Now let's do a few other things. There's three other arguments after the if not found or two more. First is match mode. This is where it's a lot better than Vlookup. X Match has the same options. You're going to see four options. An exact match, best match or next smaller item. Best match or next larger item are a wild card. I'm going to do a negative one, which is a smaller. So now if I put in $200 for 0 and I modify this, they get $200. If I put 3.5 here, they round down. It said next smaller item. If I do 4.5, it goes down to 40, you know, 1.5. Now what if I change this to be one. Now it rounds up 3.5 becomes 4, 4.5 becomes 5, 1.5, so on and so forth. If I Change this to 0.5, it's going to give me the 1. If I change it to 0.4, it's going to round up. So it's going to go to the next larger item. I Change it to 0, it's going to be 200. If I do negative one, it's going to go to 0 because 0 is the next larger item. So the key things to remember with xlookup are lookup value is the same as vlookup. The difference is they broke apart the lookup value and the return array. What that means is you're what you're looking up could be anywhere. Watch this. I'm going to take this and drag it down here. Formula still works because it's the same size of an array. I could put it on a different sheet. It doesn't care where it is. As long as it's the same size of an array, the same number of rows. It will just match the number from the one array to the other array. Doesn't matter where they're located. That's one of the key differences of xlookup. The other is also the. You don't have to wrap it in an if error anymore. And you can do two way lookup similar to an index match max with a nested xlookup. All right. Do we have another question? We got about 15 minutes left. Maybe I'll see if there's one more and then I'll share just a couple things and we'll wrap up. So feel free. Any questions? So it depends on how you're using some product. In some cases you can do you. You could use some. Some places, some ifs. Because it spills dynamically. How are you primarily referring to when you say some product? Right. Because the traditional use of some product is something like this. Say that's my quantity. That's my price. And I can just go some product. Array one, array two. Some of them together. Right. So that's five, that's six. That's 20. Equals 31. Oh, good old Eric. Coming. What's your favorite road trip snack food. Eric. I like whatever Giles likes. Team ID and date. I'm not so. But what are you. I'm not sure I fully understand. So say team ID date. I have 1, 2, 3. We'll just go today. Minus one minus one or not minus minus one. So what are you doing with these two in some. Are you using some product to filter them data across the columns? Okay, we'll transpose that. Yes. Eric. He does love some humble pie. He is the most humble MVP you've ever met. Anyone who knows Giles will understand that one. So I have this. Now what are you doing with some product? That's the part I don't understand. So are you. Is this array one? And this is array two. Okay. So revenue in the cells. 5,000. Let's just equals rand array. We'll throw some numbers in here. I want three rows, three columns. 100,000. True. All right. So now I'm gonna. Did I get a hundred for all of them? I must have done a hundred instead of a thousand anyway. Yeah, we'll fix that real quick because it's going to drive me crazy. All right. 100 to a th do control one. And we'll fix that to a number Good enough. All right, so you want to look up, say you want to return 686 as an example. You want to return employee two on date November 18th. All right, so here's how I would do it. We're going to do Go employee. I'm just going to do ID date. So I want to return the revenue based on an ID of 1 and a date of 11, 18, 20, 24. One way. And there's more than one way. We'll just do an X. Look up here. I'm going to look up the id. I'm going to look it up here. Now, instead of a return array, I'm going to do a nested xlookup. I am going to select the date, which I need to fix. I realize I have 181 there. I'll fix it in a minute. I'm going to select that for my second column, and then I'm going to select this as my return array. Going to close it, and I'm going to fix this. There you go. 557. I change this to 2, 686. I changed this to 11, 19, 898. All right, well, I'll hang on for another two, three minutes in case there's any last questions, and then we'll call it a day. And Eric, we still have room for you in the car. We could have a fourth person you and Oz can squeeze in the back. All right, thank you, everyone. I hope that was helpful. Enjoy the rest of your day and feel free to reach out if I can answer any questions.