In this post, you’ll learn about the Formula Errors in Excel and how to deal with Formula errors when working on your Excel spreadsheets.
Formula errors are errors that get displayed when you violate the syntax of the formula, pass the wrong parameters or invalid arguments, if there is an invalid data entry or if logic is defied.
How to Deal with Formula Errors in Excel?
These type of errors can be easily fixed as they get displayed if encountered.
Most of these errors can be solved manually but at times you can’t figure what’s exactly the problem in a formula in those circumstances, Go to Formulas tab<Error checking under the Formulas group< Trace error.
Let’s take a look at a few of these kinds of errors that can be solved manually and how to solve them.
Some of the most common errors that gets displayed are:
#### Error
This error gets displayed when the cell isn’t wide enough to display the result.
You can solve this error by just elongating the cell by selecting the corner of the column and dragging it.
Note: At times insufficient space isn’t the cause of the error, it may be because of an invalid operation.
In this case just the format of numbers to be displayed in the cell or just enter a valid operation.
#N/A error
This error gets displayed if the mentioned or given value is not found.
NA means NOT AVAILABLE.
To solve this use or refer to an value that is entered in the worksheet.
#NUM! error
#NUM gets displayed if the number can’t be displayed cause either the number is too small or big or the specified operation can’t be performed on the number.
You can solve this by entering a valid data and an operation accordingly to it.
#NULL! error
This one gets displayed when the cell reference is incorrect in the formula you’ve entered i.e, such as a syntax error or a typo.
When you encounter this one just go through the formula again to ensure that the syntax is correct.
#DIV/0! error
As the name says it gets displayed when any value or number is given to be divided by zero.
When this error pops up, just remove the zero from the divisor position and replace it with any other number than zero.
#NAME? error
This error gets displayed when there is a foreign text in the formula that you’ve entered i.e when Excel can’t recognize the text in the formula.
To handle this error check if there is an typo or spelling mistakes in the wordings of the formula you’ve entered.
#VALUE! error
This error occurs when there is invalid value i.e, invalid argument in the formula.
To clear this error, use the correct set of values or an alternative formula.
#REF! error
It occurs when there is an invalid or wrong reference mentioned in the formula.
This error gets displayed only if the cell can’t refer to the cells in the formula, so just use a direct formula for the operation to take place in the particular cell you are working with.
