Excel errors / #CALC!

#CALC! error in Excel

A formula that returns a list ended up with nothing to return.

#CALC! appears in Excel 2021 and Microsoft 365, with the functions that return lists. Its usual meaning is "the result is empty": FILTER was asked for the rows that meet a condition, and none did.

Excel has no way to show an empty list in a cell, so it shows this error. It rarely means the formula is wrong.

Have a formula that shows #CALC!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.

2 causes of #CALC!, and how to fix each

  1. FILTER found no matching rows

    No region below has sales over 500, so FILTER has nothing to return.

    ABCD
    1RegionSales
    2North100#CALC!
    3South80

    In D2: =FILTER(A2:B3, B2:B3>500) → #CALC!

    How to fix it. Give FILTER a third argument: what to show when nothing matches.

    ABCD
    1RegionSales
    2North100None
    3South80

    In D2: =FILTER(A2:B3, B2:B3>500, "None") → None

  2. A list inside a list

    Each cell of a spilled result holds one value. A formula that tries to put a whole list into each cell, such as a lookup that returns a row for every item of a column, has no way to show that.

    How to fix it. Return one value per cell: look up one column at a time, or join each inner list into a single text with TEXTJOIN.

Common questions

What does #CALC! mean in Excel?

A dynamic array formula could not produce a result that fits in cells. Most often FILTER matched no rows. Click the error and the small warning icon beside it says which case it is, such as "Empty array".

How do I fix #CALC! in FILTER?

Add the third argument, which FILTER shows when no row matches: =FILTER(A2:B50, B2:B50>500, "No results").

Practise it

Functions involved

Other errors