HomeMicrosoft OfficeExcelHow to Deal with Formula Errors in Excel?

How to Deal with Formula Errors in Excel?

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

How to Deal with Formula Errors in Excel

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.

Leave a Reply

You May Also Like

In this article, you will learn about the SKEW.P function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the SKEW function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the RANK.EQ function, the formula syntax and usage of the function in Microsoft...
  • Excel
  • November 23, 2021