Mastering Formula Errors in 3 minutes

Excel 2016

Formulas in Excel can be tricky. A small mistake can result in a formula displaying the wrong result, or breaking entirely. In this week's lesson, we'll explore some common Excel formula errors, how to spot them, and how to fix them.

Formula Errors

When Excel comes across a formula that it cannot calculate, it displays an error. Errors occur because of incorrectly written formulas, referencing cells that don’t exist, or breaking mathematic fundamentals.

#### Error

If a cell contains multiple pound signs, it means the column isn’t wide enough to fit the cell data.

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

    The column automatically adjusts to fit all the data.

#NAME? Error

The #NAME? error appears when Excel doesn’t recognize the text entered in a formula. When you can’t figure out how to fix an error, get help with error checking.

  1. Select the cell with the error.

  2. Click the Formulas tab.

  3. Click Error Checking.

    The dialog box tells you what’s going on with the error.

  4. Click Edit in Formula Bar.

    Tip: You can also get help, have Excel show the calculation steps, or just ignore the error from this dialog box.

  5. Enter the correct formula.

  6. Click or press Enter.

  7. Close the dialog box when you’re done.

    Tip: Error checking can also be used on other errors, if you can’t remember what they mean.

#VALUE! Error

The #VALUE! error appears when a mathematical formula references a text entry instead of a numerical entry. If you don’t know which cells are part of a formula, use Trace Precedents to display arrows that show which cells affect the selected cell.

  1. Select the cell with the error.

  2. Click the Formulas tab.

  3. Click Trace Precedents.

    Arrows appear to show which cells are part of the formula.

  1. Locate the cell that’s causing the error.

  2. Correct the formula in the formula bar.

  3. Click or press Enter.

    Tip: Similar to Trace Precedents, Trace Dependents displays arrows that point to cells that are affected by the currently selected cell.

#DIV/0 Error

The #DIV/0 error appears when a formula attempts to divide a number by zero. This error is pretty common because it shows up whenever a formula refers to a blank cell as a divisor.

  1. Select the cell with the error.

  2. Click in the formula bar and fix the error.

  3. Click or press Enter.

#REF! Error

The #REF! error appears when a cell reference isn’t valid. You’ll see this error frequently, whenever a cell range that was referenced in a formula is deleted.

  1. Select the cell with the error.

  2. Click in the formula bar and fix the error.

  3. Click or press Enter.