formulastreak

#CALC! error: what it means and how to fix it

#CALC!

Excel's calculation engine hit a case it doesn't support, often an empty result.

Microsoft: #CALC! appears when Excel's calculation engine meets a scenario it doesn't support. In everyday sheets that's mostly a FILTER that matches nothing, or a LAMBDA that was written but never called.

Common causes, ranked

  1. 1

    FILTER found no matching rows

    An empty result can't be shown, so Excel shows #CALC!.

    Fix: Give FILTER its third argument, if_empty, to say what to show instead.

    Shows the error

    =FILTER(A2:A9,B2:B9="West")

    Fixed

    =FILTER(A2:A9,B2:B9="West","None")
  2. 2

    A LAMBDA that was never called

    =LAMBDA(x, x+1) only describes a function. On its own it has no value.

    Fix: Call it with a value in brackets right after it, or name it in Name Manager and call it by name.

    Shows the error

    =LAMBDA(x,x+1)

    Fixed

    =LAMBDA(x,x+1)(1)
  3. 3

    An array inside an array

    Some formulas would produce an array of arrays, which Excel can't show.

    Fix: Simplify the formula so it returns one array.

How to hide it safely

For FILTER, the if_empty argument is the right way to handle “no matches”; it's built for exactly that:

=FILTER(A2:A9,B2:B9="West","No matches")

Careful: Microsoft's IFERROR page doesn't list #CALC! among the errors it handles, so use FILTER's if_empty instead of wrapping it.

Excel vs Google Sheets

#CALC! is an Excel error. Google's list of Sheets error values (ERROR.TYPE) doesn't include it, and Google Sheets' FILTER returns #N/A when nothing matches.

Sources

Practise this in 5 minutes

Fixing #CALC! is taught in Lesson 38: FILTER (Unit 11, Dynamic arrays), on a live sheet with instant feedback. Pro

Open “FILTER”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.