Excel tips: Power Query, LAMBDA function, and why Excel is still relevant today

In this first episode of a two-part FM podcast series on Excel with FM contributor and Excel trainer Liam Bastick, FCMA, CGMA, we look at the most common mistake accountants make when using Excel, whether Excel is outdated and should be replaced with other software, and the new LAMBDA function.

What you'll learn from this podcast:

• The most common mistake accountants make when using Excel — not using Power Query.
• Bastick's argument on why Excel is not only still relevant but complements other software.
• Introduction to the new LAMBDA function and what it is useful for.

Play the episode below or read the edited transcript:

To comment on this episode or to suggest an idea for another episode, contact Alexis See Tho at Alexis.SeeTho@aicpa-cima.com.

Transcript

Alexis See Tho: I'm Alexis See Tho, your host for today's episode. We are going to talk about all things Excel in this first part of my interview with Liam Bastick. He is a frequent contributor to FM magazine, writing on the many Excel functions that accountants can use to improve their workflow.

I asked him how he became an Excel expert, the most common mistake accountants make when using Excel, and his advice on using the new LAMBDA function that Microsoft introduced a little over a year ago. Here is our conversation.

So, Liam, I realise I don't have the backstory of how you became an Excel expert. I know you've been a CIMA member for many years and was an accountant before you became an Excel trainer. Can you share a bit of your story?

Liam Bastick: I started off I became a CIMA accountant about 1992. So back when I was about minus 10, thinking what my age is now. Basically, an old girlfriend got me into it. I was a mathematician and she said, "Oh, you would make much more money as an accountant." She has long since disappeared, but I ended up an accountant. So I worked in mergers and acquisitions, and valuations and stuff like that.

And how I got embroiled in Excel and financial modelling is that it was just a necessary tool to an end for me. But I was relying on it the whole time for valuations and mergers and acquisitions, and I had other people dealing with it. And I kept finding that they weren't doing it correctly and/or spotting errors. I ended up doing it myself, which was perhaps not the right thing to do. But we were under time pressure, and I ended up with a reputation for being a bit of an Excel expert.

And I kept trying to get out. But to quote Godfather III, every time I tried to get out, "They pulled me back in." And so I've ended up in Excel all my life. Eventually, what I've done is I realised, if it ain't broke, don't fight it, much less fix it. People see me as an Excel expert, so if I'm gonna make my money in this world then I ought to perhaps come across and say, "You know what, I give up doing what I was trying to do. Let's just do the Excel stuff because this is what everyone comes and asks me anyway."

I've sort of accidented upon it. I have a sense of humour; anyone that's ever read any of my stuff will attest. My editor tells me that on a regular basis, then he deletes it all. But the fact is that I think you need a sense of humour to keep going with Excel and not take it too seriously. I'm logical and so is Excel, so we get on fine.

See Tho: So how long have you been on this journey doing Excel trainings?

I started off about 1986. So about a year after Excel came out and I was using things like VisiCalc and SuperCalc. So the training has probably been going since about 1996. So I've probably been doing it 25 years.

But I've been doing it for quite some time. I started off many years ago as a maths lecturer. So it's become a thing that I've ended up back in training again. And I do the consulting side as well. I like the challenges of new things, and we've got a new topic that's coming out soon, which is financial modelling but in Power BI, which is the cousin. It's not a distant cousin; it's a close cousin of Excel. But I think it's gonna be a very exciting time accountants to actually start reporting in different ways, especially in a post-COVID world.

See Tho: For those who are following your Excel column, we are very familiar with the things that you've taught through your articles. Let me get started with this question: From all the years of experience training [people] on Excel, what is the most common mistake you see accountants make with Excel?

Bastick: Oh, I'm on a personal crusade for this one. I go around the world, telling people I'm trying to change the world one accountant at a time. And I'm sure nobody listening to this has ever made this mistake, but I'm sure they've all got good friends that have perhaps done this.

And what I was going to say here is, if you've ever copied and pasted data from one place to another, deleted rows, move things round, transpose this and you do this weekly or whatever, you're making a big mistake. Because this is something that can be automated and some people have tried it on VBA and things like this. But since about 2012 or 2013, you've had Power Query to automate all this.

It reminds me of the old joke which basically says, a buddy, it goes up to somebody else and says, "You really should meditate." And the person says, "I'm sorry, I'm too busy to meditate. So, we should meditate for twice as long then."

And it's pretty much the same thing here. If you don't have time to learn what Power Query is, you definitely need to learn what Power Query is. It's built into Excel. And essentially, if you ever catch yourself doing repetitive tasks where you're filtering stuff out, moving things around and things like this, this is stuff that can be readily automated and all you have to do is refresh. I once many years ago, replaced myself with the F9 function key. I completed an entire job role just by basically pressing F9.

