Use built-in tools and shortcuts to troubleshoot the formulas in a workbook
There are several built-in tools and shortcuts that you can use to troubleshoot the formulas in a workbook. For example, use the FORMULATEXT function in Excel 2013, to see both the formula result, and the formula text, while checking a column.
To see a few commands and shortcuts for auditing and troubleshooting formulas, please watch this short video tutorial.
Usually the formula results are showing on a worksheet, so you can see the numbers or text that the formulas return. In the screen shot below, cell E5 is selected, and you can see its formula in the formula bar:
The result of that calculation is showing in cell E5 -- 349.32 -- the Qty multiplied by the Unit Price.
To troubleshoot the formulas, you can show the formulas in the cells, instead of the results. To do this, use a keyboard shortcut, or a Ribbon command.
In the next screen shot, you can see the same worksheet, showing all the formulas. You can quickly scan down the columns, to see if the formulas are consistent.
When the formulas are displayed in a worksheet, all the dates are displayed as numbers, instead of a date format. They will return to date formatting when the Show Formulas setting is turned off. Other numbers will be displayed with General format, while formulas are showing.
Here is the worksheet, with dates and number formatting displayed.
Here is the same worksheet, with dates and number formatting removed, because formulas are showing. All dates and numbers are displayed in General format..
New in Excel 2013, the FORMULATEXT function shows the formula that is entered in the referenced cell. It can help you troubleshoot, by showing if formulas in a column are consistent, while still showing the formula results in the original cells.
To see how the FORMULATEXT function works, please watch this short Excel video tutorial. The written instructions are below the video.
The FORMULATEXT function takes a cell reference as its argument.
In this screen shot, the formula in row 6 is slightly different from the others. The FORMULATEXT function shows the function entered in each cell. You can still see the formula results, in the Tax column.
If you select a cell that contains a formula, you can use keyboard shortcuts to go to either its precedent or dependent cells.
If you select a cell that contains a formula, you can use Ribbon commands to trace its precedent or dependent cells. These commands create arrows on the worksheet, to trace the connections between formulas
Blue arrows will show the path to the precendent cells.
If a precedent is on a different sheet, a worksheet icon will appear at the beginning of the arrow. To go to that cell, double-click the icon, to open the Go To dialog box. Then, select a reference in the list, and click OK.
To remove the arrows, after you finish troubleshooting, click the Remove Arrows command on the Ribbon's Formulas tab.
Download the zipped sample Audit Formulas file. The workbook also uses the FORMULATEXT function, to show the formulas in the example cells. These functions only work in Excel 2013, so you'll see errors if you open the file in an earlier version.
For detailed formula auditing, try the Excel add-in, RefTreeAnalyser. There is a free trial version available.
It helps with easy auditing of formula dependents and precedents, finding circular references, checking for formula inconsistancies, and many other auditing tasks.
The FastExcel add-in can help you find calculation bottlenecks in your workbook, and understand and debug complex formulas more easily.
Learn about its troubleshooting tools and features, to see how it can help you.
Last updated: December 12, 2016 3:01 PM