Excel FILTER Function Not Working? Fix Common Causes

If Excel’s FILTER function is not working, first confirm you are using Microsoft 365 or Excel 2021 or later, then check that the include range matches the data range and that empty cells are available for the results to spill. The error message—such as #NAME?, #CALC!, or #SPILL!—usually points to the cause.

How the FILTER function works

FILTER returns rows or columns from a range that meet a condition. Unlike the drop-down filters on the Data tab, it creates a formula result in the worksheet. That result can update automatically when the source values or the condition change, which makes FILTER useful for live reports and summaries.

Its syntax is =FILTER(array,include,[if_empty]). The array is the source data, include is a TRUE-or-FALSE test for each row or column, and if_empty is an optional value to show when nothing matches. The include test must have the same number of rows as the array when filtering rows, or the same number of columns when filtering columns.

For example, with product names in A2:A10, regions in B2:B10, the complete records in A2:C10, and a region to find in F2, enter this in an open cell:

=FILTER(A2:C10,B2:B10=F2,"No matching rows")

If F2 contains West, the formula returns the rows whose region is West. If there are no such rows, it displays No matching rows. The formula result can occupy multiple cells; Excel calls this a spilled array.

Check your Excel version and function name

FILTER is available in Microsoft 365 and Excel 2021 or later. It is not available in Excel 2019, Excel 2016, or older perpetual versions. If the formula displays #NAME?, the function may not be supported by that installation, or the name may be misspelled. Check File > Account in Windows Excel to identify the product and version. On Mac, check Excel > About Microsoft Excel.

In Excel for the web, FILTER is supported when the workbook is opened in a compatible Microsoft 365 environment. If a workbook containing FILTER is shared with someone using an older desktop version, that person may see an unsupported-function error rather than the dynamic results. Updating Excel or opening the file in Excel for the web can help, but an older version cannot calculate the function locally.

Also distinguish the worksheet function FILTER from ordinary column filters. To filter a table manually, use Data > Filter and select values from the column drop-down. That feature hides rows in place; FILTER writes matching records to a separate spill range. Applying one does not repair the other.

Fix criteria and range-size problems

A frequent cause of an error is a mismatch between the number of rows in array and include. If the source covers A2:C10, the row condition should generally cover rows 2 through 10 as well. A condition over B2:B9 has only eight entries while the source has nine rows, so Excel cannot pair every record with a test.

Check the condition itself, too. Text comparisons may be affected by extra spaces or inconsistent labels, and a number stored as text may not behave like a numeric value. Confirm that the condition cell contains the value you expect, and inspect a few source entries for trailing spaces, spelling differences, or unexpected blanks.

For multiple conditions, multiplication represents AND: both tests must be true. With records in A2:C10, regions in B2:B10, sales amounts in C2:C10, the desired region in F2, and the minimum amount in G2, use:

=FILTER(A2:C10,(B2:B10=F2)*(C2:C10>=G2),"No matching rows")

For a record to appear, its region must equal F2 and its amount must be at least the value in G2. Addition can represent OR, meaning either test can be true. Make sure each test still covers the same source rows, and use parentheses around each comparison so Excel evaluates the intended conditions.

Understand common FILTER errors

#SPILL! means Excel cannot place the returned array in the required cells. The spill area may contain values, formulas, merged cells, or another obstruction.
#CALC! commonly appears when no rows match and the optional if_empty argument was omitted. Supply a clear message or another suitable fallback value.
#VALUE! can indicate that the include range has the wrong dimensions, or that an error in the include calculation is preventing Excel from evaluating the condition.
#NAME? can mean that Excel does not recognize FILTER, often because the version does not support it, or because the function name was typed incorrectly.
#REF! may indicate that a referenced range was deleted or otherwise became invalid. Recheck the source and condition references.

Do not assume every blank-looking result is an error. If the formula uses an empty string as its if_empty value, it may return a visually blank cell when there are no matches. Use a message such as No matching rows while diagnosing the formula, then decide whether a blank is preferable for the finished report.

Resolve #SPILL! and output placement issues

FILTER needs enough unoccupied worksheet cells to return every matching row and column. If the formula is in E2 and the result needs three columns and six rows, Excel needs room from E2 through G7. Clear any contents in that area, including a space character or a formula that returns an empty string. Formatting alone usually does not block a spill, but merged cells in the destination can.

