Debugging Formulae with Excel Labs

You may know SumProduct work as model auditors amongst other duties: have you ever thought of calling us in?  On occasion, it’s likely you have found yourself tangled in a web of complex Excel formulae, trying to identify the source of an error.  Well, you might want to know that the latest experiment from Excel Labs is a new formula debugger, available within the Advanced Formula Environment (AFE).

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.

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 

• Evaluation steps: each evaluation step is shown, with highlights and underlines making it easy to see what changed at every step    

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

• LAMBDA debugging: you may debug LAMBDA formulae, 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.    

Not all functions are supported just yet (XLOOKUP is one notable absentee).  If a function or scenario is blocking your work, Microsoft has asked that you let them know through the usual feedback channels.

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.