The “Formula Auditing” (“Formula Auditing”) toolbar

Excel provides us with a useful tool for checking formulas namely, the “Formula Auditing” toolbar in Excel ’97 and 2000 and it is simply called the “Auditing toolbar”.

If you are still using Excel 97 or Excel 2000 you will see this toolbar is not the same as the other toolbars.
In Excel ’97 and 2000, this is the only way to the “Auditing toolbar”.
Choose “Tools” from the menu bar, select “Auditing” – “Show Auditing Toolbar”.

When you work with Excel XP or 2003 then we can open the toolbar by clicking “Tools” from the menu bar – “Formula Auditing” – “Show Formula Auditing Toolbar” or by clicking on “View” | – “Toolbars” – “Formula Auditing” from the menu bar.

A word of explanation about the different buttons:

	debug and improve (XP and 2003)			“Error Detection”
	“Trace Precedents”			new comment
	“Delete source cells arrows”			show a red circle around cells that contain invalid data
	“Dependents”			hide red circle around cells that contain invalid data
	“Delete target cells arrows”			Watch Window (see cells and their formulas)
	“Delete arrows”			formula to evaluate

The finding of source cells and target cells

We can select each formula and each cell that is used in a formula, and find its source or target cells.

When we do this Excel will display a blue line, from the target cell to the source cells, or from the source cells to the target cells, depending on which button you clicked in the toolbar.

It is useful to know from which cells the “Formula” gets its information.

To know the source cell we need to first know the “Formula” cell, and for that we click the “Trace Precedents” button.

In some cases, the source cells are also “Formula” cells, we can find the actual source cells by repeatedly clicking on the “Trace Precedents” button:

Conversely, too.

Select a cell and click the “Dependents” button.

Here the blue arrows identify the target cells:

To delete all arrows we click the “Delete arrows” button.

To remove them one by one we click “Delete source cells arrows” or “Delete target cells arrows” .

We can also link data in another “Worksheet” or another “Workbook”.

Select the linked cell, and click the “Trace Precedents” button.

A black dotted line with a worksheet icon.

When we click the arrow, the dialog box “Go To” appears, in which we find a list of all linked cells.

Choose one and click OK.

The linked cell in another “Worksheet” is selected.

Debugging

We can detect errors in the “Formulas” of source cells.

Click the cell containing the “Formula” with the error.

Click on the “Error Detection” button..

Excel shows an arrow pointing the cause of the error.

Please correct the error in the “Formula bar”.

Checking and fixing errors (only 2003 and Xp)

We can debug and improve our “Formulas” by clicking on this button .

The dialog box appears with the first error in our “Worksheet”:

Clicking the “Help on this error” button opens the “Windows Help” window on the corresponding error.
Close the help window.

We click on the “Resume” button to continue.
Then we click on the “Show Calculation Steps …” button.

In the dialog that appears, we click the “Evaluate” button, to evaluate the underlying references.

If we have nested “Functions” in our “Formula”, we can look into the steps in the calculations carried out by our “Formula”, until we find the error.

You could get the “Restart” button to restart all calculations.

When the underlying model, which is evaluated, refers to another “Formula”, you can click on the “Step In” button to view and evaluate the “Function” in that window.

Click the “Step Out” button to return to the original “Formula.”

When you have finished evaluating your “Formula”, click “Close” to return to the “Error Checking” dialog box.

We can also click the “Ignore Error” button in order to ignore the error.

But once we do that, we can not go back and run the “Error Checking” dialog.

You can compare this with a spellcheck where once you ignore a mistake, it will always be ignored.

We can undo this by clicking on the “Options” button, and clicking on the “Reset Ignored Errors” button.

We can use the “Edit in Formula Bar” button to edit the “Formula” in the “Formula bar” where we may solve the problem.

Once we think we have solved the problem, we click the green arrow on the left side of the “Formula bar”.

Click the “Resume” button to check for more errors.

When all errors are corrected, we click on OK in the popup window.

Use “Watch Window”.

Checking the values of cells can give you some headaches if you work with large “Worksheets”.

Excel has developed a feature to make these things much easier.

The “Watch Window” displays the value of any cell in a window.

Click on the “Watch Window” button..

Click “Add Watch”

And select the cells whose values you want to show.
And click “Add”.

You can check the change in the value of the selected cell now, while you continue to work on another part of the “Spreadsheet”.
You can also click a cell in the “Watch Window” by double-clicking to jump to that cell.

To stop watching for details for a cell, we choose the cell (s) and click on “Delete Watch”.

Cell validation

Excel allows you to verify that the correct type of information is entered in a particular cell.
We can take the information as numbers, a date or a list of limit values.

Here we give an example where the information we want to enter should limit itself to an integer between 1 and 12.

Click “Data” in the toolbar and select “Validation”.

The dialog for “Data Validation” opens.

In the first tab, “Settings”, we give in what we allow.

For us that is, an integer between minimum 1 and maximum 12.

We click on the “Input Message” tab:

We give a title and a message.

If we check the “Show input message when cell is selected”, we get the following message in our “Worksheet”, when the cell is selected:

We click on the “Error Alert” tab.

We choose an image that we want to display in the “Style” dropdown box.

Type a title and an error message.

When we enter information in the cell that does not meet our conditions, we get the following popup window.

If you click Yes, then Excel accepts your information, if you click No, you ‘ll still try.

Now, in the list of values to be given in the “Data Validation” dialog box, you can also select from a “Range” of cells on your worksheet.

Or you can make a list of all options to give, separated by a comma.

the result:

In order to make changes, we select the cell, and click ” Data” in the toolbar and select “Validation”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats