BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
You can now create and use anonymous functions in Excel (usually called "Lambda functions"). Learn how to add to the stock of Excel functions without having to learn any programming code!
Posted by Andy Brown on 21 December 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Stages to follow to create a lambda function
This page shows how you can create any lambda function by breaking it down into 5 separate steps (as you become more familiar with the concept you'll be able to merge these together):
|1 - Create the formula||Create the formula for what you're trying to do without using a function.|
|2 - Identify your arguments||Work out what you need to supply to your function in order for it to return the right answer.|
|3 - Create the function||Type in the function and test it out with dummy arguments.|
|4 - Name your function||Use the Excel Name Manager dialog box to name the function that you've created.|
|5 - Try out your function||Test to see whether your function works!|
Let's begin by defining the formula we want to calculate in our function.
Step 1 - creating the formula
The first thing to do is to check that you know what you're trying to achieve:
For our simple example, we will use the & key to concatenate a person's first name, a space and their last name.
Step 2 - identify your arguments
Most functions require one or more arguments to work. Our example needs to know two bits of information: someone's first name and their last name. We will therefore create the following two arguments in our lambda function:
|FirstName||The first name of a person|
|LastName||The last name of the person|
Note that you can call the arguments anything you like. Good programmers use descriptive names for their arguments, to make it easier to remember what information they represent.
Step 3 - create the function
The syntax of a lambda function is this:
A lambda function consists of a list of arguments followed by a formula determining what the function should return.
When you type =LA into a cell the LAMBDA function should appear:
If you only see the LARGE function appearing, you haven't yet got access to the LAMBDA function (see a later page in this blog for what to do about this).
You can then type in your function (here I've pressed Alt + Enter to create carriage returns to break up the formula with new lines, to make it easier to read):
The function will take two arguments (which we've called FirstName and LastName) and return them joined together with a space in between.
When you press Enter, this will give you a calculation error:
Although you've created a function, you haven't actually passed any arguments to it.
To test your function, pass some arguments to it by adding them in parentheses after the function:
Here we're going to see if the function works with Donald Trump (resisting the temptation to insert any joke at this point). Don't put a space between the function and your list of arguments (if you do, the formula won't work).
When you press Enter, the formula gives Donald Trump:
Yeah - it works!
Step 4 - name your function
So far you haven't gained anything (in fact, you've just made a simple formula considerably more complex). To make your function reusable, first copy it:
Be careful to copy just the syntax of the function, not the values you're testing it with.
Go to Name Manager on the FORMULA tab of the ribbon:
You can also press Ctrl + F3 to go to this.
Choose to create a new name:
Click on this button to create a new lambda function (which is just a type of range name).
Type in a name for your new function, then press Tab to go to the Refers to: box:
As promised, we're calling this function WiseOwlFullName - a bit of a mouthful, perhaps.
Paste in to the Refers to: box your function code:
This is a terrible editor - if you make a mistake, it's often easier to start again rather than try to correct it! Microsoft have hinted that they may improve this editing facility in future Office 365 versions (they need to).
Press OK and then Close:
After confirming the creation of your new function, click on Close or press ESC to close down the Name Manager dialog box.
Step 5 - testing your function
Time now to see if your function works!
Initially things look promising - Excel has heard of your function.
However, when you get to the list of arguments you're not given any help:
It's up to you to remember which arguments came in which order.
But it's all been worth it, surely?
Pressing Enter should give you Donald Trump!
Having shown how to build up a formula, the next page of this blog gives 3 examples of where you might use lambda functions in practice (the last one of which uses recursion to call itself!).