Spreadsheet error
How to fix the #CALC! error
TL;DR
#CALC! appears in Excel 365 when a dynamic array function like FILTER returns no rows, or when an array of arrays cannot be represented. Usually: provide a default with the third argument.
What it means
The #CALC! error is specific to Excel 365 dynamic arrays. It indicates the formula engine could not produce a valid result — the most common case is FILTER returning an empty array.
Common causes
- •FILTER matched zero rows with no if_empty argument.
- •A dynamic array formula returned an array of arrays (nested).
- •LAMBDA or LET with recursive or undefined reference.
How to fix it
1.Use the if_empty argument of FILTER
- =FILTER(A:A, B:B="x", "No matches")
- The third argument is returned when zero rows match.
2.Flatten nested arrays
- If you have an array of arrays, use TOCOL or TOROW to flatten.
- =TOCOL(nested_array)
3.Check LAMBDA/LET logic
- Recursive LAMBDA with no base case will #CALC!.
- Ensure there is an exit condition.
Example
Broken
=FILTER(A:A, B:B="nonexistent")Fixed=FILTER(A:A, B:B="nonexistent", "No matches")When no rows match, FILTER returns #CALC! unless you provide the third if_empty argument.
Prevention
- Always provide the if_empty argument to FILTER in production formulas.
- Test dynamic array formulas against edge cases (empty range, single row).
Related errors
Let FormulaPad debug it for you
Paste your broken formula and the error message — get a diff and an explanation in seconds.
Debug a formula