FILTER Formula Not Working in Excel? Fixes That Work

If the FILTER formula is not working in Excel, first check that your Excel version supports dynamic arrays, the include range has the same number of rows or columns as the data, and the result cells are empty. In Microsoft 365 or Excel 2021 and later, the basic pattern is =FILTER(array,include,[if_empty]); the error or blank result usually points to a specific issue with one of those parts.

How the FILTER formula works

FILTER returns the rows or columns from a range that meet a condition. Its first argument is the data to return, its second is a TRUE/FALSE condition for each row or column, and its optional third argument supplies a value when there are no matches. The result is a dynamic array: Excel places the matching values in the formula cell and spills them into neighboring cells automatically.

For example, assume A1:C5 contains a small sales list, with headers in row 1 and these records in rows 2 through 5: Tea, West, 12; Coffee, East, 8; Tea, East, 5; and Juice, West, 9. With the data in A2:C5 and regions in B2:B5, this formula returns the two West rows:

With product, region, and units in A2:C5, and the region in column B, enter:

=FILTER(A2:C5,B2:B5="West","No matches")

The result is Tea | West | 12 and Juice | West | 9. The formula does not change or rearrange the source data; it displays a filtered result elsewhere. FILTER is available in Microsoft 365 and Excel 2021 or later, including current Excel for the web. Earlier perpetual versions such as Excel 2019 do not support the function and may show #NAME?.

Check the include range and criteria

A frequent cause of an error is a mismatch between the dimensions of the data and the include argument. If the array has four data rows, the include condition must also produce four results. A range that accidentally includes a header, stops early, or extends farther than the data can lead to #VALUE! or an unintended result. For a horizontal data set, the include array must line up across columns rather than down rows.

Check the condition itself, too. Text must match the underlying cell value, including spaces that may be hard to see. A criterion such as “West” will not match “West ” with a trailing space. Numbers stored as text can also behave differently from numeric values, particularly when the condition uses a comparison such as greater than or equal to. Select a suspect cell and inspect the formula bar, or use Excel’s Find and Replace or data-cleaning tools to locate inconsistent entries.

To apply two conditions at once, multiply the TRUE/FALSE tests. With product, region, and units in A2:C5, the region in B2:B5, and units in C2:C5, this returns West records with at least 10 units:

=FILTER(A2:C5,(B2:B5="West")*(C2:C5>=10),"No matches")

In the example, the only returned row is Tea | West | 12. Multiplication represents AND: both conditions must be true. Use addition for OR, where either condition can be true. With the same layout, this returns records from either West or East:

=FILTER(A2:C5,(B2:B5="West")+(B2:B5="East"),"No matches")

Since every row is in one of those regions, it returns all four records. Parentheses matter: they keep each comparison clear and make the logic easier to troubleshoot.

Understand blank results and #CALC!

FILTER returns #CALC! when no rows match and you have not provided the optional if_empty argument. That is not necessarily a broken formula; it can mean the current filter selection has no matching records. Add a useful fallback so an empty result is understandable. In the examples above, “No matches” appears when there are no qualifying rows. If you prefer a visually blank result, use an empty text string, written as two quotation marks, as the third argument.

A result that appears blank may also be caused by the condition matching cells that contain empty strings, spaces, or formulas returning an empty string. Check whether the source cells really are empty. If you want to exclude rows with no product name, make the include condition test that column for nonblank values. Also check whether the output cells use a white font, a custom number format that hides values, or a narrow column that makes the displayed result hard to read.

Fix #SPILL! and formula placement problems

#SPILL! means Excel cannot place the full result in the cells the formula needs. FILTER needs a clear spill area, including cells to the right and below the formula cell, depending on the size of the returned array. Click the error indicator or select the formula cell to see the obstructing range when Excel identifies one.

Clear values, formulas, or invisible spaces from the expected spill area. Even a cell that looks empty may contain a space or a formula returning an empty string.
Unmerge cells in the spill area. A dynamic array cannot spill through merged cells.
Move the formula away from the edge of the worksheet if the result would extend beyond the available rows or columns.
Place the formula outside an Excel Table. Dynamic array formulas cannot spill from inside a table, although the formula can refer to table columns and return results in ordinary worksheet cells.

For example, if a filter returns three columns and several rows, the cells below and to the right of the formula must be available. Moving the formula to a clear section of the sheet is a quick diagnostic: if it works there, the source logic may be fine and the original problem is the blocked spill area.

Use tables and growing data ranges correctly

Ordinary references such as A2:C100 may stop including new records when you add rows below row 100. That can make a formula seem inconsistent as the list grows. Excel Tables expand automatically when records are added, and structured references can make formulas easier to read. Give the table a name such as Sales and use its column names in the condition.

