Skip to content

Working With Formula Errors

When working with spreadsheets, it is not unusual to experience errors when writing formulas.

The good news is that the spreadsheet attempts to give some indication of exactly which error is occurring. This is why there are many different error codes.

In this article, we will show you how to deal with the formula errors you commonly see in spreadsheets.

####

When a cell contains this error code, the column is not wide enough to display the value.

 

To fix this, click on the right border of the column header and increase the column width.

 

You can also double-click on the right border of the column header. This will automatically adjust the column to fit the largest entry in column A.

#NAME?

This error occurs when the spreadsheet does not recognize the text in a formula. This is usually due to a misspelled function.

 

To fix this error, simply correct the misspelled function to the proper spelling.

#VALUE!

A spreadsheet displays the #VALUE! error when a formula contains the wrong type of argument.

A good example is a formula adding two cells, but one of the cells contains text instead of a number.

 

The number 2 cannot be added to the word “Hi,” so the #VALUE! error is displayed.

To fix this error, change the arguments of your formula.

#DIV/0

A spreadsheet displays the #DIV/0! error when a formula tries to divide a number by zero.

 

To fix this error, remove the dividing zero from the formula.

#REF!

The #REF! error is displayed when a formula refers to a cell that is not valid.

This usually occurs when a cell, row, or column that has previously been referenced is deleted.

Because the referenced cell, row, or column no longer exists, the previous reference is invalid.

To fix this error, replace the #REF! in the formula with a valid reference. You also try using the Undo command to see if this corrects the problem. Your previous action may have caused this error, although this is not always the case.

#N/A

The #N/A error is displayed when one of the lookup functions cannot find a match.

 

The lookup functions include functions like:

  • VLOOKUP
  • HLOOKUP
  • MATCH
  • etc.

To fix this error, use the IFNA function to replace the #N/A error with a specific message.

#NUM!

The #NUM! error is displayed when a formula contains invalid numeric values.

A good example is using the SQRT function on a negative number.

 

Negative numbers do not have a square root therefore the SQRT function returns the #NUM! error value.

To fix this error, get rid of or change the invalid numeric value.

#NULL!

The #NULL! error is displayed when the intersect operator (a single space) is used on two ranges that do not intersect.

This is best understood by looking at an example. See the two examples shown below.

The formula returns #NULL! because the two ranges do not intersect.

 

The formula does not return an error because the two ranges do intersect.

#SPILL!

The #SPILL! error is displayed if something is blocking a spill range.

Dynamic array formulas, entered into a single cell, fill multiple cells. The cells that get filled with data make up the spill range.

To fix this error, empty the cell that is blocking the spill range.

This Post Has One Comment

  1. I didn’t realize there were so many errors! This will be a really great reference the next time I don’t know how to fix one.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top