Announcing LAMBDAs to Production and Advanced Formula Environment, A Microsoft Garage Project
Published Feb 08 2022 08:55 AM 56.2K Views
Microsoft

 

November 17th 2022 Update

The advanced formula environment has been updated to version 1.1. See the change log for more information.

 

We are excited to announce that LAMBDA and LAMBDA helper functions are now generally available to anyone using Production: Current Channel builds of Excel. In conjunction with LAMBDA going to production, we are also announcing the release of a new add-in, the advanced formula environment, sponsored by the Microsoft Garage and Microsoft Research, which allows easy import/export and authoring of named LAMBDAs.

 

Thanks to all of our Insiders for using the LAMBDA functions and giving us feedback! As a result we've also made a few changes that we'll outline below in addition to talking about the advanced formula authoring environment, a Microsoft Garage project.

 

Let’s start with a quick example.

 

IFBLANK

A task I regularly encounter is replacing certain values in a dataset, such as errors or blanks cells. Excel provides IFERROR to replace error values, but there is no function to replace blank cells. Fortunately, with LAMBDA, we can define our own function, IFBLANK.

 

Instead of authoring this in the grid and then importing to the name manager, I will instead author this in the new formula environment and then sync it to the workbook to make use of it in the grid.

 

IFBLANK.gif

In today’s scenario I am trying to create a quick count of the meals that have been requested for a dinner party I am hosting.

 

Without IFBLANK, Excel, by default returns a blank which gets coerced to 0 when placed in the cell. I could work around this but I also need to get a count of orders which I will do using MAP and REDUCE to author one formula so I can get updated counts as I continue to get more responses from guests.

 

IFBLANKMEAL.gif

Here’s the formula I used for the counts:

