Most of us understand Excel’s error messages about as much as lights on a car’s instrument panel. We know the “check engine” light is bad, but without visiting a mechanic, we really don’t know what the light is trying to tell us. When Excel delivers a similarly ominous but inscrutable error message, you aren’t doomed. Use the following tips to figure out what the messages mean.
Ask For Help
Asking Excel itself for help may sound like an obvious solution, but many people don’t take this basic first step. The built-in Help feature provides surprising insight regarding the general language of error messages, as well as the meaning of specific problem alerts. Even pros rely on this avenue; we’ve seen professional software trainers open Help in class when students stump them with a question. Start by opening the Help feature—click the question mark in the far right corner of the Office Ribbon—and typing in the error message you’ve received. (Excel’s automatic Error Checking feature, which reviews every new formula for problems and flags trouble spots, can connect you to the same information. Click the flagged cell, the warning sign, and Help On This Error.)
If a cell produces a #REF! error, for example, enter that string of characters into the Help window’s Search box and press ENTER. Sometimes, such a search isn’t much help. Typing “#####” into the search box, for example, produces an empty search.
A Help page addressing the error message will explain why the error occurs, such as filling you in about how a “#VALUE!” error stems from using the wrong kind of argument or operand. OK, maybe that’s not the clearest explanation. But keep reading the Help page to find a list of possible solutions. One of these usually matches what you did wrong, such as the explanation that the “#VALUE!” error can crop up when you enter text in a spot where Excel looks for a number.
For a complete list of error message explanations, enter error messages into Help’s search box and press ENTER. For quick reference, here are seven errors that commonly crop up and their typical causes:
#N/A – A formula refers to a value it can’t find. The formula is probably pointing to an empty cell.
#DIV/0! – A formula is trying to divide a number by zero.
#NUM! – Excel sees one of a formula’s arguments as invalid, such as “$500” when it can only accept “500.”
#REF! – A formula refers to a missing cell.
#NAME? – Text is used incorrectly in a formula. This could stem from referring to a named range that doesn’t exist or using improper punctuation around a text string or workbook name included in a formula.
##### – The cell is not wide enough to contain the contents designated for it.
#NULL – A formula includes an improper reference to a range of cells.
Create Custom Error Reports
Excel provides a few functions that let you handle errors your way. To customize the performance of the automatic formula checking feature, click the Office button and choose Excel Options. On the left side of the dialog box, click Formulas. The Error Checking section lets you turn the feature on or off, change the flag color, and reset the list of ignored errors. (For a full explanation of formula error checking, see the March 2007 Excel Quick Study.)
The IFERROR function lets you create a plain-English message that appears when errors occur. You can set up the function to evaluate specific cells, and if their formulas cause an error, show a text message that you’ve created such as “Error in formula. Please check the values.” For more powerful custom error messages, look into the ERROR.TYPE function. It lets you tie specific text messages to specific errors, such as displaying “You cannot divide a number by zero” when a #DIV/0! error occurs.
One final tip on tracking down errors: You may find it easier to view cells that contain actual formulas rather than the results they produce. To see formulas, click the Show Formulas button on the Formulas tab.