Lambda() downsides need to be fixed

The new Lambda() feature in Excel 365 is great, in theory.  But when you try using it the problems quickly become obvious.

Lambda() itself is fine. It’s a welcome addition which more advanced users will make Excel do amazing things or simplify complex formulas.  See Lambda – strange name for a really good extra in Excel

We’ve been trying out Lambda and it’s impressive but a right pain the ‘expletive’ to work with.

Lambda has been grafted onto a very limited interface (Integrated Development Environment or IDE) designed for simple formulas.  That interface hasn’t been updated to meet the needs of modern Excel users – let alone new features like Let() and Lambda().

The approved method of using Lambda is via Define Name.  A Lambda() function is given a name and set in Formulas | Define Name

That means there’s a single short line to edit the Lambda() formula.

It gets worse. Pressing any arrow key on the ‘Refers to’ line inserts a cell reference instead of moving along the existing formula.  It’s a field designed for a simple cell reference, not a complex formula.

In Microsoft’s explanations of Lambda() they show neat and tidy multi-line formulas like this:

=LAMBDA(city1, city2, LET(
     lat_1, city1.Latitude,
     lon_1, city1.Longitude,
     lat_2, city2.Latitude,
     lon_2, city2.Longitude,
))

Paste that into Excel and this is all you’ll see. The last line only and no way to see, let alone edit the formula.

If the function works, be happy.  Bad luck if it doesn’t, because you’ll be trying to debug a mostly invisible function.

What Excel badly needs is a proper IDE for formulas that’s fit for the 21st Century.  An extension of what’s available for VBA users.

Presumably the Excel team knows about the problems with Lambda and longer formulas.  Hopefully there’s a group working on a solution – not a partial fix – a real IDE solution that Excel users can work with for years to come.

Workaround

There is a partial workaround.  It’s not very good but better than Define Name.

A Lambda() function can live in an Excel cell with the parameters after it.  Here’s a Pythagorean example …

=LAMBDA(x,y, SQRT(x^2+y^2)) (3,4)

The two parameters are in the brackets after the formula.

In Excel, the Formula bar can be expanded to show the whole formula.

At the moment, Lambda() doesn’t get the nice color coding that regular formulas do.  Hopefully that will be fixed before public release.

Tip:  use Alt+Enter to add line breaks within a formula.

Lambda – strange name for a really good extra in Excel
A better Excel Calendar Creator for Office Watch readers
Watch Window finally coming to Excel for Mac

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address