Excel FILTER #VALUE! Error: Causes and Fixes

In Excel, a FILTER formula usually returns #VALUE! when its include array is not the same height or width as the range being filtered. Make the criteria range line up with the data range, cell for cell; if you mean the drop-down filters on the Data tab rather than the FILTER function, those are a separate feature and do not use this formula.

What the FILTER error means

FILTER is a dynamic array function: it checks a condition for each row or column and returns the matching items. Its syntax is =FILTER(array,include,[if_empty]). The array is the data to return, and include is a set of TRUE or FALSE values that determines what to keep. For a vertical list, there must be one include result for every row in the array. For a horizontal list, there must be one for every column.

For example, if the data range has five rows but the criteria range has four, Excel cannot match each condition to a corresponding row. That mismatch is a common cause of #VALUE!. The ranges can be in different columns, but their dimensions must correspond. The criterion can also be calculated from other ranges, as long as the resulting include array has the right dimensions.

The function is available in Microsoft 365 and Excel 2021 or later. In older versions of desktop Excel, it may not be available as a worksheet function. Excel for the web supports dynamic array functions as well. The ribbon’s ordinary Filter command is available independently of the formula function.

Check range sizes and alignment

Start by comparing the array and include arguments, not just the text of the condition. In this example, assume A1:D6 contains a header row and five records: column A has order IDs, B has names, C has regions, and D has amounts. The data to return is A2:D6, and the region values are in C2:C6. Both ranges contain five rows, so they align.

With records in A2:D6 and regions in C2:C6, this formula returns the rows for East; the matching order IDs are 101, 103, and 105:

=FILTER(A2:D6,C2:C6="East","No matches")

If the include range is accidentally shortened to C2:C5, the formula has four conditions for five records and returns #VALUE!:

With records in A2:D6 and the mistakenly short region range C2:C5, the dimensions do not match:

=FILTER(A2:D6,C2:C5="East","No matches")

A similar mistake happens when a formula includes the header in one argument but not the other. For instance, A1:D6 contains six rows, including the header, while C2:C6 contains only five. Either use A2:D6 with C2:C6, or make both arguments cover corresponding six-row ranges and construct the include logic to handle the header appropriately. In most cases, excluding the header from both arguments is simpler.

Fix the formula systematically

Use this sequence to isolate the problem before changing more of the workbook:

Click the cell showing the error and inspect the formula bar. Identify the first argument (array) and second argument (include).
Count the rows and columns in both ranges. For vertical filtering, they need the same number of rows; for horizontal filtering, the same number of columns.
Check the starting and ending cells. Two ranges can have the same number of cells and still refer to the wrong records if one starts a row earlier.
Replace one argument temporarily with a clearly aligned range, or select each range in the formula bar to see its outline on the sheet.
Check for inserted or deleted rows that left one reference shorter than the other. Update both ranges, then verify that the results are the intended records.

If a formula is copied down or across, relative references can shift unexpectedly. Use absolute references for ranges that should remain fixed. For example, dollar signs lock the row and column references when a formula is copied. In a dynamic-array formula that is entered once, copying may not be necessary, but fixed references can still help when the source range should not move during edits.

With records in A2:D6 and region criteria in C2:C6, absolute references keep both ranges fixed if the formula is copied:

=FILTER($A$2:$D$6,$C$2:$C$6="East","No matches")

Build multi-condition filters safely

Multiple conditions are often combined with multiplication for AND logic and addition for OR logic. Each condition must produce a same-sized array as the data being filtered. With five records in A2:D6, regions in C2:C6, and amounts in D2:D6, the following keeps East records with amounts of at least 100. In the sample data, it returns orders 101 and 105.

With records in A2:D6, region values in C2:C6, and amounts in D2:D6:

=FILTER(A2:D6,(C2:C6="East")*(D2:D6>=100),"No matches")

Here, multiplication acts as AND: a row is included only when both comparisons are TRUE. Addition acts as OR. For the same layout, this example returns records from either the East or West region:

With records in A2:D6 and regions in C2:C6:

=FILTER(A2:D6,(C2:C6="East")+(C2:C6="West"),"No matches")

A frequent error is to mix full-column references with a bounded range, such as filtering A2:D1000 using a condition built from C:C. The resulting arrays are not the same size, so this is not a safe pairing. Use matching bounded ranges, or use Excel Tables and structured references so the data and criteria expand together. When building more complex logic, check the dimensions of every range involved in the include expression.

Do not confuse #VALUE! with other filter outcomes

Not every problem near a filter formula is a #VALUE! error. The message matters because it points to a different cause:

#CALC! can appear when there are no matches and the optional if_empty argument was omitted. Provide a fallback such as "No matches" to display a message instead.
#SPILL! means Excel cannot place the returned array in the cells it needs. Clear blocking contents from the output area, or move the formula. A spilled formula also cannot spill from inside an Excel Table.
#NAME? may indicate that Excel does not recognize the function name, including when the installed Excel version does not support FILTER.
An error already present in the include calculation may flow into the formula result. Check the source cells and the condition for errors such as #N/A.

The optional third argument handles the no-match case; it does not fix mismatched range sizes or a blocked spill area. Also, an empty-looking result is not always the same as a truly empty cell. If you supply an empty text string as the fallback, the formula returns a blank-looking result, but the formula cell still contains a formula.

Inspect errors in the criteria data

If the ranges have matching dimensions but an error remains, inspect the values used to build the include array. A source cell may contain an error, or a comparison may be based on unexpected data—for example, text that looks like a number rather than a numeric value. Excel’s error-checking tools and direct inspection of the criteria cells can help distinguish a bad source value from a range mismatch.

When it is appropriate to treat errors in the criteria cells as non-matches, IFERROR can convert errors in the condition to FALSE. This should be used deliberately: it hides errors in the criteria calculation, so it is not a substitute for repairing bad source data. With records in A2:D6 and regions in C2:C6:

=FILTER(A2:D6,IFERROR(C2:C6="East",FALSE),"No matches")

In this formula, an error encountered while checking a region becomes FALSE, so that row is excluded. Errors in the returned data range are a separate matter; suppressing a criteria error does not clean or correct errors in the data being returned.

When the problem is the ribbon Filter command

If by “Excel filter” you mean the arrow buttons in the column headers, you are using AutoFilter, not the FILTER worksheet function. Select a cell in the data, then choose Data > Filter in Excel for Microsoft 365 on Windows. Use a column’s arrow to select values or apply text, number, or date criteria. If a value appears missing, check for hidden rows, active criteria on other columns, inconsistent spaces, or values that are stored with different data types.

AutoFilter generally hides rows that do not meet the selected criteria; it does not return a #VALUE! result in a cell. Clearing the criteria from each column can reveal whether another active filter is narrowing the list. On Mac and Excel for the web, the Filter command is also on the Data tab, though ribbon layout and some controls can differ. If a worksheet cell itself displays #VALUE!, inspect its formula rather than the column’s drop-down filter.

Make formulas more robust as data changes

Hard-coded ranges can become misaligned when records are added or removed. One practical option is to format the source as an Excel Table using Home > Format as Table or Insert > Table. Tables expand as rows are added, and structured references can make the formula easier to read. Ensure that the data and the condition still refer to the same table rows; a structured reference does not make unrelated ranges compatible automatically.

Another option is to maintain the data in a clean rectangular block with one header row, no merged cells in the body, and consistent column types. This makes the boundaries easier to review and reduces accidental partial-range references. Avoid selecting whole columns for one argument and a small block for another. If the workbook is shared with people using older Excel releases, confirm their version supports the function; otherwise, they may see an unsupported-function error even if the formula is correct in Microsoft 365.

Frequently Asked Questions

Why does Excel FILTER return #VALUE? The most common cause is that the include array has a different number of rows or columns from the array being filtered. Check that both ranges start and end on corresponding records.

Does FILTER return #VALUE when there are no matches? Usually, no. If no rows match and you omit the optional if_empty argument, Excel generally returns #CALC!. Add a fallback value to show a useful message.

Why does FILTER show #SPILL instead of #VALUE? The formula has results to return, but Excel cannot place them in the required cells. Clear the output area, move the formula, or check whether it is inside a Table, where a dynamic array cannot spill.

Can I use FILTER with entire columns? It is safer to use matching ranges with the same dimensions. Pairing a bounded data block with an entire-column condition can create incompatible array sizes. Excel Tables are often easier to maintain as records grow.

Is Excel’s Data tab Filter the same as the FILTER function? No. Data > Filter adds drop-down controls that hide rows based on criteria. The FILTER function is a worksheet formula that returns matching data into a dynamic array.

Does FILTER work in older Excel versions? The function is supported in Microsoft 365 and Excel 2021 or later. In older releases, use an available alternative such as AutoFilter or a compatible formula approach, depending on whether you need an interactive filter or formula-generated results.

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.