Expert guide
Excel LAMBDA Function Guide: Build Custom Functions Without VBA
Turn the formulas your team retypes every week into named, tested functions
Call us at +1 (385) 386-3860
Short answer: LAMBDA turns any formula into a reusable function. Write it as =LAMBDA(param1, param2, calculation), test it in a cell by adding arguments at the end, then save it in Formulas → Name Manager under a name such as NETDAYS. After that, =NETDAYS(A2,B2) works anywhere in the workbook, with no macros. It needs Microsoft 365 or Excel 2024. For a full library of business functions built into your templates, see custom Excel spreadsheet development.
Every business has formulas that get rebuilt again and again: a margin calculation with the company's specific rounding, a working-days formula that skips the office's holidays, a commission tier lookup, a standard way to clean a customer code. Each copy is a chance for a typo. LAMBDA lets you define that logic once, give it a name, and use it like a built-in function.
LAMBDA Syntax in 60 Seconds
=LAMBDA(parameter1, [parameter2, ...], calculation) Test in a cell by supplying arguments in a second set of brackets: =LAMBDA(price, cost, (price - cost) / price)(120, 84) → 0.3
On its own, a LAMBDA in a cell returns #CALC! because it has no inputs. The trailing (120, 84) supplies test values. Once the result is correct, you save the LAMBDA part, without the test values, as a name.
Step by Step: Create Your First Custom Function
Write and test the formula in a cell with real cell references until it is right.
Rewrite it as a LAMBDA with named parameters and test it with trailing arguments.
Copy the LAMBDA(...) part of the formula (without the test arguments).
Formulas → Name Manager → New. Name: MARGINPCT. Refers to: paste the LAMBDA. Add a description in the Comment box; it appears as a tooltip.
Use it: =MARGINPCT(B2, C2).
Six Business-Ready Examples
1. Margin percentage with safe zero handling
MARGINPCT =LAMBDA(price, cost, IF(price = 0, "", ROUND((price - cost) / price, 4)))
2. Working days excluding company holidays
WORKDAYSCO (assumes a table tblHolidays with a Date column) =LAMBDA(start, finish, NETWORKDAYS(start, finish, tblHolidays[Date]))
When the office adds a holiday, update the table once and every formula in the workbook follows.
3. Tiered commission
COMMISSION
=LAMBDA(sales,
LET(t1, MIN(sales, 50000) * 0.05,
t2, MAX(MIN(sales, 150000) - 50000, 0) * 0.07,
t3, MAX(sales - 150000, 0) * 0.10,
t1 + t2 + t3))Tiered commission formulas are where spreadsheets go wrong most often. Defining the logic once removes dozens of hand-edited copies.
4. Clean a customer or product code
CLEANCODE =LAMBDA(code, UPPER(SUBSTITUTE(TRIM(SUBSTITUTE(code, CHAR(160), " ")), " ", "")))
5. Age a receivable into buckets
AGEBUCKET
=LAMBDA(due_date, [as_of],
LET(d, IF(ISOMITTED(as_of), TODAY(), as_of) - due_date,
IFS(d <= 0, "Current", d <= 30, "1-30", d <= 60, "31-60",
d <= 90, "61-90", TRUE, "90+")))Square brackets mark an optional parameter, and ISOMITTED checks whether it was supplied. =AGEBUCKET(D2) ages against today; =AGEBUCKET(D2, $H$1) ages against a month-end date.
6. Running balance for a whole column with SCAN
=SCAN(0, tblLedger[Amount], LAMBDA(balance, amt, balance + amt))
One formula, one cell, spilling a running balance for every row. No more dragging a formula down and breaking it when rows are inserted.
The Helper Functions: MAP, BYROW, BYCOL, REDUCE, SCAN
| Function | What it does | Typical use |
|---|---|---|
| MAP | Applies a LAMBDA to each element of one or more arrays | Apply CLEANCODE to a whole column at once |
| BYROW | Applies a LAMBDA to each row, returns one value per row | Row totals or max across months |
| BYCOL | Same for each column | Column-level checks |
| REDUCE | Accumulates a single result across an array | Multi-step text replacement from a lookup table |
| SCAN | Like REDUCE, but returns every intermediate step | Running balances and cumulative totals |
| MAKEARRAY | Builds an array of given size from a LAMBDA | Calendars, schedules, test data |
Highest monthly value per customer row:
=BYROW(C2:N200, LAMBDA(r, MAX(r)))
Apply a find-and-replace table (tblFix[Find], tblFix[Replace]) to text in A2:
=REDUCE(A2, SEQUENCE(ROWS(tblFix)),
LAMBDA(txt, i, SUBSTITUTE(txt, INDEX(tblFix[Find], i),
INDEX(tblFix[Replace], i))))LAMBDA vs VBA User-Defined Functions
| Feature | LAMBDA | VBA function |
|---|---|---|
| Needs a macro-enabled file | No | Yes (.xlsm) |
| Blocked by macro security | No | Often |
| Works in Excel for the web | Yes | No |
| Calculation speed | Fast, native | Slower on large ranges |
| Access files, internet, other apps | No | Yes |
| Excel versions | Microsoft 365, Excel 2024 | All desktop versions |
Good Practice for a Team Function Library
Use clear, upper-case names with a prefix for company functions, such as CO_MARGIN, so they are easy to find.
Fill in the Comment field in Name Manager; it becomes the tooltip.
Keep a Functions sheet listing each name, its parameters, and a worked example with a known answer.
Build new workbooks from a template that already contains the library.
Remember that colleagues on Excel 2021 or earlier will see #NAME?. Check versions before rolling out.
AI tools are good at drafting LAMBDA functions; the prompt approach is the same as in using ChatGPT to write Excel formulas. When the logic is business-critical, such as pricing, commissions or payroll, we build and document the function library as part of custom spreadsheet development, with test cases for every function.
FAQ
Excel LAMBDA FAQ
Custom functions without macros