Advanced Formula Editor
I previously wrote about the new LAMBDA function. You can read the blog here. Today, I am excited to share that LAMBDA has been made generally available earlier this month for those who have Office 365 and receive a monthly update. With the release of LAMBDA, a new Advanced Formula Environment that includes an editor for building LAMBDA’s has also been made available.
The new advanced editor is an excellent development for Excel as building LAMBDA in the grid or name manager is a very cumbersome process. The new formula environment also makes it easier to share LAMBDA’s as you can download them from a GISt.GitHub URL.
The editor is only available as an add-in at this time, but if you plan on using LAMBDA, it is well worth the download. Below is a screenshot of what it looks like once you have installed the add-in. In the below screenshot, you will notice how it is formatting a complex LAMBDA that somebody has written.
I am not going to spend much time talking about how I have written LAMBDA’s as that is something that I am not very good at, but I have used other people’s LAMBDA’s, and I see the value in taking advantage of the formula’s others have created. I think the real power of LAMBDA will come from the advanced formulas others create that extend what the average user can do in Excel. The average user will not create LAMBDA functions, but they will take advantage of what others have done to make working in Excel easier. With that in mind, let’s go ahead and review some of the excellent material that has already been released this month, explaining this new editor and how we can use it.
Best LAMBDA articles on the web
The first article I recommend is the release from Microsoft by Chris Gross on the Excel blog.
Microsoft: LAMBDA Announcement
The article does a great job of the following:
Providing information on how to download the Advanced Formula Editor. Link to download the environment Advanced Formula Environment Download
Sharing Examples of how the editor works to create new formulas such as IFBLANK, similar to IFERROR but you get to tell Excel what to do if a cell is blank.
Walking through all the major features of the new Manager and Editor Views of the Advanced Formula Environment
Provide details on downloading LAMBDA’s from gist URL straight into the Editor. Link: LAMBDA - Examples for
The second article I recommend is from Excel Jet Consult, written by Microsoft Excel MVP Abiola David. The article includes
Link to a video about this new tool
Walkthrough of how to download the Advanced Formula Editor
A real-world situation and how to write the formula for this situation.
At present very few articles are available on this subject as it is still very new and people are getting used to this new tool.
Best LAMBDA Videos
The first video is the most complete video I have seen regarding LAMBDA’a and the advanced formula editor. The first video is by David Benaim
The video is approximately 20 minutes long and does a great job of the following
Showing cool new formulas he has created with LAMBDA
Showing how to download the new editor
Showing how the new editor and manager works
Showing how to download the new LAMBDA’s Microsoft has made available for download
How to create a template so every time you open Excel, your new LAMBDA formulas will be available
Provides details on how to get a copy of the new LAMBDA functions he has created, including (DISTINCTCOUNT, DISTINCTOUNTIF, etc.)
The second video is from Excel MVP Bill Jelen.
The video is relatively short but provides details on
How to download the new editor
How to download LAMBDA’s that others have made available.
How the new environment makes it much easier to share LAMBDA’s with your co-workers
The third video is from another Excel MVP and one of my favorite follows on LinkedIn Wynn Hopkins of Access Analytic
This video is almost 15 minutes long and talks about LAMBDA and the Advanced Formula Environment and walks through a simple example of how one can use these new functions and environment. What I really like about this video is how he breaks down his example into easy-to-follow steps.
He starts by showing the logic he would use to build the formula he wants and then how to turn it into a LAMBDA. After walking through his example, he explains how you can save a LAMBDA into a Github repository. He also provides his GIST in his show notes so you can download the LAMBDA’s he has been playing with.
The final video is by Excel MVP Abiola David
This video complements the blog that was mentioned above by Abiola David.
CONCLUSION
If you are interested in creating custom formulas and taking your Excel game to a new level I recommend taking the time to read the blogs and watch the videos listed above. It will not take more than a few hours of your time and that is if you pause the video and follow along in Excel. Leave your thoughts below how do you see LAMBDA changing and the Advanced Formula Editor changing the way we work in Excel.