Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


EXCEL

Auditing

In a large or complicated spreadsheet it is sometimes hard to remember which cells are linked to which in the formulas.  If you get an error in a cell it may be difficult to trace where that error has come from.  Excel’s auditing tools can help display the links between cells so you can easily update or change formulas.

 To start auditing your spreadsheet, click on the Tools drop-down menu and choose Auditing and then Show Auditing Toolbar.  The toolbar appears as shown below.

In the spreadsheet shown below we have used the Trace Error icon from the Auditing Toolbar to find the cause of the error in the total cell which says #DIV/0!  By clicking the Trace Error icon twice the arrows have gone back two steps from the source of this value, further clicks would reveal other links between the cells involved in the calculations in the spreadsheet.  When you have finished you can click on the icon on the toolbar to Remove All Arrows and the blue arrows will disappear.

If data validation has been set on a cell then the Auditing Toolbar can be used to circle any invalid data .  The example below shows an invalid rate of VAT being circled by the Auditing Toolbar.

You can work on the spreadsheet while the auditing arrows are displayed and if you correct an error or invalid data the error arrows or validation circles will disappear.  When you have finished you can click on the Remove All Arrows icon to remove all the arrows or the Clear Validation Circles icon to remove the circles around errors and then close the Auditing Toolbar.

 

 

 

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk