In Excel for Microsoft 365, FILTER usually shows #CALC! when its criteria find no matching rows and the optional [if_empty] argument is missing. Add a suitable fallback—such as "No matching rows"—as FILTER’s third argument, or adjust the criteria if you expected results.
What #CALC! Means in FILTER
FILTER returns the entries from a range or array that meet a condition. If the condition matches nothing, Excel would need to return an empty array. Excel does not currently display an empty array as a normal FILTER result, so the function returns #CALC! unless you tell it what to return instead.
This is different from a spelling mistake in a function name or a missing worksheet reference. In the usual FILTER case, the formula can be valid and the source data can be intact: the problem is simply that the current filter has no matches. The result can change when the source data changes or when the criteria cell is edited, so a formula that worked yesterday may show the error today.
FILTER is a dynamic array function. When matches exist, it spills the matching rows or columns into nearby cells. When no matches exist, there is no result array to spill, which is why the optional fallback matters. FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024.
FILTER Syntax and the Empty-Result Argument
The function’s syntax is FILTER(array, include, [if_empty]). The array is the data to return, include is a TRUE/FALSE test that identifies the rows or columns to keep, and [if_empty] is an optional value to show when there are no matches. Square brackets in the syntax indicate an optional argument; they are not typed into the formula.
For example, with product names in A2:A20 and quantities in B2:B20, use the following formula to return products with a quantity greater than zero. If none qualify, the formula displays a clear message instead of #CALC!.
With product names in A2:A20 and quantities in B2:B20, enter:
=FILTER(A2:B20,B2:B20>0,"No products in stock")
If there are qualifying rows, Excel spills both columns for those rows. If there are none, the result is the single text value No products in stock. Choose wording that makes sense to someone reading the output; the fallback does not have to be an error message.
How to Diagnose the Cause
Start by checking whether the criteria genuinely return any TRUE values. Review the filter condition, the data range, and any criterion cells that the formula refers to. A dropdown selection may have changed, a date may fall outside the requested period, or a value may have been entered with an extra space. What looks like a formula failure can be an unexpected but legitimate zero-match result.
You can test the match count separately. For example, if regions are in C2:C100 and the region to find is in F1, the following formula counts exact matches:
With region names in C2:C100 and the requested region in F1, enter:
=COUNTIF(C2:C100,F1)
A result of 0 confirms that no exact match exists in that range. If the count is positive but FILTER still errors, check that the FILTER formula uses the intended ranges and that its include argument has the same number of rows as its data array. If the data array covers rows 2 through 100, for example, a row-based include range should cover the corresponding rows 2 through 100 as well.
Also inspect the referenced cells for existing errors. FILTER can pass through errors in its source data or criteria; adding an empty-result fallback does not repair an error already present in the inputs. Look for cells containing errors and confirm that the include expression evaluates as intended.
Ways to Fix or Handle No Matches
The best fix depends on whether zero results are normal or signal a problem. If an empty result is a routine possibility, use the third argument. If you expected a match, investigate the criteria before choosing a fallback that could conceal a data issue.
- Show a message: Use text such as
"No matching rows"when a reader should understand why no data appears. - Show a blank-looking result: Use
""as the third argument if an empty-looking cell is appropriate. It is still a text result, not a truly empty array. - Return a neutral value: A value such as
0may suit a calculation, but only if zero cannot be mistaken for a real matching result. - Correct the criteria: If matches should exist, verify spelling, spaces, date limits, comparison operators, and the selected source range.
For example, with order records in A2:D500, region values in C2:C500, and a selected region in G1, the formula below returns all matching records and a message when none match.
With order records in A2:D500, regions in C2:C500, and the requested region in G1, enter:
=FILTER(A2:D500,C2:C500=G1,"No orders for this region")
Do not automatically wrap every FILTER formula in IFERROR. IFERROR can hide errors caused by bad references, invalid calculations, or errors in the source data, not just the no-match situation. FILTER’s own third argument is more precise when the issue is an empty result.
Multiple Criteria and Range Alignment
FILTER can combine conditions. Multiplying logical tests works like AND: a row must satisfy both tests. Adding them works like OR: a row can satisfy either test. The include expression still needs to produce a TRUE/FALSE result for each row being considered.
For example, suppose A2:D100 contains records, column B contains departments, column D contains status, and G1 and H1 hold the requested department and status. This formula returns rows meeting both conditions:
With records in A2:D100, departments in B2:B100, statuses in D2:D100, the requested department in G1, and status in H1, enter:
=FILTER(A2:D100,(B2:B100=G1)*(D2:D100=H1),"No matching records")
For an OR condition, such as finding records in either of two departments, use addition between the tests. Make sure the criteria ranges have compatible dimensions with the array being filtered. A mismatched range size is a different problem and can produce another error, such as #VALUE!; adding an empty-result message will not fix that structural mismatch.
Why Other FILTER Errors Are Different
Not every problem with a FILTER formula is #CALC!. Distinguishing the error helps you choose a useful fix instead of masking the actual cause.
#SPILL!: FILTER has results to return, but something blocks the output area. Clear the obstructing cells or move the formula. A spilled result also cannot expand inside an Excel table; put the formula outside the table if it needs to spill.#VALUE!: The include argument may not match the size or shape of the array, or another part of the calculation may be invalid. Compare the range boundaries carefully.#NAME?: Excel may not recognize the function name, which can happen in a version that does not support FILTER or when the formula is entered incorrectly.- An unexpected empty result: FILTER may be working correctly, but the condition is too restrictive or does not match the data as stored.
A useful distinction is that #CALC! in a basic FILTER formula often means no results are available, while #SPILL! means results exist but cannot occupy their destination cells. Check the formula’s actual error and Excel’s error message before changing the function.
Practical Checks for Data and Criteria
When a match seems to be present but FILTER finds none, check how the values are stored. Text that looks like a number may not match a numeric criterion. Dates can also be stored as text, and a criterion that includes a time component may not match a date value exactly. Make sure the values being compared are genuinely comparable.
For text, look for leading or trailing spaces, different punctuation, and spelling differences. Exact comparisons such as C2:C100=F1 do not treat a longer text value as an exact match. If you need a partial-text search or case-sensitive matching, the include condition has to be designed for that requirement; the basic equality test is not a general-purpose text search.
For dates, confirm that the requested period covers the values you intend to include. A date-range filter often needs a lower-bound test and an upper-bound test combined with multiplication. Remember that a date cell can include a time fraction even if its number format displays only the date. That can affect exact equality comparisons.
Finally, check for accidental omissions in the source range. A formula using A2:D100 will not include a new record entered on row 101. Converting data to an Excel table can make expanding source data easier to manage, though the spilled FILTER formula itself should be placed in a normal worksheet range rather than inside a table.
Frequently Asked Questions
Why does FILTER return #CALC! when there are no matches?
Excel cannot return an empty array as an ordinary FILTER result. If no rows satisfy the include condition and the optional third argument is omitted, FILTER returns #CALC!. Add an appropriate [if_empty] value to handle that case.
How do I make FILTER show a blank instead of #CALC!?
Add "" as FILTER’s third argument. With names in A2:A50 and categories in B2:B50, for instance, a formula can return matching names and display a blank-looking value when there are no matches. That value is an empty text string, not a genuinely empty array.
Does IFERROR fix the FILTER #CALC! error?
It can hide the displayed error, but it may also hide unrelated problems in the formula or source data. When the only issue is that no rows match, use FILTER’s [if_empty] argument. Reserve IFERROR for cases where you intentionally want to handle a wider range of errors.
Why does FILTER show #SPILL! instead?#SPILL! means Excel cannot place the returned array in the required cells, often because cells in the spill area are occupied. Clear those cells or move the formula. It is not the same as an empty FILTER result.
Can I use FILTER in older Excel versions?
FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Versions that do not support dynamic arrays may not recognize it. Excel for the web and Excel for Mac support FILTER in current Microsoft 365, although some interface details can differ from Windows.
Choosing a Useful Fallback
A fallback should clarify the meaning of no results without hiding a problem that needs attention. Use a short message for reports and dashboards, a blank-looking result when empty output is visually preferable, or a numeric value only when it has an unambiguous meaning. If a result is required for a downstream calculation, consider whether that calculation should treat “no matching records” as zero, as missing data, or as a separate status.
The central check is simple: determine whether the criteria should match anything. If not, provide FILTER’s third argument. If they should, check the criteria and source values, confirm the ranges align, and distinguish an empty result from a spill blockage or another formula error. That approach fixes the immediate display while preserving the information needed to diagnose the workbook.
Related
- Access vs Excel: Pros, Cons, and Best Uses Compared
- Accidentally Didn’t Save an Excel File? Recover It
- Access Database vs Excel Spreadsheet: Which to Use?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Wrapping Text? Fix the CellIf Excel is not wrapping text, select the affected cell or cells and choose Home > Wrap Text.…
- Excel Cell Background Color: How to Change and Fix ItTo change an Excel cell background color, select the cell or range, then choose Home > Font >…
- Excel UNIQUE Not Working? Causes and FixesExcel’s UNIQUE function usually stops working because the installed Excel version does not support it, the formula has…
- Excel Protected Sheet Not Sorting? How to Fix ItIf Excel will not sort a protected sheet, the usual fix is to unprotect the sheet, unlock the…
- Google Sheets Filter Range: Extend It and Fix Common IssuesYes—when a Google Sheets filter does not include new rows, the reliable fix is to remove it and…
- Fix VLOOKUP Not Working: Causes and SolutionsTo fix VLOOKUP in Microsoft Excel for Microsoft 365, check that the lookup value is in the first…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.