Excel LAMBDAs Made Easy with the New Advanced Formula Environment (Editor)
Microsoft has released a new, official add-in for Excel called the “Advanced Formula Editor”.
If you have used Excel’s Name Manager to create more complex formulas that can be referenced via a single word, you no doubt experienced the pain associated with a decade’s old interface.
As Excel has evolved over the years, the Name Manager’s interface has not changed to keep up with the new demands placed upon it.
Let’s examine Excel’s new kid on the block for managing complex formulas with assigned names, the Advanced Formula Environment (Editor).
The Advanced Formula Editor was developed to aid in the writing and managing of complex formulas that are assigned names for easier use.
The driving force behind this development was the introduction of LAMBDA functions.
If you haven’t heard of the LAMBDA function, check out my videos below for a quick introduction and use cases.
(video links go here)
Formulas that use the LAMBDA function become challenging to read when displayed on a single line. They become even more challenging to read when displayed in the small edit fields of the Name Manager dialog box.
The new Advanced Formula Editor will serve as a more user-friendly alternative to the old-school Name Manager dialog box when working with more sophisticated formulas.
“How do I get the new Advanced Formula Editor?”
Great question. You are so smart to ask.
The Advanced Formula Editor is currently an add-in to Excel that can be downloaded for free from the “Get Add-Ins” feature in Excel.
Open Excel and navigate to the Insert tab/ribbon. The Add-ins group holds the Get Add-Ins button.
In the Office Add-Ins dialog box, search for “Advanced Formula Editor”. When discovered, click the Add button to install the editor into Excel.
Once installed, the launch button for the new editor will appear at the far right of the Home ribbon.
Taking a Tour of the New Editor
Clicking the Advanced Formula Environment button open the editor on the right of the screen.
Upon the first launch, we can take a tour of the interface and even try some simple examples of formulas written using the new editor.
Clicking the Try Examples button shows us a simple named formula called ISBLANK that uses the LAMBDA function to create an IF statement that uses the ISBLANK function.
Don’t worry if this looks a bit scary; LAMBDA functions are quite simple once you get the hang of them. We’ll build one from scratch in just a bit.
Don’t Forget This Important Step
If we go into Excel and try to use this newly created IFBLANK function, notice that it fails to appear in the IntelliSense list of functions.
A requirement of the new editor is that you must “publish” (i.e., “Sync”) the named formula to the Name Manager.
Now when we attempt to use the custom IFBLANK function, it appears in the IntelliSense list.
Using the IFBLANK in a simple test, it looks like so…
Opening Excel’s Name Manager will show the custom IFBLANK function as if we had created it in the older interface.
Composing and Editing Custom Functions
The new Advanced Formula Environment makes creating and editing custom functions so much easier.
When editing a formula, we have access to the IntelliSense system to help guide our understanding of what is expected of each function.
We can also rename existing custom functions…
… and delete functions I no longer need.
I can also share custom functions.
However, I am limited to only copying the function code to the Clipboard for pasting in some other area, like an email, text file, or other Excel file.
The Different Ways to View the Environment
The Advanced Formula Environment has two different ways to display its contents.
Manager View
The Manager View displays the environment contents as separate cards.
Editor View
The Editor View displays all the custom formulas like they would appear if listed in something like Notepad.
The nice thing about both views is that you can add carriage returns in the middle of the formula to break the logic into multiple lines to aid in readability.
Separating Custom Functions
To know where one custom function ends and the next one begins, a semi-colon is placed at the end of any custom function.
Adding In-Line Documentation
You can also add comments to your custom formulas by surrounding the comments with a /* and */ characters.
Creating a Custom Named Function From Scratch
We have a list of geographic locations that are separated by blank rows.
We want to derive from this list a new list that has the location names without the blank lines separating each entry.
Solving Without the Advanced Editor
We can write in a cell the following formula to achieve our result:
=FILTER(A3:A11, A3:A11<>“”, “”)
The FILTER function will return data from cells A3:A11 but only for cells where they are not equal to “nothing” (the two double-quotes denote empty text.)
Let’s say you perform this type of operation often. Wouldn’t it be nice if you could create your own custom version of FILTER that would require less input on your part? Of course, it would.
This can be achieved using a LAMBDA function.
To set this up, open the Advanced Formula Editor and click the Add Named Formula button.
We’ll call our new custom function “NoBlank” and we’ll start with the LAMBDA function.
LAMBDA functions begin by defining an argument name. In our case, this will be the argument that holds the cell range set by the user.
Let’s call this argument “myrange”. We only need to define it once even though it will be used twice in the actual formula as it points to the same location.
The second part of the LAMBDA function is the calculation that will occur using the argument supplied at the beginning. This will be the FILTER formula we created earlier. The difference is that instead of exact cell references, we will use the “myrange” argument.
Click ADD when finished.
Don’t Forget….
Remember, new custom functions do not become integrated into Excel for use unto you “sync” the Advanced Formula Editor with the Name Manager.
Using the Newly Created “NoBlank” Function
We can use the new custom function in the following way.
=NOBLANK(A3:A13)
This makes using more sophisticated functions much easier. Put in a bit of effort at the beginning and reap the rewards over and over later.
You can even nest this custom function in other functions to gain deeper functionality.
=SORT(UNIQUE(NOBLANK(A3:A13) ) )
Importing Custom Functions
Another great feature of the Advanced Formula Editor is the ability to import multiple custom functions in a single step.
This makes sharing your creations with others easier as well as taking advantage of other people’s creations who may have more skill at writing custom functions than yourself. (It’s not bad to ask for a bit of help now and then.)
Currently, the import feature limits you to supplying a GitHub Gist URL for the source of the function’s code.
If you’d like to try this out, Microsoft has created a great repository of custom functions for you to import using the following URL:
https://aka.ms/LAMBDAGist
This will take you to the Microsoft site where you can then copy the actual site URL and place it into the import dialog box.
NOTE: The actual full URL is listed below.
https://gist.github.com/chgrossMSFT/d172fd552cf6893bcdc7942223cb0e55
Currently, there are about a dozen interesting and useful custom functions you can take immediate advantage of.
Having these pre-created functions is also a great way to learn and understand LAMBDAs by reverse-engineering someone else’s creations.
Don’t Forget….
Remember, new custom functions do not become integrated into Excel for use unto you “sync” the Advanced Formula Editor with the Name Manager.
A Practical Example of the Advanced Editor
Using the APPENDROWS custom function from the Microsoft GitHub download (previous section), let’s see how we can take immediate advantage of someone else’s genius.
We have two tables named “Q1Table” and “Q2Table” that contain Revenue information for Divisions and Regions.
We would like to create a dynamically appended table that is made up of these two tables.
We can write the following formula using the APPENDROWS custom function:
=APPENDROWS(Q1Table, Q2Table)
Because the source tables are proper Excel Tables, when we add, remove, or change any of the rows, the appended table will update automatically.
Limitation of the Advanced Formula Environment
One of the downsides of the new editor is that we can’t perform direct cell referencing by clicking on cells.
Instead, you must type the cell addresses (be they relative or absolute) manually.
For example, if you wanted to point to cells A1:A4 on a sheet named “Report”, you would have to write it completely manually in the following manner.
The Future of the Advanced Formula Editor
As with most Microsoft tools, the capabilities and flexibility of the tool will increase as the tool matures.
As this new editor is still in its infancy, we are likely to see a great many new uses for this new tool as more people become aware of its existence and capabilities.
If you’ve been holding back on experimenting with LAMBDA functions because writing them in the older Name Manager was such a pain in the <blank>, perhaps the Advanced Formula Environment may be just the thing to pique your interest
ليست هناك تعليقات:
إرسال تعليق