Excel FILTER Function Errors: How to Diagnose and Fix Them

Errors & fixes · Updated November 19, 2026

Most Excel FILTER errors come from a criteria range that does not match the data range, no rows meeting the criteria, or a blocked spill area. Check the error shown in the cell, confirm the formula’s ranges have matching dimensions, and make room for the results; then use the optional third argument to handle a legitimate no-match result.

What the FILTER function does

FILTER returns the rows or columns from an array that meet a condition. Unlike the filter buttons on the Data tab, it places a formula in a worksheet cell and spills the matching results into neighboring cells. The formula is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Earlier versions of Excel do not support the FILTER function.

The syntax is =FILTER(array,include,[if_empty]). The array is the data to return, include is a TRUE/FALSE condition for which rows or columns to keep, and if_empty is an optional value to return when there are no matches. For example, with product names in A2:A100, categories in B2:B100, and status in C2:C100, this returns the rows marked Open:

With records in A2:C100 and status in C2:C100, use:

=FILTER(A2:C100,C2:C100="Open","No matching rows")

The formula returns all three columns for each row whose status is Open. If none are Open, it displays No matching rows rather than a #CALC! error. The formula needs only one entry in the destination cell; do not copy it down as though it were an ordinary single-cell formula.

Start by identifying the error

The displayed error is a useful clue, but it does not always identify the underlying cause by itself. Check both the formula and the cells where its results are meant to appear. These are the common messages:

  • #CALC! often means no rows matched and the formula has no usable if_empty value. It can also arise when a calculation inside the formula produces an unsupported empty array.
  • #SPILL! means Excel cannot place the results in the required cells. Something may be occupying the spill area, or the formula may be in a location that does not support a dynamic array result.
  • #VALUE! commonly points to incompatible dimensions: the include condition does not have the same number of rows or columns as the array being filtered.
  • #NAME? can mean Excel does not recognize FILTER, for example because the installed version does not support it or the function name was mistyped.
  • #REF! can indicate a broken reference. A FILTER formula linked to a different workbook can also return #REF! if the source workbook is closed.
  • #N/A or another error may be coming from the source data or from a calculation used to build the include condition. FILTER can pass errors in its inputs through to the result.

If Excel shows a different error, click the cell and inspect the formula bar. Look for errors in the source cells as well as errors in the FILTER expression; the function cannot reliably filter around an error that its criteria calculation encounters.

Check Excel version and formula entry

If Excel displays #NAME? for a correctly spelled FILTER formula, first check which version is installed. FILTER is included with Microsoft 365 and Excel 2021 or later, but it is not available in Excel 2019 or earlier perpetual releases. In Microsoft 365 for Windows, choose File > Account to see product information and available updates. If a workbook is shared with someone using an older version, that person may see a compatibility problem even when the formula works for you.

Also verify that the formula begins with an equals sign, uses the function name FILTER, and separates arguments with the list separator expected by that installation. In the standard English-language Excel setup, the separator is a comma. Some regional settings use semicolons, so a formula copied from another locale may need its separators adjusted. This is a regional setting difference, not a different FILTER function.

Dynamic array formulas do not need Ctrl+Shift+Enter. Enter the formula normally and press Enter. On Windows, F2 edits the active cell; on Mac, use Control+U to edit the active cell. The ribbon commands discussed here are available in current Microsoft 365 desktop Excel; Excel for the web has a similar grid experience, while some account and application settings are located differently.

Match the dimensions of array and include

The include condition must correspond to the dimension being filtered. If the array has 99 data rows, the row condition must also have 99 rows. A frequent mistake is including a header in one range but not the other, or using an entire-column reference for one range and a smaller range for the other.

With headers in row 1, product details in A2:C100, and status in C2:C100, this uses aligned ranges:

=FILTER(A2:C100,C2:C100="Open","No matching rows")

Both ranges cover rows 2 through 100, so there is one status value for every record. If the return range were A2:C100 but the condition were C2:C99, the dimensions would not match and Excel would return an error. Adjust the range endpoints together, and check that a copied formula has not shifted one range differently from the other.

For an AND condition, multiplying Boolean tests keeps rows only when both tests are TRUE. With names in A2:A100, regions in B2:B100, and status in C2:C100, this returns records in the West region that are Open:

With records in A2:C100, regions in B2:B100, and statuses in C2:C100, use:

=FILTER(A2:C100,(B2:B100="West")*(C2:C100="Open"),"No matching rows")

For an OR condition, addition keeps rows when either test is TRUE. With the same ranges, this returns rows from either the West or East region:

With records in A2:C100 and regions in B2:B100, use:

=FILTER(A2:C100,(B2:B100="West")+(B2:B100="East"),"No matching rows")

Use parentheses around each comparison so the logic is clear. For more complicated criteria, test each comparison in an empty worksheet column first. That makes it easier to see whether the condition is producing the TRUE and FALSE values you expect.

Handle no-match results without hiding real problems

When no records match, FILTER returns #CALC! if its optional if_empty argument is omitted. That is not necessarily a broken formula: it can simply mean the selected value does not exist in the current data. Add a third argument when a blank, message, or other fallback is appropriate.

With names in A2:A100 and departments in B2:B100, this displays a clear message if no employee belongs to Sales:

