Browse 537 attributed reviews, viewable separately for our classroom and online training
Creating and using Lambda Functions in Excel
Part two of a four-part series of blogs

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!

  1. Creating and using Lambda Functions in Excel
  2. Stages to follow to create a lambda function (this blog)
  3. Three examples of lambda functions
  4. When will you be able to use lambda functions?

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):

Step Notes
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:

Creating the formula

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:

Argument Notes
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:

Syntax of a lambda function

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:

Lambda function intellisense

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):

Lambda formula

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:

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:

Testing 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:

Results of test

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:

Copying your function

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:

Name Manager

You can also press Ctrl + F3 to go to this.

 

Choose to create a new name:

Creating a 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:

New Lambda function

As promised, we're calling this function WiseOwlFullName - a bit of a mouthful, perhaps.

 

Paste in to the Refers to: box your function code:

Pasting in function

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:

Name manager

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!

Trying out the function

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:

Intellisense problem

It's up to you to remember which arguments came in which order.

But it's all been worth it, surely?

Testing - final

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!).

This blog has 0 threads Add post