Skip to content

A better way to view formulas in Excel

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:

(copied from a Quora thread)

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.


Added in December 2023 is an enhanced Debug mode.


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’.

Advanced users are welcome to try the GenerativeAI model, part of Copilot.

Anyone using Python in Excel should get the Python Editor, an IDE that’s far better than in the in-built PY() editing pane.

Debug to understand and fix Excel formulas

Microsoft fixes a big hassle with Excel’s great Lambda()
Monospace font makes a difference to Excel formulas
Value Preview is coming to Excel formulas
Circle & Sphere Formulas in Excel using Pi 𝜋
Excel formula: get percentage discount

About this author

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.