Auditing

This is the name giving to examining your formulas and making sure they are generating the correct results.
Formulas can be very complicated, especially if you combine them with named ranges, absolute and relative references as well as links to other worksheets and workbooks.
Understanding and stepping through complex formulas can be a real headache but there are lots of features that you can use to make this easier.
There is also a feature that lets you identify the cells responsible for any errors returned by your formulas.
They can be accessed from the (Formulas Tab).


 

Trace Precedents - Displays arrows that indicate what cells affect the value of the currently selected cell.
Trace Dependents - Displays arrows that indicate what cells are affected by the value in the currently selected cell.
Remove Arrows - Button with Drop-Down. The button removes all the arrows drawn by the trace precedents and trace dependents. The drop-down contains the commands: Remove Arrows, Remove Precendent Arrows and Remove Dependent Arrows.
Show Formulas - (Ctrl + '). Toggles the display of the formulas rather than the result.
Error Checking - Button with Drop-Down. The button displays the "Error Checking" dialog box. The drop-down contains the commands: Error Checking, Trace Error and Circular References (Auditing Toolbar). The Circular References extension will only be enabled when the active workbook contains at least one circular reference.
Evaluate Formula - Displays the "Evaluate Formula" dialog box. This allows you to step through a formula calculation. (Auditing Toolbar).
Watch Window - Displays the Watch Window.


What are the Advantages ?

Fewer Cells
Recalculation is a lot faster
Size of the workbook is reduced


What are the Disadvantages ?

Significantly harder (if not impossible) to understand and modify.


Auditing Complicated Worksheets

There may be times when you need to modify (or check) the formulas used in a workbook (or worksheet).
This job can be quite tedious at the best of times but to make it a bit easier it is possible to display both the formulas and the results at the same time.
To achieve this you need to open a separate window on the same workbook.
Start by opening up the workbook that contains your formulas.


 

Select (Window > New Window).
Select (Window > Arrange) and select Vertical.
In the left hand window press (Ctrl + ' ) or alternatively select (Tools > Options)(View tab, "Formulas").


 


Important

To prevent the misspelling of named ranges select the Name Box to insert them into your formulas.
The AutoCorrect feature will often eliminate some of the more common formula entry errors.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext