Contextures

How to Audit Excel Formulas

Use built-in tools and shortcuts to troubleshoot the formulas in a workbook

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.

formulatext function shows inconsistent formulas

Video: Quick Tips for Auditing Formulas

To see a few commands and shortcuts for auditing and troubleshooting formulas, please watch this short video tutorial.

Or watch on YouTube: Auditing Excel Formulas - Quick Tips

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.

formula results showing

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.

formulas showing in cells

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.

    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

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.

You can read more about the FORMULATEXT function here.

formulatext function shows inconsistent formulas

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.

trace precedents

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.

trace precedents on another sheet

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

remove arrows command

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.

Learn about its troubleshooting tools and features, to see how it can help you.

More Function Tutorials

SUM Function  

VLOOKUP Function  

INDEX function and MATCH Function   

Count Functions  

INDIRECT Function  

MATCH Video   

VLOOKUP Video  

Hide VLOOKUP Errors  

Search Contextures Sites

 

30 Excel Functions in 30 Days

 

 

 

 

 

Last updated: December 21, 2015 12:12 AM