=MAP(D4#, LAMBDA(meal, REDUCE(0, IFBLANK(Table14[Meal Preference], B1), LAMBDA(meal_count, preference, IF(meal=preference, meal_count+1, meal_count)))))

 

Lets go over some improvements and changes we have made on our journey through Insiders to the LAMBDA feature in general.

 

Changes made to LAMBDA

 

Function tooltips

We have added support for function tooltips for named LAMBDAs in addition to auto-completing the open parentheses character when calling these functions. In other words, calling a function defined using a LAMBDA is exactly the same as calling a native function.

 

TooltipGIF.gif

 

Recursion limit increase

We have upped the limit of recursion by 16 times its original limit. In this example, the text string is 3200 characters long and would have previously returned a #NUM when called with a LAMBDA that recursively reverses a text string.

 

recurseGIF.gif

LAMBDA helper function outputs

We changed the way that the LAMBDA helper functions handle arrays of references.

 

Previously when a LAMBDA helper function returned an array and the associated LAMBDA returns a single cell, a #CALC error would be returned. We have changed this to automatically return the cell value as the output of the LAMBDA function.

 

In this example, Excel would have returned a #CALC but now will return the results.

helperFunOutputFormula.png

helperFunOutputResult.png

 

Advanced formula environment, a Microsoft Garage project

Today we are introducing a new tool to aid in the authoring of more complex named formulas. The advanced formula environment is a space where we are hoping to experiment and explore new and different methods for authoring formulas with special functionality designed with LAMBDAs and LET in mind.

 

Key features of the new tool:

  • Advanced formula authoring capabilities found in modern IDEs
    • Intellisense
    • Commenting
    • Inline errors
    • Auto tabulation
    • Code collapse
    • And more...
  • Undo/redo of formula edits within the manager
  • Namespaces to allow for groups of named functions
  • Import and export functionality
    • Text and GitHub Gist import
  • Different views to filter your names and edit in a single location

The environment is available on all platforms where Office Add-ins are available (Mac, Windows, Web)

Let's take a look at some of the functionality so you can get started with managing and editing your new and pre-existing named functions!

 

Manager and Editor Views

There are two major views contained within the advanced formula environment, Manager and Editor.

 

Manager

The Manager is where you will see all of your names with their own individual cards and associated quick actions. Much like the Name Manager but with more functionality.

editIcon.png Edit the name
renameIcon.png Rename the formula
deleteIcon.png Delete the entry
shareIcon.png Export the definition for sharing

 

prodmanagerview.png

 

Editor

The Editor is where you can go to edit all entries within the workbook or create new namespaces for collections of formulas.

 

This is where I usually go when I want to dive in to create more complex functions as you get the full version of the editor in this view and can create multiple names sequentially.

 

The workbook section contains all names which are not attached to a given sheet but instead are saved globally in the workbook.

prodeditorview.png

 

Some of my favorite pieces of functionality are the ability to easily add new lines, tabulate sections of the formula while also being able to comment out pieces of my formula that I might be working on, or collapse definitions so I can more easily dig into specific areas. Not to mention, if I change my mind later, I can easily undo/redo any changes I am actively working on.

 

Here's another example I made for a chess game I have been building for fun in Excel.

chessGIF.gif

 

Importing

The advanced formula environment can import definitions into the manager. You can important individual definitions as well as libraries of definitions via text or through GitHub Gists.

 

The main entry point for importing can be found by selecting the action in the actions bar. The main entry point defaults to “From URL” but the dropdown reveals the “From text” option.

prodimportdialog.png

If you’d like to try out this functionality yourself I am including a gist with some of the examples I created today on top of additional LAMBDAs I have used in prior posts. I think this is a better way to share than having you copy/paste from a blog post, so lets be the first to try it out!

 

aka.ms/LAMBDAGist (make sure to use the URL you are redirected to as the add-in doesn't let you use non gist paths)

 

We look forward to the libraries of LAMBDAs the community produces and hearing from you all about what does and doesn’t work in this new environment we have created.

 

 

Feedback

We are actively looking for feedback on the experience and would invite you to provide feedback either through techcommunity or by going to this github repository.

 

Accessing LAMBDA functions today

To get access to LAMBDA functions, please make sure you have updated to the latest version of Excel. Specifically versions greater than or equal to:

  • Windows: 16.0.14729.20260
  • Mac: 16.56 (Build 21121100)
  • iOS: 2.56 (Build 21120700)
  • Android: 16.0.14729.20176

What version of Office am I using?

 

Accessing the advanced formula environment

You can use this link or manually install if from the app

https://aka.ms/get-afe

 

To access the advanced formula environment, simply search for the “advanced formula environment” within the built-in add-ins store of Excel and install it like any other Office add-in.

  1. Go the Insert Tab
  2. Select the Get Add-ins button
  3. Search for “advanced formula environment
  4. Click the “Add” button

Once the add-in is installed, you should be able to find it on your home tab. The ribbon button looks like the picture below.

Chris_Gross_0-1644260820916.png

 

Click here to learn more about Office Add-ins

 

Learn more

To learn more about LAMBDA and the advanced formula environment, please check out the links below and in the meantime we are excited to hear more about the ways you have used LAMBDA in your own workbooks!

 

LAMBDA Help

 

Advanced Formula Environment

 

Availability notes

LAMBDA is now available to Office 365 Subscribers in Production: Current Channel


To stay connected to Excel and its community, read the Excel blog posts and also follow Excel on Facebook and Twitter

  

Do you have an idea to make Excel better? We'd love to hear about it. Go to the Excel forum in Microsoft Feedback to submit your idea or vote for other ideas. You can also click the Feedback button in Excel and choose "I have a suggestion".

 

A joint collaboration

The last thing I would like to mention is that all of this was done as a joint collaboration between Microsoft Research and Excel Engineering. It’s been a blast building out all the experiences you see today and it wouldn’t have been possible without the brilliant researchers at Microsoft Research Cambridge.

 

Chris Gross
Program Manager, Excel

 

Jack Williams

Lead Developer and Researcher, Microsoft Research Cambridge

110 Comments

Great news, very important milestone in Excel development.

Silver Contributor

I look forward to working through the changes and innovations you report!  Now I needn't feel guilty posting solutions built with Lambda helper functions on the forum!  I probably owe Excel community members an apology, it was a bit self-indulgent; but possibilities this opens up for changing the 'art of the spreadsheet' are absolutely seismic.

Copper Contributor

Nice work.  When trying this today, the AFE wasn't importing any worksheet-level defined names from my workbook, it was only importing workbook level names.  Is this deliberate, or an oversight?

Microsoft

@Dan Mayoh We currently don't import worksheet scoped names but if that would be really beneficial we could certainly consider adding support.

Copper Contributor

@Chris_Gross understood.  I don't use worksheet scoped names often - sometimes just to aid with dependent dropdown lists. And I expect they're not used much by others either (and rightly so), so adding support would probably be of little benefit.  I'm happy that the AFE can assist a little not only with LAMBDAs, but also with the writing of other named formulas that make use of standard functions.

Iron Contributor

@Chris_Gross 

Great work - with the AFE

Are there plans to make Lambdas work when stored inside add-ins - They would then be a 100% replacements to VBA based UDF's

 

Cheers

Sam

 

 

 

Silver Contributor

@Dan Mayoh 

I think you were correct to expect support for worksheet-scoped names.  It is not the most urgent of requirements but I would, in principle, support the idea that names should be scoped locally unless there is a need to reference them more widely.  Other uses of locally scoped names includes scenarios where a worksheet is copied, or moved between workbooks.

For example

= SUM(September!Expenses, October!Expenses)

reads reasonably well.  I also prefer to use them when creating dynamic charts; fully qualified workbook names can be messy.

Copper Contributor

When using this with formula argument separator equal to ; it does not work properly. I opened a workbook with a number of lambda functions, they show up but when a function contains more than 1 argument, it fails.

Copper Contributor

In support of other asking for this: please make ALL defined names part of the scope of this editor.

I use defined names a lot, including copying entire sheets to study scenarios. That way global names become sheet-local names.

Thus: reserving this editor for Workbook-global names only seems like an unnatural restriction too me.

Copper Contributor

Can this editor be integrated into the Name Manager?

=> New Name / Edit Name

The outline of the interface is practically there (only: the comment section is way too big, and the formula section is way too small).

This seems like the perfect place to integrate this.

Iron Contributor

@Chris_Gross 

Are there plans to make the editor work with Formulas (non LAMBDA) typed in a Cell ? If not the editor should be renamed to "Advanced Name Manager"

 

Cheers

Sam

 

 

 

Iron Contributor

@Chris_Gross 

Is the Chess Function available on Git - I did not find it - It looks great and reminds me of the work done by the Brilliant Daniel Ferry - here - Which believe it not is a chart (XY and Stacked Column)

 

Cheers

Sam

Copper Contributor

If I am on, what I think is, the latest version of Excel is there any reason Lambdas would not be available yet? Build 14827.20192

@ODC42 , build number practically doesn't matter ( latest one among all channels 2203.15003.20004), channel matters. Most probably you are on Current channel. If so wait a bit, deployment already started. 

@Peter Bartholomew , loading of names is not obvious question, at least for me. For your sample - it works without loading names into AFE

image.png

Now assume we have workbook with hundreds thousand of names, 98% percent of which are names of range and like. Why do we need megabytes of such names duplicate in AFE ?

I could agree that sync is far from perfect, at the same time not sure we know or may agree what the perfect is.

 

Silver Contributor

@Sergei Baklan 

"[I am] not sure we know or may agree what the perfect is"

I am sure that is correct!  There are also differences arising from the use of an external prototyping environment against what could be achieved with integrated functionality.  

 

"megabytes of such names duplicate in AFE"

I would consider any duplication beyond working data specific to the task in hand to be undesirable as a long-term solution.  My immediate feeling was that as a Name Manager replacement the AFE should provide a management overview of all the available names but have only a single name open for editing.  As a formula bar replacement, the worksheet itself provides the overview and the active cell is the only one visible for editing.

 

I suggest the two environments should be combined simply because I have found the pattern of solution development has changed; the use of 10+ line formulas now offers the clearest and most transparent route to specifying the calculation I wish to perform.  Whether the LET or LAMBDA function is then loaded as a defined name or worksheet cell may change during the course of the formula development and should not require a change of development environment.  Ultimately, I would like to see all formula definitions addressed, the other obvious examples being conditional formatting and validation. 

 

Something that would be far more ambitious would be to support test evaluation of the formula as it is being developed, so replacing the use of F9 in the formula bar and Evaluate Formula dialogue box.  I believe 365 online evaluates new formulas client side before the server calculation is triggered to recalculate the workbook so "if you were to commit this modified formula at the current location this is what you would get" calculation would appear to be possible.  At present, to check a variable from an extensive LET formula requires copy/paste to return the the value from the LET and the modified formula has to be saved so that it evaluates on the worksheet.

 

I am conscious that I may be seen as an "ungrateful bloody user" so I would like to reiterate the thought that what we have now is amazing and beyond my wildest expectations when I was badgering Microsoft for a complete rework of array formulas, which, at the time, had all the user-friendliness of a cornered rat.

Copper Contributor

Will these features be available in all languages or are they going to be limited to US English? 

Copper Contributor

Amazing new feature! Thank you for this release!

Excel has now done a major step in his history!

We may remember this in the future.

Brass Contributor

¡Gracias Equipo!

Copper Contributor

There is a bug in the Advanced Formula add on for Excel for Mac.

 

The comments and formulas in the Editor do not save properly when you close and re-open the workbook

 

E.g. if you try to import this, https://gist.githubusercontent.com/chgrossMSFT/d172fd552cf6893bcdc7942223cb0e55/raw/222fc24f6baedabf...

 

and sync to the name manager, close and re-open the workbook, the comments will be lost, and half the formulas lose their paranthesis and the text 'LAMBDA' from the formula

 

Can this be addressed?

 

 

Microsoft

@Sachin_Tandon Thanks for reporting the issue. We will look into this.

Copper Contributor

Ok - I have fixed the bug by upgrading to the latest Excel Mac Build 16.59

But the comments still disappear....

@Sachin_Tandon , is that with this setting?

image.png

Copper Contributor

Thanks!

I changed this setting to on, and now it is also keeping the comments, so no bugs for me anymore

Copper Contributor

I found that the editor is only usable for the English language, in particular the parameter separator is the "," and not the ";".
So it is not possible to synchronize the editor/manager with the Name Manager either.
Is it possible that a version for other languages will be produced?

@Sachin_Tandon , that's not the bug, that's by design. With hidden sheet option AFE keeps comments and formatting, without it - not.

Copper Contributor

Ok great - it wasn't obvious from the setting that that is what is did, e.g. with an illustrative example.....

@Sachin_Tandon , that was a message which explains the difference when change the setting. Now disappeared, and that could be considered as bug. Perhaps @Chris_Gross will clarify.

Brass Contributor

Two notes:

1) Can we export?

2) In Portugal the separator in formulas is ";" and not ",". Changing settings, import and change again? Easy but not elegant

