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.
Creating and using Lambda Functions in Excel
Lambda functions are a new Excel feature allowing you to create your own custom functions.
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:
|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.