Excel Advanced Formula Environment

Excel’s new Advanced Formula Environment (AFE) is still in its early stages of development, but it’s already very useful, particularly if you’re scared to write your own LAMBDA functions, as you’ll see it can write them for you!

You may have seen me use it a few times in tutorials lately, but recently it has undergone an update with some notable improvements, so I’m going to cover it in more detail in this tutorial.

You can get the free AFE add-in from the Office store or via the Insert tab > Get Add-ins, and it’s compatible with Excel 2013 for Desktop onward, as well as Excel for Web and Mac. Once installed, it’ll be available on the Home tab of the ribbon on the far right:

It opens in a task pane, which you can detach and resize.

Note: as this tool is still in development, the features and interface you see in this tutorial may be different by the time you come to use it.

Watch the Video

Download Workbook

Excel Advanced Formula Environment Features

The AFE is split into three main sections: Grid, Names and Modules.

Grid: the grid tab enables you to enter formulas in cells, much like typing a formula in the formula bar, except it automatically wraps the formula onto separate rows for each component and applies colour coding.

Names: The names tab is similar to the Name Manager for defined names. The first section contains Functions you write yourself i.e. LAMBDAs:

You can also author new LAMBDAs from here by clicking the + symbol shown below:

Then enter the formula. Note: here you do not need to wrap the formula in LAMBDA, simply enter your formula in the Function Definition field and click Done:

When you view the formula in the Name Manager, you’ll see it has written the LAMBDA for you:

You can also reference named constants in the AFE. These appear in the Names section under Formulas and are available in the intelisense. For example, I have a defined name for the VAT rate called VATRate, and I can reference this in my NetVAT formula:

This way should the VAT rate ever change, I only need to edit the defined name VATRate and it will feed through to any functions and formulas using it.

Another option is to generate LAMBDAs from formulas in the grid. You can convert calculations split into steps across several cells into a single LAMBDA. Select the cell containing the final result and then click the icon second from the end:

Then enter the range (these are the cells containing the intermediate formulas), the parameters (these are the other cells being referenced by the formula), and the output cell (this is the cell containing the formula):

Tip: if the range containing the formulas isn’t contiguous, you can enter the cell references separated by a comma.

Click Preview to see the LAMBDA Excel has written for you (image below). It makes some assumptions about the parameter names, which you can change here.

Tip: select one instance of the name and press F2 to rename all instances:

Before clicking Create, you can rename the Function if you wish.

It should save automatically and be available in the name manager and grid, but if it doesn’t appear, you can click Save again. This will save it in the Name Manager and you can now use the function in the grid.

Ranges: The ranges area lists any named ranges in the file, however it currently doesn’t include Tables and it cannot display defined names that reference dynamic arrays.

Formulas: The formulas tab contains formulas you write and define with a name:

Modules: The Modules tab is used to store collections of named formulas defined using Gist code files from GitHub (cloud icon) or imported from the grid (spreadsheet icon).

You can try it out with the Gist file available at this URL: https://gist.github.com/jack-williams/5859d170fcb363dad1620c4d40770527

They can be added to the Workbook module (see image above), or you can create a new module (see image below).

Both are available within the workbook and appear in function auto-complete. Functions in the Workbook module appear in the intellisense list as is, whereas functions added to a new module are prefixed by the function name, separated by a dot operator

Formula Portability

Formulas saved in the AFE are also in the Name Manager of the file. If you share the file with someone else, then they will have access to those functions in the file, just the same as any other defined names.

Localisation

AFE has some support for localisation of formulas and no support for localisation of app text. Formulas in the AFE must be edited using a comma argument separator, for example =SUM(A2,C2,E2), however the AFE will interact with workbooks using other separators such as semi-colon (;).

When reading or saving a formula, the AFE will automatically translate between formats. Function names can be written using the workbook’s locale, but you can force English function names via the settings (see screenshot below). The AFE will eventually support full formula localisation (and may do by the time you read this), rather than requiring comma argument separators.