A new way to debug formulas from Excel Labs
Published Dec 11 2023 06:00 AM 27.8K Views
Microsoft

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 debuggerThe 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 formulaLive 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 formulaEvaluation 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 rangesPreview 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 formulasDebug 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 IDMulti-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:

 

  1. First, select the range containing the formulas that will make up the function.
  2. 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.
  3. 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 gridDefine 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.

17 Comments
Version history
Last update:
‎Dec 08 2023 10:14 AM
Updated by: