#CALC! error in Excel
A dynamic array formula calculated an empty array, which Excel cannot display, most often a FILTER that found no matching rows.
What #CALC! means
#CALC! is a newer error, introduced with dynamic arrays. Excel returns it when a formula calculates an array with nothing in it. A FILTER that matches no rows is the standard case, because the filtered result is zero rows tall and there is no way to display that.
The formula itself is fine. The question is whether an empty result is expected. For a report that filters by a month with no sales, it is, and you should say so in words. For a filter that should have found rows, an empty result means the condition is wrong, usually through a data type mismatch.
FILTER gives you the right tool in its third argument, so you rarely need IFERROR around it.
The error only exists in Microsoft 365 and Excel 2021 or later. A workbook opened in Excel 2019 shows #NAME? for FILTER instead, because the older program does not know the function at all. If a colleague sees a different error from the one you see, compare Excel versions before you compare formulas.
Causes of #CALC! and how to fix each
1. FILTER with no rows that match
FILTER returns every row that meets a condition. When none do, the result is an empty array. Excel has nowhere to show an empty block, so it returns #CALC!.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | ||
| 2 | Ana | 40 | #CALC! | |
| 3 | Kim | 75 | ||
| 4 | Raj | 60 |
Fix. Use the third argument of FILTER, which is the value to return when nothing matches: FILTER(array, condition, "None"). It takes the place of the error with something a reader understands.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | ||
| 2 | Ana | 40 | None | |
| 3 | Kim | 75 | ||
| 4 | Raj | 60 |
No sale exceeds 100, so the formula returns the text from the third argument.
2. A condition that cannot match because of the data type
Comparing numbers with the text "5" never matches. A condition like B2:B4="5" fails on a column of real numbers, so the filter finds no rows and returns #CALC! even though the data looks like it should qualify.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Qty | ||
| 2 | Ana | 5 | #CALC! | |
| 3 | Kim | 3 | ||
| 4 | Raj | 5 |
Fix. Compare like with like: write the number without quotes (B2:B4=5). If the column really is text, make the criterion text too, or convert the column with VALUE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Qty | ||
| 2 | Ana | 5 | Ana | |
| 3 | Kim | 3 | Raj | |
| 4 | Raj | 5 |
Ana and Raj both have 5, so FILTER returns both names.
3. A text criterion with a typo or an extra space
Text comparisons ignore capitals but not spelling or spaces. A criterion of "West " with a trailing space matches nothing in a column of "West", and the filter comes back empty.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Region | ||
| 2 | Ana | West | #CALC! | |
| 3 | Kim | East | ||
| 4 | Raj | West |
Fix. Retype the criterion, or better, point it at a cell that holds the value so the text in the formula and the text in the data cannot drift apart. Cleaning the source column with TRIM removes the stray spaces for good.
Trapping #CALC! in a formula
FILTER has its own trap built in, so use the third argument instead of IFERROR. IFERROR also covers real faults such as a mismatched array size, and the if_empty argument only covers the case you mean, an empty result.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | ||
| 2 | Ana | 40 | No sales over 100 | |
| 3 | Kim | 75 | ||
| 4 | Raj | 60 |
Nothing meets the condition, so the message appears and a real problem would still show as an error.
#CALC! on Excel job tests
If a FILTER task could return no rows, for example "list the sales over 100" when none are that large, give FILTER a third argument such as "No matches" so the cell shows a label in place of #CALC!.
If you get #CALC! and the data should match, test the condition by itself in a spare cell. A single FALSE tells you the types or spelling are off.
Related guides
#CALC! questions
- What does #CALC! mean in Excel?
- A dynamic array formula returned an empty array, so there is nothing to display. FILTER with no matches is the usual source.
- How do I stop FILTER returning #CALC!?
- Add a third argument with the text to show when nothing matches, for example
FILTER(A2:A4,B2:B4>100,"None"). - Does #CALC! exist in older Excel?
- No. FILTER and the other dynamic array functions came with Microsoft 365 and Excel 2021, so older versions show
#NAME?for them instead.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample