Home > Formulas > Functions > Audit

How to Troubleshoot Excel Formulas

Debra Dalgleish - Contextures

Here are a few of my favourite ways to troubleshoot Excel formulas in a workbook. Use keyboard shortcuts to show or hide formulas. Spy on cells with the Watch window. Jump from a formula cell, to its precedent or dependent cells. Fix minor problems, before they grow bigger!

Add Watch dialog box and Watch Window

Formula Auditing

If you're having problems with worksheet formulas in Microsoft Excel, do some quick troubleshooting, to look for the problem.

On the Excel Ribbon, there are several built-in tools and commands, in the Formula Auditing group. In the sections below, see my tips for using these tools, so you can fix minor problems, and get back to work!

Excel formula auditing tools and commands on Ribbon

Video: Quick Tips for Auditing Formulas

Watch this short video, to see a few of my favourite commands and shortcuts for auditing and troubleshooting formulas. There are written steps below the video, and on the FORMULATEXT function page.

Video Timeline

  • 0:00 Show or Hide Worksheet Formulas
  • 1:16 Trace Precedents/Dependent Cells
  • 2:05 See Formulas with FORMULATEXT
  • 2:40 Jump to Precedent/Dependent Cells

1) Show Formulas on Worksheet

Most of the time, you can see the formula results on a worksheet.

Occasionally though, you might want to show the formulas in the cells, instead of the results. This can be an easy way to do some quick formula troubleshooting.

Formula Results Showing

For example, in the screen shot below, results are showing, and cells' formulas can be seen, one at a time:

  • Cell E5 is selected
  • Selected cell's formula shows in the formula bar: =C5*D5
  • Cell E5 shows the formula result: 349.32
    • Qty (123) multiplied by Unit Price (2.84)

formula results showing

Show Formulas on Worksheet

Instead of checking cell formulas one at a time, you can use the Show Formulas feature, to see all of the worksheet formulas.

a) Keyboard Shortcut

The quickest way to show or hide formulas on the worksheet is with a keyboard shortcut:

Ctrl + `

  • The ` is the accent grave key (above Tab key on my keyboard)
  • This shortcut toggles the Show Formulas setting on and off.

press Ctrl + ` (accent grave / grave accent)

Show Formulas On/Off

In the screen shot below, the Show Formulas setting has been turned on.

For troubleshooting, you can quickly scan down the columns, to see if the formulas look consistent.

  • Later, you can use the Ctrl + ` shortcut again, to turn Show Formulas OFF

formulas showing in cells

b) Show Formulas With Ribbon Command

If you prefer to use commands from the Excel Ribbon, follow the steps below, to turn the Show Formulas setting on or off.

  • On the Excel Ribbon, go to the Formulas tab
  • In the Formula Auditing group, click the Show Formulas command
  • Formulas appear on the worksheet
  • On the Ribbon, the Show Formulas command is highlighted, to show that it is active (ON)

Later, you can use the Show Formulas command again, to turn Show Formulas OFF

show formulas command on Ribbon

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.

date and number formatting

Here is the same worksheet, with dates and number formatting removed, because formulas are showing. All dates and numbers are displayed in General format.

date and number formatting removed

2) Go To Precedent / 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

formulatext function shows inconsistent formulas

3) Trace Precedent / 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 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.

trace precedents

To trace dependents

  • Select a cell that contains a formula with valid range references
  • On the Ribbon, click the Formulas tab, then click Trace Dependents
  • To see the next level of dependents, click the Trace Dependents command again

Blue arrows will show the path to the dependent cells.

In the screen shot below, the tax calculation in cell F5 is dependent on the total calculation in cell E5

trace dependents

Different Sheet

If a precedent or dependent cell is on a different sheet, a worksheet icon will appear at the beginning of the arrow.

  • To go to that precedent or dependent cell, double-click the icon, to open the Go To dialog box.
  • Then, select a reference in the list, and click OK.

trace precedents on another sheet

Remove Arrows

To remove the arrows, after you finish troubleshooting, click the Remove Arrows command on the Ribbon's Formulas tab.

remove arrows command

4) Watch Window

Do you ever use the Watch window, to keep an eye on the results in one cell, while changing the data in another part of the workbook?

I used it while working in a client’s price list file, where there was a multiplier on one sheet, and the final price on another sheet. With the final price in the Watch Window, I could change the multiplier, and immediately see the effect on the price! That was much easier thatn flipping back and forth between the worksheets.

Add Cells to Watch Window

To add cells to the Watch Window, follow these steps:

  • On the Excel Ribbon, go to the Formulas tab
  • In the Formula Auditing group, click the Watch Window command
    • Or use the keyboard shortcut: Alt + M, W
  • On the worksheet, select one or more cells that you want to watch
  • At the top of the Watch Window, click the Add Watch button
  • The Add Watch dialog box opens, showing the address of the selected cells
    • (Optional) If the wrong cell address is showing, click on a different cell on the worksheet
  • Click the Add button, to add the selected cells to the watch list

Add Watch dialog box and Watch Window

Use the Watch Window

After you add cells to the Watch Window, each cell is listed separately, in the Watch Window list.

  • Info: For each watched cell, six details are shown: workbook name, worksheet name, cell reference, current value, and formula
  • Monitor: To monitor a cell, check its value after making changes to its precedent cells
  • Go: To go to a watched cell, double-click its row in the watch list
  • Delete: To delete a cell from the watch list, select the cell's row in the watch list, then click the Delete Watch button

delete watched cell in Watch Window

Adjust the Watch Window

If necessary, you can adjust the Watch Window size, column widths, and location, so it's easier to see the cell information.

Window Size: To change the window size, point to its border, and drag in or out.

Column Width: Point to the dividing line between the column headings in the watch list. When the pointer changes to a two-headed arrow, drag left or right, to adjust the column widths

delete watched cell in Watch Window

Watch List Location: Usually, the Watch Window floats over the worksheet. However, you can also dock the window at the top, bottom, right or left of the worksheet.

  • Dock: Point to the Watch Window title bar, and drag the window above the formula bar, or past the edge of the worksheet cells (left, right or bottom)
    • Tip: To dock in its previous location, double-click the title bar
  • Float: To make the Watch Window float again, drag its title bar onto the worksheet.
  • Note: The location is workbook level - it can be docked in some workbooks, and floating in others.

Get the Sample File

Get the sample Audit Formulas workbook. The zipped Excel file is in xlsx format, and does not contain any macros. The FORMULATEXT function is available in Excel 2013 and later versions.

More Function Tutorials

Excel Error Values

FORMULATEXT function

Formula Audit Macro

Find and Fix Formula Cells

VLOOKUP Number Errors

Last updated: April 10, 2024 3:19 PM