This blog post is inspired by a participants question. He asked about how to understand some of Excel’s error messages so I have compiled the most common ones into a table below..
You can find a full reference set here.
Laila Gharani offers solutions to 7 common Excel errors here
Some error messages
| Function you are using | Error message | What it means | How to correct |
| Vlookup | #N/A! | Can’t find a match in your main data set | · Maybe this is the point of the exercise. Identifying what is missing?
· If they are all like that – check the data types. Is your lookup value text and the matching thing a number? · Are you using the correct look up value? |
| Vlookup | #REF! | Doesn’t mean you need a referee….
Usually means you have specified something like 3 columns in your table array and are telling it to look in column 4 |
Make sure you have highlighted all the columns in your table array data set. |
| Formulas | ###### | Your column is too narrow | Widen your column by double-clicking on it.
This is how you can widen multiple columns |
| Formulas | #Value! | You are asking Excel to do something like sum (which only works for numbers) with text entries or vice versa | Check that your entry is appropriate for your formula. |
| Formula | #DIV/0! | You are trying to divide a number by zero | Apply the iferror() function so that it returns 0 instead of the error |
| Formula | #Name? | Usually means you have misspelled a function e.g. SAM instead of SUM – there is no such function | Check you have spelled the function name correctly. |
| Formula | Formula not returning an entry.
Cursor stuck on a part of the formula – may need to check the formula bar |
Often means you have not entered matching brackets. | Look for a red bracket – that means only one has been entered. Black brackets means the bracket look has been closed. |