Errors in excel formulas are inevitable. No matter how expert you are, some or the other day, you might have come across excel formula errors. In this tutorial, what are the different types of errors in excel, and also understand when does formula errors occur in Excel.
Additionally, we would learn how to resolve and fix the common excel formula errors like #N/A, #VALUE!, #NAME?, #DIV/0!, #REF!, #NULL!, #NUM!, and ######.
- Common Types of Formula Error Codes in Excel
- ##### Error in Excel and How to Resolve
- #N/A Errors in Excel and How to Resolve
- #VALUE! error in Excel and How to Resolve
- #NAME? Errors in Excel and How to Resolve
- #DIV/0! Error in Excel and How to Resolve
- #REF! Errors in Excel and How to Resolve
- #NULL! error in Excel and How to Resolve
- #NUM! error in Excel and How to Resolve
Common Types of Formula Error Codes in Excel
While working with formulas in excel, you may encounter one of the following 8 excel formula error codes-
This appears at the time when you type some formula in an excel cell and press the Enter key on your keyboard. Also, the cell having any of the above error code has a small green triangle on the top-right corner of the cell.
Let us learn each of these formula error codes in details and possible resolution of those errors.
##### Error in Excel and How to Resolve
When you type any number, value, or amount in a cell, it may result in many hashes like this – ######. This is not actually an excel formula error. Such a hash value occurs when the cell’s width is not wide enough to accommodate the number value.
How to solve ##### error in excel cell – The fix for ##### error is very simply. Simply take your mouse cursor to the right edge of the excel column and double click over there. This would instantly autofit the width of the column to accommodate the largest value in that column.
#N/A Errors in Excel and How to Resolve
The N/A or NA Error is the most common excel formula error code that an excel user may get. This type of error generally occurs when something that the formula is trying to search for does not exist.
The #N/A error is most common for advanced excel users using functions like LOOKUP, VLOOKUP, HLOOKUP, INDEX, and MATCH. This occurs when these advanced excel formulas do not find lookup values.
How to Solve #N/A Error in Excel Cell – The best way to fix the #N/A error is to go to the lookup values and lookup tables and check the correctness of the values that the formula is trying to search for in the lookup tables.
In the below image, you can see that the VLOOKUP formula did not find the product code 106 in the lookup table range A2:B8, and hence it will give the #N/A error.
#VALUE! error in Excel and How to Resolve
The VALUE error occurs in the Excel formula when you have entered a wrong type of value or special character to the reference cell. Providing a space character or a text value to the cell that expects a numeric value as output would give a #VALUE! error.
In the below image you can see that cell A2 contains a text value “Excel Unlocked”. The result of the formula =A1+A2+A3+A4 in cell A6 therefore would give a #VALUE! error. This is so because the formula output is expected as a numeric value, and text value cannot be added.
How to Solve #VALUE! Error in Excel Cell – There is two ways to fix the #VALUE! error in Excel:
- Remove the incorrect text value from the cell and/or enter a numeric value in the cell.
- Use formulas and functions that ignore the text data type during calculation (for example, the SUM function).
#NAME? Errors in Excel and How to Resolve
The NAME Error in excel occurs probably occur due to two main reasons:
- You have misspelled the formula name itself. For example – Instead of typing =VLOOKUP, you type =VLOKUP (One ‘O’ is missing).
- You have not mentioned enough information in the formula itself.
In the below image, you can see that the information about the cell reference (within Sheet1) is missing in the formula, and hence it gives #NAME? error.
How to Solve #NAME? Error in Excel Cell – To fix #NAME? error, simply check the spelling of the formula name. However, if the spelling is still correct, check in detail each of the inner components (arguments) of the formula. Correct and complete the incorrect arguments and that’s it.
The ideal way to avoid such #NAME? formula error is to use options available in the Function Library group to create the formula (under Formulas Tab).
#DIV/0! Error in Excel and How to Resolve
The DIV error occurs in an excel formula when you are trying to divide a number by zero (0) or by a blank cell. Therefore, when the denominator is either zero 0 or an empty cell, excel gives the #DIV/0! error as a result of the formula.
See the below image for more information.
How to Solve #DIV/0! error in Excel cell – To fix #DIV/0! error in excel formula, simply add any non zero value in the cell containing denominator value or put any value other than zero, if that cell is empty.
#REF! Errors in Excel and How to Resolve
The REF Error in excel formula is the most common excel formula code that an excel user may face while working with excel formulas.
If you get #REF! errors in excel formula that means that a particular cell, range, row, or column to which the formula is trying to refer to does not exist or has been accidentally deleted.
In the below image you can see that the values in column D are derived from values in columns A, B, and C.
Now, suppose I delete the entire column B (Number 2). As a result, you would notice that the excel formula instantly turns up into the error code – #REF!.
This is because the column to which the formula in column D is trying to refer does not exist (has been deleted).
How to solve #REF! error in excel cell – The best way to avoid the #REF! error in excel formula is to refrain from deleting any cell, row, or column from excel workbook without checking cell references.
#NULL! error in Excel and How to Resolve
The NULL Excel formula occurs when you have used the wrong range operators (colon and comma) while trying to refer to cells or a cell range.
For example, if you miss adding colon between cell references in the SUM formula for adding up a range of cells. Or you have missed adding a comma in between two cell references for adding up two no adjacent cells.
See the below image for better explanation.
How to solve #NULL! error in Excel cell – To fix the #NULL! error, make sure that you use the ‘colon’ to separate first cell reference and last cell reference for referring to the ranges or use ‘comma’ for referring to individual cells.
#NUM! error in Excel and How to Resolve
When any numeric value in the formula is entered in an invalid or incorrect format, then you get the NUM error in the excel formula.
The excel throws #NUM! error in excel cell in following situations:
- The resultant numeric value of the formula is too large – For example, 10 to the power 1000 (=10^1000) gives #NUM! error.
- The numeric value entered is in an invalid or incorrect number format – For example, typing numeric value with currency symbol and thousands separator (“$1,500”) and not simply 1500.
- The calculation of the formula is mathematically not possible – For example, finding the square root of a negative number like, =SQRT(-100) will result in #NUM! error.
- Iteration formulas, like IRR, can’t find the result after multiple iterations, then you would get #NUM! error.
How to solve #NUM! error in excel cell – To fix the #NUM! formula error in excel, avoid manually typing currency symbols and thousands separator while entering a numeric value. Instead of $1,500, simply type 1500 (without any currency symbol and separator).
With this, we have completed all the common excel formula errors code and how to fix those error codes. I hope this content was useful for you, share your comments below. 🙂