Table of Contents### Working with Formula Errors

Entire Site

When you enter an incorrect value, reference, function, or operator in a formula, Excel displays an error value in the cell. Error values always begin with a pound sign (#). To help you figure out what's causing an error, choose Tools, Auditing, Trace Error. In many cases, a simple oversight, such as typing over part of the formula or using an invalid cell reference, is all that's causing the error. You'll learn more about finding error sources in Chapter 19, "Auditing and Validating Worksheets." In the meantime, Table 3.7 lists the various types of formula errors and possible solutions.

Error Message | Problem and Solution |
---|---|

#DIV/0! | The formula is attempting to divide by zero. Make sure you have not referenced any blank cells or ranges, or deleted a value needed in the formula. |

#N/A | A cell has a #N/A entry, or the array is the wrong size, or you've left out a function argument. Recheck the formula, function, and cell references. |

#NAME? | Excel doesn't recognize the name. Make sure you used the correct name and defined it properly. You might be referencing a nonexistent name. |

#NULL! | The formula references two areas that are not intersecting. Double-check the cell or range references. |

#NUM! | The formula is having a problem with the number entered. Make sure you haven't deleted cells used in the formula, or recheck the function arguments you entered. |

#REF! | The formula is having a problem with the cell or range reference. Recheck the references. |

#VALUE! | The formula is having a problem with the value not being the type expected in the argument or intersect operation. Recheck all values used in the formula. |