February 28, 2015
Hashtags look cool when seen on Twitter or Facebook but as soon as they start appearing in Excel sheet, you know you are in trouble. Yes, in today’s tutorial we are going to talk about the very annoying Excel errors. Hopefully, after going through the article, you will know the cause of the error and the solution to get rid of it.
1. ###### Error (#######)
This is one of the most common and easiest to tackle error. Basically, it is not a formula based error but occurs when the width of column is small. In the example below, the figure 15000000 is too big and the column width is too small and hence it shows a error.
When you increase the width of column A, you will see that the error disappears.
2. Name Error ( #NAME? )
This error arises whenever Excel is not able to recognize the text in the formula. Like in the example below, instead of writing SUM we have written su and that is the reason we see the #NAME? in A5.
You will see that when you have corrected the formula to =SUM(A1:A4), you get the correct answer.
3. Value Error ( #VALUE! )
Excel shows a Value error whenever the variable mentioned in the formula is wrong. If you see the image below, you will notice that in the formula cell A4 contains text whereas the other 3 cells contain numerical values and hence there is an #VALUE! Error.
When you change the formula to A1+B1+C1 then you shall receive the correct answer without any errors.
4. Division Error ( #DIV?0! )
This is very simple to remember. When you attempt to divide a number by zero, you will come across the #DIV/0! error. In the example below, A4 cell contains a 0 and that is the reason the result for A1/A4 is #DIV/0! Error.
When you replace the zero with any other number ( 5 in our example), you will see the actual result without any error.
5. Null Error ( #NULL! )
This is one of the most common types of error. When you forget to separate the two cell references correctly, then you shall receive this error. In the below mentioned example, there is no command between the cell A4 and A4 and hence, you see the #NULL! Error.
When you add a proper command between the two cell references, then you will get your desired result without any error.
6. Reference Error ( #REF! )
When your formula contains some incorrect cell references, then you shall see a Reference Error. This mainly happens when you mistakenly delete any row or column. In the example below, you will see that your data is perfectly filled without any errors.
If by chance I delete the 3rd row, then the reference error shall occur as the original formula contains the value of 3rd row.
If you found this article helpful, then share it with your friends and colleagues to make their life a little easy. If you think that we have missed out on some important stuff, then mention it in the comments box below.