Excel 2024: Store Complex Formula Logic in LAMBDA function

Excel has a great formula language with hundreds of built-in calculations, but it doesn't have functions for every possible calculation. A new research project from Microsoft's Calc Intelligence team in England will allow you to create your own functions.

The new function, LAMBDA, is available for any Microsoft 365 subscribers who opt into the Office Insiders program's Beta Channel. It's named after Princeton University mathematician Alonzo Church, who invented lambda calculus in 1936. Church was the doctoral advisor of Alan Turing, the famous mathematician who proposed the Turing machine to perform calculations long before modern computers were invented.

Imagine performing a complex calculation and passing variables to it. The variable names and logic are stored in the LAMBDA function and after testing in the worksheet you create a Name and insert the logic into name manager.

In the beta versions of LAMBDA, you were required to add LAMBDA functions to the Name Manager in Excel. This tiny dialog box with a single-row text box for entering the formula was not a fun place to try to edit your formulas.

In early 2021, Microsoft Research released an Advanced Formula Environment add-in that makes LAMBDA easier to use. Here is how to install the Advanced Formula Editor.

1. From the Insert tab in Excel, go to Add-ins, Get Add-Ins. The Office Add-Ins dialog opens.

2. In the Search box, type Advanced Formula Environment and press Enter. The top result should be "Advanced formula environment, a Microsoft Garage project".

3. Click the Add button. Click the Continue button. The Advanced Formula Environment will appear on the right side of the Home tab.

There are three main buttons in the add-in. Click the + icon to define a new LAMBDA. Click the Sync button to save your LAMBDAs to the Name Manager. Click the Import button to import from a GitHub Gist URL.

Let's start with a simple example. In a right triangle, the length of the hypotenuse is the square root of A squared plus B squared. To calculate the hypotenuse, you would use a formula such as =LAMBDA(A,B,SQRT(A^2+B^2)).

In this formula, the first two arguments are variables to hold values that will be passed to the function. The final argument is the logic to perform the calculation. You aren't limited to just two input variables. Everything up to the last argument is considered to be a variable. The final argument always holds the calculation logic.

In the Advanced Formula Editor, click the + icon and choose Function. A box will appear where you name your LAMBDA. A second box lets you edit your LAMBDA.

Give this a name of HYP. Type the formula =LAMBDA(a, b, SQRT(a^2 + b^2)). Click the Add button to add the formula definition. Caution: The formula is not yet available to use. You must first click the Sync button to Sync Names With The Excel Name Manager.

After you sync the new LAMBDA to the Name Manager, you can use the formula in the Excel grid. Excel offers intellisense, just like any other Excel function.

Here is the formula in the AFE panel:

The AFE panel is really just a conduit for getting the formula into the Name Manager:

---

---

Here are the formulas working in the grid. Notice that the intellisense even shows that the function is expecting two arguments, named a & b.

Before the AFE was available, you would sometimes want to edit and test the formula in the grid before trying to add it to the Name Manager. You could type the formula in the grid, followed by the argument values in parentheses. This still works. =LAMBDA(A,B,SQRT(A^2+B^2))(3,4) would return the answer of 5.

If you need to change the logic in the future, edit the formula in the Name Manager or the Advanced Formula Environment.

Bonus: Importing LAMBDAs from GitHub

The AFE lets you import LAMBDA from a GitHub Gist (pronouced jist). Chris Gross, from the Excel team, made this Gist available on the day the AFE was released. Import it and you will instantly have several LAMBDAs available in the workbook.

1. In the Advanced Formula Environment, click the Import icon.

2. A box appears where you can type a URL. Enter the following: https://gist.github.com/chgrossMSFT/d172fd552cf6893bcdc7942223cb0e55

3. Optionally, you can add these LAMBDAs to a Name Space. If you use a name space of MSFT, then all of the LAMBDAs imported will start with MSFT. This might help you to keep your LAMBDA formulas categorized.

4. After a successful import, you can browse the new LAMBDAs. They won't be available to use until you press the Sync button to add them to the Name Manager.

By using a Name Space of MSFT, all of these functions will appear in the Formula AutoComplete when you type =MSFT.

Here is the MSFT.TEXTREVERSE function working in the grid.