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

### 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:

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.

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

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.

## Conclusion

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.

## Related Posts

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

Very god!

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