Excel Formula Error

Sometimes Excel comes across a formula that it cannot calculate. When this happens, it displays an error value. Error values occur because of incorrectly written formulas, referencing cells or data that don’t exist, or breaking the fundamental laws of mathematics.

#### Error

The #### error occurs when the column isn't wide enough to fit the cell data.

1. Double-click the line to the right of the column letter for the column containing the error.
   

The column width automatically resizes to fit the widest string of text in the column, thus fixing the error.

To resize all columns in the sheet at once, click the Select All button in the upper-left of the worksheet before resizing a column’s width.

NAME Error

You'll see the #NAME? error when the text in the formula isn't recognized. Sometimes it's easy to figure out the error, but other times you'll need help to determine what’s happening. For this example, you’ll use Excel’s Error Checking feature to help fix the problem.

1. Select the cell with the #NAME? error.
2. Click the Formulas tab.
3. Click the Error Checking button.
   

   The Error Checking dialog box opens. The left side of the dialog box shows the formula that’s causing the error and gives a description of what’s happening.

4. Select an error checking option at the right and fix the error.
• Help on This Error: Displays information specific to the error type.
• Show Calculation Steps: Demonstrates all steps leading to the error.
• Ignore Error: Allows you to accept the formula as entered, without Excel displaying the Error Checking Options smart tag.
• Edit in Formula Bar: Allows you to edit the formula that is generating the error in the Formula Bar.
5. Close the dialog box.
   

The #NAME? error in the cell is replaced with the corrected formula.

VALUE! Error

The #VALUE! error tells you there’s something wrong with the cells you’re referencing or with the way the formula is typed. This is a very general error and it can be tricky to pinpoint the cause of it. This example uses the Trace Precedents feature to help fix the error.

1. Select the cell with the #VALUE! error.
2. Click the Trace Precedents button on the Formulas tab.
   

   Trace Precedents shows dots that indicate which cells affect the value of the currently selected cell. This helps to visually locate the error.

3. Locate the cell that’s causing the error.
4. Correct the formula in the formula bar.
5. Click or press Enter.
   

The formula updates to display the correct result and the #VALUE! error disappears.

DIV/0! Error

You will see the #DIV/0! Error any time a number is divided by zero. This includes typing “/0” in a formula or referencing a cell to divide by that contains 0 or is blank.

1. Select the cell with the error.
2. Click in the formula bar and fix the error.
3. Click or press Enter.
   

The cell updates to the correct result and the #DIV/0! Error is fixed.

REF! Error

You will get the #REF! error when a formula references a cell that’s not valid. This often happens when referenced cells get deleted or pasted over.

1. Select the cell with the #REF! error.
2. Click in the formula bar and fix the error.
3. Click or press Enter.
   

The cell reference is now valid and the #REF! error no longer displays.