Announcing LAMBDA: Turn Excel formulas into custom functions

Today we are releasing to our Beta customers a new capability that will revolutionize how you build formulas in Excel. Excel formulas are the world’s most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re-usable functions.

 

=LAMBDA

Simply put, LAMBDA allows you to define your own custom functions using Excel’s formula language. Excel already allows you to define custom functions, but only by writing them in an entirely different language such as JavaScript.  In contrast, LAMBDA allows you to define a custom function in Excel’s own formula language.   Moreover, one function can call another, so there is no limit to the power you can deploy with a single function call. For folks with a computer science background, you’re probably already familiar with the concept of lambdas, and the introduction of LAMBDA makes the Excel formula language Turing Complete...

 

Reusable Custom Functions

With LAMBDA, you can take any formula you’ve built in Excel and wrap it up in a LAMBDA function and give it a name (like “MYFUNCTION”). Then anywhere in your sheet, you can refer to MYFUNCTION, re-using that custom function throughout your sheet. I’ll show a couple examples below.

 

Recursion 

Reusable functions is reason enough to start taking advantage of LAMBDA, but there’s one more thing… you can do recursion. If you create a LAMBDA called MYFUNCTION for example, you can call MYFUNCTION within the definition of MYFUNCTION. This is something that before, was only possible in Excel through script (like VBA/JavaScript). I’ll also show an example below of how you can leverage this to build things that were not possible before without writing script.

 

Reusable Custom Functions

One of the more challenging parts of working with formulas in Excel is that you often get fairly complex formulas that are re-used numerous times through the sheet (often by just copy/pasting). This can make it hard for others to read and understand what’s going on, put you more at risk of errors, and make it hard to find and fix the errors. With LAMBDA, you have re-use and composability. Create libraries for any pieces of logic you plan to use multiple times. It offers convenience and reduces the risk of errors.

 

Station IDs

For example, imagine I have a list of station IDs, where the state is encoded in the ID, and I want to pull that value out:

 

 

There are many ways to do this with Excel functions, here’s how I did it (I’m sure many of you have much more efficient ways to doing this… forgive me… I know for example the team would remind me I should take advantage of the LET function)

 

=LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1)

If I take that formula and copy it down the column, I can get the results shown in the table above.

 

There are two challenges with this approach:

• Errors - If I find an error in my logic that I need to fix, I have to go back and update it everywhere it was used, and I might miss some. Additionally, there’s added risk whenever you have complex formulas repeated over & over again as opposed to defined just once and then referenced. If for example, there are some station IDs that look like this “105532-872332-WA-73”, my formula would not work with those. If I find this error and want to fix it, I then need to go back to every cell where I used that logic and update it.
• Composability/Readability - If I’m not the original author, it’s hard to know what the intention of that formula is (to pull out the location). It’s also hard to use this logic in combination with other logic, like if I want to take the station ID and do a lookup based on the calculated location.

Using LAMBDA, I can create a function named GETLOCATION, and put the formula logic in the definition for that function.

 

=GETLOCATION

 

=LAMBDA(stationID, LEFT(RIGHT(stationID,LEN(stationID)-FIND("-",stationID)),FIND("-",RIGHT(stationID,LEN(stationID)-FIND("-",stationID)))-1))

Notice I specify the arguments my function will take (in this case stationID) and the logic for my function. Now in my spreadsheet, I can simply write GETLOCATION as a formula and reference the cell that has the stationID, just like any other Excel function. If I notice I have an error, I fix it in one place, and everywhere that uses that function is fixed.

 

 

Another added benefit, is that I can now compose that function with additional logic. For example, if I had a table of tax rates for each location, I could write this simple formula to return the rate based on the stationID.

 

=XLOOKUP(GETLOCATION(B18), table1[locations], table1[tax]).

 

OK, lot’s more to drill into here around how you can use this capability to build up a rich set of function libraries, make your sheets easier to understand and less error prone, etc. These functions can even take data types as arguments. We’ll post an example later of a custom function that takes two cities as input and calculates the distance between them using the geo coordinates and the radius of the earth to perform the calculation.

 

Let’s shift into the other big impact this will have on what you can build in Excel. This one is a bit more complex, but is pretty revolutionary for us… recursion.

 

Recursion

One of the big missing pieces in Excel formulas has been the ability to loop… to repeat over a set of logic at a dynamically defined interval. There are ways that you can manually configure the interval at which Excel recalculates to mimic this to an extent, but it’s not inherent to the formula language. That changes with LAMBDA.

 

Let’s take an example, albeit a bit contrived, but it’s a simple way to get the point across.

Imagine I have a set of strings, and I want to specify which characters should be removed from those strings dynamically:

 

 

Because the set of characters you’re specifying are not static, there really isn’t any good way of doing this. If you knew it was always a fixed set of characters, you could do a ton of nested logic, but that would be pretty complex and error prone to author. Not to mention, if the number of characters to be removed was larger than what you’d accounted for, it would fail.

 

