Excel LAMBDA: Create Your Own Custom Functions Without VBA

What LAMBDA Does

LAMBDA wraps a formula into a reusable function. Instead of copy-pasting a complex formula, you define it once and call it by name.

Syntax: =LAMBDA(parameter1, parameter2, ..., formula)

Simple Example: Tax Calculator

You frequently calculate sales tax at 8.25%. Instead of writing =A1*0.0825 everywhere:

1. Define LAMBDA: =LAMBDA(amount, amount*0.0825)
2. Name it “SalesTax” in Name Manager
3. Use it: =SalesTax(A1)

Change tax rate? Edit the LAMBDA definition once. Every formula using SalesTax updates.

Creating Named LAMBDA Functions

LAMBDA functions only become truly reusable when named:

1. Formulas tab → Name Manager → New
2. Name: Your function name (e.g., SalesTax)
3. Refers to: Your LAMBDA formula
4. Comment: Document parameters and purpose

More Complex Example: Grade Calculator

Convert numeric scores to letter grades:

=LAMBDA(score, IF(score>=90,"A",IF(score>=80,"B",IF(score>=70,"C",IF(score>=60,"D","F")))))

Name it “LetterGrade” and use =LetterGrade(B2) anywhere.

LAMBDA with Multiple Parameters

Calculate compound interest:

=LAMBDA(principal, rate, years, principal*(1+rate)^years)

Name it “CompoundInterest” and use:
=CompoundInterest(1000, 0.05, 10)

Recursive LAMBDA Functions

LAMBDA can call itself, enabling recursion. This allows calculations that would be impossible with regular formulas, like factorial:

=LAMBDA(n, IF(n<=1, 1, n*Factorial(n-1)))

Official Resources

• LAMBDA function — Microsoft's LAMBDA guide
• Names in formulas — naming custom functions