These are six of the most common errors in Excel and how to fix them. No matter how skilled you are in Excel whether you’re an absolute wizard, Microsoft MVP it doesn’t matter.
You will continue to make mistakes. Everyone experiences these common errors in Excel.
So, let’s get started with the first error type.
- HASH MARK (####) ERROR
It is the simplest of all the errors, because all it means is that the column is not wide enough to display the underlying values and to fix it, is super simple.
All you have to do is drag or double click your column to increase the width until it can contain the value.
Above is the Hash mark error and remember it is easy to fix. You can either hover, over the edge of the column and drag or you can double click and it will auto fit the width to the widest or longest value inside of that column.
2. NAME (#NAME?) ERROR
This usually happen when Excel does not recognize a text within a formula. Excel is basically saying what are you asking me to do right now, I don’t understand.
To fix this, is to make sure that your function names are correct, make sure your references are valid and that any text that you’re trying to reference within a function is surrounded in quotes (“A”).
If you spelled MATCH function wrong, you forgot the C and you typed =math, in this scenario, Excel is confused because there is no MATH function and it gives us that Name error.
Other reason this can occur, is if you spelled the function properly but forgot to surround our text string “Asset A” with quotes.
3. VALUE (#VALUE!) ERROR
Value error is saying that your formula has the wrong type of argument. The most common cause for this value error is when you are trying to multiply text with values or if you’re trying to perform some sort of arithmetic operation on text strings or cells that are formatted as text.
There’s no real way to multiply text and numbers and that is why Excel is equally confused. So, it returned the value error.
4. DIV 0 (#DIV/0!) ERROR
DIV 0 means that you’re dividing by zero or an empty cell. Make sure to check your division or your denominator value.
Note, sometimes it is normal to have zero as your denominator, it does not necessarily mean you’ve done anything wrong. You can use things like if statements or if error formulas to kind of mask that error and display an alternate value if you choose.
5. REF (#REF!) ERROR
The REF error means that your formula is referring to a cell that’s no longer valid. Likely cause is when you reference a cell or range and then accidentally move, delete or replace that range that you had previously referenced. You have to be familiar with cell reference to understand better.
6. NA (#N/A) ERROR
Final error type is the NA error and this means that you have a formula that can’t find or locate a referenced value.
This is a little bit different from the REF error where you won’t find an entire cell reference. You are most likely to run into the NA error while using the lookup functions.
Excel file: Common Errors
Here is a visual explanation giving more insight about the common errors in Excel. Thanks, sharing is caring.