Excel Formula Errors

Unfortunately, not everything goes right the first time. Excel will sometimes give you an error saying that something isn't right. This chapter teaches you how to deal with the most common formula errors.

Excel displays the ##### error when the column isn't wide enough to display the value, the #NAME? error occurs when Excel does not recognize text in a formula, the #VALUE! error when a formula has the wrong type of argument, the #DIV/0! error when a formula tries to divide a number by 0 (or by an empty cell), and the #REF! error when a formula refers to a cell that is not valid.

##### error

When your cell contains this error code, the column isn't wide enough to display the value.

Example:

Fix the error:

1. Click on the right border of the column A header and increase the column width.

Result:

Tip: to change the column width to automatically fit the width of the widest cell in column A, double click the right border of the column A header.

#NAME? error

This error occurs when Excel does not recognize text in a formula.

Example:

Excel does not recognize the function name SM.

Fix the error:

1. Simply correct SM to SUM.

Note: Excel also gives the #NAME? error when you misspell a named range.

#VALUE! error

Excel displays the #VALUE! error when a formula has the wrong type of argument.

Example:

The formula =A1+A2+A3 contains an argument that is a text string instead of a number.

Fix the error:

1. One simply way to fix this error is by changing the value of cell A3 to a number. Another way to fix this error is by replacing the formula A1+A2+A3, with =SUM(A1:A3). By using a function (and not using arithmetic operators such as +,-,*,/), cells that contain text are ignored and no error code is displayed.

#DIV/0! error

Excel displays the #DIV/0! error when a formula tries to divide a number by 0, or by an empty cell.

Example:

Fix the error:

1. One simply way to fix this error is by changing the value of cell A2 to a value that is not equal to 0. Another way to fix this error is to prevent the error from being displayed by using the logical function IF.

Explanation: if cell A2 equals 0, an empty string is displayed. In all other cases the result of the formula A1/A2 is displayed.

#REF! error

When a cell contains a #REF! error, the formula refers to a cell that is not valid. In most cases this happens because you deleted cells that were referenced by other formulas. Confused? Let's take a look at the following example.

1. Cell C1 references cell A1 and cell B1.

2. Now delete column B.

The reference to cell B1 is not valid anymore.

Fix the error:

3. You can either delete +#REF! in the formula of cell B1 or you can undo your action by clicking on Undo in the Quick Access Toolbar (or press CTRL +Z).

Did you like this free Excel tutorial? Show your appreciation, vote for us.

Thank you very much! We've got many more free Excel tutorials for you. Read on, learn, and enjoy!

Go to Top: Excel Formula Errors    |    Go to Next Tutorial: Round Excel Numbers