The LAMBDA Function Explained – How to Create Custom Functions in Excel

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

Watch on YouTube & Subscribe to our Channel

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.

Excel File Icon 2021 xlsx LAMBDA Explained.xlsx

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.)

Turn complex formulas into simple functions using LAMBDA

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.

Data table for the LAMBDA function

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:

Averageif Function Already existing

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.

Set Parameters

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:

Lambda function with arguments parameters named.
Click to enlarge

Next, we want to replace the parameters within the calculation with the new parameter names.

replace the references in the calculation with the 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.

New Name window

Then paste the LAMBDA function into the Refers to field.

New Name Window

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.

New function available including comment

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:

Completed AVERAGE TOP PERCENT Function

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.

Add parameters after the lambda function
Click to enlarge

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.

Name Manager Window

You can choose any of the items in the list to make modifications to its name, comment, or formula.

Reusing LAMBDAs

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.

Other Workbooks

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.

Copy Lambda Functions to other workbooks
Click to enlarge

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.

Turn complex formulas into simple functions using LAMBDA

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.

Related Posts

If you like this new LAMBDA feature, I think you'll like these other posts as well:

  • Thanks for your documentation on LAMBDA. Have written a formula(works well) that uses LET Function to assign variables but cannot convert it to use LAMBDA

  • Search
    Generic filters
    Exact matches only


    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...


    Free Excel Training Webinar Modern Power Tools