Bottom Line: Learn how to create custom functions with the new LAMBDA feature of Excel. This post covers everything you need to know to get started, best practices, using LAMBDAS in other workbooks, and more.
Skill Level: Advanced
Watch the Tutorial
Download the Excel File
Follow along using the same file you see in the video. You can also copy the function we create in the video into your own workbook from this one, if you think it would be useful for your projects.
LAMBDA – An Exciting New Function
There's lots of excitement in the Excel world over a great new feature called LAMBDA. It's actually a function that allows you to write your own functions. (As of now, this is only available on the Insider's channels for Microsoft 365 users, but will roll out to other channels in the future.)
Creating functions that aren't already in the Excel function library can allow you to reuse and share custom functions that are unique to your industry, work type, or personalized projects. I want to show you an overview of how this new function works using an example inspired by Rob, a member of our Elevate Excel training group.
Rob's company often uses a performance metric calculated by taking the top 25% of entries from a data set (excluding zeros) and averaging them. This can be a fairly complex formula to write, and when transferring it to new reports, cell references and values need to be updated to make it work, which can potentially lead to mistakes and obviously takes some time.
It would be great if Rob and his fellow users could have a simplified function where only a couple of factors need to be identified—a function that can be used on any workbook by any user. That's where LAMBDA comes in.
Writing a LAMBDA Formula
With LAMBDA, we can actually write a custom formula called, say, AVERAGE. TOP. PERCENT that averages a specified portion (such as the highest 25%) of a range and excludes zeros. Let's look at how that's done.
Note: We had to add spaces after each dot in the custom function name (AVERAGE. TOP. PERCENT) for email filter purposes. However, the actual function name in Excel cannot contain spaces.
Copy the Existing Calculation
The first step is to copy the existing formula for whatever you are trying to accomplish. In our example it is an AVERAGEIF function that excludes zeros before pulling out the top 25% of values and averaging them. It looks like this:
This formula is going to be pasted into our LAMBDA function as one of its arguments.
LAMBDA has two arguments: Parameter and Calculation. We want to paste this existing formula into the Calculation portion of the LAMBDA function.
To define the Parameters argument, we must identify the inputs that we will feed into the calculation for the formula.
In our example, we want to replace Table1[Amount] with a variable name so that we can use it in other places. We will call that variable Range. We also want to replace the percentage with a variable so that we can change it whenever we write our new function. That variable we will call Percent.
So our LAMBDA formula, including the parameters and the calculation arguments, all together looks like this:
Next, we want to replace the parameters within the calculation with the new parameter names.
Pro Tip: Making the parameter names descriptive will help you to keep things straight as you put together your LAMBDA function. I do NOT recommend using parameters names like x, y, and z because you will have to take extra steps to figure out what the parameter names are used for when writing formulas.
Two Ways to Assign Values to the Parameters
At this point, Excel doesn't know what values to give to Range and Percent, so the LAMBDA formula returns a #CALC! error.
We have to tell Excel what we want the parameter values to be. There are two ways to go about this.
1. Create a Custom Function Through the Name Manager
Start by copying your entire LAMBDA function text. Then go to the Formulas tab and select Define Name.
That will bring up the New Name window where you can fill in the Name and Comment fields. It's a good idea to make note of the Range and Percent parameters in the comments.
Then paste the LAMBDA function into the Refers to field.
Hit OK. That's all you need to start using your new function. In any blank cell type the equals sign (=) and begin to type the word Average. You will see that the new function you created (AVERAGE. TOP. PERCENT) is in the list of available functions.
As of right now, when you start filling out the function by selecting the arguments, the screentip that usually shows the arguments for a function does not appear. That's why I had you write them into the comments, reminding you of what you need before you select the parameters.
So once you have tabbed into the AVERAGE. TOP. PERCENT function you can type the open parenthesis, select the range, type a comma, and then either select a cell that has your percentage in it or just type the percentage as a decimal (such as 0.25). When you close the parentheses, your completed formula should look like this:
When you hit Enter it will return a value that averages the top 25 percent of your range, excluding zeros.
2. Add the Parameters in Parentheses After the Formula
The second way to tell Excel what values to use for the parameters is to write them after the formula in parentheses. See the highlighted portion below.
As long as they are written in the same order that they appear in in the formula, those values will feed into the formula correctly.
This second method might be better to use while creating and testing your function before going to the New Name window to name the function.
Altering your LAMBDA-Created Functions
To make edits to the functions you created using LAMBDA, go to the Name Manager button on the Formulas tab to bring up the Name Manager window. (Keyboard shortcut: Ctrl + F3.) There you will see a list of the functions you've created.
You can choose any of the items in the list to make modifications to its name, comment, or formula.
One of the great benefits of creating functions using LAMBDA is that they are reusable in other worksheets and workbooks.
The Same Workbook
To use the custom LAMBDA function in the same workbook, you just write the formula with your function name and add references or values for your arguments (range and percentage in this example). Since the named range is scoped to the workbook, the function will be available on any sheet in the workbook.
It's important to
One important thing to note is that since the named range you created resides in the workbook that you created it in, you will NOT find it listed in your functions options in a other workbooks.
That's easily corrected by just copying a cell that contains the function from your original workbook and pasting it into the new workbook. That will import the named range into the new workbook. You will then see the function name in the formulas list when writing a formula.
If your function contains range references, then the formula that is pasted to the new workbook will contain links back to the original workbook. You can either delete the formula after pasting it or modify the references to ranges in the destination workbook.
If you have multiple LAMBDA functions in a workbook that you want to import into a new workbook, you can also copy the sheet tab to the new workbook. Just note that this will copy over ALL named ranges that are scoped to the workbook in the original workbook. If you use named ranges for other things, then you might not want this.
At this point, creating LAMBDA functions probably falls in the realm of advanced Excel users. But reading, understanding, and inputting formulas with most LAMBDA-created functions can easily be done by beginning and intermediate users.
As I mentioned previously, it will be much easier for the average user to read and understand the AVERAGE. TOP formula versus the complex AVERAGEIF formula that we started with. Even advanced users will spend less time trying to decipher complex formulas.
I believe this new feature has the ability to change how we use formulas in Excel, and I'm anxious to see how it evolves over time as it becomes more user-friendly.
I'd love to hear what you think about LAMDA and how you think you might be able to use it in your projects. Leave a comment below to let us know.
If you like this new LAMBDA feature, I think you'll like these other posts as well: