Excel’s LAMBDA Function [Simple Breakdown]

 

The LAMBDA Function lets you create and modify your own formulas.

Instead of creating complex sets of formulas with messy referencing, just master this one skill!

We will guide you through the basics of the function, and show just how it can save your time.

What Is The Excel Lambda Function?

It enables users to create custom functions that they can save by any name they want.

This function will then be saved and can be used throughout the workbook.

You can now compose any formula in Excel, wrap it into the LAMBDA function, give it a suitable name and save it as a function.

The next time you need to apply the same formula, don’t go all the way retyping it.

You can instead refer to the custom function built and saved by you previously.

And the good news! You don’t need to be a programmer or know how to use VBA to do all of this.

Mastering this tricky skill will take you from average to expert, just like our Excel training can!

excel-promo-1

Syntax and Arguments

The Excel LAMBDA function reads as follows.

 

• Parameter: Whenever you type any function, a small popup shows you the number of arguments a function has and that you must enter for the function to run.

This time it’s you who wears the developer’s hat. Parameters define the arguments that you want to set for your formula.

A parameter is an input value that can be in the form of cell references or values. Excel can accept up to 253 arguments.

• Calculation: This has to be the formula that you want to be converted into a function.

Let’s jump right into the article to see how you can create your very own function.

How To Create The Excel LAMBDA Function

It’s time we create our function.

Step 1: Write the LAMBDA function in Excel

• Make a core formula. The LAMBDA Excel function is a formula instilled into Excel by the name of a function.

For example, if you want to divide two numbers in Excel, you write the following formula.

It involves no function and is a simple formula. Run this in Excel, and you’d get the following answer.

The answer is 4. Simple.

So our formula is 20 (the dividend) divided by 5 (the divisor).

 

• Launch the LAMBDA function to create your function

 

• Define the parameters (arguments).

These are the input values that would be required whenever the targeted function is run. We want our function to have two input values, the dividend, and the divisor.

• Write both these as the arguments of the LAMBDA function.

 

• Next, define the calculation (the formula).

Our core formula is dividend/divisor. Add another comma to the LAMBDA function to define the said formula.

 

The Excel LAMBDA function is all complete and set to go.

Don’t hit ‘Enter’ already. Or if you do, Excel will return a #CALC! error because it has no values to process.

Step 2: Test the LAMBDA functions

Has Excel rightly picked the function defined above? There’s one way how you may check this.

The ‘function call’ allows you to check if the formula defined in the LAMBDA function works correctly.

• To test the LAMBDA function, give it some input values right after the parenthesis is closed.

 

We have added a new parenthesis to the end of the LAMBDA function (A1, A2).

The LAMBDA function will test the formula (Dividend / Divisor) using these two cell references.

The answer is 4 which is 20 divided by 5. The results provided by LAMBDA adhere to the result of the simple formula (= 20 / 5).

It is always good to test your LAMBDA function to ensure everything sits in place.

Step 3: Save the function

We have created and tested the LAMBDA function in Excel already. Now it’s time to name it and save it in Excel.

• Copy the LAMBDA formula. Make sure to only copy the following part of the LAMBDA formula.

 

You ,ust not copy the function call. It is only meant to test the function and has nothing to do with saving the function.

• Go to Formula > Defined Names > Name Manager. Or use the keyboard shortcut [Control Key + F3] to launch the Name Manager.

• This launches the name manager as shown below.

• From the Name Manager dialog box, Click ‘New’ as highlighted above.
• In the Name Manager, fill in the following fields:

1. 1. Against the Name box, type a suitable name for the function. We are setting it as DIVISION.
   2. Against the Scope box, choose ‘Workbook’. This means you can use this function across the whole workbook. Alternatively, if you select a particular Sheet as the scope, you’d only be able to use this function across that sheet.
   3. Against the Refers box, paste the formula copied above. The formula must start with an equal sign (=). To make any changes to the formula once pasted, press the F2 key to enter the editing mode.

• Click ‘Okay’ to save it.

Testing the function!

Guess what? We are all done. It’s time we test the function we’ve just created.

• Activate a cell and begin writing with an equal to sign:

 

Must Note!

Did you notice something? As soon as you typed the first three letters of DIV, Excel identified it as a function.

This means the DIVISION function is now saved to the Excel functions library. Using a custom function after it’s created in Excel is seamless enough.

• The first argument of the DIVISION function is ‘dividend’.

 

We have created a reference to cell A1 as the first argument it contains the number to be divided.

• The second argument of the DIVISION function is ‘divisor’.

 

We have created a reference to cell A2 as the second argument contains the divisor.

• Hit ‘Enter’ to see the results as follows.

Excel runs the division function as intended i.e. Dividend / Divisor.

That’s how you can create and save any function in Excel.

Things to Remember about the LAMBDA function