Select the #SPILL! cell to see Excel’s obstruction indicator, then clear or move the blocking content. If the result could grow as the source changes, leave extra space rather than clearing only the cells occupied today. A spill can also run into the edge of the worksheet if the formula asks for more rows or columns than remain.

Dynamic array formulas cannot spill from inside an Excel Table. Put the FILTER formula in a normal worksheet cell outside the table, leaving room for the result. The source itself can be a table, and structured references can make source references expand when new records are added, but the formula’s output location must still accommodate a spill.

Only the formula cell at the top-left of a spilled result is directly editable. If you try to change one of the other displayed cells, Excel may report that part of an array cannot be changed. Edit the formula in its anchor cell or adjust the source data instead.

Check calculation, references, and workbook settings

FILTER is a dynamic formula, but results may seem stale if calculation is set to Manual. In Excel for Windows, select Formulas > Calculation Options > Automatic. The Mac ribbon has the same Calculation Options control in the Formulas tab. Press F9 in Windows to recalculate; on Mac, use Fn+F9 on keyboards where the function keys control hardware features. In Excel for the web, calculation is generally automatic, though a workbook’s calculation settings can affect behavior when opened in desktop Excel.

Inspect the formula bar to confirm that the formula points to the intended cells. Copying a formula can shift relative references, so a condition that worked in one location may refer to a different range after it is moved. Use fixed references such as $B$2:$B$10 when a range must stay in place as you copy a formula, or use a table reference when the data should expand as rows are added.

When errors remain, test the condition separately in an unused cell or compare a few rows manually. Check for errors in the source cells, since an error value used in the include test can propagate into FILTER. Also check whether a filter condition excludes all rows by design, rather than indicating that Excel has failed.

Choose between FILTER, AutoFilter, and other options

Choose FILTER when you want matching records displayed in another area and updated by a formula. Choose the Data tab’s Filter command when you want to hide nonmatching rows in the original table and inspect the remaining records interactively. The two approaches solve related but different problems: FILTER creates a separate result, while AutoFilter changes which source rows are visible.

For older Excel versions without FILTER, AutoFilter is often the simplest built-in alternative. Advanced Filter can also copy matching records to another location, but it is a manual operation rather than a dynamic spill formula. If you need a formula-based solution in a legacy version, the design depends on the data and may require older functions; there is no single replacement that behaves exactly like FILTER.

For recurring imports or substantial data-cleaning workflows, Power Query may be more appropriate than a worksheet formula. It can transform and filter data through a refreshable query, but it does not update in the same immediate way as a recalculating FILTER formula. Choose based on whether the task is live worksheet analysis, interactive inspection, or repeatable data preparation.

A practical troubleshooting sequence

Read the exact error in the formula cell and confirm the Excel version supports FILTER.
Check that the source and include ranges have matching row or column counts.
Verify the criteria value and inspect whether any rows should actually match.
If Excel shows #SPILL!, clear the output area and move the formula outside any table.
If there are no matches, add an if_empty value so the result is understandable.
Confirm calculation is Automatic and inspect references for shifted or invalid ranges.

This order separates compatibility and formula-design problems from simple output obstructions. It also avoids changing the source data before confirming what the formula is testing.

Frequently Asked Questions

Why does Excel say FILTER is not a function?
FILTER requires Microsoft 365 or Excel 2021 or later. In older versions, Excel may show #NAME? because it does not recognize the function. Check the installed version and update Excel if your license includes a compatible release.

Why does FILTER return #CALC! when there are no results?
By default, FILTER returns #CALC! when its condition finds no matches. Add the optional third argument, if_empty, to show a message or other fallback instead. First confirm that the criteria are correct if you expected matching records.

Why does FILTER show #SPILL! even though the cells look empty?
A destination cell may contain an invisible space, a formula returning an empty string, or a merged cell. Select the error cell and use Excel’s spill-range indicator to locate the obstruction. Also confirm the output range is not inside an Excel Table.

Can I use FILTER inside an Excel Table?
The source data may be in a table, but a dynamic array formula cannot spill its results from a table cell. Enter FILTER in a regular worksheet cell outside the table and leave enough space for the returned records.

Does the FILTER function hide rows like the Data tab filter?
No. FILTER returns matching records as a formula result in another range. Data > Filter hides rows in the original list without creating a separate spill result. Use the one that matches how you want to work with the data.

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.