#CALC! in Excel: what it means and how to fix it
#CALC! means a newer function such as FILTER returns an empty result: not a single row meets your condition.
Try the first exercises for freeIn the lesson The FILTER function
What does #CALC! mean?
#CALC! means that a formula gives an empty result, and Excel can't show an empty list. You mainly see the error with FILTER, when no row meets your condition.
So your formula isn't broken: nothing was found, that's all. That's why FILTER has a third argument, in which you say what Excel should show if the list is empty, such as No results or an empty text.
When do you get #CALC!?
- You filter on a region that isn't in the list: =FILTER(C2:C6,A2:A6="Cambridge").
- You filter on an amount nobody reaches, such as all orders over 5000.
- Your condition contains a typo, such as Leds instead of Leeds.
- You filter a list that's still empty, because the data only comes in later.
How to fix #CALC!, step by step
This is how to fix the error:
- Click the cell with #CALC! and read the condition in the formula.
- Check that there really are rows that meet it. A typo or one space too many is enough to find nothing.
- If it's normal that sometimes nothing is found, give FILTER a third argument: the text Excel should show then.
=FILTER(C2:C6,A2:A6="Cambridge","No results")
Examples
The error
E1 contains =FILTER(C2:C6,A2:A6="Cambridge"). Cambridge isn't in the list, so the result stays empty.
With a third argument
E1 contains =FILTER(C2:C6,A2:A6="Cambridge","No results"). Now FILTER shows a message instead of the error. Change Cambridge to Leeds, and you get the revenue from Leeds.
Found after all
E1 contains =FILTER(C2:C6,A2:A6="Leeds","No results"). Leeds is in the list: FILTER shows the two amounts, 1200 and 410, underneath each other.
Where it often goes wrong
- The error doesn't mean your formula is wrong: there's simply nothing that meets your condition. Do check the condition for typing mistakes, though.
Practise this in the lesson The FILTER function. The first exercises are free.
Frequently asked questions
Does #CALC! exist in older versions?
No. You only see the error in Excel 365 and in Excel 2021 or later, with the new functions that spill over several cells.