To have a seamless experience creating and running functions with the LAMBDA function, take note of the following:

1. The function or parameter name must not be longer than 255 characters.
2. It must not start with any punctuation mark except for an underscore ( _ ) or backslash ( \ ).
3. Do not use names that coincide with the names of built-in Excel functions.
4. Do not use names that are similar to cell references like AAB1 etc.
5. Must not enter more than 253 parameters into a single LAMBDA function.

Lambda Function Examples

We are already across one basic example of creating a function in Excel using LAMBDA.

Rest is all about practice. By introducing the LAMBDA function, Microsoft Excel has set forth limitless opportunities for Excel users to create functions.

Let’s see some more examples of how you can create amazing functions in Excel which you always thought should’ve been there!

Example No. 1: Basic Formula

Remember the formula for finding the area of a right-angled triangle? No?

Neither do we. Let’s feed it into Excel as a function so that the memorizing responsibility shifts to Excel.

Step 1:

• The formula for finding the area of a triangle is as follows:

 

B = Base of the triangle; and

H = Height of the triangle

• Launch the LAMBDA function to create your function

 

• Define the parameters (arguments). So our core formula has two parameters and is:

 

• Let’s formulate this into the LAMBDA function.

We want our function to have two input values, the base, and the height.

• Write both these as the input parameters of the LAMBDA function.

 

• Next, define the calculation (the formula).

 

The LAMBDA function is all complete and set to go.

Step 2:

Time to test the LAMBDA function!

• Give it the input values for base and height right after the parenthesis is closed.

 

• The LAMBDA function will test the formula (Base*Height / 2) using the values in Cell A2 and B2.

The answer is 6. The result provided by the TAREA function adheres to the result of the simple formula (= (4*3)/2).

This means we are all good to go.

• Copy the following part of the LAMBDA formula.

 

• Go to Formula > Defined Names > Name Manager.
• This launches the name manager as shown below.

• From the Name Manager dialog box, Click ‘New’ as highlighted above.

• In the Name Manager, fill in the following fields:

1. 1. Against the Name box, type a suitable name for the function. We are setting it as TAREA.
   2. Against the Scope box, choose ‘Workbook’.
   3. Against the Refers box, paste the formula copied above.

• Click ‘Okay’ to save it.

Step 4:

And we are all done. Let’s test the function we’ve just created.

Here we have some data points for the base and height of a triangle.

• To find the area using these data points, activate a cell and begin writing with an equal to sign:

 

As soon as you type the first few letters, Excel identifies it as a function.

• The first argument of the TAREA function is ‘base’.

 

We have created a reference to cell A1 as the first argument it contains the bases.

• The second argument of the TAREA function is ‘height’.

 

We have created a reference to cell B2 as the second argument contains the height.

• Hit ‘Enter’ to see the results as follows.

Viola! Excel runs the TAREA function as intended i.e. (Base*Height) / 2.

With this, you now have your function for calculating the area of a triangle in Excel.

Seem handy? Check out the XLOOKUP and EFFECT, two other advanced and very powerful functions!

Possible Errors And Troubleshooting

The magnitude of problems that the LAMBDA functions might pose is proportionate to its versatility.

Here are a few common problems you might face with the LAMBDA functions.

1.      Inappropriate Naming:

The LAMBDA functions give you the privilege to set function and parameter names of your choice.

But this comes for a price. You must be careful while you set the names for the function you create. And for the parameters of that function.

For example, if you set the names of the parameters as DIV1, DIV2, and so on, here’s how the LAMBDA function would react.

This is because DIV1 and DIV2 are cell references in Excel. When named as a parameter, Excel takes these as a cell reference and not as the name of parameters.

You may change it to something different such as DIVIDEND and DIVISOR.

And Excel wouldn’t pose the error as before.

2.      #VALUE! error

If your LAMBDA function gives back a #VALUE error, there could be many reasons for it. Check for either of the following errors:

• Your LAMBDA function has more than 253 parameters to it. This breaches the maximum limit for the parameters of the LAMBDA function.
• The names defined for the parameters are not the same as those used in the calculation. This might be a spelling error or an oversight. For example:

 

The parameters defined in the above function have different spelling. However, the names used in the calculation differ in spelling. This will give back a #VALUE error.

• You have used the wrong number of arguments while using the same function in the workbook.

3.      #CALC! error

With the LAMBDA functions, Excel is likely to pose a #CALC! error if you save the function without testing it by using the function call.

If you are still getting errors, make sure to use formula auditing to help yourself.

Conclusion

Excel experts call it the most powerful function of Excel – a function that makes Excel Turing-complete.

By introducing the LAMBDA functions, Microsoft has made Excel computationally universal.

At any time, if you find the function library of Excel short for your creative needs, create and add new user defined functions to it.