=FILTER(A2:B100,B2:B100="Sales","No matching employees")

If there are no Sales records, the result is No matching employees. To show a visually blank result instead, use an empty text string as the third argument:

With names in A2:A100 and departments in B2:B100, use:

=FILTER(A2:B100,B2:B100="Sales","")

A blank-looking cell is not the same as a zero-row result; Excel returns the specified empty text value. Choose a message during troubleshooting so an unexpected lack of matches is visible. Once the formula is understood, you can decide whether a blank is better for the finished report.

Clear a blocked spill range

A FILTER formula can return multiple cells, so Excel needs an unobstructed spill area. If any destination cell contains a value, a space, or another formula, Excel may display #SPILL!. Select the error cell and, where available, use the error indicator to identify the obstruction. Clear the occupied cells or move the formula to an empty area. Check for merged cells in the intended output area too; merged cells can prevent a dynamic array from spilling.

Dynamic array results cannot spill inside an Excel table. If the formula is in a table column and is meant to return several rows, put it in a normal worksheet range outside the table. The table itself can remain the source data. You can also resize or move nearby content to provide enough room for the expected results.

Spill ranges can grow or shrink when the source data changes, so leave space below and beside the formula. Do not type notes or totals into cells where future results may appear. If the output is intended for a formatted report, format the destination cells rather than converting the result area into a table that blocks spilling.

Check source data, references, and workbook links

When the formula appears structurally correct, inspect the values it uses. A condition for Open will not match a cell containing Open followed by a space, a different status such as In progress, or a formula error. Extra spaces and inconsistent spelling can make a valid FILTER formula appear to return the wrong result or no result. Compare a few source cells directly and check whether the intended criterion is actually present.

Errors in the include expression matter too. For example, if a criterion relies on a lookup that returns #N/A for some rows, FILTER may display that error rather than simply treating the row as a non-match. Correct the upstream issue or deliberately handle it in the criteria calculation. Avoid wrapping the entire FILTER formula in IFERROR before diagnosing it: that can replace a useful #SPILL! or #VALUE! clue with a misleading blank.

References to another workbook deserve special attention. Dynamic arrays linked between workbooks can require both workbooks to be open; if the source workbook is closed, a linked FILTER formula may return #REF!. If the data must work when the source file is closed, consider importing or copying the needed data into the current workbook, or use a workflow designed for refreshable external data rather than relying on a live spill formula.

FILTER function versus Excel’s Filter command

The name causes a common misunderstanding: the FILTER worksheet function is not the same as the Filter command on the ribbon. The function creates a formula-driven result in a separate spill range. The ribbon filter hides rows in the existing list so that only rows meeting the selected criteria are visible; it does not generate a separate dynamic array.

To use the built-in filter on a list, select a cell in the data and choose Data > Filter. Use the drop-down in a header to select values or set criteria. This is often simpler for manually exploring a list, and it avoids formula spill errors. Use FILTER when you want a live, formula-based extract that updates as source values change or that can feed another formula.

These tools can be used in the same workbook, but remember that a ribbon filter changes which source rows are visible, while a FILTER formula calculates its output from the source range. A formula does not automatically mean “only the rows currently visible.” If you need a filtered extract based on a fixed condition, make that condition explicit in the formula.

A practical troubleshooting order

Work through the checks in this order to find the cause without masking it:

  1. Read the exact error and confirm that the installed Excel version supports FILTER.
  2. Check that array and include ranges start and end on matching rows or columns.
  3. Test whether the criteria actually match any source values; add a temporary if_empty message if needed.
  4. Inspect the include calculation and source cells for errors, extra spaces, or inconsistent labels.
  5. Clear the output area, unmerge any cells there, and move the formula outside an Excel table if necessary.
  6. If the formula refers to another workbook, open the source workbook and check whether the link is broken.

After fixing the cause, verify the result against a few source rows rather than assuming that a disappearing error means the answer is correct. A formula that returns an empty string can look successful while matching no records, so a diagnostic message is useful until the criteria have been confirmed.

Frequently Asked Questions

Why does FILTER return #CALC! when there are matching values?
Check whether the condition and returned array cover the same records and whether the values truly match, including spaces and spelling. Also inspect the include expression for errors. If a simple exact-match formula still fails, test the condition in a helper column to see which rows evaluate as TRUE.

How do I fix #SPILL! in a FILTER formula?
Clear the cells in the output area, check for merged cells, and make sure the formula is not trying to spill from inside an Excel table. Then enter the formula in a normal range with enough empty space for the results.

Can FILTER return several columns and rows?
Yes. Set array to the full block of columns you want returned and use an include condition with one TRUE or FALSE result per row. For example, filtering A2:C100 using a condition in C2:C100 returns the matching rows across all three columns.

Why does Excel say FILTER is an unrecognized function?
FILTER requires Microsoft 365 or Excel 2021 and later. If the workbook is opened in an older release, that version may not calculate the function. Check the application version and consider a compatible alternative if recipients use older Excel.

Does FILTER work inside an Excel table?
A FILTER formula can use a table as a data source, but its multi-cell result cannot spill inside a table. Place the formula in a regular worksheet range outside the table and leave its spill area clear.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.