SubscribeExcel Formula Errors – Understand and Debug Them
“Trial and Error” has to be the most effective ways to learn something new. Most of the stuff I learned in my life is through that. Be it insurance or excel or programming. My learning has always gone up when I make a mistake. I am sure most you agree with me…
So in this installment of spreadcheats we will learn about errors. Those annoying #SOMETHING!s that you see when your excel formulas have something wrong with them.
#DIV/0! Formula Error
This is the easiest of all. When you divide something with 0, you see this error. For eg. a cell with the formula =23/0 would return in this error.
How to fix #DIV/0 error?
Simple, do not divide the value by Zero. You know the answer anyway.
#NAME? Formula Error
The most common reason why you see this error is because you misspelled a formula or named range. For eg. if you write =summa(a1:a10) in a cell, it would return #NAME? error. There are few other reasons why this can happen. If you forget to close a text in double quotes or omit the range operator :. All these examples should return #NAME? error. =sum(range1, UNDEFIED_RANGE_NAME), =sum(a1a10)
How to fix #NAME? Error?
- Make sure you have mentioned the correct formula name. If you are using excel 2007, when you are typing the formula excel shows all the matched formulas. In earlier versions of excel, if you use correct formulas, they will be automatically capitalized. For eg. if you type =sum(1,2,3) in a cell and press enter, it will be changed to =SUM(1,2,3). You can use this feedback to correct formulas.
- Make sure you have defined all the named ranges you are using in the formula.
- Make sure any user defined functions you are using are properly installed.
- Double check the ranges and string parameters in your formulas.
#N/A Formula Error
This is one of the frequent errors you see while using vlookup formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the lookup functions (vlookup, hlookup etc.) of if the list is not sorted and you are trying to lookup using sort option. You can also generate a #N/A error by writing =NA() in a cell.
How to fix #N/A error?
Make sure you wrap the lookup functions with some error handling mechanism. For eg. if you are not sure the value you are looking is available, you can write something like =if(iserror(vlookup(…)),”not found”,vlookup(…)). This will print “not found” whenever the vlookup returns any error (including #N/A)
#NULL! Formula Error
This is rare error. When you use incorrect range operators often you get this error. For eg. the formula =SUM(D30:D32 C31:C33) returns a #NULL! error because there is no seperator between range 1 and range2.
How to fix the #NULL! error?
Make sure you have mentioned the ranges properly.
#NUM! Formula Error
This is number error that you see when your formula returns a value bigger than what excel can represent. You will also get this error if you are using iterative functions like IRR and the function cannot find any result. For eg. the formula =4389^7E+37 returns a #NUM! error.
How to fix #NUM! error?
Simple, make your numbers smaller or provide right starting values to your iterative formulas.
#REF! Formula Error
This is one of the most common error messages you see when you fiddle with a worksheet full of formulas. You get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because you deleted the cells. For eg. try to write a sum forumla like =SUM(A1:A10, B1:B10, C1:C10) and then delete the column C. Immediately the sum formula returns #REF! error.
How to fix the #REF! error?
First press ctrl+Z and undo the actions you have performed. And then rethink if there is a better way to write the formula or perform the action (deleting cells).
#VALUE! Formula Error
Value error is shown when you use text parameters to a function that accepts numbers. For eg. the formula =SUM(”ab”,”cd”) returns #VALUE! error.
How to fix the #VALUE! error?
Make sure your formula parameters have correct data types. If you are using functions that work on numbers (like sum, sumproduct etc.) then the parameters should be numbers.
###### Error
You see a cell full of # symbols when the contents cannot fit in the cell. For eg. a long number like 2339432094394 entered in a small cell will show ####s. Also, you see the ###### when you format negative numbers as dates.
How to fix the ###### error?
Simple, adjust the column width. And if the error is due to negative dates, make them positive.
Further Reading on Excel Formula Debugging
Formula Debugging using F9 Key
Learn to work with Circular References – Tutorial on Generating Timestamps
Understand the difference between absolute and relative references
Tell me how you debug formulas? What is the most common error you get?
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
Trackbacks & Pingbacks
- Pingback by Most Popular Posts in PHD - The March and April edition | blogging | Pointy Haired Dilbert - Chandoo.org on May 6, 2009 @ 3:59 am
- Pingback by Debug Excel Formula Errors | Reality Upgrade on May 17, 2009 @ 9:45 pm
- Pingback by Hide Formula Errors While Printing [quick tip] | Excel Howtos | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 27, 2009 @ 5:01 pm
- Pingback by Best of Pointy Haired Dilbert – 2009 | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on December 30, 2009 @ 2:53 pm
- Pingback by 31 Excel Tutorials – Learn and Be Awesome in Excel | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on March 11, 2010 @ 1:01 pm
Comments
RSS feed for comments on this post. TrackBack URI
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. 
Good post. But I think you could improve the #DIV/0 section by explaining how to avoid this error. Simply not dividing by zero, is not always that simple.
One answer is to use error handling similar to the ones you describe in other sections.
For example
=if(b1=0,0,a1/b1)
@Gerald: Agree with you. Error handling is a good suggestion.
A few months back Jimmy Penna hosted a VBA Code competition and the winning post (by myself) featured a formula error check routine. It checks all formulas on the active sheet for known errors as featured in your post. Helpful if you have thousand of rows of formulas to check. You can get the routine from:
http://www.codeforexcelandoutlook.com/blog/2008/12/code-contest-submissions-and-voting-information/
Hi Chandoo,
Very interesting post. I think it should be included in the basis teaching of Excel. Lots of my colleague have question when they received an error message because they do not understant the nature of the error. This post will definitively help.
One small remark however is that I would not recommend using the ISERROR function (in the #N/A explanation) but preferably the ISNA. The ISERROR can hide another type of error (misspell of the function for example).
@Tim: Good suggestion. I have seen your code at that time. Thanks for providing the link here.
@Stephane: I agree with you about the iserror() part. I end up using iserror over isna as most of the time NA is the only error I get when I use vlookup. But it is a good practice to catch exceptions at individual error level than at one global level like iserror().
normal I use the Iserror() for vlookup() formula.
thank your tips.
Hi Chandoo.
I’ve understand that when following the IF(ISNA(VLOOKUP…),”Error”,VLOOKUP…) method it can be a bit inefficient in large spreadsheets.
If the first VLOOKUP does NOT return an error, you then force Excel to repeat the VLOOKUP.
If you do the VLOOKUP just once in one cell, then do the IF(ISNA(… in the next cell and point to the cell containing the result of the VLOOKUP, then you only ever do the VLOOKUP once.
I tend to do it the ‘all in one’ way for my smaller spreadsheets. What I don’t know is at what point the spreadsheet becomes so big that it is worth doing it the two-step way? 1000 rows? 10,000 rows? 100,000 rows? Any thoughts?