How to Audit Excel Formulas
Use built-in tools and shortcuts to troubleshoot the formulas in a workbook. Also, see the Formula Audit Macro page, for a quick way to spot formula setup problems
Introduction
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.
Video: Quick Tips for Auditing Formulas
To see a few commands and shortcuts for auditing and troubleshooting formulas, please watch this short video tutorial.
View All Formulas on a Worksheet
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:
=C5*D5
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.
Show Formulas With a Keyboard Shortcut:
- To see the formulas, press Ctrl + ` (the accent grave key, above the Tab key -- international keyboards might use a different shortcut)
- NOTE: To hide the formulas later, use the same keyboard shortcut.
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.
Show Formulas With a Ribbon Command:
- To see the formulas, click the Formulas tab on the Ribbon, then click the Show Formulas command
- NOTE: To hide the formulas later, use the same Ribbon command.
Dates Are Displayed As Numbers
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..
Check Formulas with FORMULATEXT Function
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, you can watch this short Excel video tutorial. The written instructions are below the video.
How It Works
The FORMULATEXT function takes a cell reference as its argument.
=FORMULATEXT(F5)
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.
You can read more about the FORMULATEXT function here.
Go To Precedent or Dependent Cells
If you select a cell that contains a formula, you can use keyboard shortcuts to go to either its precedent or dependent cells.
- Precendent cells are the cells that affect the formula in the selected cell
- Dependent cells are the cells that are affected by the formula in the selected cell
Keyboard Shortcuts
- Press Ctrl + [ to go to the selected cell's precedent cells
- Press Ctrl + ] to go to the selected cell's dependent cells
Trace 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
To trace the precedents
- Select a cell that contains a formula with valid range references
- On the Ribbon, click the Formulas tab, then click Trace Precedents
- To see the next level of precedents, click the Trace Precedents command again
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.
Get the Sample File
Get 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.
More Function Tutorials
Last updated: March 5, 2023 3:19 PM