When working with Excel formulas, you would most likely have encountered errors at least once. Although seeing an error message can be annoying and a bit unclear, it is still better than silent errors that cause erroneous results.
In this blog post, we’ll walk you through some of the most common Excel errors, explain what each one implies, and, most importantly, show you how to troubleshoot or fix the errors.
Error #1: ##### – The Hashtag Error
This has to be one of the most popular errors beginners face when working in Excel. It appears when a cell contains large numbers and can not be displayed because the cell size is not wide enough.
It’s very easy to solve this error. You can simply expand the cell size. Alternatively, you can select the column to be adjusted and then select the format dropdown on the home Tab, then AutoFit Column Width.
Error #2: #NAME?
You will most likely encounter this error when there is a typo in your formula like an “avrage” instead of an “AVERAGE” function.
Error #3: #DIV/0!
You may encounter this error when you attempt to divide a number by zero. Note that empty cells are also treated as zero in Excel.
The solution to this error message is to wrap the formula in an “IFERROR” function as seen below.
Error #4: #NUM!
It is not so common to encounter the #NUM error. Excel displays this error when the result of a calculation is too large or too small to be represented as a number, or when the calculation is impossible to perform.
To fix this error, make sure that the formula value is within the range of valid Excel numbers.
Error #5: #VALUE!
The #VALUE error will appear if the function or formula has been imputed erroneously, or if there is an inconsistent data type in your formula.
To fix it, make sure that the arguments you are passing to the function or formula are of the same data type and valid.
Error #6: #REF!
You may encounter the #REF error when a formula refers to a cell that no longer exists, either on the sheet, or another sheet. It is common when copying and pasting formulas across sheets.
To fix it, make sure that the cell reference in your formula is correct.
Error #7: #SPILL!
This error occurs when a function tries to return multiple rows (or columns) of results, but there is something in the target cells that prevents Excel from doing so. This is a fairly new error as it works with dynamic array formulas.
To fix this, we just need to move the text outside the needed range of the formula.
Error #8: #N/A!
This is a very common error you may face when using Excel’s Lookup functions and the value you are looking for cannot be found within the specified range. #N/A means Not Available.
The solution to this error is to ensure that there are no inconsistencies or formatting mismatches in the lookup value. The IFNA function can be used to replace the #N/A error.
Error #9: #NULL!
The #NULL error occurs when two or more cell references are separated by a space character instead of a comma or colon unintentionally or intentionally.
To solve this error, simply inspect the formula to ensure there are no incorrectly placed spaces. Named ranges can also help to make the formula more readable and easier to manage.
| Error | Meaning |
| #NAME? | The name of the formula has been incorrectly spelled or the formula does not exist. e.g. AVG (A12:A27) instead of AVERAGE(A12:A27) |
| #DIV/0! | Divided by zero |
| #NUM! | This means there is an invalid number e.g., when you use positive numbers in a function when negative numbers are required. |
| #VALUE! | This happens when there are inconsistent datatypes in your formula |
| #REF! | There is no reference or invalid reference. |
| #SPILL! | There is an obstruction in the way of a function that returns a dynamic array |
| #N/A! | This means Not Available. It is very common with Lookup functions. It occurs when the lookup value is not found. |
| #NULL! | Incorrect Range in a function e.g., typing COUNTA(B4 B6) instead of COUNTA(B4:B6) |
Share in the comments what Excel errors you commonly encounter. And how do you troubleshoot these types of errors in Excel?