We spend about 80% to 90% of our time preparing data rather than analysing it. And you can invert it with things like Power Query and Power Pivot. I think people should actually draw their attention to it and look more into it. No sales pitch. It's all free. It's in Excel, but it's something that you really need to understand and look at.

See Tho: Power Query. I'm sure you have an article on that. And we can provide the link.

Bastick: Yeah, you find it on the Data tab in Excel. On the very left-hand side it says Get and Transform.

See Tho: Some people say that Excel is outdated [and that] accountants should be moving to other platforms. You mentioned Power BI yourself earlier, and I was just speaking to another member recently, and he said there's so many other tools out there today other than Excel that can do the forecasting they need to do, [and] all sorts of [other] needs that accountants have. What would you say to that argument?

Bastick: It'd be a good full sound of a deep hearty laugh, I think. I resisted answering your last question by telling a beginning story to this one. How I got involved in the first place with CIMA and writing articles that started with the Chartered Institute of Management Accountants (CIMA) and now gone over to AICPA as well, is I responded to an article about 15 years ago where someone said Excel is dead and it's over. Nobody uses it anymore.

Now it's about three quarters of a billion users worldwide will probably beg to differ with that and I pointed out, I don't think so. I think people that think Excel has had its time, don't really know it, probably are inexperienced and lack imagination as to what it can do. I always say Excel is the most popular software in the world, but it's the second best software in the world. People say, "Second best to what?" Whatever it is that you can't afford. And the fact is because it's so versatile, nobody reads any instructions and you can just go in there and do it. It's always going to be that. Any software that comes out, needs three buttons for definite; it needs "OK", "Cancel", and "Export to Excel" because accountants always end up with wanting it back in Excel for some reason or another.

So I don't think it's about moving to other platforms. It's not that Excel is a threat or stopping or stifling people from going to other platforms. It's something that complements the other platforms. You mentioned Power BI, for instance. Power BI, it's not a replacement for Excel.

It's got DAX and M, two languages which are neither complete. So if you wanted to go to last period's sales and grow by 1 + 2%. That's easy in Excel equals self to the left, multiply by 1.02. Try writing that in DAX or M. Let's have around maybe half an hour to have a go with that. And you'll be looking at index columns, you'll be using Max formulas, you'll be doing other things and realise that you'll look up on Wikipedia or Google what Turing complete means.

There are things that are powerful at doing other things. Power BI is a great visualisation tool. There's other tools that are particularly good at some things. Tableau is good for some motorised versions of pivot tables, and it's also got grid charts that Excel doesn't support at the moment.

I'm not belittling any of these other software. What I'm saying is it's something that goes with it and it's like a portfolio of tools. If you're a plumber, just go to every single job with a spanner. You have other things in there. I just have a spanner and a hammer. That's what I need for everything, but enough about my nonexistent DIY skills, but that's what it's all about here. Excel is just one of the tools. It's versatile, and I think everything that you use with it has to have some way of reporting back.

At the end of the day, people want things customising. You mentioned the Power BI. Once you put it into Power BI, it's difficult to get it back into Excel. You can only analyse it in Excel. And accountants get frustrated when they realise that even though Microsoft has very good and valid reasons why it can't take it straight back into Excel. There are logistic reasons why that's not possible. And I think it's when you realise there are these things, but it's just so portable and it's so common. It's a reason why it's taken over the world.

And it's not that outdated anyway. I know Microsoft in the last year has released nearly a dozen new functions. We had XLOOKUP, XMATCH, LET, LAMBDA and now you've got the LAMBDA Helper functions out there, such as BYROW, BYCOL, MAKEARRAY, and so on. They're trying to move on and make Excel do other things. They've also brought in recently Dynamic Arrays, the Spell functions [and] they've got two new errors, #error and #CALC!. These have all come through on top of the 502 Excel functions that are presently in there. It was just over 400 functions, is what they [Microsoft] say on their help pages. Bill Jelen and I, we think there are 522 functions at the moment. And we've got language variants and other things, especially in Thai, where it's even greater.

So I think if you think it's outdated, have another look at Excel because it's keeping up with the times. I think what's keeping it up there and challenging is young upstarts like Google Sheets and others, which people are going to. It's a free alternative. You can do this at the other end, and I think it's keeping it honest because it's doing the same things. Personal opinion.

See Tho: You mentioned the 400 or 500 some functions and there's the new LAMBDA function. Could you share a bit more about the LAMBDA function and why it is such an important advance in Excel's capabilities?

