In Excel, FILTER most often returns #CALC! because no rows meet its criteria and the optional [if_empty] argument was omitted. Add a fallback value as the third argument, or adjust the criteria so at least one row matches; if you mean the Data tab’s filter controls rather than the formula, those controls do not normally produce a worksheet #CALC! error.
What the FILTER error means
FILTER is a dynamic-array function: it evaluates a condition for each row or column, then returns the matching values into nearby cells. It is available in Microsoft 365 and Excel 2021 or later. When the condition finds no results, Excel cannot return an empty array. If you leave the optional third argument out, the formula returns #CALC!.
This is different from a formula returning a blank cell. An empty result set contains no values at all, while an empty string is a text value that looks blank. Excel’s worksheet grid does not support an empty array as a formula result, so FILTER needs an alternative value when there are no matches.
The name can also cause confusion: Excel has both the FILTER function and the filter buttons used to hide nonmatching rows. A filter applied from the Data tab changes which rows are displayed; it does not create a #CALC! formula result. If you see that error in a cell, inspect the formula in the formula bar.
How FILTER evaluates its arguments
The function’s syntax is FILTER(array, include, [if_empty]). The array is the data to return, and include supplies TRUE or FALSE values identifying which rows or columns to keep. The optional if_empty value tells Excel what to return when nothing qualifies.
The include condition must line up with the data being filtered. For a row filter, it should have the same number of rows as the source array. For a column filter, it should have the same number of columns. Multiplying two TRUE/FALSE conditions applies an AND test; adding them applies an OR test. The expressions must still produce arrays with compatible dimensions.
Because the result can contain several cells, Excel spills it into adjacent empty cells. A blocked spill area normally produces #SPILL!, not #CALC!. That distinction is useful: #CALC! usually points to a calculation or empty-result issue, while #SPILL! means Excel cannot place the output where it belongs.
Check whether any rows actually match
Before changing a formula, confirm that its criteria match the underlying data. A criterion may look right but differ from the values in the source because of an extra space, a different spelling, a number stored as text, or a date that includes an unexpected time. Also check for a reference that moved after the formula was copied.
For example, a dropdown may display “West” while the source contains “West ” with a trailing space. Excel’s equality test treats those strings as different. A criterion pointing at the wrong cell can have the same effect: the formula may be valid, but no record qualifies.
- Compare the criterion with a source value by copying and pasting it into the criteria cell.
- Check that the include range starts and ends on the same rows as the array range.
- Look for hidden spaces, inconsistent punctuation, and text-versus-number differences.
- If you combine conditions, test each condition separately before combining them.
Do not treat a no-match result as proof that the source data is missing. First verify the data and the intended business rule. A fallback message can make the result easier to interpret, but it cannot correct a faulty criterion.
Fix the common no-match case
The usual fix is to supply a useful third argument. With item names in A2:A5, regions in B2:B5, units in C2:C5, and the region to find in E2, the formula below returns matching rows or a message if none match. Suppose E2 contains West, and the sample rows are Pencils / West / 12, Folders / East / 8, Markers / West / 20, and Tape / North / 6. The result is the Pencils and Markers rows; if E2 were South, the result would be the message.
Assuming the source data is in A2:C5 and the requested region is in E2, enter:
=FILTER(A2:C5,B2:B5=E2,"No matching rows")
The fallback text should describe the no-match situation rather than conceal a broader problem. An empty string can make a report look cleaner, but it also makes it harder to tell whether the formula found nothing. If downstream calculations depend on the result, choose a fallback that those calculations can handle.
Use multiple criteria without creating a mismatch
A frequent reason for unexpected results is combining criteria incorrectly. For an AND condition, multiply the Boolean tests; a row is included only when both tests are TRUE. For an OR condition, add the tests; a row is included when either test is TRUE. The data ranges used in each test must be the same height as the rows being returned.
Assuming items are in A2:A5, regions in B2:B5, units in C2:C5, the requested region is in E2, and the minimum units value is in F2, this formula returns items meeting both conditions. With E2 set to West and F2 set to 15, the result is Markers, because it is the only West row with at least 15 units.
=FILTER(A2:A5,(B2:B5=E2)*(C2:C5>=F2),"No rows meet both conditions")
If you intend an OR condition instead, replace the multiplication sign between the tests with a plus sign. Be careful not to use OR as a substitute for AND: that change broadens the result and can make a formula appear to work while returning records that do not satisfy the rule you intended.
Investigate spaces and inconsistent source values
Imported data is a common source of criteria mismatches. Ordinary spaces at the start or end of text can interfere with exact matches. Data copied from websites or other systems may also contain a nonbreaking space, which does not always behave like a standard space. Numbers and dates imported as text can likewise fail comparisons that expect numeric values.
For a small dataset, correcting the source cells directly may be simplest. For recurring imports, clean the data in a helper column or in the import process, then filter the cleaned values. Excel’s TRIM removes ordinary extra spaces, but it does not remove every nonprinting character; cleaning may require identifying the specific character in the source. Avoid changing source data blindly, especially when spaces or punctuation have meaning in an identifier.
When values appear identical, test one sample from each source rather than assuming the display tells the whole story. Check the cell’s number format and whether a value is stored as text. For dates, confirm that the criterion and source are real Excel dates, not differently formatted text strings.
Distinguish #CALC! from other FILTER problems
Not every problem with a FILTER formula is a no-match error. The error code and the formula’s behavior help narrow down the cause:
#SPILL!usually means one or more cells in the required output area are occupied, merged, or otherwise preventing the array from expanding. Clear the blocking cells or move the formula.#VALUE!can indicate incompatible dimensions, such as an include range that does not correspond to the rows or columns in the array.- An error such as
#N/Ainside the source data or a condition can be passed through the calculation. Inspect the cells used by both arguments rather than replacing every error with a blank. #NAME?may appear if Excel does not recognize the function name, for example in an older version that does not supportFILTER.
A formula can also return unexpected values without showing an error. For instance, a condition using the wrong comparison operator may match too many rows. Check the returned records against the rule you meant to apply, not just whether the formula stops displaying an error.
Check version, workbook, and display behavior
FILTER is supported in Microsoft 365 and Excel 2021 or later. In older perpetual versions, the function may not be available, so the formula can be treated as an unrecognized name. Excel for the web also supports modern dynamic-array formulas, though the ribbon layout can differ from the Windows desktop app.
On Windows, select the formula cell and inspect the formula bar; press F2 to edit the formula in the cell. On Mac, use the formula bar or press Control+U to edit the active cell, depending on keyboard settings. Keyboard shortcuts can vary with system preferences. On both platforms, clicking a referenced range while editing can help confirm that the formula points to the intended cells.
If a formula was copied from another spreadsheet program or region, check the argument separators. Excel installations using a regional list separator may display semicolons instead of commas. The formulas in this guide use commas, as in the standard English-language syntax specified for Excel. Do not mix separator styles in one formula.
Make filtered reports easier to maintain
For a workbook used repeatedly, turn the source range into an Excel Table with Insert > Table. Table columns expand as records are added, which reduces the chance that a fixed range omits new rows. Dynamic-array formulas spill outside the table; place the formula in a normal worksheet area rather than inside a table column, because spilled formulas cannot spill within a table.
Choose a fallback that makes sense to the people reading the report: a short message for a dashboard, or a clearly defined value for a calculation. Keep the result area clear, and label the criteria cells so users know what the formula is filtering for. If a no-match result is valid, communicate that clearly rather than presenting it as a calculation failure.
When troubleshooting, change one thing at a time: verify the criteria, test the include range, then add the fallback. This makes it easier to distinguish a legitimate empty result from a broken reference or mismatched data type.
Frequently Asked Questions
Why does FILTER return #CALC! when there are no matches?
Excel does not return an empty array from FILTER. If no rows match and you omit the third argument, Excel displays #CALC!. Provide an [if_empty] value such as a message to handle that case.
Does Excel’s Data tab Filter feature cause #CALC!?
Ordinary worksheet filtering from Data > Filter hides rows that do not meet the selected conditions; it does not normally create a #CALC! formula error. Look for a cell formula using the FILTER function if the error appears in a cell.
Why does FILTER return a blank instead of a message?
The formula may use an empty string, such as "", as its third argument, or the matching result itself may contain blank cells. A blank-looking result is not necessarily an empty array. Use a visible fallback message while diagnosing the formula.
Why do matching rows fail to appear even though the text looks the same?
Check for leading or trailing spaces, nonbreaking spaces, text-versus-number differences, and an incorrect criteria reference. Identical-looking values are not always identical underlying values, especially after importing or copying data.
Can I use FILTER in older Excel versions?
FILTER requires Microsoft 365 or Excel 2021 or later. In older versions, use an available alternative such as AutoFilter or a different formula approach compatible with that version.
Related
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
- Excel Data Validation List Search: Setup and Fixes Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- COUNTIF Duplicates in Excel: Examples and Ways to Fix ThemTo count how often a value appears in Excel, enter =COUNTIF($A$2:$A$7,A2) beside your list and fill it down.…
- VLOOKUP Column Index Number Not Working? How to Fix ItIf a VLOOKUP column index number is not working, check that it is a positive number no larger…
- Excel #DIV/0! Error: Causes and FixesExcel’s #DIV/0! error means a formula is trying to divide by zero or by a blank cell. If…
- Excel Cell Green Triangle: What It Means and How to Fix ItA green triangle in the upper-left corner of an Excel cell is an error-checking indicator: Excel has noticed…
- Outlook Sleep Mode Not Working? Fix Notifications and SyncIf Outlook “sleep mode” is not working, first identify what you mean: Outlook has no single sleep-mode switch.…
- Excel Unique Records Only Not Working? Fix the FilterIf Excel’s Unique records only option is not giving the result you expect, check the list range first:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.