562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
Lambda functions are a new Excel feature allowing you to create your own custom functions.
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.
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.
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.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street