#CALC! error: what it means and how to fix it
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
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
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
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”Need a quick reference? The formula cheat sheet is free to read and print.