Bastick: Well, what it does is it closes the loop. I don't know whether LAMBDA will be useful for everybody out there. It closes the loop on some of the things that frustrate people in being able to do in a spreadsheet. Sometimes you want to be able to have a formula refers to itself. This is known as recursion. Now you have only been able to do that in Excel using VBA, using loops and things like that. You can now do this using LAMBDA. LAMBDA is something where you can create a write your own functions, and you just call it a name and off you go. So you just say LAMBDA, I want an x, I want a y, I want to add the two together, done.

Because it's so versatile, the help is a little bit interesting and there's next to nothing in that, but it allows you to actually write functions without knowing VBA code. You can just go into the Name Manager and start saying I want it to be a times B plus C, etc., and away you go, and it's completely transportable all through that workbook. And that's what's going to make things happen; it's going to add to people's vocabulary. Once they've got these particular functions, they don't need to keep writing it.

So, for instance, if you're writing a formula over and over again, that Tolstoy would have been proud of — it's nice and long. If you were to actually define this in a LAMBDA function, so you declare your variables, you say what the formula is, and you give it a name. So let's call it Alexis. This function can be called Alexis, and I don't know what you would do in it. Let's say you're going to take A plus B minus C. So what you do is equals LAMBDA. You'd have the A, B, C, and A plus B minus C. And then you just say whatever you wanted, then you just go equals Alexis and you define your A, B, and C. So one comma, two comma, three would be 0. One plus two minus three would be 0. And it's as simple as that. And people get it. It's opening it up to a whole new world.

But the thing is LAMBDA can call itself. So you can have the function. I'm going to call this number triangle, and I'm going to say write this function triangle. It's going to be this number. It's going to take the triangle of 1 less than and add this number on, which basically means you got the triangle of 2. It's going to add on the number 1. If you have a triangle three, it's going to take the number of triangle of two, which is the triangle of one plus one. So it goes. The first number is going to be a one, then it's going to be a three, then it's going to be a six, because it's going to add the numbers together to have one plus three plus six plus 10 plus 15. That's recursion because it's going backwards and I'm calling itself the whole time.

And these are things you find yourself doing, especially for accountants in things like calculating interests, sometimes for working out what the debt draw down is because when you draw down more debt, you've got to think about what am I going to have to get as well to service this payment. You have things that call themselves. People have written copy and paste macros and got it wrong and things like that in the past. With LAMBDA, you're going to be able to come different ways of going about it, which don't need you to be an advanced mathematician solving simultaneous equations, which is the other alternative.

It's a bit of a technical answer but this is one of the things I think with LAMBDA; I don't think LAMBDA's going to be for everybody. At the moment is still in the beta version of Office 365, unlike its counterpart LET, which has now been let out in all versions that 365 and it will come out.

I think for people who are trying to build up formulas where it's all in one cell, and they're trying to make more complicated uses of array formula and calculations like that, where you're trying to condense everything down quite quickly. I think it's going to be revolutionary.

But as I say, I don't think it's necessarily for everybody, but it's something that rounds off and completes Excel because before you couldn't do what we call recursion.

See Tho: How quickly can somebody pick up the LAMBDA function?

Bastick: It's easier than VBA. So that's the whole point. It's more accessible, because the whole point with VBA is, you need to know the code. So how most people learn VBA to begin with, is they will actually record something and go into the code and edit it and start playing with it. Then copying chunks and keeping it on their library to be able to put things together. And that's how they learn.

The thing with VBA is, I think the best analogy I can give this has been playing with DAX and M in Power Pivot and Power Query, respectively, for most accountants, I think they would probably agree that the DAX, the data analysis expressions code, is easier to understand than the M Power Query code. The M Power Query code has more to do with SQL, whereas DAX is instantly recognisable. A lot of the functions just completely transport into Excel. So you've got EOMONTH, you've got SUM, you've got IF, you've got all those functions in there.

And in a similar way, the analogy with LAMBDA and VBA, VBA is more like the M side, so it's more technical and for more code-literate people, whereas with the LAMBDA, you just go into the Name Manager and you basically write the formula you want, as you would have done in Excel cell. And I think therefore that makes it quite easy.

I think what people don't realise, though, is just because it's quite simple, they don't realise the power there is in it. As they use it more and more, they'll go, "Oh my goodness, that means I can do XYZ" and so on.

See Tho: And that is what Liam had to say in this first part of our interview on Excel. In our next episode in two weeks, he will share what he considers the top three functions that all accountants should be using, and what Excel is lousy at doing. I also probe into what he thinks our future updates and functions that Microsoft could be launching.