This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Creating and using Lambda Functions in Excel (this blog) Stages to follow to create a lambda function Three examples of lambda functions When will you be able to use lambda functions?

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!

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

Posted by Andy Brown on 21 December 2020

In this blog

Example of a lambda function
Advantages of Lambda Functions
Disadvantages of Lambda Functions

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:

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:

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:

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:

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:

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:

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.

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