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.
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.
Select the cell with the #NAME? error.
Click the Formulas tab.
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.
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.
Close the dialog box.
The #NAME? error in the cell is replaced with the corrected formula.
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.
Select the cell with the #VALUE! error.
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.
Locate the cell that’s causing the error.
Correct the formula in the formula bar.
Click or press Enter.
The formula updates to display the correct result and the #VALUE! error disappears.