Excel #CALC! error: what it means and how to fix it

1. Give FILTER a value to show when nothing matches

1. Click the cell showing #CALC! and look at the formula in the formula bar.
2. If it uses FILTER with only two arguments, such as =FILTER(A2:C20,B2:B20="East"), add a third argument: =FILTER(A2:C20,B2:B20="East","No results").
3. Use "" instead of "No results" if you want the cell to look blank, or 0 if other formulas need a number.
4. Press Enter. When rows match, the list comes back automatically.
5. If you expected matches, check the criteria for extra spaces, numbers stored as text, or a typo in the text you are filtering on.

2. Find the exact cause from the error tooltip and Evaluate Formula

Evaluate Formula is in desktop Excel for Windows and in current Excel for Mac (Microsoft 365). Excel for the web shows the tooltip but has no Evaluate Formula.

1. Select the #CALC! cell and hover over the yellow warning icon next to it. Read the reason it gives (for example Empty array or Nested array).
2. Go to the Formulas tab, then Formula Auditing, then Evaluate Formula.
3. Click Evaluate repeatedly to step through the formula. Note the step where #CALC! first appears.
4. Fix that part of the formula using the matching fix on this page.

3. Make BYROW, BYCOL, MAP or SCAN return one value per item

Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel for the web

1. Look at the calculation inside the LAMBDA. It must return a single value for each row, column or item.
2. If you want one summary value per row, wrap the calculation in a function that reduces it to a single value, for example =BYROW(A2:C10,LAMBDA(r,SUM(r))) or TEXTJOIN(", ",TRUE,...) to join several results into one text value.
3. If you really need several values per row, use REDUCE with VSTACK instead, for example =DROP(REDUCE("",A2:A10,LAMBDA(acc,x,VSTACK(acc,TEXTSPLIT(x,",")))),1).
4. Press Enter and check that the results spill without errors.

4. Remove the inner array from functions that expect a single value

1. Find the argument that contains an array constant (values in curly braces like {2,3}) or a whole range where the function expects one number.
2. Replace it with a single value, for example change =MUNIT({1,2}) to =MUNIT(2).
3. For reference functions, pass separate arguments instead of an array, for example change =OFFSET(A1,0,0,{2,3}) to =OFFSET(A1,0,0,2,3).
4. If you need a result for each value in the list, put each one in its own cell or use MAP.

5. Call the LAMBDA instead of returning it

Excel for Microsoft 365, Excel 2024, Excel for the web

1. If the cell has =LAMBDA(x, x+1), add the input values in brackets straight after it: =LAMBDA(x, x+1)(1). This returns 2.
2. If you saved the LAMBDA as a name, call it with its arguments, for example =AddOne(A2), not =AddOne.
3. To save a LAMBDA for reuse, go to Formulas, then Name Manager, then New. Give it a name and paste the LAMBDA into Refers to.

6. Open the workbook in desktop Excel or retry failed functions

1. If you are in Excel for the web and the formula uses an add-in custom function over a large range, choose Editing, then Open in Desktop App.
2. If the error comes and goes, recalculate with F9 (Windows) or Cmd+= (Mac) after a short wait. Asynchronous functions can fail temporarily.
3. For Python cells, reduce the data you pass with xl() (the limit is 100 MB) and check that the Python code returns a supported object.