Home > Formulas > Functions > Audit

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

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!

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

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.

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)

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

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

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

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

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.

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.

- 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

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.

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

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.

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

- Or use the keyboard shortcut:
- 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

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

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

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

Last updated: April 10, 2024 3:19 PM