The Lambda() feature in Excel 365 is great, when you try using it the downsides quickly become obvious. Happily there are workarounds.
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.
Tip: press F2 to switch into Edit mode. Then you can edit the formula using the arrow keys to move through the formula. Thanks to Office Watcher, Peter W for the info.
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.
Tip: use Alt+Enter to add line breaks within a formula.
Advanced Formula Environment
A better way to manage Lambda() and other complex formulas is the Excel Advanced Formula Environment.
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
Microsoft fixes a big hassle with Excel’s great Lambda()
