Empty array
An empty array can trigger a #CALC! error, and this is the most common reason you may see a #CALC! error in a worksheet, especially when using the FILTER function. This is because FILTER returns a #CALC! error when no values meet criteria – in other words, FILTER returns an empty array. For example, in the screen below, the FILTER function is set up to filter the source data in B5:D11. However, the formula is asking for all data in the group “x”, which doesn’t exist. The result is a #CALC! error:
Fix #1 - adjust filter logic
One option to fix this error is to adjust the filter criteria to return valid results. In the screen below, the formula has adjusted to filter on group “A”, and the formula works normally:
Fix #2 - set is_empty argument
Another option is to provide a “not found” value to return when no results are returned. In the screen below FILTER returns “No results” instead of an error:
Note: Microsoft documentation mentions other cases that may cause #CALC! errors, notably nested arrays, and unsupported arrays. However, I have not been able to reproduce the error with the examples provided. If you have examples of formulas that throw #CALC! errors, please let me know.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
