digest
Short Summary
Main recommendation: Combine LET and LAMBDA in Excel to build complex single-cell reports, then save the whole formula as a Defined Name so an end user only enters the input columns (date, person, sales). The video also shows that LAMBDA alone can turn any formula with variables into a reusable custom function.
Key Points
- LAMBDA lets you define your own variables and a custom function; the video says it had just been released in Microsoft 365.
- The example function is a percentage change formula, defined as (end / start) minus 1, tested with start=100 and end=110, returning 10%.
- Once saved as a Defined Name, the LAMBDA can be called anywhere in the workbook, like a normal function.
- Wrapping a LET formula inside LAMBDA lets a user generate a complicated report by entering only three columns.
Details Worth Noting
- Concrete instructions:
- To create a reusable LAMBDA, write it in a cell with the variable names followed by the formula, for example =LAMBDA(start, end, (end/start)-1). Test it in the worksheet by adding parentheses and inputs, such as (100, 110).
- Copy the LAMBDA, go to Formulas, open Name Manager (or press Control+F3), click New, give it a name (the video uses "decimal change"), paste into "Refers to," and click OK.
- Call it in a cell with =name(100,110), using the Defined Name followed by the inputs in parentheses.
- To format output as text with a percent, wrap the result in TEXT(..., "0.00%") inside the LAMBDA body.
- For the LET report, copy the LET formula, change the input references to LAMBDA parameters (dd for date, pp for person, ss for sales), and test by selecting the three columns in parentheses.
- Save the LAMBDA LET as a Defined Name (for example "Person End of Month Report"), add comments describing the three comma-separated column inputs, then call it with the columns as arguments.
- The video notes that the formatting in the final report was NOT preserved in the output, which the presenter found surprising.
- The video does not give exact details on version availability beyond Microsoft 365 and its initial release timing.
Bottom Line
LAMBDA turns any formula, including long LET-based report logic, into a named function that anyone can reuse by supplying only the required inputs.
