With records in A2:C20 and region names in C2:C20, use =FILTER(A2:C20,C2:C20="West","No matches") to return West-region rows and display a message if none match. If FILTER shows #N/A, the error usually comes from a value in the source data or criteria; no matches normally produce #CALC! when you omit FILTER’s optional empty-result argument.
What “Excel filter na error” can mean
The phrase can refer to two different Excel features. FILTER, written as a worksheet function, creates a formula-driven result from rows that meet a condition. The ordinary Filter command, found on the Data tab, hides rows in place so you can inspect selected records. Neither feature normally invents #N/A simply because a search has no results: the dynamic-array function has its own empty-result behavior, while the dropdown filter displays values and errors already present in the column.
That distinction matters when choosing a fix. If the error appears in a cell containing a formula that starts with FILTER, inspect the formula’s source array and include condition. If you see #N/A in a table column or in the ordinary filter dropdown, inspect the formulas or imported values in that column. Filtering can hide rows, but it does not repair an error in the underlying cell.
How FILTER handles no matching rows
FILTER takes an array to return and an include argument that identifies which rows to keep. In the opening example, A2:C20 is the data to return, while C2:C20="West" creates a TRUE/FALSE test for each row. The ranges need matching dimensions: a row-based include test must have one result for every row in the array. The third argument, if_empty, supplies a value when no rows pass the test.
Without that third argument, FILTER returns #CALC! when nothing matches, because Excel does not return an empty array in this situation. This is different from #N/A, which means a value is unavailable or that an error value has flowed into the calculation. Adding an if_empty message is useful for a report, but it does not clean errors in matching source rows.
For example, if the data contains three rows whose region is West, the opening formula spills those three rows into the cells below and to the right of its starting cell. If there are no West rows, it displays No matches in the starting cell. The spill range must be clear; otherwise, Excel may report #SPILL! instead of showing the expected result.
Why #N/A appears in a filtered result
FILTER can return an error that already exists in the data it is asked to return. Imagine a matching row whose product code is in A, customer name is in B, and sales region is in C. If the customer-name cell in B contains #N/A because an earlier lookup did not find a customer, a FILTER result that includes that cell can show the same error. The filter has selected the row; it has not caused the original lookup failure.
An error can also arise in the include test. Suppose the region column is calculated by formulas and one of those formulas returns #N/A. Comparing that cell with West does not produce a normal TRUE or FALSE result. An error in the include array can therefore make FILTER return an error rather than complete the selection. Correct the source calculation, or decide deliberately how an invalid region should be treated.
- #N/A: Commonly propagated from a lookup or another source value; check both the returned columns and the condition column.
- #CALC!: Often means FILTER found no matching rows and no if_empty result was supplied.
- #VALUE!: Can indicate that the include array does not have compatible dimensions with the array being filtered.
- #SPILL!: Means the dynamic result cannot expand into its destination cells, often because cells are occupied or the formula is inside an Excel table.
These errors point to different causes. Replacing every error with a blank may make the sheet look tidy while concealing a broken lookup, a range-size problem, or a blocked output area.
Steps to diagnose the error
- Check the formula cell. Select it and read the formula bar. Confirm whether the cell contains FILTER or whether it is simply a source cell being shown by an ordinary filter.
- Inspect the source ranges. Follow the array and include references, including any formulas that calculate their values. Look for #N/A in the rows that should be returned and in the condition column.
- Check range sizes. If the array covers rows 2 through 20, a row-wise condition should generally cover the same rows. A condition covering fewer or more rows can cause a dimension error.
- Test whether a match exists. Compare the condition values with the criterion, checking for leading or trailing spaces, inconsistent spelling, and numbers stored as text. A visually similar value may not equal the criterion.
- Inspect the output area. For #SPILL!, clear any blocking cells around and below the formula. Also check for merged cells and whether the formula was entered inside an Excel table, where dynamic-array spilling is restricted.
To check whether one specific cell contains #N/A, with a formula result in D2, enter =ISNA(D2) in another cell. It returns TRUE for #N/A and FALSE for other values or errors. This is a quick diagnostic, not a correction to the source formula.
Ways to fix or handle #N/A
The most reliable fix is usually to correct the formula or data that first produced #N/A. If a lookup is supposed to find a matching product, verify that its lookup value and lookup range use the same type and spelling, and that the expected product exists. If the value is genuinely optional, a clear message or blank may be appropriate; if the value should exist, silently hiding the error can make a report misleading.
IFNA can replace #N/A from a particular calculation while leaving other kinds of errors visible. With an existing result or lookup formula in D2, use =IFNA(D2,"Not found") in another cell. It returns the value in D2 unless that value is #N/A, in which case it returns Not found. This example handles one result cell; it does not automatically fix every error in the original data range.
If errors in the source are expected and should be treated as blank, you can clean the returned data and make an error in the region test count as a non-match. With source data in A2:C20 and the region criterion in C2:C20, use =FILTER(IFNA(A2:C20,""),IFNA(C2:C20="West",FALSE),"No matches"). The formula returns matching rows, replaces #N/A values in the returned array with blanks, ignores #N/A values in the region test, and displays No matches if no rows qualify.
Use that pattern only when blanking those errors is an intentional reporting decision. A blank can look like missing data rather than a failed calculation, and IFNA will not replace other errors such as #VALUE! or #REF!. If the original error signals a data problem that needs attention, fix it at its source instead of masking it in the filtered output.
Using the ordinary Data Filter with error values
If you are not using the FILTER function, select a cell in the data range and choose Data > Filter in Excel for Microsoft 365 on Windows. Open the dropdown in the column that contains the error. The checklist reflects values in that column; select the error value, such as #N/A, to show rows containing it, or clear its checkbox to hide those rows. The exact list depends on the contents of the column.
This operation changes which rows are visible, not the values in their cells. If a filtered list seems to show #N/A, clearing or selecting that entry only changes the view. To repair it, edit the formula or value that generates the error. If the error is in a calculated column, investigate the formula and its referenced data rather than copying a displayed result over the formula.
Remember that a filtered worksheet can make hidden rows easy to overlook. Clear the filter with the column dropdown or use Data > Sort & Filter > Clear to return to the full list. Filtering is useful for finding problematic records, but it is not a substitute for checking all rows before treating a dataset as error-free.
Version and alternative considerations
FILTER is available in Microsoft 365 and Excel 2021 or later. It is a dynamic-array function: enter it in one cell and Excel spills the results into neighboring cells. Excel for the web and current Mac editions also support FILTER, although ribbon placement and some interface details can differ from Windows. The function’s essential arguments and error behavior are the same.
In versions without FILTER, use the ordinary Data > Filter command to display matching records, or use other formulas suited to the workbook’s version. The ordinary filter is often enough when a person needs to inspect or copy a subset manually. FILTER is more useful when another part of a workbook needs a live, formula-driven list that updates as source data changes. Avoid putting a spill formula inside an Excel table; place it in a clear worksheet area and let its results expand outside the table.
Frequently Asked Questions
Does FILTER return #N/A when there are no matches?
No. If the optional if_empty argument is omitted, FILTER normally returns #CALC! when there are no matching rows. Supply a message or other value as the third argument to control that case.
Why does FILTER show #N/A even though matching rows exist?
One or more matching source cells may already contain #N/A, or an error in the include condition may be preventing FILTER from evaluating normally. Check the formulas in the returned columns and the column used for the condition.
Can I make FILTER ignore #N/A values?
Yes, if treating them as blanks or non-matches is appropriate. IFNA can wrap the returned array and the condition, as in the example above. First decide whether those errors are harmless omissions or evidence of a source-data problem that should be corrected.
Why does Excel show #SPILL! instead of #N/A?
FILTER returns multiple cells when several rows match. If cells needed for that result are occupied, or the formula is entered in a table area that cannot accept a spill, Excel displays #SPILL!. Clear the destination area or move the formula to a suitable empty range.
Does the Data Filter command remove #N/A from my worksheet?
No. It only hides rows that do not meet the selected criteria. The error remains in its cell until you fix the source formula or value.
Related
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
- Excel Direct Access vs Link: What’s the Difference?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Find Not Working? Fix Search and Find SettingsIf Excel Find is not working, press Ctrl+F, open Options, and check the search scope and Look in…
- Hyperlink Formula Not Working in Excel? Fixes That HelpIf an Excel HYPERLINK formula is not working, first check that the cell is not formatted as Text,…
- Excel Too Many Arguments Error: Causes, Fixes, and ExamplesExcel’s “You’ve entered too many arguments for this function” message means a formula supplies more inputs than that…
- Excel Filter Not Working for All Rows? Find the CauseIf Excel’s filter is not working for all rows, its filter range may stop before the missing records,…
- Excel Not Highlighting Text? Fix Selection and FormattingIf Excel is not highlighting text, first work out whether you mean the selected cell is hard to…
- Excel Keeps Doing Text to Columns? Causes and FixesExcel does not normally rerun Text to Columns on its own. If data keeps splitting, a CSV or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.