This episode is 53 minutes packed with practical Excel insights for an FP&A practitioner and Excel advocate at the top of his game.
Jeff Gudim is a senior financial analyst, Excel evangelist and hardcore financial model architect at Strategic Education, listed on the NASDAQ and which owns for-profit, online Capella University and Strayer University, Jeff made the leap from private wealth management to corporate FP&A, leveraging his financial planning skills and Excel skills rising up as a data-driven problem solver. Jeff leads forecasting and analysis for a hundred million dollars IT function.
In this episode
- Getting from private wealth management to FP&A
- How Excel jumpstarted my career
- My favorite ever Excel model
- Monte Carlo simulations
- 3 time saving tips every FP&A professional needs
- Dynamic arrays and Lambda functions and “fundamental” Excel change
How to convince your team to use advanced Excel
Completely new favorite Excel function or feature
Connect with Jeff Gudim on LinkedIn: https://www.linkedin.com/in/jeff-gudim-7a994546/
Full blog post and transcript
Glenn Hopper:
If you would like to earn CPE credit for listening to the show, visit earmark cpe.com/fpe. Download the app, take a short quiz, and get your CPE certificate. Finally, if you enjoy listening to FPA today, please go to your podcast platform of choice. Click the subscribe button and leave a rating in review of the show. And now onto the show from Data Rails.
This is fp NA today. Welcome to fp NA today, I’m your host, Glenn Hopper. Today I’m joined by Jeff Guten, a senior financial analyst, Excel evangelist and hardcore financial model architect. Jeff made the leap from private wealth management to corporate fp and a, leveraging his deep financial planning skills and passion for Excel to quickly rise as a data-driven problem solver. Jeff brings a unique blend of analytical rigor and spreadsheet creativity to his work at Strategic Education where he leads forecasting and analysis for a hundred million dollars IT function.
He’s found some pretty cool ways to push the boundaries of what Excel can do, whether it’s building dynamic dashboards, automating workflows with Power Query, or developing money Carlo simulations from scratch. I thought today would be a great day to just dig into some Excel nerd stuff, and I think we have the perfect guest for this venture. Jeff, welcome to the show. Thanks, Lynn. Yeah, happy to be here. We talked a fair amount about this the other day, but for our audience, walk us through your career background, starting with what you’re doing today at Strategic Education and kind of your career progressions to this point. Sure,
Jeff Gudim:
Yeah, yeah. Like you said, I’m a senior financial analyst, uh, strategic education and Strategic education is a publicly traded for-profit university. So that’s kinda the background there. Doing a senior, the senior analysts role, I am supporting the IT function. I’m the primary analyst supporting the IT function. Like Glen said, it’s about a hundred million between CapEx and opex. I’ve been at the company for about three years, which is also the the point in time where I kind of made the jump from private wealth management to fp and a. Uh, obviously there’s definitely some finance and, uh, analyst adjacent work that I was doing in private wealth management, but prior to that I did not have any direct fp and a experience. And I really credit my kind of to the point of this podcast, like my, it’s been my experience in Excel that’s kind of allowed me to rise as quickly as I did.
In fact, I actually credit my experience in Excel for landing me the job at Strategic Education in the first place three years ago. Started out three years ago as sort of like a junior analyst as a kind of poorly defined role. About a year after that, made the bump up to full analyst, and about a year and a half after that I bumped up to senior analyst, uh, where I’m now, like you said, prior to that I was in private wealth management, kind of did the associate financial advisor thing, doing a lot of retirement planning, investment management, you know, analyzing mutual funds, ETFs, incorporating that kind of thing into client reporting and building out retirement and estate plans.
Glenn Hopper:
Gotcha. And we talked about this a bit yesterday, and I think I we’ll get into some of the modeling you’ve done, but I think, you know, a lot of, uh, wealth advisors and, uh, people in that space are more, almost become more salespeople than than analysts, you know, when they’re, uh,
Jeff Gudim:
And that was why I’ve had to get out <laugh>.
Glenn Hopper:
Yep, yep.
Jeff Gudim:
That was, uh, I was, it was not really working for me. It’s a great industry. I made a lot of good acquaintances there, uh, just wasn’t my, wasn’t my cup of tea. And I probably honestly spent longer there than I should have <laugh>.
Glenn Hopper:
Yeah, I hear you. I hear you. And it’s funny, like if you’re, you know, a model builder at heart, it’s hard to keep you outta that and, and, and be in that place where you’re, you know, really more on the sales side. So I, I totally get it. And I think we could spend a lot of time today talking about your career, but after we talked the other day in the pre-call, I thought, no, we’re just gonna geek out on Excel today. Yep. <laugh>, I love episode. Do you know, so you’re an Excel evangelist. I know. And at Data Rails we love Excel. Um, and, uh, you know, I all, I’m a, I’m a huge AI, generative ai, uh, evangelist myself. However, every project I do seems to start in Excel. I’m not picturing Excel going away for any of us, but when we were talking before the show, <laugh>, I thought I loved your origin story with Excel. Was was pretty interesting. So can you say what it tell us what it is about Excel that kind of, that made you fall in love with the tool and, and, um, become this, uh, Excel evangelist and, and how, how did that passion begin?
Jeff Gudim:
Yeah, sure. I was thinking about it this morning, going even further back as a kid, I really had, I was not a techie type kid. Uh, I opened, I remember opening up Excel, you know, when I was 10 or 12, and just being clueless, like absolutely no idea. You know, it was giant mystery to me. The only experience I had with Excel growing up was I have a cousin who has his PhD in Meteor meteorology and this, and he’s the type of guy who’s like, he’s actually in the models. He is not like presenting on tv. He is like, he’s like a researcher in the models. And he would build this, uh, this score track for this family tournament, uh, that we had every Christmas. And he built this like complex score tracker in Excel that, uh, that I always was fascinated with as a kid, uh, but had no idea how it worked.
So my first experience, uh, where I really started to love Excel was I was working a job between colleges, kind of like we talked, uh, like you and I were talking about before, I, I kind of also was trying to go the philosophy route before I realized that that wasn’t gonna pay the bills. Um, and so I, I was between colleges, uh, because I did a whole big college transfer in there too, between colleges. I was working a job as a cost estimator at a manufacturing company. So I was, my responsibility was I would get requests for quotes and I would turn around our formal quote. So it was, uh, estimating the cost of extruded and dye cut rubber and plastic products. And I worked for a guy who had built this Big Cal, essentially a calculator in Excel. And this was, you know, this was back in like 2010.
So we didn’t have, like, we didn’t have all like, the fancy functions that we have available now. This was all VLOOKUPs and old sum if and, and all that sort of stuff. Um, but the calculator was, uh, it was just fascinating. I loved seeing how just everything, you know, we had all of our v lookup tables. Uh, we, you know, had we had our margin calculation, um, we used goal seek every so often, like, and we, I just loved seeing how all that data just kind of came together in that final number. Right? We have all these variables, we have all these inputs, all these dropdown menus, and it all just kind of like came together in that final number that I then pop in our quote and send it back to the client. And that was like the first time that I really was like, wow, this is really cool. And like I said, this was actually before I started my, uh, my, my finance degree. Um, so by the time I got in college and was taking like spreadsheets courses in my, as part of my finance program, they were just worthless compared to kinda what I had already been doing.
Glenn Hopper:
What I love about your origin stories, ’cause when we were talking about it the other day, I thought, oh yeah, you would like everybody, you know, get exposed to Excel in the, in the finance courses, but no, you had actually used it before. Yeah. And without the courses, like when you figure out on your own, like how to make Excel do really cool things and you’re not doing it just, and it is actually helping you in your job. Yeah.
Jeff Gudim:
It
Glenn Hopper:
Just, it’s so much more motivating than you’re, you know, doing some case study or something. Totally. You’re <laugh> Yep. Work, working to Excel. So, so you, you had a need and Excel solved it and I think that yeah. That, that made it connect for you. Tell us about your favorite model you’ve ever built in Excel and what made it so cool or or fun to create.
Jeff Gudim:
Yeah, so this would be going back to kind of my days, uh, maybe somewhat surprisingly, right? Going back to my days working with financial advisors in the private wealth management industry. I just, one day I had this sort of this light bulb moment where I’m working in these very, very advanced retirement planning software platforms. Uh, there’s various, various wands, like e-money, money guide pro things that were used for, like very complex estate planning. Uh, but I was working in those one day and I just had the, the light bulb moment of kind of two things. One, I was like, it would be really nice if you could get one of these that was just a little bit simpler, right? Because for those, you had to enter hundreds of vari, well maybe not literally hundreds, but it was tons, it was dozens at a minimum of variables before you could get any sort of like, output.
So it took several hours in talking with the client. Then you had at least another hour or two of massaging the data in the program. And I kind of had this thought of like, could I do this? Could I just get like five details from the client and make sort of an educated guess at, at whether or not they’ll be able to retire on the timeline that they want. And then number two was one, like I could totally build this in Excel, and it would be really fun to challenge myself by trying to build AM Monte Carlo component to this also in Excel, because these, you know, these platforms had Monte Carlo analyses, right? Where in, in this setting, the Monte Carlo is you’re running out, you know, a thousand or 5,000 or 10,000 kind of depending, whatever, different scenarios for market, like annual market returns.
You essentially have to create a scenario where it’s like, I have to create a thousand different 80 year periods with market performance, you know, randomized, but randomized, you know, within, within with certain controls. And so I was like, this would be really fun to try to create this in Excel one, try to make something’s a little bit more simple, and two, build a Monte Carlo component. Let’s try to do this in Excel. And I did, I built it, it fully worked. I actually sort of <laugh> epic failure, but I launched a company around it trying to, trying to do it wasn’t the right time, didn’t have enough runway it, terrible life decision, but like, it was fun. I, it was a, the ’cause the model worked well enough that I could actually, I could present it to people and be like, Hey, this is like, this is legit.
Like it actually, I can get your details. There’s like, you know, five or 10 details from you and I can like give you a money Carlo estimate the nitty gritty to kind of get like, ’cause we’re gonna get, you know, we’re gonna nerd out about Excel a little bit. So the, the nitty gritty of how this worked was to create the Montecarlo side of it was I essentially used the, the Rand function. It was a nested Rand function, basically. So it was a whole bunch of logic with the rand and the rand between functions to kind of guide the, uh, the randomization of market returns over my, in this case I did a thousand different 80 year periods. Okay. And so I used this, I know, you know, it was very complicated ’cause it was like I had to like do a whole bunch of like market research and be like, okay, you know, how many years, you know, out of every five years or every 10 years, how many of those years have a negative market return, right?
So I have to kind of set that limiting factor. And then I also have set to the limiting factor of like, well, I can’t have, I can’t have it showing a 200% return and I also can’t have it showing a negative 80% return. It’s like those, those never happen, you know? So I kind of had to build those in, but I built out this kind of like this complex logic using nested Rand functions. Um, and a whole bunch of like if then statements now, because the Rand function is a volatile function in Excel, which means it recalculates every time you make any change at all to the spreadsheet. There’s a handful of these in Excel, the Rand and Rand between functions are one of them. The indirect function is another one. Um, and there’s a couple more. Um, because it does that ultimately speaking, I actually just created my scenario and then hard coded the results. I, you know, I hard coded in a thousand different 80 year scenarios. Uh, and then I would then I would just tweak kind of like the variables per client, you know, per client. Um, and they would each be assessed kind of different hard-coded values. So that was, that was kind of the solution I built. That’s how I did it. Um, it was fun and really fun to like have a product that I didn’t excel that was like, I didn’t even know if it was possible, but it was just like, let’s just try this <laugh>.
Glenn Hopper:
Yeah, that’s, and it’s, I love money Carlos simulations also. I do ’em all the time. I love them for scenario analysis. And I’ll be, and just doing forecasting and planning, I’ll be like, I don’t know, let’s run out some Monte Carlo and do the, uh, you know, the 95% confidence interval and all that. And I think about like in Python, you know, it’s pretty straightforward. There’s a you num pie, uh, there’s like, uh, a, a ra you just generating the random numbers and you can put a, uh, you know, random seed so that you get around that issue with. So it’s a pseudo random number, meaning you, you’ve set the scene.
Jeff Gudim:
Yeah. Right. Yep, yep. And if I were doing it today, I would probably try to rely on Power Query because that way I could restate the whole, whole model. And it only takes, I mean, 80,000 rows in Power Query is nothing. So like, you know, I could restate my entire model in one second, right. Rather than, because like having, having the volatile function, I, like, I tried it and it was like, it just crashed. Well, it didn’t technically crash, but it took way too long. Um, yeah. So, yeah.
Glenn Hopper:
But, and the other thing was building it in Excel. Like you actually, because you’re down in the weeds like that, you actually get like a more fundamental understanding that if you just write a couple lines of code and do it, you’re actually building it out and you’re sort of
Jeff Gudim:
That’s true.
Glenn Hopper:
’cause of that you’re seeing the, you know, the value, you’re understanding Monte Carlo better and it’s like, oh, now I get it. This is why we would run into a thousand <laugh>.
Jeff Gudim:
Yeah. Yeah.
Glenn Hopper:
We could, you know, go into models all day. But one of the things that just kind of came up naturally when we were talking was you mentioned, I just, just a couple of little hacks that you do in Excel that, um, save time and make your work easier. And I thought that would actually be a, a great sort of, um, fp and a newsletter where it’s just you’re in Excel all day. Um, what are some ideas of, uh, <laugh> of time saving tips or whatever. And so we talked about, uh, and I thought our listeners would really appreciate this, if you’ve got say, three time saving tips for Excel, stuff that you use all the time, maybe underrated features that, uh, that you could share with, uh, other fp and a analysts, what, uh, what would those be?
Jeff Gudim:
Sure.
Glenn Hopper:
Yeah.
Jeff Gudim:
And it’s so funny on the note of like, uh, a newsletter, I’ve, what I’ve actually started doing in my team is every, it’s random. I don’t, I don’t have a cadence to it at the moment, but I randomly just spam out to the team. I was like, Hey guys, like, I think you should know about this like, new feature that I’ve been tracking in Excel for the last year. Like, I think you should know about this. Like, so, and I kind of highlight it for my team. It’s kind of fun. I’m definitely, you know, the sort of like the token Excel guy on my team. Yeah. <laugh>. Um, yeah. But, uh, yeah, so top three, I would say first one, utilizing kind of some of these new functions in Excel. These, uh, sort of, they’re called dynamic array functions. There’s a really nice kind of combo that I use a lot for doing really quick and dirty analysis where say you’ve got a column with a whole bunch of, you know, repeated values in it.
It’s like you wanna extract the unique values, right? You used to be able, uh, Excel’s been able to do this forever, right? With like, you know, remove duplicates. But now we actually have a function that does it. And so you can use the unique function to just unique is a single argument function where you just type in equals unique. You point it to the column, um, or kinda like the range that where you want to grab the unique values, you close app parenthesis, you hit enter, and it will, you know, getting into kinda like how these dynamic array functions work. Your results will actually spill down, um, the, the number of unique values that you have in that data set. So the unique function is huge and helps, I use it all the time when I just need, I just need to know the unique values here.
Let’s say, you know, I have a list of like, cost centers, right? I got a column, maybe I have a bunch of flat data where I have cost center in one field, but that cost center’s repeated, and I just wanna know which cost centers are in there, put unique on that. Boom. Done. It’s like, takes two seconds. And then in combination with that, put the, you can put unique inside the sort function to get your unique values sorted, uh, ascending or descending. Um, and that’s also super easy, very simple to sort functions. Just two arguments. You have your range reference as your first argument. Your second argument is just a one or a negative one depending on what you, you want your data to be ascending or descending. So sort unique, great, quick and dirty analysis. Number two, I’d say this is a little bit more like process focused.
Get your data into tables. Like if at all possible, your source data should be in a formal Excel table. Some, like, sometimes it’s not possible, right? Like I use a lot of, uh, I use a lot of data that comes in through an add-in. Uh, and in that case, it, it, in, in my case, I don’t, I don’t know how all add-ins work, but in my case, the data can’t be placed into a table because that add-in has to refresh and, you know, it does, it just doesn’t work. So there are some cases where you can’t, but if at all possible, you know, get your source data into a formal table so that you can use what are called structured references in all your formulas. Where when you’re typing your formula, you don’t have to type in, you know, like a one through B 10.
You can type in, you know, the name of your table with the name of the column. So you’re building your sum ifs, right? You have your criteria column. You can actually specify the name of the criteria column and the name of the amount column and all those things. So when you open that file back up a month later, you know what your formulas are doing. <laugh>. So that’s a, that’s a good one. Uh, and then I’d say just like this one’s high level, I would kind of expect certainly anyone who has like five to 10 years of experience in fp a most, most people are probably doing this already, but if you’re not, definitely use like the, what I would call kind of like the simple keyboard shortcuts. I’m not one of those people who’s like, you should never use your mouse like <laugh>. It’s not, that’s not me.
Um, but there are some really simple ones like the control shift arrow buttons to navigate like contiguous data, um, to kinda like select or kinda like navigate between like different data on one sheet. Uh, control shift arrow is kind of a must in my opinion. Um, using control space bar or shift space bar for selecting entire columns, entire rows. Again, I, I would, I kind of assume like if you got five, 10 years in fp a, you probably know those, the one that I would highlight though is control shift V, which was just done in the last like year or so, where we can now paste values with a keyboard shortcut. That’s like, that’s a big deal, and that’s relatively new. So if you’re not doing control shift V for pace values, that’s a must
Glenn Hopper:
<laugh>. I love that one because I’m just picturing back, you know, it’s always the right click. I know, and I know, yeah. Yeah. Years ago, I remember, you know, when I worked with a bunch of analysts, when I, back when I was in telecom, there were, uh, several people, uh, on the, the developer side and on the Excel side who do things like, you know, uh, smash their mouth or hang it, you know, on, on a noose on their, uh, cubicle or whatever. And, um, but I was thinking as you were talking about sort and unique, like how frustrated I would be when I would watch, you know, people like fumble around with my spreadsheets and I’m like, get, just get out the way. But I, as you were talking about those, I was picturing my process of going through, you know, highlighting the table, uh, filter, then sort and copy, you know, copy, uh, visible rows and yeah.
Jeff Gudim:
Paste
Glenn Hopper:
Over just how clunky that is and how much more time that would take than sort and unique. But I’m, I, you know, I’ve hit that point in my career where I just don’t spend the time in Excel that I used to, and I’m
Jeff Gudim:
Absolutely, yeah. Yeah.
Glenn Hopper:
I’m embarrassing when I get in front of it. <laugh> <laugh>.
Jeff Gudim:
Well, it’s, it’s funny. That’s my, that’s my supervisor. My supervisor’s a senior director. She’s a CFA, uh, very, very, very, very good on the engineering, kind of like, and she always wants to nerd out about Excel, but she’s just, you know, she’s in a position where she’s just, she’s, you know, she’s not really valued for her Excel expertise. <laugh>,
Glenn Hopper:
Yeah. Yeah.
Jeff Gudim:
<laugh>. So, yeah, yeah,
Glenn Hopper:
Yeah. When I’m still, when my default is still to go back to VLOOKUP and everything you were talking about back in the day, that’s, that was like my Excel aday. Yeah. Yeah. So I’m still, I still have that muscle memory, but, uh, the new stuff, it’s harder for me to keep up with. But, uh, yeah, control schiffy, I’ve gotta, uh, remember that for, based on the values, <laugh> fp and a today is brought to you by Data Rails. The world’s number one fp and a solution data rails is the artificial intelligence powered financial planning and analysis platform built for Excel users. That’s right, you can stay in Excel, but instead of facing hell for every budget month end close or forecast, you can enjoy a paradise of data consolidation, advanced visualization reporting and AI capabilities, plus game changing insights, giving you instant answers and your story created in seconds. Find out why more than a thousand finance teams use data rails to uncover their company’s real story. Don’t replace Excel, embrace Excel, learn more@datarails.com.
You mentioned, uh, dynamic arrays and lambda functions. I want to, I wanna go into that a little more, and if you could, I, I know a lot of our, probably most of our listeners, um, are familiar with those, but kind of walk us through what dynamic arrays and La Lambda functions are, and then how these newer Excel features have kind of changed the way that you, you build models.
Jeff Gudim:
Yeah, great question. Um, because this was what I would call a fundamental change to how Excel operates. Uh, and this happened in 2018. So in 2018, Excel or Microsoft kind of made a pretty core change to how Excel handles, uh, what are called arrays. Uh, and an array is just, uh, an array is just columns and rows of data, right? Um, and Excel always had array handling capabilities. These were just the old control shift enter formulas where the entire formula would be put in like the curly brackets, right? So, um, if you wanted Excel to handle an array or trying how to process the formula as an array, right? Um, and an example of this would be, say you wanted to multiply two entire columns against each other, right? You could, some product does that, but like, there are other scenarios where you might want to do that inside a function that is not some product.
And so you would have to, you would’ve to tell Excel that you want it to process that as an array, and you actually want those two whole columns multiplied against each other at the row level. And so, like the, the old way to do that was you had to like hit control shift enter when you entered the formula, and it would put the curly brackets around your formula, whatever. And that’s kind of how it was handled. Um, but Excel had been a pretty core change in 2018 where they said, you can, now, not only can you just use arrays across all the for, well, not all the formulas, but across most of the formulas, you can just, you use arrays. You don’t have to do control shift enter, but they introduced a new kind of type of function where the, the results of the, well, it’s not even a type.
’cause some of the old functions do this too now, but they just, um, it’s a new way where if you have, if the output of your formula is an array, right? So output in a, a really good example of this is like, let’s suppose you have a column that has the names of months and you have, uh, say 10,000 rows in this one column, and you got January through December just all over in there, right? If you go into another cell and you type in equals column A, and then you type in equals again, and you put, you know, January and double quotes, so it’s a text value of January, and then hit enter. What’s gonna happen is Excel is going to spill down those 10,000 rows directly underneath the cell where you enter the formula are gonna contain a whole bunch of true false values, depending upon whether the value in that row from column A is January or not.
Um, and that’s a new thing. So Excel also introduced the spill error, which essentially is an error that shows up. Now if you don’t have enough space on your spreadsheet, um, for the data to spill into. So if you’ve got like data below a function where you’re kind of, where you have data that’s gonna spill the spill error kinda comes in and tells you, we can’t do this right now because you’ve got data where this formula, uh, is gonna spill down to, it gets, like, it gets a little bit interesting. It’s hard to much, much easier to demonstrate, um, on a, on a spreadsheet. Yeah. Um, it gets a little ethereal trying to explain it, but, but functionally, you now have this system now where Excel handles arrays really well. You can like, you can do lots of kind, like, you can do what you might call matrix math.
Now, inside the formulas themselves, which hasn’t, you know, hasn’t really been possible and Excel is they keep adding things like, um, they have like say the V stack function now where you can actually take, you can say, I want, say you have like two tables that have identical columns, right? And you want to append them, right? Um, well, you can do the manual copy paste and, you know, put your table together, or you can actually just use the VST stack formula, you could say equals VST stack. You put in the name of your first table, the name of your second table, um, it can also be range references, close the brackets, and you now, your data will now be stacked, and you’ll get both of those tables in kind of one single range, and you only have ever enter one single formula in one cell, and the rest of that, that data just spills all the way across your sheet.
So fundamentally, that’s what these dynamic array formulas are doing. Um, and the lambda functions are, there’s at least like three different types of Lambda functions kind of excel. I’m only gonna focus on like the sort of like the simplest one, which is what I might call like the built-in lambda functions where you now have functions like, um, by row as a really good example where you can actually tell Excel, I want to sum by row on this array. So I have this array that’s gonna spill across my spreadsheet, and I want to get the sum of that row in a new column that doesn’t actually exist anywhere except for in the logic of the function, right? And so, and you could create that, and so then it will spill all your data. You’ve got all your columns and all your rows, and you got this extra column out, off to the right that has the sum of all those different rows, right?
And that, but that is, that sum column is created entirely inside that single formula you entered over in that cell where you have that dynamic array formula. So that’s kinda the power that you’re working with. That’s the, that’s the whole new world that kind of, everyone is still figuring out. Because I would say I’ve been pretty active in the LinkedIn community in Excel for the last like, couple years, and I would like several years, I would say the dynamic array formulas really didn’t start gaining traction until, I don’t know, like three or four years ago. They were technically available back in 2018, but it took a few years for people to kind of fully digest what this meant. Um, and now we’re kind of really getting this age of like, people like, wow, you could do crazy stuff. As an example of something that I’ve done, something that I’ve done in my work as an FP&A analyst on this topic, there are a couple of really cool formulas.
One of the really cool formulas that Excel now has is called the Let Formula and let allows you to assign variables that are local to the cell in which you’re typing the formula. So these are not variables that can be called anywhere else in your spreadsheet, um, anywhere else in the workbook, but you can actually assign variables. So say you have a, a match operation or maybe a VLOOKUP operation, but you want it to, um, it’s gonna like repeat a whole bunch of times or it’s gonna, or you’re gonna kind of like use it in some logic that’s gonna be repeated, and I’ll kind of give, I’ll get into a specific example here in a second, but you can actually assign that match operation or that VLOOKUP operation to a variable. And then at the end of the let function you can put in kind of like the calculation steps.
So rather than having to paste in this long VLOOKUP or this long index match match thing, you know, and you get this formula that’s really complex and just, it’s tons of range references everywhere. You get this formula that has really clean names and you can kind of tell, okay, like this process, you know, this name means that we’re getting this piece of data in this part of the process, so on and so forth. It’s really clean, really clean. What I’ve done in a very, like, specific example is I have, I’m pulling a lot of data, uh, in an add-in. And so in my case, I don’t, I can’t use the nice, uh, layout you get from a table, right? In my case, what I’ve had to do is I’ve kinda had to rely on kind of the old fashioned, uh, dynamic named range of like using offset and counter to capture the, the data on my, uh, for that’s coming in from my add-in.
But I would like to create a report where I can copy the same formula that essentially performs a, some ifs operation on certain columns based on the quarter, right? So I’m pre preparing like a quarter summary, uh, or summary summary by quarter summary by quarter. My source data has quarter columns, but since it’s not in a table, and some ifs actually can’t work with arrays right now. Um, so like you, you, it either has to be like a range reference or a table reference in order to be in some ifs. So it’s like I have to somehow bring this like, you know, bring these array quarters onto my report spreadsheet, and you can write the shortcut for this is like either you just hard code in the range reference and you do some ifs anyway, or you hard code in some sort of column match number that just sits in a helper cell somewhere.
Um, so you, you, you know that you’re gonna match that, but I really, I really don’t like that solution because if your source data ever changes and you just forget to account for it, all your numbers are messed up and you never know. A solution I have is to use the dynamic array functions and put essentially, I’m gonna try to simplify this because I think it’ll be too complicated if I explain the whole thing. Uh, there’s a, there’s sort of a new function that’s called choose, choose calls, choose columns that allows you to specify which column in an array you want to grab, um, for your, kinda like your sum operation. And so using that in combination with the match function where I say, Hey, match on, in this case like Q2, right match on Q2 in the headers for my source data, put that inside the Choose columns function. And now I have, now I have a formula that is completely dynamic to my source data dependent on the sort of the column headers in my report that never has to be updated. Um, it will always, it’s always going to either show the correct value or an error, right? And that’s like, in my opinion, that’s like, that’s ideal, right? You don’t want your, to the extent possible, you never want your formula to be able to give you an answer that’s totally wrong. Yeah. I, that was long-winded. Um, we can, we can talk more. Yeah.
Glenn Hopper:
<laugh>. Yeah, no, that’s great. And I, um, it’s so interesting walking through the history of, in, in the, as a user of Excel, how they’ve modified o over the years. And I know another one that I always think about is, you know, VBA was huge back in, in the day, and I know we’re, we’re past the heyday of that. But, um, kind of along the lines of that, how do you think like the shift from that legacy VBA toward now Power Query dynamic functions, and even like the, uh, copilot assistant, what do you think about the shift and what are you seeing now and are you, you know, ’cause it, it’s crazy, but Excel just continues to evolve.
Jeff Gudim:
I mean, honestly, I think we’re in a really cool sweet spot right now because, you know, if you, if you pull the LinkedIn Excel community, the, it’s kind of, there’s kind of the assumption that BA is gonna probably go away completely at some point. It’s not, not in the next year, not in the next five years, but probably at some point BA is gonna go the way the dinosaur. And because of that though, we’re at this really unique point where it’s really cool. What, what, how much automation, just how much you can automate right now for a relatively small amount of work is amazing. You no longer have to rely on VBA to transform data, right? You can do tons of data transformation, data cleaning in Power Query. You can, you know, you can save all of your SSRS reports in a single folder somewhere.
And you can have Power Query go out and grab everything that’s in that folder, append it all into one table, put it into Power Pivot, where you can build a relational table structure, bring that data into your spreadsheet via, uh, you know, a pivot table where you have all sorts of tables that are connected and, and linked, right? You can do that in terms of like data transformation, but then where you want to automate anything that’s point and click, you can go to copilot and you can grab your code. Like, I mean, it’s like, uh, you know, you have this really, it’s this really cool situation we’re in right now where you can do tons of workflow automation at Power Query, and then if there are sort of point and click changes you still wanna make where you would need some VBA, you can just go to copilot and you can grab, I mean, you know, relatively, you know, anything that’s like kind of beginner to intermediate VBAI would say copilot can easily provide right now, um, I’ve done it numerous times in the last like year or two where it’s just like, oh, like I have this macro, ’cause I’m not a heavy VBA user.
I kind of came into Excel, kind of passed the VBA heyday, but you can now, you know, you can go in, use copilot, bring that code in seamlessly. It works great. I’ve done this numerous times, just like randomly I’ll be sitting there, I was like, oh, it’d be really nice if I had a button that, you know, did this like relatively simple operation, but it’s something I do like dozens of times a month, right? And you just do that now super easy. So I love where we’re at, even though VBA is probably on its way out in some way.
Glenn Hopper:
Yeah. You know, Microsoft put 15 billion plus or 15 billion initially into open ai and they’ve done their acquihires and, uh, you know, they’re all in on ai and I think rightfully so, but co-pilot, like getting it to right formulas is one thing, but the integration, I’m kind of surprised, I don’t know, that’s not really fair because AI is moving so quickly that there’s so many things happening, but the AI integration co-pilot integration directly into Excel has, has been limited. You know, the, uh, whatever copilot for finance is pretty
Jeff Gudim:
Limited
Glenn Hopper:
Functionality, right? It’s really not, not anything that a power user would benefit from. What I think that Microsoft is is picturing is this is like Clip’s revenge, remember Clippy from the Yeah.
Jeff Gudim:
Was
Glenn Hopper:
That the nineties, early 2000, but all the stuff that you’ve been talking about and all the domain expertise that, that, that fp and a people have kind of built over their whole career. Are we gonna get to a point where with generative ai, you know, you’re just gonna come, Clippy is gonna pop up and you’re gonna <laugh> tell Clippy what you want and it’s gonna go do all this complex stuff and bring the data to you. I don’t, do you think that’s where we’re
Jeff Gudim:
Headed? I think that’s where it’s headed. So I haven’t experimented with copilot in Excel, um, you know, with copilot, I’m saying I’m still going out to, you know, Bing or whatever and, and yeah. And grabbing and just, you know, interacting with like the, the message or the, you know, the messaging side of copilot. Um, I know that Excel has copilot in Excel. Um, there’s a, uh, there’s a guy on, uh, the guy on LinkedIn, David Fortune, he’s gonna, one of the mm-hmm. Um, he’s a, uh, he is Canadian, I think has covered the copilot stuff. I mean, from what I understand, you can even do some of that with copilot in Excel right now, where you can be like, I need this data to be summed in this way, right? And it, and they will it actually provide you the dynamic array function that you need to use. Um, and so I know that is happening to a certain extent. I haven’t personally played with it much. Um, kind of, yeah, because kind of to your point, it’s like the use cases for me are probably somewhat limited. Um, but I, I know it’s, it seems to be going that direction.
Glenn Hopper:
Yeah. It’s similar to, to data science to be a really good fp and a pro. You, we, throughout our whole careers, you’ve had to be really good at Excel or it’s gonna take you forever if you’re manually trying to bang through and you don’t know these automations, and it’s kinda like on the data science side to be really good at data science, you had to know Python and, and be able to write SQL queries and or r you know, r and other, um, components of it. But I, it’s gonna be super interesting and I don’t know how long it’s gonna take, maybe it’s one year, maybe it’s two years, maybe it’s eight years, I don’t know. But this barrier to entry of being able to do all the cool stuff like that you’ve been talking about in Excel, that’s gonna go away and anyone will be able to do it.
But there’s also that same thing that made you power through and figure out how to do Money Carlo simulations in Excel. You’ve gotta have that sort of modeler’s mindset and that engineering problem solver and all that. So even if this stuff gets more automated, you’re gonna have to still have that sort of logical thought. I, I think I, I don’t know. I mean, unless you just end up with a Star Trek computer that you just bark out what you want and it goes off like an agent and comes back <laugh>, you know, two minutes later with a complete, you know, incredible model that, uh, you know, would’ve taken a human 20 days to build or whatever. Yeah,
Jeff Gudim:
<laugh>. Yeah, no, I agree. I agree. You’re still gonna need that mindset, right? Even if you are in some ways just a glorified prompt engineer, you know, you’re still gonna need kind of like that data and curiosity mindset. Completely agree.
Glenn Hopper:
Yeah. You’re really pushing the limits with what, what you can do in Excel. And we talked a little bit also about Power bi, and I know you, you’ve built a semantic model in Power BI that integrate, integrated your, uh, what was it, forecasting, software, GL and vendor systems. Yep. Maybe talk through that a little bit. And the reason I’m, I’m shifting to this now is just thinking of it’s great to have the Excel skills, but also right now because with era of Big Data and all that, and as more and more is getting expected, and everybody’s talking about ai, well, AI could be a lot of the, what we were doing with machine learning in, in Power bi. So I mean, walk through that and, and sort of the integration and crossover from Excel into, uh, into Power bi.
Jeff Gudim:
Yeah. If I have my timeline straight, I bel I believe Power Query was first built in Excel, uh, and then Microsoft used it to build Power bi.
Glenn Hopper:
I think that’s right. Yeah.
Jeff Gudim:
That was the timeline. And so yeah, I, I built a big semantic model in Power bi sort of in the same way that Excel kind of like really got me into data. It was Power Query in Excel that really kind of got me into Power bi, where I started automating some workflows using Power Query and Excel. And then when I kind of figured out that like, uh, the hard part of Power BI is just Power Query. So I was like, once I kind of figured that out, I was like, oh, hey, well we could just, we can, like, we could just be doing a whole bunch of stuff in Power bi. Uh, so I actually, uh, I was pretty instrumental on my team. I, I, I pushed pretty hard and ultimately convinced the senior VP of finance that we should really put some time and resources into building out some Power BI models, um, which was, you know, it was mostly it was, it was me, it was <laugh>, you know, I was totally just volunteering to do the job, which was fine with me. I loved it. Um, yeah.
But yeah, so I built this big semantic model in Power BI utilizing a lot of the, uh, a lot of kind of like the skillset I had honed in Power Query in Excel Power Query and Power Pivot. Um, because a lot of that is the same, right? So you have Power Query where you do a lot of your data transformations, uh, and then you have Power Pivot in Excel where you can set up table relationships. And those two steps are, are replicated nearly perfectly in Power bi. Uh, and so when you, you know, if you, you, you log in your Power BI desktop and you go into like your, oh, I think it’s like, forget what that button is called, but like edit data or whatever, and, uh, that brings you sort of into the Power Query interface where you have, you know, you have all your tables over on the left, uh, you, and then you have kinda all your transformation steps for each table over on the right.
Uh, and sort of, you know, in the middle you got the big, uh, table, which kinda shows the preview of your data, kind of use my knowledge power query to build this big semantic model. Um, kinda like you referenced, what we were trying to do is we were trying to bring in, we had various attributes in our vendor system, our vendor managed Coupa, um, for managing our invoices. And so we had various attributes in there where we wanted, that we wanted to bring in at the line transaction level, um, on our general ledger. Um, so the way we’d been doing all of our general ledger reporting, um, you know, so far and for all of history was we’d go out, we’d manually load an SSRS report, um, you know, trial balance that gives us what we’re looking for in the time period we want, right?
The goal was we need to replicate that. We need to, you know, we need to make sure we’re pulling the same numbers as that report, but we also want to add some line level detail in here so that when you’re looking at transactions, you can say, oh, this is the, you know, this is the purchase order number that that transaction is assigned to. And, or this is like the, this is sort of like the corporate sponsor, um, you know, from the business unit and the department where kind of where this invoice is coming from. We wanted to add that line level detail. And then we also wanted to be able to summarize our general ledger by various p and l categories that we work with in our forecasting software. Um, so it was bringing those three things together, which we did, you know, very, very complicated, right?
I mean, the, I think the, some of ’em were challenging things about that were, you know, identifying like the unique identifier that you need to use for all of your table relationships. Um, right. And oftentimes that was not very straightforward, you know, oftentimes it took several transformation steps, you know, you had to like, go out to, well, like, part of the unique identifiers in one table, part of it’s in another table, you know, you gotta, you know, you either you, you either merge it in or you, you use like m code to like pull out your, to like pull ’em together, you can cat ’em, you know, whatever. Like, there’s lots of stuff like that to get the data into kind of like a format where we could kind of identify and, and kind of map all of that, all those table relationships.
Glenn Hopper:
Yeah, that, and it’s so kind of a three part question I’m gonna throw at you here now. So one, like the kind of the mindset shift going from Excel to realizing, going from Power Query to realizing, oh wow, there’s a whole new world with Power bi. Um, so there’s the mindset shift and then what really the biggest unlock was there. And then I guess to kind of put a bow on it, if somebody is, you know, great at Excel, but new to Power bi, how would you advise them to, um, sort of make that shift something <laugh> a word, a word of wisdom for someone who’s, who’s crossed that chasm?
Jeff Gudim:
Yeah. Yeah. If you don’t mind, maybe I’ll start with kinda like that last one there. Um, yeah, yeah. ’cause I, I kind of touched on it honestly in my last one and it in my sort of, in my, uh, sort previously where if you’re good at Excel, absolutely start using Power Query. Because Power Query is where you can really bring some substantial, uh, data transformation and automation into your Excel workflow. Um, like I said, most finance people listening to this probably have some monthly systems where they’re going out and they’re grabbing, you know, files that are saved in certain folders. And it’s a monthly process, right? The file is always in that folder. The file has the same name, or at the very least, the file has the same, um, you know, internal structure, right? It’s the CSV, it’s got, you know, 12 columns. They all, they always have the same name, whatever that you got, you almost everyone listening to this probably has some type of, some type of process that they follow on a monthly or quarterly basis that, that, that takes that form in some way, right?
Well, you can automate that entirely in Power Query and you can just, you can have Power Query go and point to that folder, grab everything there, bring it through some transformation steps, you know, you can, you can kind of do what might otherwise kind of be, uh, you know, maybe you, you maybe you relied on like b lookups to kind of like add dimensionality to your table, or, you know, you’re removing duplicates or you’re, you know, you wanted to, maybe you can cat like two or three different columns together. Like all that stuff could just be knocked out in Power Query. Um, and so then you can bring that data into your spreadsheet in a totally different, um, kind of a, a, a clean, a clean form already. You don’t have to rely on a whole bunch of like manual transformation steps anymore. Um, so do that.
And then once you kind of have that, you can totally leverage that understanding of Power Query, because like I said, that is the hard part of Power bi, the visualization, kinda like the sexy side of Power bi right, is very easy. Like, you know, once you have your data clean and once you have a structured well, and you have your table relationships built out, the, the visualization side of of PBI is easy. Like it’s, you know, it’s, it’s pretty much just drop it in, you know, put the right field in, you know, maybe update some of the formatting and, and boom and it, and it just looks amazing. Um, and, and Power BI is much more, power BI is much more seamless on the data side than Excel is, you know, sort of for better or worse, um, I’ve definitely noticed like Power BI is, is it’s just more seamless when you’re like going out and connecting the data sources and stuff. Power BI handles the connections a lot better. Um, and so you can, but you can totally leverage knowledge of Power Query Excel to kind of become good at Power bi because it is the, getting good to Power Query in Excel means that you’ve mastered the hard part of PBI,
Glenn Hopper:
So little bit different episode, and I love these, I think we might need to start doing these more often, honestly, because, uh, this is, you know, Excel is the language that we all speak. And so what we, we focus today on Excel to the exclusion of all the other things we do in fp and a of business partnering and of, um, you know, storytelling and all that. And I think though, and, and we talk about all those, whether it’s soft skills or, or different management styles and all that, but what we’ve talked about today, this is the kind of the core fundamental stuff that we need to be able to do. You then have to layer on, you know, the, uh, uh, the, the, uh, business partnering and the storytelling and all the other, uh, you know, sort of forming a narrative from the data and all that.
So I guess before we get to our sort of, uh, boilerplate questions that we throw out to everybody, to wrap all this up, I mean, I know you’ve got, you’ve carved out a niche as the go-to data guy on your fp and a team, and, uh, it obviously because you have all these skills, it’s, you know, people could be <laugh>, uh, driving around using the same Excel you are, but if they don’t have the skills, it’s gonna take ’em a lot longer to do the exact same thing with these skills. I guess, you know, what advice would you give someone kind of trying to build out that reputation and, and to be the sort of the, the master of this platform beyond just all the soft skills that we talk about? Because it maybe, maybe there’s a caveat here, like when you’re talking to senior management, they may, they don’t care how the sausage is made, they just want great sausage <laugh>. Yeah, yeah, yeah. So I mean, it’s, you’re kind of in an interesting spot because I, I have a feeling that where you work, you probably are the guy that everyone would go to to answer any kind of Excel question and all that, so
Jeff Gudim:
Yeah. Oh, no, it’s good. Uh, it’s a great question. And actually crazy, just last night I had someone reach out to me on LinkedIn to someone I, you know, haven’t really connected with before. They reached out to me asking almost this exact thing where they said, Hey, I’m in a really position, similar position to you. I’ve seen your work, I’ve seen your content kind on LinkedIn, like, I really like how you think about things. Uh, they’re like, I, I too have done a lot of exploration and data, but I can’t, like, I can’t really, I haven’t, I’ve had trouble kind of convincing my team to kind of adopt some of this stuff. Uh, like what do you, what do you recommend for how to, like, how to handle that? And what I told them, which is what my answer will be here too, is identify a problem that’s facing the team and then use the new Excel stuff to solve that problem.
Um, because that’s absolutely what I did. Uh, and, and I think it would probably generally work, right? I don’t think the difficulty and the uphill battle that we face with this stuff, right, is that if you’ve got a director who is, uh, they’ve been using Excel for 20 years, you know, they really believe themselves to be a, a master of Excel right now, <laugh>, right? The hard, the hard part is given all the changes have been made, they’re probably not right. They’re probably not anymore <laugh>, right? Um, but they don’t believe that. And since they can still tech, you know, they can get all the answers they need without using the new stuff. So it’s like, how do you, you know, how do you convince a person like that of the need for change. There’s, it’s pretty hard. It’s, you, you can’t really, unless right, unless you can identify a problem that’s facing, you know, it’s not just your problem, it’s everybody’s problem.
And you can identify that and say, Hey, you know what? I think we could use some of the new features, whether it’s Power Query, whether it’s, uh, dynamic rate, formula, whatever. I think we can use some of the new features to solve that problem. You solve that problem and suddenly, you know, you got the senior VP saying like, Hey, this guy, this guy knows what he’s talking about. Like, we should, we gotta allocate more resources like that. You know, like, and those conversations start happening. So I think you identify a problem that, uh, that impacts more than just you, um, and solve it for everybody and you’ll have a platform.
Glenn Hopper:
Yeah. Well said. That’s great. Alright, well let’s bring it home with our, our closing questions. So the first one we always throw out there is, uh, what’s something that, uh, not many people know about you? Maybe something we couldn’t find just from, uh, looking you up online? Uh,
Jeff Gudim:
I love this question. I, I, uh, I feel like you kind of find this in the tech space sometimes. Um, so in as much as I love data and in as much as I’m a bit of a tech guy myself, I’m actually sort of at the philosophical level, I’m very much one of those, like, is this, like, is this good for, like, is this good for us? <laugh>, like, you know, for humanity in general, right? Like, I’m very much one of those people where I’m just like, I think about this a lot. And it’s like, you know, and particularly with ai, it’s like, I mean, we all, you were all, you, you weren’t stupid for asking this question before ai, but now it’s like, with all the AI stuff, it’s really like, oh my goodness. Like this is going, you know, where’s the off ramp? Kind of like what, like what’s the, what’s the end game here? Like I, we were doing pretty cool stuff 10 years ago. Like, do does it really ha like, you know, what’s what? So I’m, I’m totally one of those people. Um, despite my love for data and, and how much I rely on technology for my job at a philosophical level, I’m one of those people that kind of questions the general trend here and kind of like put my hand up being like, Hey, is there, like, you know, where, where’s the off ramp <laugh>? So, yeah.
Glenn Hopper:
<laugh> and it’s, it’s interesting right now because the philosophical voices are not getting heard as much now. Yeah. It’s just sort of the acceleration that’s just go, go, go. Yeah. Uh, have you ever read, uh, have you ever read any Nick Bostrom?
Jeff Gudim:
I have not.
Glenn Hopper:
So he’s a, he writes on AI and he does take a philosophical angle. So he’s, I I would recommend, uh, he’s got a, a couple of books, uh, out from, they’re, that’s several years old now, but talking about what does it mean in sort of a post a GI world of stuff, highly recommend Nick Bostrom though, to, uh, to sort of That’s good. Take it At least quench that philosophical, uh, question you have. ’cause he’s, he’s sort of done these thought experiments where he is, he is kind of run out the ground ball on that. So. All right, well, this one’s gonna be, I’m, I’ve actually been looking forward to this question all day talking to you because we ask every guest what your favorite Excel function is and why. And, um, I, I keep saying we need to log these and I’m, I’m gonna start one day, or maybe we could get AI to go listen to all the old, Hey, you could episodes that’s, yeah, yeah. But with everything you’ve talked about, tell me what’s your favorite Excel function?
Jeff Gudim:
I, I gotta try it too. All right. Whether that’s fair or not. Uh, but I mean, it’s either Choose Columns, which is one of the new, it’s, and it’s the, the syntax. There’s Choose calls, it’s c Choose COLS. Choose the Choose Calls function. I use a lot these days where I want to specify the, a certain column in my array for calculation. I use, I use Choose Columns all over usually. Um, kinda like in the rather complicated example I gave, you know, half hour ago, usually in combination with Match where, you know, I put the match function inside the Choose Columns function to say I wanna choose a specific column in my array. And I, you know, want, I want that column to be based on some, you know, match identifier, um, that I have sort of on my report or on my dashboard. Um, it’s the choose, let’s choose, you know, what I can, I can do that one. Choose Columns. That’s my go-to these days.
Glenn Hopper:
Cool. Cool. Love it. Love it. Uh, how can our listeners connect with you? Again, just learn. I don’t, because you, you’ve had some pretty good, uh, uh, LinkedIn posts on, on Excel stuff too, right? I, and I got sick.
Jeff Gudim:
I got sick and it like knocked me off my game, but I’m trying to get back, trying to get back. But yeah, I mean, I’m on LinkedIn. Um, my name is Jeff Gut. It’s G-U-D-I-M as in Mary. Um, I do, uh, think my, uh, my title’s like, uh, Excel Evangelist and senior FBA Analyst or something like that. Um, yeah, happy to connect. I love, love, uh, sharing content. I’m fairly active in the Excel community on LinkedIn. Um, can I interact with a bunch of those guys there? Um, and yeah, love to connect. Great.
Glenn Hopper:
We’ll, uh, and we’ll put links to both of the, to, uh, your profile and to the Excel community. I’m sure a lot of, uh, of our listeners are already members of that. Sure. But in case they’re not, we’ll, we’ll be bringing a new resource to ’em. So. Well, Jeff, I really appreciate you having on, I think this was a, a great episode. And I would say to our listeners, reach, if you like this kind of episode, reach out. Let us know and, and we’ll start doing these more often. Um, so again, Jeff, thank you so much for, for coming on the show. Yeah,
Jeff Gudim:
Absolutely. Thank you, Glen. I loved it.
