- Normally you will find the below errors in Excel and we will discuss the same one by one:
Normally you will find the below errors in Excel and we will discuss the same one by one:• #DIV/0! • #NAME? • #NULL! • #REF! • #NUM! • #VALUE! • #N/A Formula Error
1. #DIV/0! Formula Error in excel:This is the easiest error and we also face in all our day to day work life. For example, if there is a number in your A1 cell and you are trying to divide it by 0 in another then your formula definitely should be =D1/0. Then it will show you the #DIV/0! Error. So the solution does not divide the value by 0. As because you know the answer what will be. Or you need to use another if a function like below not to get #DIV/0! Error. =IF (D1=0, 0, = D1/D2)
2. #NAME Error in excel:This is the error you will find when you will wrong formula name or wrong named range. This is the error meaning that the formula is not recognized by Excel. Then you need to check the spelling of the formula name. For example, if are going to use vlookup formula and then you write = vlookup(and then after giving all the parameters if you press enter then you will get #NAME error From the above picture, it is clear that in case of Vlookup formula. I have written vlookup which is not a formula to Excel. That’s why it is showing as #NAME? Error.
How to fix it:To avoid this error always choose the formula from the list and then press the tab key to use that formula. From 2007 onwards version in Excel if you write any formula you will get the formula name in the list. You do not need to write the entire formula. You choose your Excel formula and then press the tab key to use that formula. Not only it will select the formula you choose, but it will open the brackets for you. For example, you are going to sum formula. So in your desired cell just type =su and you get the all the formula names start with SU. You choose sum and then press the tab key to select that formula which will help you to get relief from formula error.
- Please make sure that all the named ranges you have used in the formula are correct.
- Check the ranges also. =Sum(B10B15) will also give you the same error. It should be =Sum(B10: B15).
3. #NULL! Error in excel:You will get this kind of error when you have forgotten to put the separator. Suppose for example you want to sum of two ranges and you have written the formula like below = Sum(F1:F5 G1:G5) You have forgotten to put the comma between the two ranges and if you press enter then you will get #NULL! Error. To fix the problems always make sure that you have placed the correct ranges.
Learn advanced advanced excel with our Excel training program with lifetime support
4. #REF! Error in excel:When your formula is pointing towards an invalid range then you will get such kind of error. For example, you are applying some excel vlookup formula and you want to get the output of no 3 columns from your table array but at the time of choosing the table, you have selected only 2 columns. Then you will get this kind of error as you are trying to fetch the value of 3 no columns and at the time of choosing your table, you have not chosen that part. Let’s look at the example In Table 1, I am looking for the name against the numbers and when I choose table array, then, have not chosen the entire table and that’s why I am getting the reference error. So if you get such kind of error, then please check the ranges you have selected for your formula. You can also get the same error if you used the formula and then deleting the original range. For example, you are doing sum where the ranges are F1 to F5, G1 to G5 and H1 to H5. After writing the below formula, you mistakenly delete H column. Then also you will get #REF! Error. = Sum(F2:F6,G2:G6,H2:H6) As soon as I have deleted H column from Table 1 then I got the #REF! Error. So Always check twice before deleting any unnecessary columns from your spreadsheet.
5. #NUM! Error in excel:When your formula returns a bigger than excel can manage then you will find this kind of error. 9.99999999999999E+307 is the biggest number that excel can manage. If your formula returns value more than above said value then you will get the #NUM! Error.
6. #VALUE! Error in excel:This type of error occurs when we use text parameters in a function when the function itself is looking for a number parameters. For example, if you are using the SUM function, then you should give the number parameter. If you give any value you will get #VALUE! Error. Always be careful with those functions which normally use the number of the parameter like sum, Min, Max, Average or sumproduct formula. If you are using a function that always works on numbers, you should give the number parameters. If you give the value like I have shown in the above formula then you will get the #VALUE! Error.
7. #N/A Error in excel:This is found especially in lookup function when the lookup value is not available in your table array. From the above example, it is clear that I have used vlook function to get the names and ages against the numbers in Table 1. I am getting one #N/A against the number 8322322222 as this number is not available in my table array (Table 2). If you don’t want to show #N/A to others then you can use iferror function. You can get #N/A error when you are using an array formula, but the referenced ranges are not the equal size in each array.
Looking for Free Online Excel course, its meant for beginner