@pbarbosa 

1) If from AFE interface, so far only click on Share icon, in appeared window on "copy definitions to clipboard" icon. Intermediate step(s) where to keep that's up to you. Such text could be imported by From text. Or if you save as GitHub gist using url on it.

Copper Contributor

I like it!

 

Would it be possible to specify whether names are visible or hidden? FYI the AFE picks up hidden names from the workbook, and if you use it to change anything about them then they silently get converted to visible.

 

I would like to see support for worksheet-scoped names. Sometimes people go crazy with them, and you could make it considerably easier for us to review and maintain them.

 

It looks like the hidden metadata sheet would generally be bloat, and users who happen to unhide it would typically just see it as some cryptic thing. If there is a reason that users might want it past whatever is required for AFE to work behind the scenes, it seems like the kind of thing that would be appropriate to explicitly call when they want it (similar to the "Paste List" button from the "Paste Name" dialogue box).

 

When I use the AFE to create simple names that are just references to ranges (and sync), they don't appear in the dropdown of the name box, then name does not appear in the name box when the relevant range is selected, and they do not appear in the list of names within the "Go To" dialogue box. Everything else that I have looked at seems right (i.e. they do show in the name manager, their .Visible property is True, they can be referenced by other formulae, and typing them into the "Go To" dialogue box works). Saving the workbook, closing Excel then reopening does not make them appear in name box.

 

It would be nice if the comments the AFE editor and GitHub Gist could be tied to the comment field of the name manager (i.e. the .Comment property). Upon superficial inspection, it appears as though the comments in LAMBDA EXAMPLES.txt are structured in a way that lends itself to this.

Copper Contributor

If you create a name using the AFE, then change it using name manager, the change doesn't seem to flow back into the AFE - even if you sync or restarted Excel. You can make a bunch of incremental changes in the name manager, then do something trivial in AFE like rename it and your changes are lost because it reverts to the most recent definition that the AFE was aware of.

 

Indentation and line breaks are really helpful when viewing in AFE and it is useful that it automatically adds them to names that were created in the name manager. On the other hand, the line breaks are a hindrance when viewing in the name manager. I expect this to cause a problem for those of us that create workbooks for other people to then use and maintain.

Brass Contributor

@LeviBailey 

 

Very good analysis. Comment is a great suggestion, otherwise it looks like something important is lost.
The AFE and the Name Manager should have a full bidirectional flow.

Sync documentation is updated here https://github.com/microsoft/advanced-formula-environment/blob/main/documentation/Name_Sync.md In general it's more effective to discuss all opened issues on GitHub https://github.com/microsoft/advanced-formula-environment/issues

 

 

Brass Contributor

Everything is there to be discussed. @Sergei Baklan Thanks.

Iron Contributor

@Chris_Gross , AFE is a milestone indeed.

Would appreciate an alert in the AFE that it is out of sync with the name manager. It could be as simple as something like an asterix * that we see in MS Notepad window title. Amid formula edits, it is sometimes easy to miss a sync.

Copper Contributor

The LAMBDA Help link goes to the "LET" function instead of the "LAMBDA" function.

Copper Contributor

Same "list separator" problem here.

I'm getting "Sync names with Excel name manager. failed: The argument is invalid or missing or has an incorrect format." error when I click on sync names button.

 

We use ";" by default in Turkey but the editor is pre-set to recognize "," only.

Exporting to TXT file, changing all commas to semi-colons and import it back to the editor is also doesn't work.

Hoping for a fix to this specific problem.

At least the editor can read what the list separator is set to from the system settings or there can be added an options section into the editor to choose the separator.

Copper Contributor

the same .... same "list seperator" problem here for Italy
Even if the editor can read what the list seperator is set to from the system settings   ...  it is very inconvenient to change ... please add an options section into the editor to choose the seperator.
Many thanks

Silver Contributor

As I see it, LET and LAMBDA have changed the nature of Excel formulas utterly, but the GUI/IDE has not kept up with the calculation engine.  I would argue that a complete rethink is required but that is fraught with user acceptance issues.

The AFE is a tool that has allowed ideas to be prototyped with sufficient success that it is considered to worth making it available to users.  That is not the same as fully integrated functionality that has been internationalised, so any limitations within the editor will be present in the AFE.

However, it is quite possible to develop Lambda functions directly on the worksheet and I do it quite a lot, so all is not lost if the AFE does not work in your particular locale. An example such development might be

= LET(
Sumλ; LAMBDA(s;v; SUM(s;v));
Accumulateλ; LAMBDA(values;v₀; SCAN(v₀;values; Sumλ));
Accumulateλ(list;100)
)

The key is that LET is used to provide a formula-local name space.

The next line defines a Lambda function with the name Sumλ [please excuse my use of the Greek alphabet from the symbol menu, I have it in my autocorrect to replace "\lambda"].
Once that is done, the new function is used within the definition of a further Lambda function Accumulateλ.
Only when that is done, have I called the function to accumulate a list of credit/debits to form a running balance.

So, I recommend using LAMBDA functions even if you can't access the AFE, just avoid Name Manager until the Lambda function is tried and tested.

 

@Peter Bartholomew , as a comment, with this technique it's not necessary to avoid Name Manager at all. After lambdas are tested within LET they could be easy copy/pasted into name manger (especially if take care about proper formatting) and used alone.

Silver Contributor

Agreed!

Copper Contributor

Hi, thanks for this great piece of software. My question, however is how does it deal with localized formula names?

I have seen that the intellisense suggests English named formula names, with the comma as separator, but my Excel is in Italian and we use semicolons...

Copper Contributor

Yes Unbezin ...

this notice has already been the subject of several notices

Copper Contributor

Hello
I have a question about the "Advanced Formula environment" application
is it possible to use it for the french version?

When I use "Advanced Formula environment", my formula does not appear in the name manager. I use the English terms in my formula so that my formula is valid (for example SUMIF in French it is SOMME.SI). I guess that's the reason for this failure.
Do you know if there is a way around this?
thank you for your reply

Microsoft

Great News !!

Copper Contributor

Was 'lambda' just removed from production? I was able to use it a few days ago but it is not available anymore today.

@Marko Jug , it happens from time to time at the beginning of deployment. I assume you are on Current channel. Try to update, or sign out/ sign in, that could help.

Copper Contributor

Was so much looking forward to this....

 

I use the English version of xls on a MAC with danish keyboard. Hence my separator is ";" not ",". Sad. Is there a workaround for this?

Copper Contributor

@Sergei Baklan Thanks. It does not make a difference but I will keep trying.

Version history
Last update:
‎Nov 18 2022 08:13 AM
Updated by: