Every Excel user eventually sees a strange code appear in a cell instead of a sensible answer. By the end of this page you will be able to recognise the most common ones and know exactly what usually causes each and how to put it right.
An error message in Excel looks alarming, but it is really just the programme telling you plainly that something specific has gone wrong, rather than silently giving you a wrong answer. Learning to recognise a handful of common errors by sight means you can usually fix the problem in seconds rather than guessing.
- 1Column too narrow
- 2Wrong type of data
- 3Dividing by zero
- 4Unrecognised name
A row of hash symbols, #####, is the gentlest of the errors: it simply means the column is too narrow to display the number or date inside it, and the value itself is completely fine underneath. Widen the column, by double-clicking the divider on its right edge, and the real value reappears immediately. #VALUE! usually means a formula is trying to do arithmetic with something that is not actually a number, such as adding a cell that contains text by mistake. #DIV/0! means a formula is trying to divide by zero, or by an empty cell, which is mathematically impossible, so Excel refuses and shows this instead.
Try it yourself
Type a long number such as 123456789 into a very narrow column and watch it show as #####.
This demonstrates the friendliest error: the value is fine, the column is simply too narrow to show it.
Widen that column by double-clicking the divider on its right edge.
This immediately reveals the real number, proving nothing was actually lost.
In one cell type the word 'hello', in another type 5, and in a third type =A1+A2 referring to both.
This creates a #VALUE! error, since Excel cannot add a number to a word.
In a new cell, type =10/0 and press Enter.
This produces #DIV/0!, showing what happens whenever a formula tries to divide by zero.
Type =SUM(A1:A5) but misspell it as =SUN(A1:A5), and press Enter.
This produces #NAME?, because Excel does not recognise SUN as a real function name, usually caused by a typo.
Words people use
Once you can recognise these few error codes on sight, they stop feeling like a crisis and become simply the next small thing to fix, much like a spelling underline in a word processor. Most are solved in moments once you know what each one is actually saying.
Check yourself
If you can say yes to each of these, you are ready for the next lesson.
- I know ##### means a column is too narrow, not a broken value.
- I can recognise #VALUE! and #DIV/0! and understand their common causes.
- I understand #REF! usually means a formula pointed at something now deleted.
- I can spot a #NAME? error as most likely a spelling mistake in a function name.