Excel tips: Top 4 functions to improve efficiency in 2022
In this second part of this series on Excel, Liam Bastick, FCMA, CGMA, an Excel MVP, shares what he thinks are the top functions for accountants and finance professionals, Excel's limitations, and what he thinks is the future direction of this most popular spreadsheet software in the world.
What you'll learn from this podcast:
- Why XLOOKUP, SUM PRODUCT, OFFSET, and MOD are great functions all accountants and finance professionals should explore and use.
- What Excel is poor at doing.
- The possible future direction of Excel and Bastick's wish list of changes for it.
- General best practice for Excel formulas — consistent, robust, flexible, and transparent.
Play the episode below or read the edited transcript:
— To comment on this podcast episode or to suggest an idea for another episode, contact Drew Adamek at Andrew.Adamek@aicpa-cima.com.
Alexis See Tho: Hello and welcome to the FM podcast. I'm Alexis See Tho, your host for this episode. In the last episode on Excel, Liam Bastick, an Excel MVP, an award given by Microsoft to technology experts who have deep knowledge of Excel, talked about how everyone should start using Power Query, if you're not already using it; his thoughts on the new LAMBDA function; and why he thinks Excel is still relevant today, even though there is so more software that accountants could be using. In this second part of the series on Excel, Liam shares more tips and what he thinks is the future direction Microsoft could take for the most widely used spreadsheet software in the world. And here is our conversation.
We're going to move on to more tips from you. What are three functions you think that most accountants are not using, but they should be using?
Liam Bastick: They're none of the new ones. Microsoft will probably be disappointed. But I'm going to put an honourable runner-up first. An honourable runner-up is XLOOKUP.
For all of you who use VLOOKUP and HLOOKUP out there, if anyone knows me at all, we're not supposed to divulge what happens as MVPs when we go to Redmond about things that happened. But a few years ago before XLOOKUP was announced to the world, we were given a preview of it at Redmond when we went over there. They put up on screen all these people who love VLOOKUP. There was a lot of people. This is the function that they think they're in advanced views, if they do it.
I have a reputation in many Excel classes, just showing what goes wrong with VLOOKUP and why it's a bit dangerous to use. For all these people who are MVPs who love VLOOKUP, and then they said, and here's the list of people who hate VLOOKUP. They just had a massive picture of me on a PowerPoint slide and everyone just laughed. I have a bit of a reputation for going around saying how much I hate it.
What XLOOKUP has done is, it's got rid of some of the drawbacks. If you don't know what are some of the drawbacks, I do invite you to look at one of the FM articles on VLOOKUP. I've gone through before. You can see with your own eyes how I've destroyed the VLOOKUP function because it doesn't always work as you think it does or doesn't even work, full stop.
XLOOKUP gets around it. It's for those that think, I don't want to use two functions like INDEX(MATCH). I've heard of it, but it's too complicated. XLOOKUP is the happy alternative. I would honestly say that it's not in all versions of Excel yet, which is why I gave it only an honorary mention. Try and forget VLOOKUP and HLOOKUP, and learn XLOOKUP as well if you've got to.
But the three I'm going for, in no particular order, is I'll start up with the name of our company, actually. I was so impressed with this function, I bought the company, to quote that famous advert for razors in the '70s or whatever. It's a Victor Kiam.
But the first one is the function SUMPRODUCT. It's not perhaps the most imaginative one to begin with because you think what this does is you go and you have two columns of data, and if I've got quantity in one column and I've got unit price in the second column, and I wanted to work out what my total revenue is, what a lot of people will do is they have a helper column, which would take the quantity times the unit price on each row. At the very bottom, it would have add them all up.
What SUMPRODUCT does is all that in one go. So you just do SUMPRODUCT, open brackets, column A times column B, close brackets. That would take each row individually, multiply them together, and add them at the end. When people hear this, the general response is one of underwhelmed. They think, "Well, that's not the sexy, exciting function I thought you're going to tell me. I'm not really sure of the practical use of it or anything like that."
But it is, because on its own described like that it doesn't sound exciting. But if you know the actual x functions in DAX, for instance, what it means is you can do a calculation on a record by record basis and then aggregate them in some way using SUMPRODUCT here.
There's nothing to stop me going, SUMPRODUCT, open brackets, column A divided by column B, close brackets. Then it will take the first number in column A divided by column B, work out the answer, to the second one, divided by column B, the answer. And at the end, add them all together. How many other functions could do that? Or you can add two together, or you can subtract them. You can do other things.
But you can also do like a SUMIFS where you can do multiple criteria. But SUMIFS requires everything to be in columns or rows. This is why SUMPRODUCT comes into its own. Sometimes you have a matrix where you want to actually see things that are going down a column and you want to look at things to only equal to certain criteria in that column and then compare it to some things that are happening in rows.
You're thinking, how would I do that? SUMIFS won't do that, but SUMPRODUCT will. If you're not sure about that, certainly go to have a look because you can do so many wonderful things with it in short formulas and it's quite impressive. That's the first one.
The second one, a well-known accounting firm out there has its axis of evil of functions you should never ever use. They've got OFFSET on there. With me and my sense of humour, if somebody tells me I can't use a function, then I'm all for it. That's just how it is. But I love the OFFSET function because it's actually quite powerful.
What OFFSET does is you start in a cell and you'd say "How many rows and how many columns do you want to move from that cell?" Again, described like this, you think, "Well, that sounds very interesting. I don't see the point of it. What is it?" But a lot of stuff that we do as accountants is we build models. The reason we build them in Excel rather than Word and PowerPoint is we want to do What-if analysis. We want to be able to change the data.
Now if you put tables or rows of different inputs for different scenarios or sensitivities, most people will actually change the data by using a VLOOKUP formula or using an XLOOKUP calculation or SUMIFS or INDEX(MATCH) or XLOOKUP, any of those. But trouble with all of those formulas for this particular thing is on your table, you've got your inputs, you have to define the number of rows and columns. It needs to know.
Studies have shown, when we're building models, about 5% of the time, we make errors. But it's not linear. When we're just starting off, we don't tend to make too many errors, because it's simple, we got plenty of time. Everything's working fine.
As we go later on in the piece and time under pressure, and we're trying to change things. Somebody asks us to tweak something at the last minute and we've got five minutes before meeting, we change it, we think it's OK, but we forget some of the knock-on effects it has somewhere else.
One of the most dangerous things you can do is change range dimensions at the last minute. This is where we often do things because people say, "Can you add another scenario? Can you do another sensitivity?". These are the common questions that'll strike a chord with the audience all over the world. Yeah, we get asked this the whole time. But as soon as you change that range, some of your formulas might break.
It's like trying to add another year at the end of your actual forecast data. The trick is we have to insert a column before the end and then copy it across. But then if we do that, we need to check that all our other formulas have had the column inserted at the same time and then it hasn't. You'll find you've got misreferences and all sorts of things.
There'll be people out there going, "I remember that, I've been burned somewhere." Or "I've got a 'good friend' that's been burned in the past". The beauty of OFFSET is you don't need to change the range. It just says if I need to go out and add another column, well, you just type it in. Or you just say instead of going out seven columns, go out eight columns, you just change the number because all it needs is the base cell and where you're moving from that.
Now, why the one particular company in the Big Four doesn't like this function. It's because when you're actually doing this, it makes this function nonauditable because you started off with a base cell and you're defining how many rows and how many columns that can move from there. This confuses the built-in auditing tools. Now if you've ever worked on an Excel spreadsheet, you'll find that it always calculates everything like that. Then suddenly it slows down.
This is because you've got more and more calculations going on. Excel's inherently lazy. When you change a value, it says, well, what does that affect? What does that affect? What does that affect? Eventually it stops and it just goes and changes all those things and leaves everything else alone.
The problem is though, at some point, either depending on memory or what version of Excel whether you're in 32- or 64-bit Office, it loses track of these things that are called dependency trees, and it has to do a full-blown recalculation.
The problem with OFFSET is it never knew what it had to change in the first place because it couldn't keep track. Every time you scroll up, scroll down, change the colour, sound, move to another sheet, it recalculates. This can be the screen goes white, you get the blue circle of death and things like this. It's called a volatile function. It means it just calculates all the time.
Now, before we get on our high horses here, VLOOKUP is volatile. Because if you have something in two columns and you're taking Column 1 and Column 5 as your two values, if you put something in-between them, it will recalculate, which is unnecessary. That's the definition of a volatile function. I don't know why OFFSET gets this horrible singling out treatment when VLOOKUP doesn't.
We did some work on this. We had a 280-megabyte file. We were working with a client. They wanted to go faster and they used OFFSET. They were worried because they had read this literature that was out there, that it is a volatile function and therefore it's recalculating the whole time. But when you replace it, most of the time you get other than MVPs and they'll tell you the best function to to replace it with is INDEX(MATCH).
It's not volatile, but it can be on occasion. [But] you'll find it actually takes a lot longer to calculate. Because OFFSET says start here and go there. It doesn't care about all the things in between. But with an INDEX, it needs to know the whole range and then decide where to go. That can take more time. Whilst OFFSET might calculate all the time, it's quite quick. And it shouldn't be a reason just to not have it at all. It's very versatile for being able to move somewhere else and extend ranges without messing up your formulas. So it's my number two.
Third one is going to be the MOD function. If you've ever had regular payments or regular transactions in a model, then this is a function for you. Because what the MOD function does is it looks at the remainder when you divide one number by another. If we had three people in a room and we dealt out playing cards. There are 52 cards in a deck.
Let's say there's Alexis, there's me, and the invisible man while all being in a room together. And so I deal to Alexis, first of all ladies first, then to the invisible man, then to myself, and so on. I get the third card, the sixth, the ninth card, etc. Question is, who would get the final card? And the answer is you would Alexis because you're sitting to my left and 52 divided by 3 is 17, remainder 1. It will go around 17 times and then the 52nd card will go to you. If we get remainder one it goes to you, if we get remainder 2 it goes to the invisible man, and if there's no remainder it comes to me as the dealer. That's how it goes.
The same if we had five people we were dealing with. It's called clock arithmetic doing this. Again, it doesn't sound the most exciting or impressive thing, but let's be honest. What accountants, a lot of what we do is mundane, sorry to break it to you, but it is. But if we can make things a little bit faster. Imagine you're paying tax quarterly in a second period of each month. Well, you could actually just put your month numbers up at the top and use the clock arithmetic.
So you've got MOD of the month number comma 3 will give you 1, 2, 0, 1, 2, 0, 1, 2, 0, going across the whole way. Now, a little trick on that if you want to make it 1, 2, 3, 1, 2, 3, you can use an IF statement and say, if it's 0 make it to 3. But an easier way it's go MOD, open brackets, the counter number, minus 1, 3, close brackets plus 1. If you've written that down there that would then give you the formula 1, 2, 3, 1, 2, 3, 1, 2, 3. You'd be a frustrated musician 1, 2, 3. It could never go anywhere, but it would do that.
Then you can have another formula right beneath that says if that MOD number is a 2, then pay tax, and then every second, fifth, eighth, 11th, 14th, 17th month, you pay the tax and you could do your calculation. If you have dividends that are paid every six months in let's say the second and eighth month of the year, you would use the MOD of the actual month number comma 6, and when the month number is 2, you will pay the dividend.
You'll probably think of other things, interest payments, progress payments, or milestone payments, lots of different things. Again, it's one of the things we use all the time and I see horrible things done in Excel to try and do it because people don't know these functions. In summary, XLOOKUP gets the honourable mention, but SUMPRODUCT, OFFSET, and MOD are my three.
See Tho: If you would like to look at the articles that Liam has written on these functions, go to our website, look for this specific podcast episode, and we will provide the links in the transcript.
You have said a lot about how great Excel is. I'm curious, what is something that Excel cannot do or does very poorly that you wouldn't advise accountants to use Excel to do?
Bastick: As I said before, people have said over years, "You can't do this in Excel". I'm not saying it will necessarily be the best software. Things it's not good at is security. Going back to what I said, you can do anything in Excel. If you can't figure out how to do it, you either lack the vocabulary, the imagination, or a combination of all of those things. You can do anything. That's why it's so popular.
What it's not so good at is security. Since I think Excel 2013 you can actually have it pretty well encrypted so that if you forget the file password to open, you might as well rebuild the whole model because you're never going to find out how to get into it.
But if you have a workbook and you hide a sheet and you password-protect and things like that, I'll be able to get into this in about two or three seconds, without any software. It's very easy to crack. But that's what makes it so popular is because it's so flexible and versatile.
Other things where it's a bit more awkward are when you're wanting a drop-down list that you want to be able to update where you get software to do that. Now, you can do this with VBA code and things like that as well. But again it's more [challenging]. I would say it's a great testing ground for everything. Whether it's the thing that you should use in all occasions, I'm not so sure.
But the fact now, people say, "Oh, well, once you get past a 1,048,576 rows, you're stuffed." Well, now the largest file I've ever had in Excel was for an electricity distribution company that was monitoring everybody's usage on a half-hourly basis since they became a customer and that had about 900 million records.
The only reason we didn't put more data in is we didn't have any more data. That was 782-megabyte file. That's opened in less than half a second. Because we didn't get it to render in Excel, we just put it in the backstage, so there's ways and means. There's things where I think when you're working on transforming a lot of data, I think SQL works better or R or Python than Power Query. But for most people you aren't working with 900 million records, and therefore, I think you're fine.
This is when we're in the real-world big data. Everyone talks about big data, but if you actually worked out how big big data is then there's only about eight companies in the world who could actually say they work with big data. The rest just have lots of data, but not necessarily big data with a capital B and D. So I'll get off my soapbox now.
See Tho: Liam, as an MVP, you have conversations with Microsoft, and you mentioned earlier that they share previews to you of new cool things that they develop. From your conversations with Microsoft, what have they said about the future of Excel?
Bastick: Years ago, I had a newsletter. We had a wish list of things we'd love to see in Excel and we got told by Microsoft, please don't do this because being an MVPs, people think that we've got inside information and therefore we'll know what it is. You've stumped me with that question Alexis because there's lots of things I'd love. I'm going to say there's thing I'm very vocal about, as I'll keep going on. I'll be very clear. I have no idea whether Microsoft will ever fix this.
But a couple of things that just bother me that I don't understand why they don't change. For instance, in a slicer, why are the default values always the opposite of what you want? When you create a chart and you want to hide the data, why is the default that if you hide the data the chart becomes invisible? Who'd want that? When you are actually putting data on a chart access and you want to have the maximum, minimum as formulas, why can't you do that in VBA?
These are things I'd love to see Microsoft do. I don't think they've got any intention of doing any of these. These are things I've tried to put out. These are my rhetorical questions here of what I'd love to see. They know because I keep saying these things to them. I have no idea whether they'll ever change it.
On dynamic arrays I'd love to see them have it so that it's great that when you write a formula it will spill and calculate for the areas. Wouldn't it be nice if you took the formatting with it? If I format the first one as a currency, why can't I have all the rest as a currency as well? They'd be nice things to have. These are questions I'm sure lots of other people have asked too.
The future of Excel, though, in terms of where it's going, have a look at all the new functions they put in and what's coming. For a long time, I think Microsoft just trod water. I think they did a big rework under the hood when Excel 2007 came around after 2003.
But my feeling is — I've got no anecdotal evidence, this is nothing more than my own feeling — is that I think they're reasonably satisfied with the engine that's is in there now. I think it's got some limitations. But now they're playing with — they want to take it to the next level like, how do you do three-dimensional lookups? How do you turn it into a data cube? Everyone treats Excel as a database. Well, how do we turn it into a database? All those sorts of things.
If you look at the functions have come out like the LAMBDA, the MAKEARRAY, the BYROW, the BYCOL, all those in a year, I think you can see where Microsoft is thinking of taking it.
See Tho: Well, I suppose I wasn't very successful in trying to get some inside information for our listeners, but thanks for sharing. Three-dimensional lookups and Excel as a database are good food for thought for its future possibilities.
What's a final advice you have for fellow accountants to improve on their Excel skills this year?
Bastick: The main advice I would say is — people are surprised when they see me present. They think because I've been working about this for so long. I mean, first of all I think they're surprised that I can actually make a living out of this. My background, I think they'll probably say, "No, I don't think he does." But that's another story.
But one of the things is that I think people expect me to always do things and come up with really complex calculations. Most of the time I follow something, I say Liam's rule of thumb, which is try and make it every formula you write is not longer than your thumb.
That forces you to step it out. Over the years in financial modelling, I've helped to co-write various best practice treaties out there for various organisations about what constitutes a best practice proper noun model. I water it down to one word, CRAFT.
That stands for C for consistency. Make your formulas consistent, which is one formula across the row. Robust is the R, so there's no errors in your model. The A is just so it doesn't sound like a place in Poland. F is flexible. So the whole point is there's no hard code in there. Make sure your inputs will update the model. Have no hard inputs in there that don't do anything.
The T is transparent. Most people do not have Ph.D.s in nonlinear approximation theory, and I'm not happy seeing an Excel formula that Tolstoy would be proud of. It's better to keep it so that they can follow it. Most people don't even look at the formula bar. If you were to print out your spreadsheet, could someone else follow it just by looking at your pictures? That's what you want to aim for. That will be my tip.