Troubleshooting Formula Errors

When you enter a formula incorrectly into a cell, Excel displays an error message. Formulas can be entered incorrectly for a variety of reasons. Typing mistakes are usually the biggest cause of formula errors. If you mistype an operator or function name, Excel returns an error instead of the desired result.

The next biggest reason that formulas return error values is improper syntax. Syntax is everything when you're entering formulas; your formula must conform exactly to Excel's format. For example, if your formula contains a blank space, the formula errors out.

Excel often provides information about the errors it finds in formulas. With Excel's help, you can usually fix the problem. If the formula looks okay but still isn't producing the results you expect, check it over. If you inadvertently reference the wrong cell or use the wrong function, such as SUM instead of AVERAGE, your answer will not be correct.


Garbage in, garbage out! If the data you're using for your calculations is bad, the results of the formulas will be wrong as well. Make sure that you're using current data to perform your calculation. Computing a foreign currency rate on last month's rate is probably a waste of time.

Table 5.3 shows some common formula errors with explanations.

Table 5.3. Common Formula Errors




The column is not wide enough to accommodate the data. Widen the column to see the entire cell contents by clicking on the column border and dragging it to increase the size of the column width. You can also double-click the column border to automatically size the column to exactly fit the data. The error disappears.


The formula is trying to divide a number by 0 or an empty cell.


The formula contains incorrectly spelled cell or function names.


The formula contains non-numeric data, or cell or function names that cannot be used in the formula.


The formula contains a reference to a cell that is invalid. Often, this means you deleted a referenced cell.

Circular reference

Results when one of the cells you are referencing in the formula is the cell in which you want the formula to appear. All the other items in this table show up as codes in the cells, while the circular reference error shows up as an error message.

    Part I: Excel Basics