“Excel error messages aren’t always terribly clear to us,” says Melissa Esquibel, a Microsoft Certified Trainer who writes “The Office Tech Pro” blog. “We know we have a problem, but we don’t know where to go to start fixing it!”
For example, she says, here’s how to decipher three of the most common messages:
1. #REF is probably the most common error message in Excel. It means a cell reference in a formula or function cannot be found. For example, let’s say you had a workbook with three worksheets and a formula on the first one that used a value on the third one, then deleted the third one. Your formula would resolve to a #REF.
2. The #NAME? error is telling you about a symptom which may not clearly define the problem. For example, if you inadvertently delete either or both parentheses in a function like SUM, Excel thinks you are trying to find a named range of cells called SUMA1. So consider the parentheses missing from =SUM(A1:A6). =SUMA1:A6 would give you a #NAME? error.
“This error also happens if you are using a named range of cells and mistype or delete the range in your workbook,” Esquibel says. “However, if you are not using named ranges, before you delete the formula, look for the typo.”
3. #DIV/0! is probably the clearest message. “It is basically saying to you, in surprised shock, ‘You want to divide by ZERO?!?,’” she says. Sometimes it’s not obvious that you are dividing by 0 when you look at your formula.
Try using Trace Precedents. In Excel 2003, you will find the icon on your Formula Auditing tool bar. In Excel 2007, it is on the Formulas Tab in the Formula Auditing Group. It allows you to see all the cells your formulas are using to come up with its answer. It may be necessary to use Trace Precedents back a few levels to find your zero.