#CALC!Excel
How to fix the #CALC! error in Excel
Quick answerIf the formula uses FILTER, add the third argument so it has something to show when nothing matches:
=FILTER(A2:C100,B2:B100="North","No results").Causes and fixes
- FILTER finds no matching rows
- BYROW, MAP or SCAN returns an array for each item
- A LAMBDA is entered without being called
1FILTER finds no matching rows
Excel can't show an empty result, so FILTER with no matches returns #CALC!. Formulas around it, such as UNIQUE or SORT, return #CALC! as well.
How to recognise it
The formula works for some criteria and fails for others.
Fix
Use FILTER's third argument, if_empty:
=FILTER(A2:C100,B2:B100=F1,"No results")
=SORT(FILTER(A2:A100,B2:B100=F1,""))2BYROW, MAP or SCAN returns an array for each item
These functions must return one value per row or item. If the LAMBDA inside returns several values, the result would be an array of arrays, which Excel doesn't support.
Fix
Reduce each row to one value, for example with SUM, MAX or TEXTJOIN:
=BYROW(B2:D10,LAMBDA(r,TEXTJOIN(", ",TRUE,r)))3A LAMBDA is entered without being called
=LAMBDA(x,x*2) on its own defines a function but gives it nothing to work on.
Fix
Call it with a value right after it, =LAMBDA(x,x*2)(A2), or save it with a name in Formulas > Name Manager and use =Double(A2).
Still stuck?
- #CALC! only appears in versions with dynamic arrays. In older versions the same formulas show #NAME?.
- If the error is #SPILL! instead, something is blocking the cells the result needs. See How to fix #SPILL!.
- Select the cell and use Formulas > Evaluate Formula to find the step that returns an empty array.