Excel Formula Errors – Understand and Debug Them

Posted on April 20th, 2009 in All Time Hits , Excel Howtos , Featured , Learn Excel - 29 comments

Excel Formula Errors - Understanding and Fixing 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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

29 Responses to “Excel Formula Errors – Understand and Debug Them”

  1. Gerald Higgins says:

    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)

  2. Tim Buckingham says:

    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/

  3. Stéphane Nolf says:

    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).

  4. Chandoo says:

    @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().

  5. [...] those of us who are not Excel Gurus, there is this nifty list of common formula errors that can help keep you from throwing your shoe at your new LCD [...]

  6. hwsris says:

    normal I use the Iserror() for vlookup() formula.

    thank your tips.

  7. [...] to select how you want to handle the formula errors when the worksheet is printed (More on errors: understand and fix excel formula errors). You can set it to [...]

  8. Alasdair says:

    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?

  9. [...] is also featured on Lifehacker for the Excel Formulas Errors – How to handle them? [...]

  10. [...] How to understand and handle Excel formula errors2. Excel formulas not working? What to do?3. Handling errors and 5 other tips for writing better [...]

  11. stan says:

    Hi Chandoo. When interfacing to excel from c#, we’d like to detect and log #REF! errors as our program visits each cell to harvest formulas. Do you know how we can detect that a visited cell has a #REF! issue?

  12. Clare says:

    Hi I’m having problems using excel. I have a spreadsheet and the first worksheet sets up fine, with all the formulas adding, multiplying, and updating if I change one of the fields. I then copied the it to make a second worksheet. Now on all the pages, although the formulas are in the forula bar, they do not automatically update. Meaning if I change the quantity of one item, asking it to multiply by the price of a single unit I then have to click onto the formula and press enter before it applies the formula. Obviously this somewhat defies the point of an automated system. If you could help I would be so grateful! I have spent ages setting up this sytem thinking it would be more efficient in the long run and I’ve run out of ideas! Thanks Clare

  13. bilal says:

    how to fix #VALUE! Formula Error?
    i typed in =A9*E2 and i still get #value

  14. Li Bunger says:

    I found this post interesting and was happy to see my particular error – #VALUE! -listed but the fix is unclear.  What I am trying to do is create a timesheet with 4 columns of in/out times and then a formula that will compute the total hours worked.  I’ve gotten a formula that will work, but unless there are values in the in/out columns, I get the error message until I put values in all four columns (2 in and 2 out times columns).  Since some of our employees are part-time, they would probably only use one in/out section.  The in/out columns are formatted for Time (13:30 PM) and the formula column is formatted as Custom “hh.mm”

  15. Karl Drexel says:

    I Googled an error in Excel and got to this page, but can’t find the answer. When I enter a formula, the result displays zeros. When I click on fx the proper formula and result shows up, but it still displays zeros on the spreadsheet. Where can I find the fix for this? Thanks.

  16. Thq@01 says:

    _”this site was very helpful…

    _”thank you.

  17. [...] can validate excel workbook for the #REF, #VALUE, #DIV/0, #N/A, #NULL, #NUM and #NAME errors. I understand and we know that it is tidious and ridiculous for you to look into every cell of [...]

  18. Theophilus Takyi says:

    How many erros can be found in microsoft excel?

  19. Jeevan S says:

    Hiya,
     
    When I copy the cells from an excel sheet downloaded from the Internet into a local Excel sheet (Which has formulas).
    The formluas dont work. Is there a remedy for this.
     
    Thanks,
     
    JS

    • Hui... says:

      @JS
      When you download a file from the internet Excel should prompt you to allow Edit, You must accept this if your trust the file
       
      Next ensure calculation is set to Automatic.
      Goto the Data, Calculations tab and select Automatic.
      Press F9 to recalculate
       
       

  20. Jacque says:

    All your articles are BEYOND awesome! They’re so helpful and creative, and they’ve definitely made my work life more efficient.

    May I suggest a topic you could possibly write about in the future: how to use Excel functions/formulas to minimize or check for data errors when processing/analyzing huge amounts of data.

    Thanks!

  21. [...] can validate excel workbook for the #REF, #VALUE, #DIV/0, #N/A, #NULL, #NUM and #NAME errors. I understand and we know that it is tidious and ridiculous for you to look into every cell of [...]

  22. richard says:

    When saving an excel sheet in formula view and then reopening the saved doc to show the formula view…. the excel sheet shows in normal view? I have repeated the process several times as i need to show the work book in formula view when it opens>>>> any thoughts

  23. Swati says:

    When I divide 2 numbers in a particular cell , then apart from said division, excel was automatically redividing the numerator and denominator by another no., showing a smaller resultant.

    How should i rectify it?

  24. jraju says:

    Hi
    May i know the reason for this error,if i enter =1-.78-.22, i get the answer 0. but if i enclose the same in the bracket =(1-.78-.22) then i get an error result -2.77556E-17. May i expect your reply

Leave a Reply