Audit Tools In Excel
Sarbanes-Oxley Section 404 doesn’t affect all spreadsheets in all companies,
but it’s given us all a lot to think about in terms of our own spreadsheets.
This article will show you a few ways of simplifying the auditing of
spreadsheets using both mouse-driven features and shortcut keys.
Incorrect references are common mistakes in spreadsheet formulas. For
instance, the totals for Q2 sum up April, May, and June but Q3 (due to
sticky mouse) sums June again with July, August, and September. It looks
like Q3 was a great quarter when really it’s just a bad formula.
To find errors like these use Formula Auditing. Go to the Tools > Formula
Auditing menu and Show the Formula Auditing Toolbar. You can also find it
under View > Toolbars. This toolbar contains some nice features for pointing
out the cells that depend on the current highlighted cell (dependents)
and for pointing out the cells
upon which the current
highlighted cell depends (precedents).
Beside each button you’ll also see a button for turning off the arrows
(it can get a little busy after you select a few cells and point out their
dependents and precedents!) Click on each cell to be evaluated in turn and
then click on the dependents or precedents button. Your incorrect formula
should jump out at you in no time.
If you’re a fan of
shortcut keys or just want a little extra power, you’ll like the
following combinations. Click on any cell and instead of using the Trace
Dependents button on the menubar, click Ctrl + ]. This will select all of
the cells on the worksheet that directly depend on the current highlighted
cell. If you hold down the shift key as well, Shift + Ctrl + ], you’ll get
the direct descendents but also their descendents.
If you want to see the cells upon which the current cell directly depends,
click Ctrl + [.
Again, if you want to see all of the cells upon which it depends (all precedents),
click Shift + Ctrl + [.
You can think of the two brackets as one pointing out (where does this
cell lead?) and one pointing back (where did this cell come from?). See the
demo video for an excellent
visual explanation.
You may also run into the issue of formulas replaced by values or ‘adjusted’
formulas. To quickly check your worksheet ‘s formulas, just click Ctrl + `
(accent grave—at the top left of your keyboard!). To switch back, click the
combination again. This is a great view for finding formulas that have been
typed over with numbers or formulas that don’t match their neighbors. See
the
demo video for another
excellent visual explanation of this functionality.
The Excel Shortcut key mousepad contains these shortcut keys and others -
order yours today!