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

Part 9. Working with Data and Charts > Correcting Formula and Function Errors

Correcting Formula and Function Errors

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.


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.


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.



Not a subscriber?

Start A Free Trial

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