Click a cell with a #VALUE! message and review the cell's formula in the Formula bar. Can you find the error?
To add the values C2 through C12 and divide by 10, the formula must include "SUM" in the function. Type SUM in the Formula Bar and press Enter.
Click a cell with a #NAME? message. Move the mouse pointer over the cell to review a ScreenTip about the error.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions. #VALUE? means the formula in the cell contains either nonnumeric data or cell/function names that cannot be used in the calculation. #NAME? means the formula contains incorrectly spelled cell/function names. #REF! indicates that the formula contains a reference to a cell that isn't valid. #### means the column is not wide enough to display the data. #DIV/0! means that the formula is trying to divide a number by 0 or that the formula is referencing an empty cell.
TIP
Tracing Errors
Check formulas by tracing precedents (all cells that are referenced [in order] in the formula). You can also trace dependents (start with a cell that is referenced in a formula, and then trace all the cells that reference that cell).
In this example, avg is not the correct name of the desired function; the correct name is average. In the Formula bar, replace avg with average and press Enter.
If the status bar displays a Circular reference error (for example, Circular: D15), double-click the cell it references.
In this case, the formula is referencing its own cell. Type the correct formula in the Formula bar (in this example, =SUM(D3:D12)/10) and press Enter.
If a cell displays #####, the column isn't wide enough to display all the cell's data. Click and drag the column border to make it wider, and the error is gone.
TIP
Performing a Trace
Click the cell with the error, select Tools, Formula Auditing, and select either Trace Precedents or Trace Dependents.
TIP
Circular References
The formula for cell E15 in this task is the average for fourth-quarter sales for the 10 regions, displayed as follows: =E14/10. If you instead wrote this formula as =E3:E12/10, it would still be correct (E14 just happens to be the total for the regions). But, if you accidentally wrote =E3:E15/10, including the cell in which you want the answer, there would be a circular reference. You cannot produce an answer for cell E15 while including it in the calculation; the answer would go round and round, continuously changing hence the name circular reference.
