How to use LAMBDA Function in Excel

Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.

How to use the LAMBDA function to create custom functions

You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be error-prone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so non-programmers can also benefit from its use. 


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

parameterA value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters. This argument is optional.
calculationThe formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required.

Take our Advanced Microsoft Excel Master Class


  • Lambda names and parameters follow the Excel syntax rules for names, with one exception: don’t use a period (.) in a parameter name. For more information, see Names in formulas.
  • Make sure you follow best practices when creating a LAMBDA function as you do with any native Excel formula, such as passing of the correct number and type of arguments, matching open and close parentheses, and entering numbers as unformatted . Also, when you use the Evaluate command, Excel immediately returns the result of the LAMBDA function and you can’t step into it.


  • If you enter more than 253 parameters, Excel returns a #VALUE! error.
  • If an incorrect number of arguments is passed to a LAMBDA function, Excel returns a #VALUE! error.
  • If you call a LAMBDA function from within itself and the call is circular, Excel can return a #NUM! error if there are too many recursive calls.
  • If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.

Creating a LAMBDA function

Here’s a step-by-step process to follow that helps make sure your Lambda works as you intended and closely resembles the behavior of a native Excel function.

Step 1: Test Formula

Make sure the formula you use in the calculation argument is working correctly. This is vital because as you create the LAMBDA function, you want to ensure that the formula works and you can rule that out if you encounter errors or unexpected behavior.

Step 2: Create Lambda in a cell

A good practice is to create and test your LAMBDA function in a cell to make sure it works correctly, including the definition and the passing of parameters. To avoid the #CALC! error, add a call to the LAMBDA function to immediately return the result:

=LAMBDA function ([parameter1, parameter2, …],calculation) (function call)

The following example returns a value of 2.

=LAMBDA(number, number + 1)(1)

Step 3: Add the Lambda to the Name Manager

Once you have finalized the LAMBDA function, move it to the Name Manager for final definition. By doing so, you give the LAMBDA function a meaningful name, provide a description, and make it re-usable from any cell in the workbook. You can also manage the LAMBDA function as you can for any name, such as a string constant, a cell range, or a table.

Lambda in Action: Calculating Value Added Tax (VAT) to a Column in a Table

Step I:

Do one of the following:

  • In Excel for Windows, select Formulas > Name Manager.
  • n Excel for Mac, select Formulas > Define Name.
Go to the Name Manager

Step II: Name Manager

Click New – highlighted

Create a New name Called “VATCALC”

Step III: Write the Lambda Expression inside “refers to” to calculate 16% of the cost

Write Lambda

Step IV: Lambda at Work. Apply the VATCalc formula to the cell to calculate VAT

Lamda at Work

Last Step: The Result

The VATCalc Result

Leave Comment

Your email address will not be published. Required fields are marked *

OCL Learning | Training, Consulting, Certification