SheetSprout
Start learning
Menu
Advanced / Lesson 06

Reusable functions with LAMBDA

Test a small formula, then give the repeated calculation a clear interface.

~35 minutes4 exercisesSee lesson notes for version and platform support

By the end, you’ll be able to:

  • Understand parameters, a calculation and supplied arguments
  • Test a LAMBDA directly in a worksheet cell
  • Save a tested named function and document its boundaries

Turn a repeated calculation into a small function

Several reports calculate stock cover. Rewriting the ratio and zero-demand rule in every report invites inconsistent changes. A LAMBDA can package the calculation behind meaningful input names.

Start by testing the ordinary formula =B2/C2. Then parameterize it: =LAMBDA(stock,demand,stock/demand)(B2,C2). The final parentheses supply the actual stock and demand values. For 120 and 30 the result is 4.

Example worksheet with column letters and row numbers
ABC
1SKUStockWeekly demand
2P10112030
3P1024020
4P103010

Separate parameters from arguments

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

parameters are the input names used by the function. calculation returns the result. When testing immediately, add (argument1, argument2, ...) after the closing parenthesis to supply values in the same order.

Stock and demand are parameters; B2 and C2 are the arguments in our call. Define every required parameter and pass the same number of arguments. Use valid names rather than cell addresses; periods are not allowed in LAMBDA parameter names.

A LAMBDA left in a cell without being called can show #CALC!. That does not mean its intended arithmetic is wrong; it may simply be an uninvoked function.

Worked example: a cover function with a zero rule

=LAMBDA(stock,demand,IF(demand=0,"No demand",stock/demand))(B2,C2) returns 4 for 120 stock and 30 weekly demand, 0 for zero stock and positive demand, and No demand for a zero denominator.

The function assumes stock and demand are numeric quantities expressed in compatible units, with demand measured per week. It does not automatically validate negative inputs, missing records or whether demand is a reliable forecast. Write those assumptions beside the function.

Save a tested function in desktop Excel

  1. Test the immediately invoked formula on ordinary and boundary cases.
  2. In desktop Excel, open Formulas → Name Manager → New.
  3. Enter the workbook-scoped name StockCover.
  4. In Refers to, enter =LAMBDA(stock,demand,IF(demand=0,"No demand",stock/demand)) without the test call.
  5. Add a comment describing inputs, weekly units and the zero-demand result.
  6. Use =StockCover(B2,C2) in the workbook.

A named function belongs to its workbook scope; sharing a different workbook does not automatically transfer its definition. Keep a small test sheet so later edits can be checked.

Keep the first custom functions small

Excel versions: LAMBDA is available in Microsoft 365 and Excel 2024, including supported Mac versions. It is not an Excel 2021 function. Named-function management interfaces vary by platform; these saving steps target desktop Excel.

The browser checker supports direct calls of small LAMBDAs using the functions taught here. It does not create Name Manager entries or execute recursive custom functions. Start with one clearly defined purpose and three or four independent test cases before considering more elaborate patterns.

Now make it yours

Give it a try.

Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.

0 of 4 exercises complete

Exercise 01 / Formula practice

Call a cover function

Write an immediately invoked LAMBDA with parameters stock and demand. Return stock/demand using B2 and C2 as its arguments. Enter E2.

Example worksheet with column letters and row numbers
ABC
1SKUStockWeekly demand
2P10112030
3P1024020
4P103010
Exercise 02 / Formula practice

Handle zero demand

Write a directly called LAMBDA that returns No demand for demand zero, otherwise stock/demand. Supply B2 and C2. Enter E2.

Example worksheet with column letters and row numbers
ABC
1SKUStockWeekly demand
2P10112030
3P1024020
4P103010
Exercise 03 / Formula practice

Parameterize a variance

B2 is budget and C2 is actual. Write an immediately invoked LAMBDA with parameters budget and actual returning actual-budget. Enter E2.

Example worksheet with column letters and row numbers
ABC
1CategoryBudgetActual
2Freight400460
Exercise 04 / Knowledge check

Diagnose an uncalled function

You typed =LAMBDA(stock,demand,stock/demand) into a cell and see #CALC!. What is missing from a direct test?

Choose one answer

The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.

Take this with you

LAMBDA packages a tested calculation behind parameters. A clear input contract and boundary tests matter as much as saving the function name.

Practice in Excel

Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.

Download practice workbook Check my workbook ↗
Technical references and lesson review

Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.

Find your next skill