Table of Contents### Task 18 How to Fix Formula Errors

Entire Site

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).

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.

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.

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.