In Excel version 365, create custom, reusable functions in Excel using LAMBDA functions.
Links to topics on this page
Excel Lambda function syntax
=LAMBDA([parameter1, parameter2, …], calculation)
For example, we’ve created a custom function named get_first_letter().
The function is:
=LAMBDA(x, LEFT(x, 1)), where the parameter is ‘x’ and the ‘calculation’ is
Below shows how we call the function from a cell in the worksheet. We’re inputting the value held in Cell C9 (‘Hello’), and the function returns the first letter of the string ‘H’.
Steps to create and use an Excel LAMBDA function
- Test the function – create a simple formula in a worksheet cell. Try not to jump the gun and head straight for the LAMBDA function, test out your formula first in a cell.
- Insert the function into Excel’s LAMBDA formula syntax, and test again in a cell in the worksheet. To test the function (before saving and naming it in step 3 below) try it out first directly in the worksheet – create it AND call it from within the same cell. E.g.
=LAMBDA(x, x+5)(6). [result = 11]; you’ll get an error if you try to create it without calling it, see Errors with Lambda functions below.
- Add the LAMBDA formula to the Name Manager so that it can be reused throughout the workbook, or on individual worksheets. At this stage you give your custom Lambda function a user-friendly name so that you can call it from cells in your worksheet/workbook.
- To use the Excel LAMBDA function, call it from a cell in the workbook. When you start to type the name of your new function after ‘=’ in the cell, you’ll be able to select it from the list of functions in the prompt list.
Naming a Lambda function in Excel 365
- Open the Name Manager (from the Formulas tab in the ribbon) and select ‘New’.
- Follow Excel’s syntax rules for names, EXCEPT don’t use a period (.).
- Use upper, lower case letters, symbols, numbers.
- If you prefix your custom functions with the same combination (e.g. ‘myLambda_’) then when you want to call them, they will all appear together in the Excel Native functions prompt list – helpful if you forget what you have called your functions 🙂
- The comment that you add for the function details will appear in a tooltip when you select the function.
- You can also edit or delete your custom functions from the Name Manager.
Inputting number and text values into Lambda functions
You can input number and string values into Lambda functions, and values can be:
- fed in directly (in the same cell as where the function is called), or
- read in via a cell reference.
When inputting strings into a function directly, remember to wrap the values inside quotes [single ”, or double “”], otherwise you’ll get a
Error results using Excel Lambda functions
- If you create a LAMBDA function in versions of Excel other than 365, you’ll get a #NAME? error when you try to call the function. So although you seem to be able to create the function and save it in the Name Manager, you can’t actually call the function.
- #VALUE! error, if:
- you enter more than 253 parameters;
- an incorrect number of arguments is passed to a LAMBDA function.
- Calling a LAMBDA function from within itself and if the call is circular, Excel can return a #NUM! error, if there are too many recursive calls.
- Creating a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.
>> On the next page we look at how to share and copy Lambda functions between Excel workbooks, and the potential complications that can arise from doing so!