New way to debug formulas in Excel

Brink · Dec 11, 2023

Have you ever found yourself tangled in a web of complex Excel formulas, desperately trying to pinpoint the source of an error? Fret no more! The latest experiment from Excel Labs is a new formula debugger, available within the Advanced Formula Environment (AFE).

The AFE debugger

Getting started
The debugging capabilities are activated using the new button on the Grid page in AFE. When the button is activated the debugger will be visible in the same editing pane, and you are ready to go!

Key features
Here are some of the key features to look out for:

- Live debugging
The debugger updates as you type, making it quick to explore how different variations of a formula are evaluated.

Live debugging as you edit the formula

- Evaluation steps
Each evaluation step is shown, with highlights and underlines making it easy to see what changed at every step.

Evaluation steps show each change in the formula

- Range preview
View a preview of the grid when hovering over a reference. Previews show the surrounding context to make it easier to navigate.

Preview referenced grid ranges

- LAMBDA debugging
Debug LAMBDA formulas, such as SalesForBestCategory, shown below. Every function call is contained in an expandable card. This lets you dive into the particular functions you are interested in, to see how they evaluate.

Debug LAMBDA formulas

Function support
Not all functions are supported just yet. If a function or scenario is blocking your work, we would love to hear about it!

In case you missed it: Sheet-defined functions
Another experimental feature in the advanced formula environment is the ability to automatically convert a multi-cell calculation into a function, also known as sheet-defined functions. This capability has been part of AFE for a while, but in case you missed it – here is a brief overview.

Often, we find ourselves spreading a complex calculation over many cells, like this one, that extracts an ID from a piece of text.

Multi-cell calculation for extracting an ID

After writing the calculation we are either faced with many intermediate cells, or we must roll the formulas into a single mega-formula. With AFE and the “Add function from grid” feature, we can automatically convert these formulas into a tidy function using three simple steps:

First, select the range containing the formulas that will make up the function.

Then click the “Add function from grid” button and provide the references that will define the inputs and outputs. Sometimes, AFE can automatically detect the inputs and outputs based on the dependencies.

Finally, AFE will present you with the generated function that combines the formulas in the range. If you use labels adjacent to formulas, AFE can also use those to generate friendly parameter names, like Text or After.

Define a custom function from cells in the grid

How to access
The debugger will be pushed to Excel Labs automatically and there is no need to explicitly update the add-in. If you do not have Excel Labs, you can install the add-in from the Office store. Follow this link.