Excel #CALC! Error: Empty Arrays, Nested Arrays and Uncalled Functions
Fix #CALC! by identifying what the formula is trying to return, not by hiding the error. Start with a copy of the formula in a spare area. Keep its original inputs intact so you can compare both the values and the shape after repair.
Which Excel versions does this cover?
The array exercises below cover Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac, editions listed in Microsoft’s #CALC! reference. The LAMBDA exercise is limited to Microsoft 365 and Excel 2024 on Windows/Mac, following its function reference. The web custom-function limit has its own section; it does not establish web availability for every exercise here.
Choose the matching symptom
Microsoft distinguishes these calculation failures:
| Formula or error detail | First thing to check |
|---|---|
| FILTER returns no matching records | Supply its if_empty result. |
| Nested array | Remove an array-within-an-array structure. |
| Array contains ranges | Return a single range rather than an array of range references. |
| Cell contains a lambda, or result is a function | Invoke the function instead of returning its definition. |
| Too many cells in a web custom function | Its references may exceed 10,000 cells. |
| Function failed | An asynchronous operation failed; retry later. |
These categories come from the Microsoft error reference. Use the sections below to check the intended result before making a replacement.
FILTER: no matches is not the same as zero
The third argument of FILTER defines what to return when nothing matches. A missing third argument can produce #CALC!; an empty text value "" is a fallback value, not an empty array. Microsoft FILTER documentation.
For a quick diagnostic, put 125, 160 and 210 in B2:B4. In an unused cell, compare:
=FILTER(B2:B4,B2:B4<100)has no qualifying rows: all three comparisons are false.=FILTER(B2:B4,B2:B4<100,"No matches")should return No matches.=FILTER(B2:B4,B2:B4<150,"No matches")should return 125.
The last check matters: the fallback must not replace a real matching result. Choose a fallback appropriate for the report. Text is clearer than a numeric zero when “nothing found” and “a measured value of zero” have different meanings. For complete multi-column and multiple-condition examples, use the existing FILTER guide.
If an error persists with a fallback present, inspect the condition inputs themselves. FILTER also propagates errors in its include argument; if_empty is not a general error handler. Avoid changing a valid business criterion merely to force a nonempty answer.
Nested arrays: choose one output size
Microsoft’s failing example is =MUNIT({1,2}). Its correction, =MUNIT(2), requests one matrix instead. #CALC! reference.
To inspect the repaired result, enter =MUNIT(2) in E2 on a scratch sheet with E2:F3 clear. The expected two-by-two identity matrix is:
| Cell | Value |
|---|---|
| E2 | 1 |
| F2 | 0 |
| E3 | 0 |
| F3 | 1 |
A unit matrix has ones on the main diagonal and zeros elsewhere. This checks the entire result rather than just E2. Microsoft MUNIT documentation.
This repair changes the request: it does not deliver both a one-by-one matrix and a two-by-two matrix. If both are needed, calculate them separately in nonoverlapping areas.
Array of ranges: make the intended rectangle explicit
Microsoft contrasts =OFFSET(A1,0,0,{2,3}), which fails, with =OFFSET(A1,0,0,2,3). #CALC! reference.
In OFFSET, the fourth argument is height and the fifth is width. The second formula therefore refers to A1:C2, one rectangle two rows high and three columns wide. It is not a request for two alternative heights. Microsoft OFFSET documentation.
As a result check, enter 1, 2, 3 across A1:C1 and 4, 5, 6 across A2:C2. In E5 enter =SUM(OFFSET(A1,0,0,2,3)). The expected total is 21: 1 + 2 + 3 + 4 + 5 + 6. Keep E5 outside the referenced rectangle. Before adopting the repair, confirm that a two-by-three rectangle actually matches your original intent.
LAMBDA: call the calculation
On the supported LAMBDA editions above, =LAMBDA(amount,amount*2) defines a function but does not call it, so it produces #CALC!. Use =LAMBDA(amount,amount*2)(7) to supply the input: the expected result is 14.
The trailing (7) is a call, not another parameter definition. Supplying the wrong number of arguments instead produces #VALUE!, a different diagnosis. See Microsoft’s LAMBDA errors and creation steps. For naming and reusing the calculation, continue with the LAMBDA guide rather than treating the temporary cell formula as the finished reusable function.
Web custom functions and unresolved cases
The documented web limit is more than 10,000 referenced cells in a custom function, not a blanket limit on worksheet formulas or workbook size. Microsoft recommends opening that case in desktop Excel. This is not a claim of free desktop access. Microsoft #CALC! reference.
If you cannot use desktop Excel, preserve the workbook and record the custom function’s name and input ranges for its maintainer. A smaller test input may help diagnose the issue, but silently dropping records would change the answer.
For a long expression, test its components separately in spare cells and record the first failing component. If the repaired formula instead reports #SPILL!, check the output-area troubleshooting guide before deleting anything. For other error codes, use Excel troubleshooting.
Verification and scope
Checked October 23, 2026 against the linked Microsoft documentation. The sample comparisons, matrix, range dimensions and arithmetic were independently checked outside Excel. No native Excel session, screenshot, web custom-function execution or desktop/web parity test was performed. Formulas use English function names and comma argument separators; adapt syntax to your locale. Python in Excel errors are outside this guide’s scope.