Getting Started With Custom LAMBDA Functions

Overview

If you haven’t yet used the LAMBDA function in Excel, you’re really missing out – it’s one of the best things to come out in Excel in the last few years!  It gives you the ability to write your own custom functions that you can reuse all throughout your files and share with others.

For many years, the only way to do this was by using user-defined functions (aka UDFs) using VBA, which required macro-enabled files.  There are still some reasons to use those, and I wrote about that in a previous article.  But now you have another option that many pros consider easier to use and manage.  And the timing couldn’t be better with the growing library of dynamic array functions and generative AI helping to use all of these new tools.

In this article, we’ll walk through why LAMBDA is so helpful, how to write a LAMBDA function and how to add it to your workbook, three different examples, and some tools that make LAMBDA easier to work with.

Why use LAMBDA

There are a variety of reasons why LAMBDA is helpful to users who frequently use Excel formulas:

1. Simplicity – instead of typing out an entire formula, just type out your custom function’s name and arguments
2. Updatability – make changes to your custom function in one place and they update all throughout your file
3. Consistency – ensure your calculations are correct every time you use them
4. Spill-ability – they can naturally spill results across multiple cells when the calculation results in an array of values
5. Recursion – the ability for the function to call itself to loop through until a condition is met (see example below)
6. Security – no need to use VBA code or save macro-enabled files

How to use LAMBDA

To use custom LAMBDA functions all throughout your file, follow these steps:

1. Define your function – start with a formula you already use (or start from scratch).  Identify any parameters you need to use (such as cell references).  These can be required or optional (see below).  Make sure to name them logically as they are displayed when you type out your function.
2. Add your function – use the Name Manager (on the Formulas ribbon tab) to add your formula.  Give it a name (I like to start with “L_” to indicate it’s a custom LAMBDA function), and include comments so you know what it is and how to use it.  See this screenshot 🖼️ for what your function should look like within the Name Manager.
3. Use your function – type an equals sign then start typing the name of your new custom function.  Note that it’s included with the function library just like the native ones!  Make sure to enter all the parameters you want to use.  You can copy and paste cells with custom functions just like native functions.
4. Edit your function – if you need to adjust the function’s definition, use the Name Manager.  Edit in one place and it will update any cells that use it, all throughout your workbook!

Optional parameters
To make a parameter optional, type brackets around it like this: “[cell_reference]”. Make sure to handle whether they are used and set default values with the ISOMITTED helper function. See this screenshot 🖼️ for an example, as well as the second example below in the Examples section.

Variables
While LAMBDA functions don’t support variables exactly like traditional programming concepts, you can get the same effect by using the LET function.  Haven’t used the LET function before?  It’s another newer, extremely useful function I covered in a previous article.  See the second example below where the LET function is used.

Recursion
Recall that a key benefit of the LAMBDA function is recursion – the ability for a custom function to call or reference itself until some condition is met.  It’s a method of using loops without full-scale programming.  See the third example below in the Examples section.

Remember that not everyone has used LAMBDA before – it might be helpful to document that your workbook has custom functions in it so that people understand what’s going on.  And of course, share the love with the custom LAMBDA functions you create!

Examples

To help you see what common LAMBDA functions look like, here are three examples, ranging from basic to more complex.  Make sure to download the example workbook so you can easily copy, paste, and tweak these!

1. Basic example: calculate the beginning of the month with an offset

For the first example, let’s define a custom function that can calculate the beginning of a month with an optional offset, very similar to the built-in (and super-useful!) EOMONTH function.  This is about as easy as it gets:

2. Intermediate example: spill while splitting text (includes optional parameter)

This example has more going on with the calculation using ISOMITTED, LET, and SEQUENCE as helper functions. Note that the split_ct parameter has brackets around it, making it optional. This custom LAMBDA function uses textsplit to split a string of text using a delimiter (like a comma). Without some creative usage of the SEQUENCE function, this cannot spill downward as well, but this custom LAMBDA function can do it!

3. Recursion example: calculate the factorial of a number

This is a basic example of a recursion function – one that refers to itself to calculate a result until a condition is met. It’s important to specify a condition so that the recursion doesn’t continue forever into an infinite loop. In this case, that condition is that the parameter must be greater than 1.

Looking for more examples you can leverage?  Then read on!

Valuable Tools

To help you use the LAMBDA function super-efficiently, here are some valuable tools you can use (see a couple in the video below!):

• LAMBDA Library Template – get started with over 50 great LAMBDA examples and spur your creativity to make your own!
• LAMBDA Search Box – use an autocomplete-driven search box to add custom LAMBDA functions from your library to any file instantly.  This is one of the hundreds of tools in the XLEV8 Excel Add-in, helping you save hundreds of hours a year.
• Excel Labs Add-in – makes it easier to see indented complex LAMBDA function definitions and offers troubleshooting and other tools.
• Generative AI – whichever tool you prefer (ChatGPT, Gemini, Copilot, etc.), GenAI is extremely helpful at writing custom LAMBDA functions you can easily copy and paste into your workbooks.  Check out this screenshot 🖼️ for an example prompt you can use.  A big reason GenAI is so helpful is it generally explains how the function works (see the link that includes the full explanation)!

Video

Summary

LAMBDA has changed the game in Excel.  It helps you think about building out your models and other files in completely new ways.  Whether you are trying to make your files more readable for yourself and others, or you want to future-proof your files to make them easy to manage and update, it’s a welcome addition to the robust function library Excel already has.

What kind of custom functions have you written using LAMBDA (or maybe with UDFs)?  What kinds of tips and tricks have you found along the way?  Let us know in the comments below!