With an Excel Table named Sales that has Product, Region, and Units columns, and a region selector in G1, enter this formula in a normal worksheet cell outside the table:

=FILTER(Sales,Sales[Region]=G1,"No matches")

This returns the full matching table rows and updates as table records are added. If G1 contains West, the example returns the Tea and Juice records. If the formula is entered inside the table, it cannot spill there; move it outside the table and leave room for the result. Also verify the table name and column spelling if Excel shows #NAME?.

Check versions, separators, and calculation settings

If Excel displays #NAME? immediately, confirm the function is spelled FILTER and that the installed Excel edition supports it. FILTER is included in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or older perpetual releases. A workbook created with a newer version can contain a function that an older copy of Excel cannot calculate. Updating Excel may help if your license includes Microsoft 365; otherwise, use a compatible alternative such as the built-in AutoFilter or a helper-column approach.

In some regional settings, Excel expects semicolons rather than commas between formula arguments. If Excel rejects a formula when you enter it, use the list separator configured for your system. The examples here use commas, as in the standard English-language syntax. This is a separator difference, not a different FILTER function.

Calculation mode is another check when results do not update after source values change. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. If the workbook is set to Manual, formulas may retain old results until recalculated. Excel for Mac has calculation settings under Excel > Settings (or Preferences, depending on the release) > Calculation. Excel for the web generally recalculates automatically. Recalculate after changing the setting and verify that the source cells themselves contain the values you expect.

Search text, dates, and other common criteria

Exact equality is not the only possible condition. To find a word inside a product name, combine SEARCH with ISNUMBER. SEARCH is case-insensitive, so it can find “tea” in “Green Tea”. With product names in A2:A5 and the full records in A2:C5, use:

=FILTER(A2:C5,ISNUMBER(SEARCH("tea",A2:A5)),"No matches")

This returns Tea | West | 12 and Tea | East | 5 in the sample data. FILTER itself does not interpret asterisks as wildcard characters in a basic equality test; SEARCH is one way to build a contains-text condition. If the text may be absent from some cells, ISNUMBER converts SEARCH’s found/not-found result into a TRUE/FALSE condition that FILTER can use.

Date criteria work best when the source cells contain real Excel dates rather than date-looking text. A date is stored as a serial number and displayed according to its number format. If a date filter behaves unexpectedly, check the source cell format and whether the values are actual dates. You can compare against a cell containing a date rather than typing a date string into the formula, which avoids ambiguity between regional date formats. Similarly, for numeric thresholds, ensure the source values are numbers, not text that only looks numeric.

FILTER compared with AutoFilter and older alternatives

The FILTER function and the Data tab’s AutoFilter feature both help show selected records, but they work differently. AutoFilter hides rows in the original list according to dropdown selections. FILTER creates a separate, formula-driven result that can feed another formula, chart, or report while leaving the source list visible. Use AutoFilter when you need to inspect or edit the original records; use FILTER when you want a live extract in another location.

For versions without FILTER, AutoFilter is often the simplest option. A helper column can also mark rows that meet a condition, after which older lookup or extraction formulas can return them, but that setup takes more work and is less convenient to maintain. Copying filtered rows manually may be acceptable for a one-time task, but it will not update when source data changes. Do not confuse FILTER with the older database function DGET or with Advanced Filter: those are separate tools with different behavior.

Frequently Asked Questions

Why does my FILTER formula show #SPILL?
Excel cannot place all returned cells because something blocks the spill range, a cell is merged, or the formula is inside a Table. Clear or move the obstruction, or place the formula in an ordinary worksheet area with enough room.

Why does FILTER return #CALC! when there are matching records?
Check that the condition references the correct column and rows, and that the source values match the criterion exactly. Spaces, text-versus-number differences, or a mismatched range can prevent an apparent match. Add an if_empty value while diagnosing to distinguish no matches from other errors.

Can I use FILTER in Excel 2019?
No. FILTER is a dynamic array function available in Microsoft 365 and Excel 2021 or later. In Excel 2019 or older, use AutoFilter or build a compatible helper-column solution.

Why does FILTER not update after I change the source data?
Check that the formula’s ranges include the changed cells and that calculation is set to Automatic. A fixed reference such as A2:C100 will not include a new row below row 100; using an Excel Table as the source can make the range expand with new records.

Can FILTER return matching columns instead of rows?
Yes. The array can be arranged horizontally, provided the include argument has the same number of columns as the array. The condition must line up with the dimension you want Excel to filter.

Why does Excel reject commas in my FILTER formula?
Your regional settings may use semicolons as the formula argument separator. Replace the separators with the one Excel expects; the function name and its arguments remain the same.

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.