Excel Labs has just added a nice formula Debug option that helps you understand the steps inside a formula and fix errors.
There are already three ways to look inside a formula; Evaluate Formula, F9 to calculate part of a formula and the recent Visual Preview.
Now Microsoft’s Excel Labs Advanced Formula Environment has a Debug option that shows the steps to calculate a formula.
The steps show the interim results in the squares. For example it shows that Today() converts to the serial date 45272 then Year(45272) becomes 2023.
But it gets better. Click on a formula element and Debug will show its source in a previous step using a matching color.
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’.
Anyone using Python in Excel should get the Python Editor, an IDE that’s far better than in the in-built PY() editing pane.
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