Defining Custom Functions With LAMBDA

Excel ships with hundreds of functions like SUM and XLOOKUP. But what if you need one that does not exist, such as converting Celsius to Fahrenheit?

The LAMBDA function lets you define your own reusable function using only formulas, no programming required. You describe the inputs (called parameters) and the calculation, and Excel treats it like any built-in function.

LAMBDA is available in Microsoft 365 and Google Sheets. In this lesson you will learn to wrap a calculation into a reusable named function.

A LAMBDA has two parts: a list of parameter names and a final calculation that uses them.

The pattern looks just like LET: every argument except the last is a parameter name, and the last argument is the formula that does the work.

A LAMBDA on its own does nothing until you give it values, just like a recipe does nothing until you cook with ingredients.

To test a LAMBDA immediately, wrap it in parentheses and pass values right after. This is called an inline call.

Here we define a LAMBDA that takes x and returns x*2, then immediately call it with 5. The result is 10. The trailing (5) supplies the value for x.

This is the quickest way to confirm your LAMBDA logic works before naming it.

Let's build something useful. The formula to convert Celsius to Fahrenheit is multiply by 1.8 and add 32.

We define a LAMBDA with one parameter c, then call it with the value in cell A2. If A2 holds 20, the result is 68.

This already behaves like a custom function, but typing the whole LAMBDA every time is tedious. Soon we will give it a name.

LAMBDAs can take several inputs. Just list each parameter name before the final calculation.

Here is a function that computes the area of a rectangle from width and height. We call it inline with 4 and 5 to get 20.

Parameters are matched by position: the first value goes to the first parameter, the second value to the second, and so on.

The real power comes from naming a LAMBDA so you can call it like a built-in function.

In Excel go to Formulas, Name Manager, New. Type a name such as ToFahrenheit and paste the LAMBDA (without the inline values) into the Refers to box.

From then on you can use =ToFahrenheit(A2) anywhere in the workbook, just like SUM. We will explore named LAMBDAs in depth in a later lesson.

Inside a LAMBDA you can use LET to name intermediate steps, keeping complex logic tidy.

This function takes a price and a rate, computes the tax as a named step, and returns the total. The body reads cleanly thanks to LET.

Google Sheets supports LAMBDA with the same syntax. You define parameters and a calculation, and you can call it inline the same way.

Sheets does not have a Name Manager, but it offers Named functions under Data, Named functions, which serve the same purpose. The LAMBDA body you write is identical to Excel's.

LAMBDA lets you capture business logic once and reuse it everywhere:

• No more copy-pasting the same complex formula
• Fix the logic in one place and every use updates
• Self-documenting names like ToFahrenheit explain intent

It turns the spreadsheet into a place where you can build a small library of your own functions tailored to your work.

If you type a LAMBDA into a cell without calling it, Excel returns a #CALC! error or shows the text of the function. That is expected.

A LAMBDA needs values to run. Either add an inline call like (A2) at the end, or save it as a named function and call the name with arguments. A bare LAMBDA is just a definition waiting to be used.

You now know that LAMBDA defines parameters and a calculation, behaving like a custom function once it receives values.

• Test logic with an inline call: =LAMBDA(x, x*2)(5)
• Support multiple parameters matched by position
• Combine with LET for clean internal steps
• Name it in the Name Manager to reuse across the workbook

Next you will learn how to apply a LAMBDA across whole arrays using MAP and REDUCE.

Test your understanding of how a LAMBDA is called.

LAMBDA lets you build your own functions from formulas alone.

• Syntax: parameter names followed by a final calculation
• Call inline with values to test, or name it for reuse
• Parameters match arguments by position
• Works the same in Excel and Google Sheets
• A bare LAMBDA needs values or it returns #CALC!

You can now turn repeated logic into a clean, named tool. Up next: applying LAMBDAs across arrays with MAP and REDUCE.