With LAMBDA, we can create a function called REPLACECHARS that references itself allowing you to iterate over the list of characters to be removed:

  

=REPLACECHARS

=LAMBDA(textString, illegalChars,
     IF(illegalChars="", textstring,
       REPLACECHARS( 
       SUBSTITUTE(textString, LEFT(illegalChars, 1), ""), 
       RIGHT(illegalChars, LEN(illegalChars)-1)
)))

Notice that in the definition of REPLACECHARS, there is a reference to REPLACECHARS. The IF statement says if there are no more illegal characters, return the input textString, and otherwise remove each occurrence of the leftmost character in illegalChars. Recursion kicks in with the request to call REPLACECHARS again with the updated string, and the rest of illegalChars. This means it will keep calling itself until it has parsed over every character to be removed, giving the desired result.

 

 

Not just numbers & strings

If you’ve been following the Excel improvements over the past couple years, you probably noticed two significant improvements with the type of data you can work with in Excel:

• Dynamic arrays - Rather than passing a single value into a function, you can pass an array of values, and functions can also return arrays of values. You can learn more about arrays here.
• Data Types – The value stored in a cell is no longer just a string or a number. A single cell can contain a rich data type, with a large set of properties. You can learn more about data types here.

Functions can take data types and arrays as arguments, and they can also return results as data types and arrays. The same is true with the lambdas you build.

 

Let’s take an example... I have a list of cities, and I want to calculate the total distance I’d travel if I were to go to each city in order.

 

We’ll post the code for this one in a later post, but it’s pretty basic:

• We have an array of City data types. The city data type has the latitude & longitude properties.
• With latitude & longitude, we can do some basic math using the radius of the earth to approximate the distance between two points (that’s the first Lambda we call DistanceBetweenCities)
• We create a recursive lambda, DistanceBetweenMultipleCities, to iterate over the cities in the array. In addition to calling itself, to iterate over the list of cities, it also calls the DistanceBetweenCities function to get a running total of the distance traveled.

 

Try it yourself

If you are in our Beta program, you can try it now yourself. We’d love your feedback as we continue to improve on this new capability.

 

 

LAMBDA Overview

There are three key pieces of =LAMBDA to understand:

1. LAMBDA function components
2. Naming a lambda
3. Calling a lambda function

 

LAMBDA function components

Let’s look at an example which creates a basic lambda function.

 

Suppose we have the following formula:

=LAMBDA(x, x+122)

In this, x is the argument you can pass in when calling the LAMBDA, and x+122 is the logic.

 

For example, suppose you called the lambda and input the value 1 for x, Excel would do the following calculation:

1 + 122

Which, as we all know:

1 + 122 = 123

But how do you use these? If you've been pasting our examples into Excel, you may have noticed some #CALC! errors. To resolve those, you'll need to learn the next step.

 

Naming a lambda

To give your LAMBDA a name so it can be re-used, you will want to make use of the Name Manager.

 

The Name Manager can be found in the Ribbon by going to:

Formulas > Name Manager

 

Once you open the Name Manager you will see the following window

 

From this point you’ll want to create a new entry (New…) and fill out the associated fields

Name: The Name of your function

Comment: A description and associated tooltip which will be shown when calling your function

Refers to: Your lambda function definition

 

Once you’re done, you can hit “OK” to store your lambda and you should see the definition returned in the resultant window.

And that’s it! Now you can make use of your newly crafted custom function in the workbook by calling it by its name. 

 

Calling LAMBDA

To put it simply, you call a lambda function the same way you call native functions in Excel.

 

To illustrate this, lets revisit the previous example and show how to call MYLAMBDA with a value.

=MYLAMBDA(122)

Which of course returns the value:

123

One last thing to note, is that you can call a lambda without naming it. If we hadn’t named the previous formula, and just authored it in the grid, we could call it like this:

=LAMBDA(x, x+122)(1)

This passes in 1 for x, which returns

123

 

Just the beginning... join us for the journey

As you’ve probably noticed, we are improving the product on a regular basis. The desktop version of Excel for Windows & Mac updates monthly, and the web app much more frequently than that. We have a ton of improvements already planned for LAMBDA, but we’re looking to you for feedback along the way. One that I can tell you gets me every time is the experience of editing in the name manager... definitely lots of room for improvement there. We’ll also add some more array manipulation functions in the coming months that will help you build even more powerful lambdas taking more advantage of dynamic arrays.

 

Office Insiders

Join the Office Insider Program and choose the Beta Channel to get early access to LAMBDA in Excel. 

 

To give feedback and suggestions, click Help > Feedback, and Add #LAMBDA in your feedback so that we can easily find input about the feature. You can also post in the Excel Tech Community.

 

Connect with us

Stay connected with us.  Join our Excel Tech Community and check out this LAMBDA sample. Let us know what you think and we’d love to see what you build with LAMBDA!

 

Learn more

To learn more about LAMBDA, please check out our help article and in the meantime we are excited to hear more from you about the LAMBDA formulas you have created!

 

LAMBDA Help