Microsoft Excel has more than 450 built-in functions, which is admittedly a lot to master. These range from simple mathematical functions to more sophisticated functions for finance, statistics, engineering tasks and more. But even with this huge arsenal of features, it can be impossible to find a solution for every scenario. To accommodate this, Microsoft has included a way to convert your complex formula into a custom function using LAMBDA, eliminating the need for extensive scrolling through nested formulas. Creating a custom function simplifies complex calculations and eliminates the need for excessive stacking of functions.

Additionally, managing and sharing formulas can sometimes be challenging due to their layered structure and calculations. Here comes LAMBDA, the solution. A single, efficient function encapsulates the desired functionality. Let's take a closer look at how to use it.

## Understand the structure of a LAMBDA function

A LAMBDA function receives two types of arguments, the parameters for your formula and the formula itself. The parameters can be values that you pass directly to the LAMBDA function, or they can be cell references. The LAMBDA formula looks like this:

=LAMBDA([parameter1, parameter2, …], formula)

No matter how many parameters you specify, the last argument is always interpreted as a formula. You can specify up to 253 parameters for a LAMBDA function with any name. For example, the following formula would convert a Fahrenheit temperature to Celsius:

=LAMBDA(temp, (5/9) * (temp-32)

To test this LAMBDA function directly in your spreadsheet, enter the function along with its parameters and formula. Then enter your parameter(s) in another pair of brackets:

=LAMBDA(temp, (5/9) * (temp-32)(A1)

Of course, this isn't the most efficient use of the LAMBDA function because it doesn't save you from repeated typing. For this reason, Excel allows you to save the function with its own name.

## Create a named LAMBDA function in your table

Excel's Name Manager allows you to configure custom names for various items in your spreadsheet. You can use it to define names for specific cell ranges, individual cells or formulas. Here's how to use it to save your LAMBDA function.

- In your spreadsheet, click
**Formulas**Band and choose**Name manager**. - Click
**New**to create a new name. - In your custom function's dialog, enter a
**name**. For**Scope**You can leave the value at the default Workbook to make your LAMBDA function available across the entire spreadsheet, or you can select a sheet to limit its availability. It's also a good idea to include a description of your role in the**comment**Field. - In the
**Refers to**Enter your custom function in the field and click**OK**. - Click
**Close**to exit the name manager.

Now you can use your LAMBDA function anywhere in your sheet or workbook, saving the time you would otherwise spend entering your formula repeatedly. For this example, I would enter the following into the formula bar for B1 to convert A1 to Celsius

=ToCelsius(A1)

Excel automatically calls the custom function and displays the conversion. However, I'm not happy with all the decimal places. I can change the cell formatting to eliminate the decimals, but I would prefer the function to round up the value.

To do this, I add the ROUNDUP function to my LAMBDA function.

- On the
**formula**Ribbon, click**Name manager**. - Select the LAMBDA function you want to edit and revise it
**Refers to**Field. The new formula is:=LAMBDA(temp, ROUNDUP((5/9) \* (temp-32),0))

- Click
**Close**and the results should change wherever you used the LAMBDA function to reflect the rounded up conversion.

## Show off your Excel power user skills

The LAMBDA function can be a real revolution when creating complicated spreadsheets. Instead of having to repeatedly type or copy and paste the same long formula, you can create your own custom function. Once done, you can easily call this function.

Since you can easily combine multiple functions and formulas into one LAMBDA function, this function can help you significantly increase your productivity through the software.

Related

##### How I created my own functions in Excel using VBA

Automate boring Excel tasks by writing your own functions