Quick Answer & Formula
Excel
Sheets
Advanced
=LAMBDA([parameter1, ...], calculation)(value1, ...) LAMBDA lets you turn complex spreadsheet formulas into custom reusable functions with clean names (e.g. =CALCULATEOT(hours, rate)) by saving the LAMBDA expression in the Name Manager.
LAMBDA Function in Excel & Google Sheets
The LAMBDA function allows you to author your own custom functions without writing a single line of VBA, JavaScript, or Apps Script.
1. Creating and Testing a LAMBDA
To test a custom temperature conversion function in a cell:
=LAMBDA(celsius, (celsius * 9/5) + 32)(25)- Returns:
77($25^\circ C = 77^\circ F$).
2. Converting a Formula to a Global Workbook Function
Step 1: Define in Name Manager
- Go to Formulas > Name Manager > New
- Name:
OVERTIMEPAY - Refers to:
=LAMBDA(hours, rate, (MIN(40, hours) * rate) + (MAX(0, hours - 40) * rate * 1.5))
Step 2: Use Anywhere in Your Sheet
Now you can call your new custom function just like any native Excel formula:
=OVERTIMEPAY(B2, C2)
?
Frequently Asked Questions
How do I save a LAMBDA function so I can use it anywhere in my workbook?
Open Excel's Name Manager (Formulas > Name Manager > New). Name your function (e.g. OvertimePay) and paste your =LAMBDA(hours, rate, ...) expression into the 'Refers to' box.