Excel Cell Errors Explained
Staring at a worksheet with a plethora of errors is not sightly, but it can be your saving grace for diagnosing formula problems.
Over a dozen error types exist, and chances are you will have seen most of them at some point, but you wouldn’t necessarily be able to pinpoint when and why they appear.
This article looks in-depth at the types of cell errors and the scenarios that cause them to occur.
Before We Start
Download the complementary workbook here:
Please view in a Windows-based Microsoft 365 desktop version of Excel for maximum compatibility.
The examples for each cell error are housed in separate worksheets. Use the tabs to navigate between them.
Cell Errors
#####
Cause(s):
Narrow Column Width
This isn’t an error caused by a formula, but if a column’s width is too narrow to display a numeric value, it will result in a set of hash symbols. However, the cell format also determines when they show up.
The General format will make the number display in exponential notation first — with fewer decimal places as the column narrows. For example, 6752548925 becomes 6.753E+09, 6.75E+09, 6.8E+09 and then 7E+09. When the column gets even narrower, the hash symbols appear.
Scientific does the same, except as it defaults to two decimal places, the hash symbols kick in at a later point.
The Number, Currency, Accounting and Percentage formats also default to two decimal places but do not work like this. They either show the whole number or fill the cell with hash symbols if it doesn’t fit.
Text isn’t affected by any of these things and will display what it can regardless of the column width.
#CALC!
Cause(s):
Uncalled LAMBDA Function
When you’re testing out a LAMBDA function, typically you’ll construct it in a cell first before pasting it into Name Manager. Failing to call it by not providing any parameter values will result in a #CALC! error.
Here, x and y are the parameters and x+y is the calculation, but no values have been given for x and y.
The problem is solved when 2 and 6 are fed in as x and y, respectively, allowing the calculation 2+6 to take place.
Empty Array
Any array returned by the FILTER function must consist of at least one row, otherwise, it isn’t valid.
#CALC! appears in cell M18 as there are no marks under 50 in the range J18:J20.
The optional if_empty argument can be used to replace the error with a value of your choice.
#DIV/0!
Cause(s):
Division by Zero
In mathematical terms, a non-zero figure divided by zero produces an undefined result, whilst zero divided by itself is called indeterminate. Excel doesn’t differentiate between these, so #DIV/0! will occur for both.
It’s worth remembering that empty cell references are also considered zeros.
#FIELD!
Cause(s):
Invalid Data Field
Excel fails to retrieve additional information from a cell using a data type because it’s an unknown value.
Any cell can be converted to a data type by going to the Data tab and selecting the appropriate one from the Data Types section.
Although Ireland is a recognised geographic entry, it does not have an Abbreviation option like the United Kingdom and the United States. That’s why the formula =J13.Abbreviation returns the #FIELD! error.
#N/A
N/A is a commonly seen initialism that means not available. This gives you a clue what the #N/A error does.
It appears when a lookup formula fails to return a value — and is associated with the XLOOKUP, VLOOKUP, HLOOKUP, INDEX and MATCH functions.
Sometimes a missing value is obvious, but other times it’s caused by obscure things such as extra spaces, misspellings, or incorrect ranges.
Cause(s):
Non-Existent Value
In this XLOOKUP formula, Latte is the coffee name to return the price for, but no such table value exists.
Extra Spaces
There is nothing wrong with the formula here, and it’s easy to be baffled about why it isn’t working. However, the issue lies with the data.
Spaces are invisible and virtually undetectable, especially when they appear at the end of values.
In cell I19, there is a solitary space after the word Cappuccino. It might seem trivial, but this differs from the lookup value in L19, making it impossible to find.
Incorrect Range
The lookup value is Mocha, which appears in the table but isn’t part of the reference in the lookup_array argument— neither is the price for return_array. If just one of these was referenced properly though, a #VALUE! error would be produced instead of #NA.
#NAME?
Cause(s):
Misspelt Function Name
Always ensure you have spelt a function correctly, as it’s easy to double type or omit a character.
The SUM function has been spelt ‘SUMM’, which is non-existent.
Incorrect Range
A range must be consistent on both sides of the colon. For instance, you can’t reference a cell on the left side and only a column on the right.
Misspelt Named Range
A named range must be referenced correctly in the formula, so ensure there are no misspellings.
In cell L26, a non-existent name called Value is included in the SUM formula. If you go to the Formulas tab and click on Name Manager, you’ll see an entry called Values. This was the intended name.
Named Range Scoped to Another Worksheet
Named ranges must have the correct scope to be used in a worksheet.
Inside Name Manager, highlight NAME_ErrorType and click on the Edit button; this opens the Edit Name window.
The Scope dropdown determines which worksheet the named range is compatible with. As it’s bound to Error Handling, it will only work in cells there.
Once you have assigned a workbook-wide or worksheet-specific scope and clicked OK, it cannot be changed as the dropdown will be greyed out. You will have to create a new name if you want a different scope. That’s why it’s better to stick to Workbook, like for NAME_ErrorType2.
Missing Quotes in Text Value
A text string must be surrounded by quotation marks, otherwise, it will be interpreted as a name.
Smart Quotes in Text Value
Be careful not to use the wrong type of quotation marks. Only the straight variant will work.
#NULL!
Cause(s):
Missing Mathematical Operator
An absent mathematical operator like the plus (+), minus (–), multiply (*), or division (/) symbol will make the calculation incomplete.
Missing Range Operator (Colon)
A colon (:) sandwiched between two cell references is necessary to indicate it is a range.
Missing Union Operator (Comma)
A comma is an argument separator. It’s needed here to separate the [number1] and [number2] SUM arguments.
Non-Existent Intersection
A space character is an intersection operator. If it’s used, the ranges must overlap each other for the calculation to be valid.
#NUM!
Cause(s):
Impossible Calculation
A mathematically impossible calculation will fail to compute and produce a #NUM! error.
√-1 doesn’t work because the square root of any number is always positive (except √0), so the #NUM! error is invoked.
Number Outside Min/Max Boundaries
The smallest number possible in Excel is -1*³⁰⁸, whereas the largest is 1*³⁰⁸. That is -1 and 1 both followed by 308 zeros!
Any number outside these boundaries isn’t valid.
End Date Precedes Start Date
DATEDIF is an undocumented function and does not appear in the Insert Function window or IntelliSense. However, if you use it to find the difference between two dates, the end date must not precede the start date.
Iterative Formula Failure
Functions like RATE, IRR, and XIRR use iteration to find a result. Excel restricts the number of iterations, so if a result isn’t found before the limit, a #NUM! error is returned.
The iteration settings can be adjusted by going to File > Options > Formulas > Calculation.
#REF!
Cause(s):
Invalid Reference
An invalid reference will produce a #REF! error. Usually, this happens because sheets, rows or columns have been deleted.
In the example, try deleting rows 12 to 21.
This triggers a #REF! error in J12.
After, press Ctrl + Z to undo and return to the previous state.
Invalid VLOOKUP Column
You will also see a #REF error with the VLOOKUP function when an invalid number is specified in the col_index_num argument.
As there are only two columns in the lookup table, a col_index_num of 3 cannot work.
*TIP*
It’s best to ensure there are no cell references that point to what you’re deleting, so always be cautious. However, if you find any #REF errors, it’s good practice to fix them immediately.
Manually check for them using the Find and Replace window (Ctrl + F or Ctrl + H). You can also replace each instance with a new reference.#SPILL!
Cause(s):
Spill Range is Blocked
A non-blank cell must not exist in the range the formula is trying to spill in.
As the value 2 exists in cell K12, it prevents the adjacent formula from working.
#VALUE!
Cause(s):
Contains Text Value
If you add a group of values by summing them individually, there cannot be any text values. However, if you use the SUM function, text values will be ignored.
Contains Unexpected Type
The NETWORKDAYS function requires a start and end date in the correct format. Text is not permitted.
Date Stored as Text
Entering an invalid date format will automatically convert to text, so ensure it is one Excel recognises.
Errant Space
When summing individual cells, be aware of ones that look blank but actually contain a space.
Other Errors
There are also a few other errors that appear less frequently, but let’s quickly run through them:
#BLOCKED!
Returned when a required resource cannot be accessed.
This has been known to appear when attempting to use the STOCKHISTORY function in the free online version of Excel. It won’t work because a Microsoft 365 subscription is required.
#BUSY!
This indicates a formula is fetching a required resource that is taking a long time to access. This might be because:
- an external workbook link contains values a formula is waiting for
- an image is being rendered
- a function is still executing due to a complex calculation or the retrieval of an external resource
⛔ ️#CONNECT!
At the time of writing, no source has documented this error.
#GETTING_DATA
In large, complex worksheets, this will temporarily show up in cells still processing.
#UNKNOWN
Appears when the cell’s assigned data type is not supported in the Excel version you are running.
Error Handling
General Errors
There are three functions for general error handling.
IFERROR targets all error types, and the one returned is replaced with a chosen value.
It has two arguments:
value—the value to check for an error.
value_if_error—the value you want to replace the error with.
ISERROR evaluates the value argument and outputs TRUE if it returns an error.
ISERR evaluates the value argument and returns TRUE for all error types except #N/A.
These functions were tested on each error type to clarify what they return.
Trapping every error isn’t always what you’ll want. Sometimes you’ll want to home in on certain ones.
Target a specific error with the ERROR.TYPE function. For example, =IF(ERROR.TYPE(K22)=6,TRUE,FALSE) checks cell K22 for error 6, which equates to #NUM!. As it indeed does, TRUE is returned.
There seems to be a bug associated with the #GETTING_DATA error in K24. If you save the workbook, close it and then reopen it, it changes to #N/A. However, the adjacent ERROR.TYPE remains as 8 until you reconfirm the formula.
N/A Handling
Given the prevalence of #N/A errors, it’s lucky enough to get its own set of functions! There are three:
- NA — returns the value #N/A
- ISNA — returns TRUE if a value is #N/A
- IFNA — returns a specified value if an expression resolves to #N/A
Final Words
There are more error types in Excel than you probably realised! However, the likelihood is you will only encounter a few of them regularly. Still, it’s good to be aware of them as you’ll be able to fix formulas faster.
The IFERROR function is useful for clearing up a worksheet of errors, but try to understand your formulas first so you know what errors are returned and why.
If, for example, you have a lookup formula, the most common error will be #N/A. However, suppressing all the others as well makes it harder to know their cause. That’s why IFNA might be a better choice.
ERROR.TYPE gives you the ultimate flexibility though. Used in conjunction with the AND function, you can target not just one, but multiple error types.
Whatever you use, think carefully about your choices to aid you in creating more efficient formulas.
