Tag Archives: Formula Error Messages

Excel Formula Problems? Try These Troubleshooting Tips!

Ribbon with FBWhen you work with formulas in Microsoft Excel, there are sometimes some glitches. Excel is good about letting you know when there is an error and even offering some help. The Error Checking feature on the Formulas tab in the Formula Auditing Group will find errors such as Divide by zero errors or errors in cell references (#VALUE!) where a cell referenced in the formula does not contain a number.

Also in the Formula Auditing group are the Trace Precedents, Trace Dependents, and Remove Arrows buttons. The Trace Precedents button shows arrows that indicate which cells affect the value of the currently selected cell. The Trace Dependents button show arrows that indicate which cells are affected by the value of the currently selected cell. The Remove Arrows button removes the arrows drawn by the Trace Precedents and Trace Dependent buttons. These buttons are very helpful when determining incorrect cell references in formulas.

However, with especially long formulas, it is sometimes necessary to examine small parts of the formula at a time to figure out the problem. You may use your mouse pointer to look at only a certain part of a formula and press F9 to evaluate just that section. Press Esc to go back and keep searching. The Evaluate Formula button on the Formula Auditing group will also allow you to examine small parts of a long formula.

Another common problem in Excel formulas is a circular reference. A circular reference in an Excel formula is one that depends on its own value. The most common type of circular reference occurs when you mistakenly refer in the formula to the cell in which you are building the formula itself. For example, suppose that cell B10 is active when you build this formula,  =A10+B10.   As soon as you enter this formula in cell B10 (assuming the program is in Automatic recalculation mode), Excel displays an Alert dialog box, stating that it cannot calculate the formula due to the circular reference.

Do not forget, too, that by clicking the down arrow at the right end of the formula bar you can expand the size of the formula bar to include more than one line. Use these tips to become an expert in troubleshooting Excel formula problems—something that we all encounter when using Excel’s powerful calculation features!

____________________________________________________________________________________________________
For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.