Skip to content
ArumSoft Learning
KO
    #CALC!Excel

    How to fix the #CALC! error in Excel

    Works in: Excel for Microsoft 365, Excel 2021 and later, and Excel for the web · Updated

    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

    1. FILTER finds no matching rows
    2. BYROW, MAP or SCAN returns an array for each item
    3. 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.