• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Part 3. Using Excel > Task 18 How to Fix Formula Errors

Task 18 How to Fix Formula Errors

Not all formulas are perfect, and when they aren't, Excel lets you know by displaying a green triangle in the upper-left corner of the formula's cell, along with an error message. For example, you might have entered an incorrect value or operator, the wrong cell reference, or function. The first thing to do when you see an error value is to recheck the formula and references used. For complex worksheets, it isn't always easy to see whether your formulas and data references are correct. Use Excel's auditing tools to help you find your mistakes. You can display tracer lines that locate precedents (cell references referred to in a formula) and dependents (cell references that are referenced in another cell, such as those used in a formula).

  1. Display Formula Auditing Toolbar

    When errors appear, one of your most powerful allies in correcting them is the Formula Auditing toolbar. Select Tools, Formula Auditing, Show Formula Auditing Toolbar to display it.

  2. Review Errors

    To check each cell marked with an error triangle, click the Error Checking button in the Formula Auditing toolbar. Excel highlights the first cell with an error and provides you with options for fixing it.

  3. Correct Error

    Here, Excel has noticed that one cell uses a formula that's vastly different from the formulas in surrounding cells. This might or might not be an error—if you want, you can tell Excel to Ignore Error. However, this is a mistake, so click Copy Formula from Above. You can change the formula yourself by clicking Edit in Formula Bar.

  4. Correct Additional Errors

    Excel highlights the next cell with an error. Select the option you want from the Error Checking dialog box; continue until all errors have been addressed. When it's through checking errors, Excel displays a message box; click OK.

  5. Trace Precedents

    Not all mistakes result in an error. If something doesn't look right, such as the average sales total shown here, you can trace the values used in the formula by displaying its precedents. Click the cell you want to evaluate and then click the Trace Precedents button. If necessary, click the Trace Precedents button again to trace the values for these cells.

  6. View the Trace

    Excel displays trace arrows that point out the sources of the formula to help you track down the error. Here, I accidentally included the totals in column G in the ranges I used in the AVERAGE function! After locating your error, make the necessary corrections and turn off the trace arrows by clicking Remove All Arrows.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint