Home  »  ExcelOffice   »   Common Excel formulas errors explained

Common Excel formulas errors explained

By | November 12, 2014

#VALUE!

The formula has the wrong type of argument
Excel found type of data which was not expected there. The most common cause is when you use a function or create a formula with a cell that has text instead of numbers. Other might be that single value is expected but a whole range is returned.

#NAME?

Name not recognized
This error value is displayed when you type a wrong range name, point to a removed range, or you did not put quotation marks around a text strings in a formula (this makes Excel think that you are pointing to a range).

Also check if you are not using function that does not exist in this Excel version.

#NUM!

Problem with a number in the formula
This can show up because of an invalid argument in function or expression producing number too large or too small for Excel to deal with.

#DIV/0

Division by zero
You tried to divide by zero. This also happens when you try to divide by a cell that is blank, because Excel treats blank cells as zeros.

Check this article from Microsoft which shows you how to use the IF worksheet function to suppress the #DIV/0! error value.

#REF!

Invalid cell reference
You are referring to cell that cannot be found. This happens when you delete a cell referred in the formula or copy/paste formulas between worksheets.

#N/A

Value not available
This is not an error. This is information that value is not available. You can see this error when performing certain types of lookup or statistical functions which work with cell ranges. Example: when you use a function to search in a range and it cannot find what you need, you might get this information.
This error often occurs when a user enters an invalid lookup value in a
VLOOKUP formula.

Some people enter a #N/A value manually to tell Excel to ignore a
cell when creating charts and graphs. Better and easier
way to do this is to use the NA() function.

#NULL!

Intersection operator used incorrectly.
It shows when ranges specified in the intersection have no cells in common. Oftentimes this shows up when you forget to put : sign in cell range like

instead

#####

Column not wide enough to display the value
Actually this is not an error. It means that Excel has successfully calculated the formula but it can’t display the result because the number’s too wide to fit in the cell using the cell’s current number format.

To fix this: simply widen the column, or change the number format in cell’s properties

Related reading

Leave a Reply

Your email address will not be published.