A new way to debug formulas from Excel Labs
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). 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. - 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 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. 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. 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. 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.
