Announcing LAMBDAs to Production and Advanced Formula Environment, A Microsoft Garage Project
Published Feb 08 2022 08:55 AM 40.3K 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

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