What Makes Excel's Lambda Functions so Awesome (and what doesn't)?

In December 2020, Microsoft announced Lambda Functions as a new Excel feature. Lambda functions are an exciting way to define custom functions (a.k.a. user-defined functions) by only relying on Excel formulas, without having to use VBA or writing an add-in. In this blog post, we’re going to see how Lambda Functions work and why they are so awesome. It’s not all roses though: we’ll also look at the current limitations of Lambda Functions and how to solve some of them. Currently, Lambda Functions are only available to Microsoft 365 subscribers on the Beta Channel. To see if you can already use them, start typing =LAMBDA in a cell and if Excel suggests the function name, you are all set. Would you rather lean back and watch the gist of it in a video? Here you go:

Table of Contents

1. Why Do We Need Lambda Functions?
2. Create your First Lambda Function!
3. How Do Lambda Functions Work?
4. Why are Lambda Functions so Awesome
5. Limitations with Lambda Functions
6. Managing Lambda Functions Across Workbooks
7. Keeping Track of Your Lambda Functions
8. Conclusion

Why Do We Need Lambda Functions?

Let’s say you have an Excel workbook with a list of temperatures in Fahrenheit and Kelvin that you want to transform into Celsius. After looking up the formulas on Wikipedia, chances are you end up writing the following cell formula into cell C2, before copying it to the cells below:

This approach has two issues:

• If it wasn’t for the header in cell C1, you wouldn’t necessarily know what that formula is doing, which makes it harder to review and spot errors
• If you make an error in your formula, let’s say you use 9/5 instead of 5/9 in the Fahrenheit conversion, you need to search for all formulas across your entire workbook and make sure that every single one is corrected

The new Lambda Functions solve both of these issues, let’s see how!

Create your First Lambda Function!

Let’s start by defining and using a Lambda Function so we’ll get a feeling for how they work before we’ll get to a more detailed explanation in the next section. In Excel, go to Formulas > Name Manager and click on New (macOS: Formulas > Define Name). Fill in the following values; see screenshot:

• Name: ToCelsius
• Refers to: =LAMBDA(degrees, source, IF(source = "fahrenheit", (degrees-32) * (5/9), IF(source = "kelvin", degrees - 273.15, NA())))
• Comments: Feel free to add comments to explain the parameters and formula, but this is only available on Windows, not macOS.

On macOS, the two fields appear right after clicking on Define Name and have the verbose titles Enter a name for the data range instead of Name and Select the range of cells instead of Refers to.

Save by clicking on OK and now you’re able to use your new formula like this:

That’s quite a bit easier to understand, isn’t it? And the best part is that you can make changes to the formula in a single place (via the Name Manager) and it’s fixed everywhere automatically.

But how do Lambda Functions work exactly? The next section has the details.

How Do Lambda Functions Work?

Lambda is a new function that accepts the following parameters:

=LAMBDA(parameter1, parameter2, ..., calculation)

The first n parameters are the parameters of your custom function (you can use a max. of 253 parameters), in our case we use two: degrees and source. The last parameter is the actual custom function, here called calculation. Being able to refer to explicit parameter names instead of cell references makes the formula already quite a bit easier to understand. Here is, for example, the Fahrenheit conversion:

• Without Lambda Functions: IF(B2 = "fahrenheit", (A2-32) * (5/9)
• In the context of a Lambda Function: IF(source = "fahrenheit", (degrees-32) * (5/9)

If you like, you could try out a Lambda Function directly in an Excel cell like any other formula, but you will have to call it in the very same cell by adding parentheses with the parameters at the end, otherwise, you would get a #CALC! error. Accordingly, to test our formula without setting it up in the Name Manager, you could write the following in a cell (note the (20, "fahrenheit") at the end of the formula which is calling the Lambda Function with these two parameters):

=LAMBDA(degrees, source, IF(source = "fahrenheit", (degrees-32) * (5/9), IF(source = "kelvin", degrees - 273.15, NA())))(20, "fahrenheit")

This can be helpful for testing, but you can’t reuse your formula until you assign a name to it in the Name Manager. Now that we have seen how you define and use Lambda Functions, let’s summarize their advantages.

Why are Lambda Functions so Awesome

As a summary, here is why Lambda Functions are such a big deal:

• Users don’t need to learn another language like VBA to write custom functions.
• This means that you can define custom functions without having to save your Excel workbook in the macro-enabled xlsm file—not having to click on the Enable Macros button will make everybody’s life easier.
• Using Lambda Function means that you change formulas in exactly one spot and it’s going to be changed everywhere you use it. This is a massive improvement in terms of making your workbook less error-prone.
• Instead of long, difficult-to-guess formulas, you can use short, descriptive names for your custom functions which makes it much easier to understand your workbook—especially for others!

With the advantages covered, let’s not forget about the current limitations and downsides of Lambda Functions.

Limitations with Lambda Functions

To be fair: Lambda Functions are brand new, so it’s expected that there are still a few rough edges. Microsoft also explicitly mentions on their announcement that this initial release is just the beginning and that they’ll look into improving the functionality based on user feedback going forward. Accordingly, the following points should be understood as constructive feedback:

• To start with the obvious: everybody you work with will need the latest version of Microsoft Excel to get access to Lambda Functions.
• Lambda Functions are hidden away: it may not be instantly obvious to a user why two seemingly same workbooks can yield different values. This could happen if you define the ToCelsius formula in one workbook with an error and in another one correctly.
• This is related to the previous point: Lambda Function can’t be reused across different workbooks which is—in my view—their biggest limitation (but I’ll show you a workaround below).
• All parameters of a Lambda Function are optional, which becomes an issue if you just want to provide the 7th parameter in a function with 10 parameters: =MyFunction(,,,,,,"some argument",,,). Good luck figuring out which argument that is! It would help, if Lambda Functions would accept named parameters similar to how VBA does it, so you could write instead =MyFunction(some_parameter:="some argument") without having to rely on an argument’s position.
• With Lambda Functions, you don’t get a tooltip that shows you which argument is which, something that you are used to from the built-in functions. I really hope this will be added later on so they will become easier to use.

• It’s very hard to type in a formula into the Name Manager (and macOS doesn’t even have a proper Name Manager!). Even though our ToCelsius function is very simple, even just looking for a typo is nothing else than awkward. So please, Microsoft, give us a proper editor with (a) multiple lines, (b) a monospaced font, and (c) syntax highlighting to edit these functions (something that would be useful for normal functions, too, by the way). Seeing the formula capped to 40 characters in the Name Manager is no fun:

    =LAMBDA(degrees, source, IF(source = "fa
  

  Instead, a proper formula editor would show it like this, which gives you a chance to actually understand what’s going on:

    =LAMBDA(
        degrees,
        source,
        IF(
           source = "fahrenheit",
           (degrees-32) * (5/9),
           IF(
              source = "kelvin",
              degrees - 273.15,
              NA()
           )
        )
    )
  

Some of these issues have a solution: the next section continues by showing you how to work around the inability of sharing Lambda Functions across workbooks.

Managing Lambda Functions Across Workbooks

I am a Python guy, so this is the tool I use to solve pretty much all my issues in life, including the one that my Lambda Functions from one workbook don’t magically appear in all my other workbooks where I want them. I won’t be covering how to get started with Python in this blog post, but there are enough resources on the web in case you are a Python newbie (downloading the Anaconda or WinPython distribution is usually a good place to start—if you use a different distro, just make sure to run pip install xlwings to install the xlwings package). At a later point, I might also consider turning this into a little command line application to make it easier to use. Anyhow, for now, create the following file and call it lambda_report.py:

# lambda_report.py
from pathlib import Path
import xlwings as xw

# TODO: adjust the DIRECTORY path
DIRECTORY = r'C:\Users\felix\Desktop\lambda'

app = xw.App(visible=False)
for path in Path(DIRECTORY).rglob('[!~$]*.xls*'):
    book = app.books.open(path)
    print(f'------ {path} ------')
    for name in book.names:
        refers_to = name.refers_to.replace('_xlfn.', '').replace('_xlpm.', '')
        lambda_functions = []
        if refers_to.lower().startswith('=lambda'):
            lambda_functions.append(f'{name.name}: {refers_to}')
        if lambda_functions:
            for func in lambda_functions:
                print(func)
    print()
    book.close()
app.quit()

Make sure to update the path for DIRECTORY and run it on your Anaconda Prompt or Command Prompt on Windows or Terminal on macOS as follows (or use your favorite Python editor):

python lambda_report.py

This will print all Lambda Functions for all Excel workbooks in the indicated DIRECTORY including its sub-directories like so (the Lambda Functions from test_book.xlsx are taken from the official documentation):

------ C:\Users\felix\Desktop\lambda\temperature.xlsx ------
ToCelsius: =LAMBDA(degrees,source, IF(source = "fahrenheit", (degrees-32) * (5/9), IF(source = "kelvin", degrees - 273.15, NA())))

------ C:\Users\felix\Desktop\lambda\subfolder\test_book.xlsx ------
Hypotenuse: =LAMBDA(a, b, SQRT((a^2+b^2)))
CountWords: =LAMBDA(text, LEN(TRIM(text)) - LEN(SUBSTITUTE(TRIM(text), " ", "")) + 1)
ThanksgivingDate: =LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))

Now that you have your report and know which workbooks contain which Lambda Functions, you may want to add or correct a couple of Lambda Functions across a bunch of workbooks. Here is how this works: create a new Python file called add_lambdas.py:

# add_lambdas.py
from pathlib import Path
import xlwings as xw

# TODO: adjust the DIRECTORY path and the LAMBDAS you want to add
DIRECTORY = r'C:\Users\felix\Desktop\lambda'

LAMBDAS = (
    ('Hypotenuse', '=LAMBDA(a, b, SQRT((a^2+b^2)))'),
    ('CountWords', '=LAMBDA(text, LEN(TRIM(text)) - LEN(SUBSTITUTE(TRIM(text), " ", "")) + 1)')
)

app = xw.App(visible=False)
for path in Path(DIRECTORY).rglob('[!~$]*.xls*'):
    print(f'Adding Lambda Functions to: {path}')
    book = app.books.open(path)
    for func in LAMBDAS:
        name, refers_to = func[0], func[1]
        if name in [n.name for n in book.names]:
            book.names[func[0]].delete()
        book.names.add(name=name, refers_to=refers_to)
    book.save()
    book.close()
app.quit()

Again, make sure to update DIRECTORY and LAMBDAS to suit your needs, then run the script like this:

python add_lambdas.py

This will add the two Lambda Functions Hypotenuse and CountWords to all your Excel files in the specified DIRECTORY and its subdirectories—if the name already exists, it will be replaced.

Now that we’ve seen an easy way how to manage your Lambda Functions across workbooks, let’s learn about how we can keep track of them and make sure that nobody messes with our Lambada Functions!

Keeping Track of Your Lambda Functions

When you use Lambda Functions, you have your critical business logic sitting in single-line formulas of which you can only see about 40 characters at a time in the Name Manager. This doesn’t exactly make it easy to review your formulas. If you make a change to a Lambda Function, it’s easy to introduce errors (especially because Excel constantly thinks you want to reference cells in the Refers To section), so you better watch your back!

To keep the upper hand in software development, professional coders use a version control system like Git, usually through a provider like GitHub, GitLab, or BitBucket. Git keeps track of code changes and allows team members to perform a peer review by highlighting changes (think of it as the track changes functionality in Word). Git, however, has two issues when you use it with Excel files:

• Git doesn’t understand Excel files and isn’t able to show you their contents or changes
• Git has a steep learning curve that doesn’t really appeal to spreadsheet users, as the vast majority of them are business users, not software developers

Still, we believe that version control is not something that you should ignore just because you use Excel, which is the reason why we built xltrail: xltrail is a Git system that is easy to use for business users via drag and drop and knows how to deal with Excel files. Let’s see how xltrail keeps track of Lambda Functions by looking at the Excel file of the first sample of this blog post:

Clicking on Sheet1 will give you the same view as the very first screenshot in this blog post, but you will see the formulas:

But let’s not get sidetracked here—after all, I wanted to show you how Lambda Functions are tracked. When click on DefinedNames instead of Sheet1, you will see all your Lambda Functions:

Now, the right sidebar doesn’t just give you access to past versions but most importantly, you can click on Show changes to see what changed:

This allows you to verify that I have indeed properly fixed the formula for the Fahrenheit conversion (red is the previous version and green is the current version) and you know why you’re getting wrong results when you use an older version of this workbook. If you want to try this out on your own, sign up for a free trial!

Conclusion

My goal with this blog post was to show you what Lambda Functions are, why they are great and how you use them in practice. Unlike many other blog posts though, I didn’t leave it at this but moved on by looking at the challenges that come with Lambda Functions, my main critique being that they are hard to share and keep track of—especially since the Name Manager isn’t a good formula editor.

Given how ecstatic Lambda Functions have been received by the community, I am pretty sure that Microsoft will make them better over time, so I do hope that we will get a proper formula editor and a way to share Lambda Functions between workbooks. In the meantime, you can always use my short Python scripts to keep your Lambda Functions under control with a minimum of fuss!