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

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.

Download the Sample File

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.

Excel Add-ins for Auditing Formulas

RefTreeAnalyser

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.

FastExcel

The FastExcel
add-in can help you find calculation bottlenecks in your workbook,
and understand and debug complex formulas more easily.