#CALC! Error in Excel: What It Means and How to Fix It

The #CALC! error in Excel means a formula asked for an array result that Excel couldn't calculate. Nine times out of ten, the formula is FILTER and nothing matched, so the result is empty. This guide explains what this calc error means in Excel, shows the one-argument fix for FILTER, and covers the less common causes.

What does #CALC! mean in Excel?#

#CALC! is a calculation error for array formulas. An array formula returns a list of values instead of one value, and in Microsoft 365 and Excel 2021 those values spill into the cells below and to the right.

Excel shows #CALC! when it can't produce that list. It came with dynamic arrays, so you only see it in Microsoft 365, Excel 2021 and later. It is different from #SPILL!, which means the result exists but has no room to spill. The article on fixing the #SPILL! error covers that one.

There are three main causes:

The examples use this order list in A1:C7:

Cause 1: FILTER finds no matching rows#

When no row meets FILTER's condition, the result is an empty array, and Excel can't put "nothing" in a cell. So it shows #CALC!. This formula works, because three orders are East:

=FILTER(A2:C7,B2:B7="East")

It spills orders 1001, 1003 and 1006 with their regions and amounts. Change the region to one that isn't in the data, and the error appears:

=FILTER(A2:C7,B2:B7="North")

There are no North orders, so the result is #CALC!. The formula isn't wrong. It just has nothing to show. This often happens when the condition comes from a drop-down cell and someone picks a value with no data yet.

How to fix #CALC! with FILTER's if_empty argument#

FILTER has a third, optional argument called if_empty. Whatever you put there is shown when nothing matches:

=FILTER(A2:C7,B2:B7="North","No orders")

Now the cell shows No orders instead of the error. You can use any text, "" for a blank-looking cell, or a number such as 0.

The syntax is =FILTER(array, include, [if_empty]). The FILTER function guide explains each argument, and the article on the FILTER function with several conditions shows how to combine criteria with * and +.

You could wrap the formula in IFERROR instead, but that hides every error, not just the empty case. If your ranges are different sizes, FILTER returns #VALUE!, and IFERROR would hide that real mistake too. The if_empty argument only covers "nothing found", so it's the safer choice.

How to stop #CALC! inside SUM, COUNT and other formulas#

When FILTER sits inside another function, the #CALC! passes through to the outer formula. For example, to total the West orders:

=SUM(FILTER(C2:C7,B2:B7="West"))

The result is 490 (400 + 90). But for North, SUM receives #CALC! and returns #CALC! too. Give FILTER a 0 as its if_empty value and the total becomes 0:

=SUM(FILTER(C2:C7,B2:B7="North",0))

Watch out when counting. =ROWS(FILTER(A2:A7,B2:B7="North","")) returns 1, not 0, because the if_empty text is one cell. For counts, =COUNTIFS(B2:B7,"North") is simpler and correct. In the same way, for a single total, SUMIFS often does the job without FILTER at all.

SORT, UNIQUE and other array functions wrapped around an empty FILTER also return #CALC!. Put the if_empty argument on the inner FILTER and the whole chain works.

Cause 2: a nested array (an array inside each cell)#

Excel can return a list of values, but it can't put a list inside each cell of another list. That's called a nested array, and it gives #CALC!.

This happens with functions that work row by row, such as BYROW and MAP. Say A2:A4 hold text like "red,blue". This formula asks for a split list for every row:

=BYROW(A2:A4,LAMBDA(r,TEXTSPLIT(r,",")))

Each row would need several cells, so Excel returns #CALC!. Fix it by making the inner calculation return one value per row, such as a count or a joined string, or by splitting all the text in one step instead of row by row.

Cause 3: a LAMBDA that is never called#

A LAMBDA is a custom function you write with a formula. Typed into a cell on its own, it has no values to work on, so Excel shows #CALC!:

=LAMBDA(x,x*2)

Add the values in brackets straight after it to test it: =LAMBDA(x,x*2)(5) returns 10. To reuse it, save it as a name in Formulas > Name Manager and call it like a normal function. LAMBDA, BYROW and MAP need Microsoft 365 or Excel 2024.

How to check for #CALC! before it appears#

A few habits stop #CALC! from surprising you, or anyone else who opens the file:

• Always give FILTER an if_empty value in reports, dashboards and drop-down driven sheets.
• Test with a value that has no data. Type a region or name that doesn't exist and check the result looks sensible.
• Know the other errors. #CALC! is about empty or impossible arrays. A #DIV/0! after FILTER usually means you divided by a count of zero; see how to fix #DIV/0!.
• Learn how spilling works. The guide to dynamic arrays in Excel explains spill ranges, and returning several lookup results at once shows FILTER as a lookup.

Practise it#

Practise the FILTER function in a live spreadsheet: you'll filter rows by one and two conditions and add an if_empty message, and each formula is checked as soon as you press Enter. Start with the North orders exercise on this page.