Understand and fix 6 common excel errors you’re tired of seeing while working on your worksheet. These excel errors are ####, #NAME?, #DIV/0!, #NULL!, #REF!, #VALUE!.
Excel is a very important tool in any organisation. Without the same, data organisation as well as formulating the same can be a difficult task. The things that can be done in seconds through the help of excel would take many hours to do the same manually.
Excel helps us to formulate data through the help of functions. Yet at times, there are many types of errors that are noticed when one is working with the same.
These are the following errors that can show up which using the various functions of excel:
Common Excel Errors
let’s discuss each error one by one to examine the reason behind its cause and how can it be fixed:
This error occurs on the screen when the data so inserted in the cell, does not fit in.
As you can observe in the image above, the function we are using is too big to be accommodated in the cell B2, which is why an error of #### has occurred.
The best solution for the same is to expand the cell length which can be done from the Column B. Here’s how you can do it:
Just by expanding the cell, you can get away from the error.
It’s that easy.
This type of error pops on the screen when we have written the wrong function. Here by wrong function we mean the wrong spellings.
At times, we are working in such a fast pace that all we do is type, type and type and ignore to look on the screen for whether what we are typing is coming out correctly or not?
As you can observe in the image above, everything was going perfect. The SUM Function was used. All the data was provided. But due to the wrong spellings of the function, we received an error instead of the solution.
So, the only way to solve such error is to correct the spellings.
After correcting the same, your function works smoothly.
So, its quite simple to fix the same, all you have to do is spell check!
This is the type of error that occurs on the screen when you divide something by zero. As we are aware of the fact that something divided by zero is considered as an indeterminate form.
As you can observe in the image above, we have divided 28 by 0 which led to an error.
To fix the same, you need to check your data for whether the next value is 0 only or not. And if it’s confirmed to be 0 then you can just write NIL or whatever is comfortable to the company’s management.
There is another solution to fix such error. You can use the IF Function in times like this.
The formula for the same becomes as follows:
Here, E2<>0 is the logical test.
D2/E2 is the value if true and “” (quotation marks) is the value if false.
This means that if the value in cell E2 is greater than or less than 0 then the value that will be shown on the screen will be cell D2 divided by cell E2, but if the logical test comes out false then the result of the formula will be shown as blank.
The quotation marks are always shown as adding some specifications to the formula. Its your call to either leave them empty or jot something in them. If you will write something like “NIL” or “False” or “No” then the same will be shown if the logical test is not true.
As you can observe in the image above, the following calculations were going on perfectly fine but just because of a missing sign it ended up into #NULL! Error.
#NULL! Error, from the name itself you can pretty much tell the following error shows up when the value is null. The formula comes to this end when there is an error in between the formula or there is a missing calculation.
In the above example, you can see that the cell E2 and F2 are not being calculated i.e. there is a missing sign of addition “+” in the formula.
So, to resolve the same all you have to do is recheck the formula you have applied and input the missing calculations in the same.
This error pops on the screen when one of the cells that we have referred to, in the formula is not valid.
The reason behind invalidity can be deletion of the row or column.
To fix the same, either you can check your undo list and get the value back.
And if the same is not present in your undo list then you need to find that value or else your formula will remain incomplete.
As you can observe in the image above, one of the values is not valid as per the calculation being used.
So, the best solution for the same is to take care of the value that is, to either correct it or eliminate it from the error.
There is another way to sort such error. As we have added each value, so it’s quite clear that we can use the SUM Function in this situation as well.
But the only difference will be that the SUM Function will ignore the invalid value, so if there is an error in the data, you will never know, all you will know is the result of the valid values.
These were the errors that can pop in the excel sheet while you are dealing with tons of data.
Just a heads up, you can always use the IFERROR Function to check the presence of error in your data.
Problem solved, right😉?