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(
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.
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.