Microsoft Excel XP/X
Troubleshooting Formulas
In order to troubleshoot your worksheet, you need to identify its problems. A few ways to accomplish this include:
Introduction to Troubleshooting
As you are reviewing your worksheets, asking yourself the following questions can help you identify problems within your worksheet and ensure the integrity and accuracy of your worksheets.
-
Compute some formulas by hand - do they equal the result that Excel got?
-
Are the numbers reasonable? Within the range of what you expected?
-
Verify the accuracy with source documents - did you transfer the information correctly?
-
Do your labels accurately describe the values within the worksheet?
-
When multiplying numbers by values with decimals, do the values that print out add up correctly? This is a common problem because of the rounding that Excel automatically performs.
-
When creating formulas that you are uncomfortable with, use values that are easy to test.
Tracking Down Circular References
Circular references occur when a formula refers to itself to determine the answer. For example, if the function =sum(b1:b10) was to be displayed in cell B10, we would have a circular reference because the result of the formula is required to determine the result. To correct the problem, the function should either be placed in a different cell, perhaps B11 or the range should be edited to stop at B9.
When you try to enter a formula that has a circular reference, a message dialog box appears, helping you avoid such references. The Formula Auditing and Circular Reference toolbars can also help you avoid circular references.
To access the Formula Auditing toolbar:
Windows:
- From the View menu, select Toolbars » Formula Auditing
The Formula Auditing toolbar appears.
Macintosh:
-
From the View menu, select Toolbars » Customize…
The Customize dialog box appears. -
Select the Toolbars tab
-
Select Auditing
There should be a check next to the selection.
The Auditing toolbar appears.
- Click OK
To access the Circular Reference toolbar:
-
From the View menu, select Toolbars » Customize…
The Customize dialog box appears. -
Select the Toolbars tab
-
Select Circular Reference
There should be a check next to the selection.
The Circular Reference toolbar appears. -
Windows: Click CLOSE
Macintosh: Click OK
Adjusting Circular References
-
On the Circular Reference toolbar, from the Navigate Circular Reference pull-down list, select the cell reference
This is where the circular reference is located. -
In the Formula bar, review the formula for the cell
-
Correct the formula until the status bar no longer reflects a circular reference
HINT: In the Circular Reference toolbar, you can click TRACE DEPENDENTS or TRACE PRECEDENTS to insert arrows to point out which cells are referenced in the formula.
