In this blog, I will try to help you to debug your excel formula. As soon as you will use excel formula, you will likely need to know how to correct the error in excel formula. First of all, we need to know how many types of error, we can get we are applying excel formula in spreadsheets.
Normally you will find the below errors in Excel and we will discuss the same one by one.
• #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 formulaI have written vlokup 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.
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 work 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
8. Fixed formula Error in excel:
The most important thing you need to learn to fix a formula. If you don’t know how to fix your cell then you need to write the formula again and again. I have a long list of client names and I want to give them 5% discount to all.
So In my desired cell I have written the formula to get the discount. Now If I drag the formula down to get the discount amount for an individual client what will happen? It will show some error. And your result will look like below.
It is showing error as because you are dragging down the formula and the cell value D2 which is currently 5% is also changing. As you are moving down D2 is also changing to D3, D4 and so on. But for all the client my discount is fixed which is 5%. So this cell should be fixed or locked. To fix or lock a cell, you need press F4 key.
As my discount is fixed which is 5% I have fixed that cell by pressing F4. Now If I move down the formula discount cell is not moving as it is fixed. In the same I have not locked C5 cell as when I move down this cell should be C6 for the next client.
Now imagine if I have a data set where 4 discounts are placed and I want to give the discount to my client on a quarterly basis. In First Quarter, I want to give them 5%, in second 10% and 15% in the third and 20 % in the fourth quarter.
Now my question is that how will I write the formula so that I can move down and it should work for all the quarters.
Now C5 cell which shows the amount of the client. When I will move down after writing the formula it should be C6 and C6 should be C7 so 5 will not be locked but when I will move side C5 will never be D5 so C of C5 should be locked.
Again for the discount part I select the first discount 5% which is in D2 cell. Now think if I move down D2 will never be D3 or D4. So 2 of D2 should be locked. But If I am moving right then 5% (D2 cell) should be E2 so D of D2 will not be locked. So the final formula will be in D5 Cell
After writing the formula in D5 cell, you can move down and right to get the discount for all the quarters. Your formula should look like below
This is very crucial for all of us and if we don’t know how to lock or fix a cell then we will have to write the formula a number of times. So it is better to know how to fix the formula.
We will discuss more on the errors on our next blogs. Please follow our other blogs too.
Do you face formula errors or alternate solutions which I have missed? Comment now!