In this and next week’s article we’ll examine 6 common errors in Excel and how they show up, including 2 error messages that may not be errors at all!
This week, we’re looking at #REF, #NAME? and #DIV/0!
Sign up for Melissa's Excel training class and the first 50 registrants will receive a complimentary copy of our all-time best seller, 59 Technology Tips. Register here...#REF
#REF is probably the most common error message we see in Excel. It means that there is a cell reference in a formula or function that 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.
The #NAME? error is telling you about a symptom which may not clearly define the problem. For example, if you inadvertently deleted 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. However, if you are not using named ranges, before you delete the formula, look for the typo!
The divide by zero error is probably the most clear. It is basically saying to you, in surprised shock, “You want to divide by ZERO?!?” 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 will allow 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.
Come back next week for our discussion of #N/A, FALSE and circular cell references. As always, if you have questions about these tips and tricks, please post them, and I’ll get you answers. By the way, save the date, 4/8/2010! Business Management Daily will present How to Excel at Excel with yours truly at the helm.
By the way, save the date, 4/8/2010! Business Management Daily will present Microsoft Excel: Power Tools for Everyone with yours truly at the helm. You'll learn:
- Keystroke-saving methods for creating and copying formulas.
- How to create a multi-worksheet workbook with identical worksheets and a summary page with formulas in under 5 minutes!
- How to trace and track formulas work throughout the workbook without straining your eyes, your brain or your patience.
- How to use one of the most powerful functions in Excel: the IF statement.
- AND MORE!