A better way to view formulas in Excel

Office-Watch.comLast updated: 12 December 2023

Microsoft 365,Microsoft Excel,Office 2019,Office 2021 / Office LTSC,Office 365

Here’s a far better way to view and edit formulas in Excel than the small formula bar. It’s a free add-in from Microsoft’s Excel Labs called Advanced Formula Environment (AFE)

Understanding Excel formulas can be difficult, even ones you’ve written yourself. Imagine trying to work with a monster formula like this:

Happily, there’s an easier way to view and edit any Excel formula. All nicely nested and linked like this:

There’s more in this tool which is free and comes from Microsoft itself. The only question is why the company doesn’t make this part of the mainstream product?

Advanced Formula Environment

The fearsomely named “Advanced Formula Environment” (AFE) is for everyone who uses Excel, especially complex formulas with many IF/CHOOSE/SEARCH and other conditions.

Even simple formulas are a lot easier to deal with, like this one from a Calendar workbook (in the Office Watch 2023 Annual). See how the Date() parameters are clearly set out.

If anything in Excel’s Advanced Formula Environment seems familiar, that’s because it uses features from Visual Studio’s integrated development environment (IDE).

Brackets / Function highlight

The opening/closing brackets of a function are highlighted when you click inside it. That’s very handy when there are many functions and it’s hard to figure out where one ends.

As you type, possible functions appear for you to choose from

The closing bracket is inserted automatically and a function summary appears with the current parameter highlighted with blue text.

Function problems are explained

Press Alt + F8 to see more detail. Press the Down/up arrows at right to see other errors. Ignore the ID string of letters/numbers.

Name Manager

The Names pane is a much-improved summary of anything setup in the Name Manager; Functions, Ranges, Formulas. A readable list of named ranges alone makes AFE worthwhile.

The buttons at right of each range let you edit, duplicate or delete. The + sign allows a new range to be created.

Right-click choices

As usual, there’s a right-click menu with more options to explore.

Debug

Added in December 2023 is an enhanced Debug mode.

Lambda()

AFE is especially useful, if not essential, for making Lambda() formulas. The in-built single line in Name Manager is useless.

Import from GitHub

Lambda examples from GitHub can be imported via the Import from URL button.

If you want to try this out and get some useful Excel formulas, try this GitHub Gist page or this.

How to get Advanced Formula Environment

Open the Add-ins store within Excel or go to this link ‘Excel Labs’ then click the ‘Get it now’ button.

Excel Labs will appear on the far-right of the Home tab and has three components. Click on Open next to Advanced Formula Environment. If you like AFE, select ‘Make Default’.