Like the #DIV/0 error, #NAME? Error and so on. And there’s always some specific reason why you’d see these errors in Excel.
Learn about that reason and remove it to fix the errors in your Excel sheet through the guide below. We have them all (the Excel errors) sorted for you here.
So glide in and download our free sample workbook on your way to tag along with the guide 📩
Table of Contents
The #DIV/0 error tells that you are dividing a number by zero. And let me take you back to Grade 6, where we learned that a number divided by zero is undefined 👩🏫
Practically, no number is divisible by zero. And that is why if you try dividing a number by zero in Excel, you end up with a #DIV/0 error.
For example, check this out:
Similarly, if you divide a number by a blank cell, here’s what you get.
Also, if you have a #DIV/0 error in a cell and you refer to it in any other formula, you’d still get the #DIV/0 error.
For example, here we’ve referred to Cell A2 (that contains the #DIV/0 error) in our formula =A1+A2+A3, and this is what we get 🤦♀️
The answer is again a #DIV/0 error.
Some quick ways how you can prevent the #DIV/0 error in Excel include ensuring the following:
In addition to these, you can also use the IFERROR function to replace the #DIV/0 error in your worksheet with any other value as desired 👌
How? Learn more details about it by reading our tutorial on the #DIV/0 error here!
In simple words, the #N/A error means that the underlying formula failed to find what it had been told to find.
For example, if you write the VLOOKUP function to find the sales of “P” from the data in the image below:
You’ll get the #N/A error because there is no “P” in the data above. And the VLOOKUP function is set to find the data under the exact match mode.
It fails to find the given lookup value – and we end up with a #N/A error ❌
The function might not be able to find the given value for several reasons. Maybe because it’s not in the right format, or maybe because the spelling of the look-up value doesn’t match.
You’d most likely see the #N/A error in Excel when you’re using the lookup functions i.e. VLOOKUP, HLOOKUP, MATCH, etc.
Kasper Langmann , Microsoft Office Specialist
You can prevent (or fix) the #N/A error by trying the following:
As the name suggests, the #NAME error occurs when you have misspelled a name. It could be the name of a function, the name of a named range, or even an incorrect argument.
For example, check out here 👀
If you misspell the SUM function as the SIM function, you get the #NAME error.
Similarly, if you have saved a range by a name in Excel, misspelling it will cause the #NAME error as below.
Here the name of the range was “Numbers” and we misspelled it as “Number” 🔢
When the range is rightly spelled as “Numbers”, the results change as follows:
To avoid this problem, type in the first letter of the formula and press the “Tab” key.
Excel will enter the name of the function for you so there is no more chance of spelling mistakes.
And yeah! You’d also get the #NAME error if you wrongly write the reference for a range. Like we have forgotten the colon between A2 and A4 (A2A4 instead of A2:A4) here. So we get the #NAME? error 📍
The #REF error is Excel’s way of saying that there’s something wrong with the cell references in your formula.
For example, if you write a function with some cell references (say A2, A3, and A4).
And then delete a row (say Row 3), and you’d get the #Ref! error as below 👎
#REF! error also occurs when you copy-paste formulas that contain relative references from one cell to another.
Like here we copied the formula =A2+A3+A4 from one cell to another, and this is what we get.
Some ways how you can avoid the #REF errors in Microsoft Excel include the following 🤩
There are much more details to the #REF error of Excel – what causes it and how it is fixed. Learn all about it here.
You’d see the #VALUE error popping up in your Excel sheet when there is something wrong:
For example, here we have specified the wrong data type for the PRODUCT function:
Excel can surely multiply numbers but not text. As the list contains the text “ten”, the formula to add them up returns a #VALUE error ✖
Similarly, if you write arguments for a function that don’t make sense, you’d again see the logical error.
For example, the FIND function of Excel finds the position of a character from a text string.
The formula FIND (“p”, “paint”) gives the result 1. Because the substring “1” comes in the second position in the word “paint” 🎨
Try writing this formula as below:
=FIND (“b”, “kite”)
And Excel returns a #VALUE error. Because the word “kite” just doesn’t contain the character “b”, let alone its position therein 🪁
The exact reasons why the #VALUE error may occur depending upon the formula being used. However, the following general hacks can help you fix it.
It might be a little unfair to call this an error. If you are an Excel user, you must have come across a series of hashes in your spreadsheet in Excel that looks like this:
Well, that’s not a reason to worry #️⃣
This simply means that the column width for the subject cell is not enough to contain the value you’re trying to fit in.
So how can you fix this? Need not ask. Simply resize the column to broaden its width. To do that:
Alternatively, to autofit the width of your column:
And as a last resort, you can also broaden a column manually until it is wide enough to contain the given value (you’ll get to know that when the hashes are replaced by the value).
For example, here we resized the column, and the hashes are no more there 🔧
