The next step

Details on how to join Office Insiders

[Weblink]

Creating your own functions in Excel using LAMBDA

New LAMBDA function

A new research project from Microsoft’s Calc Intelligence team will allow you to create your own functions and store them in Excel’s Name Manager. The new function, LAMBDA, is available for any Microsoft 365 subscribers who opt into the free Office Insiders programme (see The next step ).

Tip. You’ve always been able to create your own user-defined functions in Excel using VBA, but LAMBDA functions allow you to do this without learning a separate programming language.

How to use the LAMBDA function

Example. Let’s say you have your contacts’ first names in one column (B) and their last names in a second column (C) and you want to create their full names by joining together their first and last names with a space in between in column D. Excel doesn’t have an inbuilt formula for this so you would need to create your own formula in column D which would be =(B2&” “&C2).

Using LAMBDA. However, it would be nice to be able to create and use a function, let’s call it Fullname, to do this instead: =Fullname(B2,C2).

1. Create the formula. The first thing to do is check that you know what you’re trying to achieve, i.e. =Fullname(B2,C2).
2. Identify your arguments. Most functions require one or more arguments to work. Our example needs to know two pieces of information: someone’s first name and their last name. You therefore need to create two arguments in our LAMBDA function: FirstName and LastName.

Tip. You can call the arguments anything you like. Using descriptive names for the arguments makes it easier to remember what information they represent.

1. Create the function. The syntax of a LAMBDA function is: =LAMBDA(First argument, second argument, calculation formula). In our example this would be: =LAMBDA(FirstName, LastName, FirstName&“ “&LastName)(B2,C2)
2. Name your function. So far you haven’t gained anything (in fact, you’ve just made a simple formula more complex). To make the function reusable, first copy the syntax, i.e. =LAMBDA(FirstName, LastName, FirstName&“ “&LastName). Don’t copy the arguments. Go to Name Manager on the Formula tab of the ribbon and choose to create a new name. Type in the name for your new function (e.g. Fullname ) and then paste the function syntax into the “Refers to:” box.
3. Using the function. In future when you want the full name, all you need to do is use the function: =Fullname(B2,C2).

Advantages of the LAMBDA function

We have obviously used a very simple example but if you are continually using a more complex formula, a great benefit of using LAMBDA is that you can use your newly created LAMBDA function anywhere within a workbook without having to copy existing formulae.

The second advantage is that you can encapsulate the logic of how the function works in one place, so you only need to make changes in one place.

For details on how to join Office Insiders, visit https://www.tips-and-advice.co.uk , Download Zone, year 13, issue 7.