Lambda – strange name for a really good extra in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel users are rightly interested in Lambda() a new and very powerful addition to Excel. 

Lambda lets you create under-defined functions without VBA.  No need for .xlsm files with all the security concerns they entail.  

A long formula can be written once then referenced anywhere in the workbook by a short name of your choosing.

UDF’s have been possible for a long time via VBA, we’ve used them ourselves to do things not possible or difficult with standard functions.  See Many Ordinal RANK() options in Excel with joint, equal rankings, words and more .  But VBA means using .xlsm files which add security concerns about what’s in the VBA.

Lambda() is a partial solution to that problem as well as adding something that’s long been missing from core Excel; recursion.

Basic Lambda() example

Let’s start with a simple example of Lambda() using a long formula from the Office Watch article on nested IF’s in Excel.

This long formula returns a name for a numbered grade. 

IF(Mark>89,"HIGH DISTINCTION", IF(Mark>79,"DISTINCTION",
IF(Mark>69,"CREDIT",IF(Mark>49,"PASS","FAIL")))))

That formula has to be copied across many cells and worksheets. There’s a risk of the formula going awry or becoming inconsistent (e.g. the names or levels changing).

Instead of many copies of the formula, Lambda gives you a single custom-named function (such as GradeName() ) which applies throughout the workbook.

If there’s a change in grading rules, there’s only one formula that needs to be fixed.

Setup the custom function in Formulas | Defined Names | Define Name.

Name – The name of the custom function

Refers to – the Lambda() formula.

In this case the complete formula looks like this:

=LAMBDA(Mark,IF(Mark>89,"HIGH DISTINCTION",IF(Mark>79,"DISTINCTION",IF(Mark>69,"CREDIT",IF(Mark>49,"PASS","FAIL")))))

The custom function appears as you type.

What about Let()?

If Lambda() seems familiar, it’s because of another Excel innovation – Let() from earlier this year.

Let() allows you to assign a name to a calculation then use that name many times within a formula.  Lambda() is similar but works across an entire workbook instead of a single formula.

LET() assigns names to calculations in Excel

Making a Lambda()

Lambda() syntax is:

Lambda( <input names> … , <calculation>)
Source: Microsoft

Multiple input parameters are possible as long as the final item is the formula/calculation).

These are all simple Lambda() examples:

Lambda( x, x * 3.14159 )  multiplies x by Pi

Lambda ( Fred, Wilma, Fred + Wilma) adds the values of Fred and Wilma

The input parameters have names (like ‘Mark’ above) that are referenced in the formula.

There can be up to 253 parameters but there can’t be a period/fullstop in a name.

Dynamic Arrays are supported either as input or returning from a Lambda().

Data Types can also be inserted into a formula or returned.

Recursion

A missing part of core Excel has been recursion – a function calls itself to repeat a process as many times as necessary.

It’s been possible in VBA with a DO … UNTIL or FOR loops but not in plain formulas, until now.

We’ll use Microsoft’s good example of recursion.  It loops through a string to remove unwanted characters from a string.  In this case, delete any digits to leave text only.

Source: Microsoft

That’s done with a Lambda() function called ReplaceChars() which calls itself.

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

Notice that inside ReplaceChars() (as made with Lambda() ) it calls ReplaceChars().  That means it’ll keep looking for unwanted characters until the IF() test is True.  Recursion needs an exit test, if not the formula would continue forever!

Great work!

We could write a lot more about Lambda() but we don’t have to!  For once, Microsoft has published good and (almost) comprehensive documentation for a new feature.

Too often, new Office features get more hype than detail.  Sometimes just a single, vague paragraph.  Customers are left to discover the details and shortcomings for themselves.

The Lambda() team is to be congratulated.  The opening article has good examples; we’ve used one above.  There’s already standard page for the Lambda() including limitations and more examples – though it’s misleading about which Excel’s have the new feature … see below.

For more on Lambda start with:

Announcing LAMBDA: Turn Excel formulas into custom functions

Then hop over to LAMBDA Examples: Distance between two cities for a longer example using data types.

Who gets it?

At the moment, Lambda() is only available for Insiders … but which releases?

This is where the early Microsoft documentation lets customers down.  Even the Excel team doesn’t seem to be sure which versions have Lambda()!

Some pages suggest that Beta Insiders for Windows and Mac have Lambda.  There are also suggestions that it’s available in Excel Online (web based).

The main Lambda() details page makes NO mention of it’s beta status, saying it’s available to all ‘Office 365 subscription’ customers.   That’s definitely NOT true (yet) and besides it’s now ‘Microsoft 365’.

We know for sure that Lambda() is available in Excel for Windows, beta channel version 2101 build 13604.20000.  It’s not in Excel for Mac beta channel nor Excel online at the time of writing.  Presumably it’ll roll out to those platforms and channels in the coming weeks.

LET() assigns names to calculations in Excel
The big difference between what Excel shows and what Excel knows
Excel is getting better Conditional Formatting with much needed improvements

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address