Browse 554 attributed reviews, viewable separately for our classroom and online training
Creating and using Lambda Functions in Excel
Part one 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 (this blog)
  2. Stages to follow to create a lambda function
  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.

Creating and using Lambda Functions in Excel

Lambda functions are a new Excel feature allowing you to create your own custom functions. 

You've always been able to create your own user-defined functions in Excel using VBA (or even latterly in JavaScript), but lambda functions allow you to do this without learning a separate programming language.

Example of a lambda function

Suppose you want to show someone's full name:

Example of function

An example of where a function would be useful: creating a full name by joining together someone's first and last names, with a space in between.

 

You could copy the formula above give each person's full name:

Copied formula

The same formula applied to different names.

 

However, it would be nice to be able to create and use a function (let's call it WiseOwlFullName) to do this instead:

Lambda function

A lambda function to do the same thing.

Advantages of Lambda Functions

There are two great benefits to creating a function like the one shown above.  The first is that you've encapsulated the logic of how the function works in one place, so you only need to make changes in one place:

Change to algorithm

Rather than having to change the formula giving someone's name wherever it appears, you can just change your lambda function to accommodate (for example) someone's middle name when your calculation rules change.

The other advantage is that you can use lambda functions anywhere within a workbook, without having to rely on copying existing formulae:

Formula for name

Rather than having to remember the logic of a formula, you could just use the WiseOwlFullName function instead.

Disadvantages of Lambda Functions

To dampen your euphoria, gentle reader, here are three drawbacks to using lambda functions:

Drawback Notes
Lack of availability Lambda functions are currently in preview - see later in this blog for more notes on when you can expect to see them in your copy of Excel.
Difficulty If you're an accomplished programmer, you'll be familiar with the idea of lambda functions; otherwise, it can take a while to get used to the concept.
Lack of intellisense As I mentioned above, it can be difficult using a lambda function, since no intellisense appears suggesting what arguments you should insert.

Here's an example of this last point:

Arguments not showing

Here's hoping that you remember the arguments that your function takes!

 

Having seen what a lambda function is (and why you might and might not want to create one), let's have a detailed look at how to create and use one! 

You can see all of the examples in this blog working if you download this workbook.

This blog has 0 threads Add post