So what are these new lambda functions Excel has?

 LAMBDA: The great new addition coming to Excel and available in Microsoft's beta programme. 

Excel's Lambda function is a method of a taking a formula that you would normally enter into a cell and giving it a name.  You can then use that name wherever you like instead of repeating the formula. 

Or as Microsoft puts it, users will be able to use the Excel formula language itself to define short reusable functions within the spreadsheet itself without resourcing to VBA.  As one function will be able to call another, there is potentially no limit to the complexity and power of what you can do through this function.  It is, for the geeks and Microsoft says, making the language Turing Complete.

More detail can be found here in Microsoft's technical blog.

This looks as if it will be hugely useful and I can see it simplifying the maintenance of spreadsheets everywhere.  It will potentially mean that some spreadsheets can be re-written without using VBA at all,  a great benefit for those organisations where security prohibits the use of VBA code. It comes however with the downside that the use of the LAMBDA() function as implemented will make spreadsheets more difficult to follow and debug – but more of that anon.  

But it isn’t really a lambda though.

Lambda functions are normally described as anonymous functions and would be used without naming.  The purpose of the Excel LAMBA() function is, somewhat different, and serves to encapsulate an Excel formula and deliver it as a named function for multiple use in cells.

Let us give an example.  I’ll use Python to illustrate:

I could write a simple function as follows:

def square(x): return x**2                                                                       (1)

and use it as follows:

map (square,[0,1,2,3])                                                                          (2)

This is pretty standard approach to a function and you can see how something similar can be done in Excel.  In Python however if I didn’t want a function which could be used elsewhere I could write a lambda statement thus:

map(lambda x: x**2,[0,1,2,3])                                                              (3)

This arguably is similar to the new Excel LET() function which can be used in a similar way:

=LET(x,{0,1,2,3,4},x*2)                                                                        (4)

The Excel LAMBDA function is arguably much more useful as it is reusable, as in (1) above.  We write a function:  =LAMBDA(x,x^2), and once we have added it to the Name Manager and call it “square”, we can use it in any cell as in:
  =square(2).

But do you see what we have done by adding a name?  We have really turned it into a version of (1).  In fact, it is functionally identical to the VBA:

public function square (x as double) as double
     square = x^2
end function                                                                                          (5)       

It’s not a lambda function as such but a clever way of introducing re-usable formulae as user-defined functions without needing to resort to VBA.

As I say, it is a welcome addition to make maintenance of formulae much easier – a “change-once change-all” approach which has to be commended. It however still a little clunky in its implementation though than it should be. The formulae, rather than being in visible cells, are now hidden away in the Name Manager.  This makes its use more tricky and debugging spreadsheets considerably more difficult.  It almost seems as if it has been shoe-horned into Name Manager and needs a little bit more thought around its interface.  

It would be much better if the LAMBA() function names were in their own manager.  The Lambda Name manager could then be designed to check the syntax of the function in the same way as it is checked when a formula is entered into a cell and have a help dialog as the fx function does in the formula bar.  In fact, the function bar functionality should be imported wholesale into a Lambda Name manager. Finally, when a LAMBDA() function is used, the Lambda function manager should be automatically available showing the underlying formula being called, rather the convoluted way we will need to check at present via Name Manager.

I’ve made these suggestions to Microsoft and I am sure others have had similar ideas.  It is, like a lot of early iterations of Excel additions, an extremely powerful new tool which just needs a better implementation.

Overall, what is the conclusion of its first use?   It's not a lambda function: it has the potential to be very much more useful.