Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #CALC! error
Excel onlyWhat #CALC! means
#CALC! is an Excel-specific error returned by dynamic array functions when the calculation cannot complete — most commonly because the function is asked to return an empty array (no results), or because it encounters a logical impossibility such as intersecting empty sets. It signals a calculation boundary, not a syntax error.
Common causes
- FILTER returns no matching rows because the logical condition matches nothing, producing an empty array which is not displayable.
- UNIQUE is applied to a range that is entirely blank or contains only errors.
- Nested dynamic array functions produce incompatible array shapes that cannot be reconciled during evaluation.
- A LAMBDA or LET variable resolves to an empty result, which propagates as #CALC! through the outer function.
- CHOOSECOLS or CHOOSEROWS is given a column/row index of 0 or an index beyond the array dimensions.
Example fix
Broken
=FILTER(A2:B50,A2:A50="Closed")Fixed
=FILTER(A2:B50,A2:A50="Closed","No closed items")Added the third argument (if_empty) to FILTER so it returns a message instead of #CALC! when no rows match the condition.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Wrap FILTER with an if_empty argument to return a fallback value when no rows match: =FILTER(A2:B100,A2:A100="Done","No results").
- 2Check the filter condition in a separate column to confirm it returns at least one TRUE before relying on FILTER's output.
- 3For UNIQUE on a potentially empty range, add a pre-check: =IF(COUNTA(A2:A100)=0,"No data",UNIQUE(A2:A100)).
- 4Verify CHOOSECOLS/CHOOSEROWS index arguments are within bounds: column indices must be between 1 and the number of columns in the array.
- 5Simplify nested dynamic array expressions step-by-step to isolate which sub-expression produces the empty result.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every #CALC! and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Is #CALC! the same as #VALUE! or #N/A?
#CALC! is distinct and Excel-only. It specifically means a dynamic array calculation could not produce a result array, usually due to an empty result set. #VALUE! and #N/A point to type mismatches and missing lookups respectively.
Can I suppress #CALC! with IFERROR?
IFERROR will catch #CALC! and let you show a fallback, but always fix the real cause first — a hidden empty filter can mask data problems. Use FILTER's built-in if_empty argument instead of IFERROR where possible.
Does #CALC! appear in Google Sheets?
No. Google Sheets does not use the #CALC! error code. Equivalent situations in Sheets typically return an empty result or a different error such as #ERROR!.