Excel errors / #CALC!
#CALC! error in Excel
A formula that returns a list ended up with nothing to return.
#CALC! appears in Excel 2021 and Microsoft 365, with the functions that return lists. Its usual meaning is "the result is empty": FILTER was asked for the rows that meet a condition, and none did.
Excel has no way to show an empty list in a cell, so it shows this error. It rarely means the formula is wrong.
Have a formula that shows #CALC!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
2 causes of #CALC!, and how to fix each
FILTER found no matching rows
No region below has sales over 500, so FILTER has nothing to return.
A B C D 1 Region Sales 2 North 100 #CALC! 3 South 80 In D2:
=FILTER(A2:B3, B2:B3>500)→ #CALC!How to fix it. Give FILTER a third argument: what to show when nothing matches.
A B C D 1 Region Sales 2 North 100 None 3 South 80 In D2:
=FILTER(A2:B3, B2:B3>500, "None")→ NoneA list inside a list
Each cell of a spilled result holds one value. A formula that tries to put a whole list into each cell, such as a lookup that returns a row for every item of a column, has no way to show that.
How to fix it. Return one value per cell: look up one column at a time, or join each inner list into a single text with TEXTJOIN.
Common questions
What does #CALC! mean in Excel?
A dynamic array formula could not produce a result that fits in cells. Most often FILTER matched no rows. Click the error and the small warning icon beside it says which case it is, such as "Empty array".
How do I fix #CALC! in FILTER?
Add the third argument, which FILTER shows when no row matches: =FILTER(A2:B50, B2:B50>500, "No results").
Practise it
Functions involved
- FILTER: Keeps only the rows that meet a condition. The result spills.
- UNIQUE: Removes duplicates from a list. The result spills.
- SORT: Sorts a range. The result spills.
- TEXTJOIN: Joins text with a separator between each item.
- IFERROR: Returns a fallback value if a formula results in an error.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.