Excel tips: how to use custom functions without macros

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood CPA:
My name is Neil Blackwood, and in this episode, I'm covering a brand new function that allows you to create your own custom functions within Excel. Now, back in December 21, I covered a way to create custom functions using macros. So the VBA language, so visual basic for applications, these work as well. But the problem with using VBA is that you have to save the files as either macro enabled or binary. And also when the user opens the file, they need to enable the content. Well, with this new Lambda function, you don't need to worry about macros because there's no macros involved, and so you can just use the normal Excel file and they just calculate normally. To be honest, to get a good grasp of how this Lambda function works, you probably do need to watch the companion video. So it goes for over 20 minutes.

It does cover quite a few things, so check out the, in the black website for that. But, I thought I'd give you an overview, in the podcast. And so basically you have the ability now to create custom functions that can be either based on a file, or you can, create functions that will work in any file. And I've given, the examples used in the article, do both. So, we've got a, a file based one as well as a general, custom function.

Now, the beauty with creating custom functions is you can cut down the size of complex formulas, basically by sort of capturing the business logic. Um, and the beauty with a custom function is that the business logic is in one place. So if you did need to change it, you only need to change it in one place, and that will flow through all of the calculations that use that particular custom function. So the example we'll use is that, we are importing data from another system and it's invoice data, and we only get the GST inclusive amount, but we want to use the exclusive amount.

Now, obviously, we can use the round function and, calculate that, but we can also create a custom function to give us an exclusive value based on an inclusive value, because this uses GST, and, this is entered actually in a cell in the file. This example is a file based custom function. So it, it works in the file because it needs to reference that 10% cell, so there's a cell with 10% in it, and the custom function uses that to calculate the GXT exclusive amount. So if you update the cell, then that will flow through to all of the calculations that, use that 10%. Now, the Lambda function is quite unusual in terms of Excel. So firstly, the Lambda function itself doesn't calculate anything. It's a little bit like the let function that I've covered previously where you can use variables.

And the Lambda function also uses variables, within it. The idea is that the Lambda has a variable at the front, and as its first argument, you can have multiple variables, and they're basically the inputs into the Lambda. and then you process a calculation using those variables. When you are using Lambda in a cell, you only pretty much ever do that for testing purposes. So you can test the, the Lambda function to make sure it's working correctly. And the way you do that is you have your Lambda function in the cell, and on the end of the cell you have, brackets a separate brackets following the Lambda closing bracket. You have the input for the Lambda function. So basically the what's in the brackets at the end of the lambda goes into the variable that's at the start of the Lambda, and then that flows through into the calculation. And again, you only do that for testing purposes to get the actual custom function. So a function name that you create, what you do is you create a range name of that name.

So I, I use the term GST exclusive, so it's fairly long. Um, and then you paste in the Lambda function into the, when you create the name, there's a box down the bottom for the formula, and you basically paste the Lambda function into that section. You don't include those extra brackets on the end. They, they were only for testing purposes. And that's it. And then that, and that custom function can be used throughout the file, and it's in that one location. So if you did need to change it, that you only need to change it in one place. and then that will update all the way through the, the file.

So it does use range names. So the range names are the, become the custom functions. there's also, when you create the name, there's a, a, there's a space for comments. And if you put some comments in there, then that will actually appear when you are starting to type, much like when you start to type a function in Excel, it gives you some, information on it. you can get that, by including something in the comments section, and as an example of that on the video. Okay, so that's a file based example. So that's a custom function that you create to use in a particular file.

Now that's really powerful for things like, financial modelling, um, and budgeting and things like that where you might have some fairly complex formulas that you want to capture, but then simplify by, putting them in a Lambda and then just being able to use a custom function to actually access that, say that complex calculation. what that gives you is a lot shorter formulas. The example for a general custom function is actually, it's, it's an example I've used before when I use the Let Function, it's the sheet name. So you can extract the sheet name using quite a long formula, and the Let Function actually allows you to cut that formula down quite a bit. Well, we can actually use that, let function within the Lambda to do the same thing.

But, the final custom function is quite short. It's just actually sheet name, open bracket, a one, close the bracket, and there you go. that's the end of it. So the example given, again, it's in the video, I take you through all of the different steps of the creating the, I show you the, the full, function and that that function or formula, by the way, will work in all versions of Excel, but it is very long, and we can cut it down using the Let Function.

Now, again, let, and Lambda, I should mention these, the Lambda function only works in the, subscription version of Excel. It's a brand new function that's come out this year. So you do need the subscription version of Excel to be able to use this Lambda function. You can copy these between file. So if you copy a sheet that has the formula in it, um, to another file, then that custom function is, exists in that other file as well.

So these functions do exist in Files, but , there are a couple of things, a couple of ways you can, you can copy them between them. Also, I did share in the video a little shortcut. It's, if you press the F three function key, one of the options is paste list. And what you can do is you can actually list all of your range names. Now, I use range names all the time, generally, and so I will also be using range names with the Lambda function Range names are a, another topic in Excel, but, they are quite powerful to coincide with the Lambda function coming out.

There's a few other functions that came out as well, which I haven't covered. I will be covering them in a future article. There is something that is, it's a new free add-in to Excel, from Microsoft, and it's called the Advanced Formula Environment. Now, the idea behind this is it makes it easier to sort of handle and build the, the Lambda functions. Also, it has an interface with what's called GitHub.

Now , if you're a programmer or if you've got some sort of programming background, you would've heard about GitHub. GitHub is a cloud-based, basically code repository. So you can create your own code, put it into GitHub, um, they're called gifts, and you can access them from pretty much anywhere. and part of the advanced formula environment is extracting a, a custom function from GitHub. Now, Microsoft, um, acquired GitHub, fairly recently, and so it's starting to build the interfaces into its various software.

GitHub, I don't wanna get too into detail about GitHub, but, it's free to join. you can capture your custom functions there, and then you can share them with other people. You can share them with yourself, you can download it into a file. and in the, the companion video, I take you through, an example where we bring that sheet name, custom function into a, just a blank file, and the steps you have to go to to, get it to work. Okay? So Lambda, it's exciting, it building your own custom functions can help in a lot of areas.

So things like budgeting, financial modelling, reporting, dashboards, all of those areas where you can have fairly complex formulas, you can basically cut them down into a descriptive name and have that complex formula in the background. So Lambda is a little bit of a game changer when it comes to Excel. but just remember, you do need the subscription version of Excel to have access to it at the moment. Hope you found that useful. Thanks for listening.

Garreth Hanley:
Thanks for listening to XL tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best XL tips and tricks. To find out more about our other podcasts and C P Australia, check out the show notes for this episode, and we hope you can join us next time for another episode of Excel Tips.