Skip to content
Reading settings
ExcelAdvanced~40 min practiceLesson 31 of 34

Make a calculation readable with LET and LAMBDA

Make a row calculation easier to read with LET and reuse it with LAMBDA.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Compare an ordinary formula with LET and LAMBDA, then keep a compatible fallback.

Example

Give a repeated line calculation a name

Enter Item, Quantity and UnitPrice in A1:C4: Keyboard 2 30, Monitor 1 180 and Dock 3 40. Calculate each line total, then add a practice ten-percent uplift. Compare the ordinary formula with newer ways to name and reuse the calculation.

How it works

LET names an intermediate expression inside one formula. That local name can make repeated logic easier to read without adding a workbook-wide named range.

LAMBDA defines a calculation with parameters. Test it with arguments in a cell before adding it to Name Manager, then check the recipient’s function support.

Steps

  1. Type PlainTotal in D1 and enter the formula below in D2. Fill down and check 60, 180 and 120.

    =B2*C2

    English function names. Formula settings →

  2. Type WithUplift in E1 and enter the LET formula below in E2. Fill down. On the first row, compare it with =ROUND(D2*1.1,2).

    =LET(subtotal,B2*C2,ROUND(subtotal*1.1,2))

    English function names. Formula settings →

  3. Enter the direct LAMBDA test below in G2. The final (B2,C2) passes the row’s inputs and runs the function.

    Excel argument separator
    =LAMBDA(quantity,unitPrice,quantity*unitPrice)(B2,C2)

    English function names. Formula settings →

  4. On a supporting desktop edition, choose Formulas > Name Manager > New. Name it LineCost and set Refers to to =LAMBDA(quantity,unitPrice,quantity*unitPrice). Test =LineCost(B3,C3) in a free cell.

  5. If your Microsoft 365 or Excel 2024 build supports MAP, enter the formula below in a clear spill area. If not, keep the filled row formulas.

    Excel argument separator
    =MAP(B2:B4,C2:C4,LAMBDA(quantity,unitPrice,quantity*unitPrice))

    English function names. Formula settings →

  6. Compare the ordinary, LET, direct LAMBDA and named-function results for each row. Note which functions your edition supports and keep ordinary formulas for recipients who need them.

Try it yourself

  1. Add Headset Quantity 4 and UnitPrice 15, explicitly extend any fixed MAP ranges, and test the ordinary and reusable calculations for that row.
  2. Call the named function with only one argument on a disposable cell, inspect its error and correct the call. Explain why argument count is part of a reusable function’s contract.
Show expected result
  • Original plain totals are 60, 180 and 120, totaling 360. The uplift values are 66, 198 and 132, totaling 396.
  • Headset adds plain total 60 and uplift 66. Updated totals become 420 and 462, and an incomplete function call must be corrected rather than treated as zero.

If something goes wrong

A LAMBDA in a cell shows #CALC!.
During a direct test, append the required argument call. For a named function, save the tested definition in Name Manager and call its name with those arguments.
The recipient’s Excel does not recognize the function.
Check that function’s version support. Send the equivalent ordinary formula when LAMBDA or MAP is unavailable; support differs among perpetual releases.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What does the first-row direct LAMBDA return?
2. What is a LET name’s scope here?
3. What is the plain total after adding Headset?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Compare the ordinary, LET and LAMBDA formulas for these rows. Explain the local name, required arguments and version support before suggesting LineCost.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…