How to create new function in Excel with LAMBDA

Although Excel appeared in 1987, it was only in the years of the pandemic that it underwent revolutionary changes. Okay, maybe some more versions of Excel can get that adjective, but the advent of the LAMBDA function allows the user to create their own function without the need to know programming, Visual Basic, and the like.

The whole idea of ​​expanding the set of functions did not come by chance. You can read more about this in the Microsoft Blog and the indisputable contribution of Alonzo Church , who made a great contribution to mathematical logic and the theory of computer science.

In essence, the whole story of how to create a function in Excel using LAMBDA is actually the process of converting a formula into a function. If you are worried about the concept of a function or formula in Excel, you can read more about it in our article The difference between a formula and a function in Excel.

Simply, the first parameter of this function are the variables that we will use and the formula itself, which can be purely mathematical or formed from existing functions in excel. For the simplest example, I used the variable ri mathematical formula of the square r * r.

This simplest example is pointless to use, but it is good for us to write our first LAMBDA function.

Why create a new function in Excel using LAMBDA

If you are ready to create your first custom function, keep in mind that it is not the same when you use the LAMBDA function in a table cell or when it is named as a new function.

The syntax of the function is: = LAMBDA (variable,…, formula)

LAMBDA functions are usually created and debugged in the formula bar on the worksheet, and then moved to the name manager to assign a name that can be used anywhere in the workbook.

There are four basic steps to creating and using a custom LAMBDA feature:

1. Check the logic you will use with the standard formula
2. Create and test a generic (unnamed) LAMBDA version of the formula
3. Name and define the LAMBDA formula using the name manager
4. Call a new custom function with a defined name

The examples below discuss these steps in more detail.

First example: how to create a new function in Excel using LAMBDA

The simplest example is the quadratic function. As you can see in the picture, there are ordinal numbers in the first column, and in the second column, the LAMBDA function is called after the equals sign.

= LAMBDA (r; r * r) the first parameter is a variable that I have given here an arbitrary name (lowercase letter r) the second parameter is the formula itself, in this case simple when multiplying r times r.

Behind the closed parenthesis are the cell addresses from the worksheet whose value will be calculated by this function in parentheses (B3), etc.

Another example: Definition of a new function name

First, the essential definition = LAMBDA (r; r * r) should be entered in the Name Manager located on the Formulas toolbar.

You can see how simple it is from the attached picture.

You can download the finished file here.