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:

**=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.

- 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.

- 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.

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.

**=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.

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

- Press
**Ctrl + [**to go to the selected cell's precedent cells - Press
**Ctrl + ]**to go to the selected cell's 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

- 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.

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