In Excel, a FILTER formula returns #REF! when it depends on a reference Excel can no longer resolve, such as a deleted range or an unavailable external workbook. Check the formula’s array, include range, and any referenced names or workbooks; correcting that broken source is the fix, while adding an “if empty” message will not repair a bad reference.
What the FILTER #REF! error means
FILTER is a dynamic-array function in Microsoft 365 and Excel 2021 or later. It returns the rows or columns from an array that meet a condition, spilling the results into nearby cells. Its syntax is =FILTER(array,include,[if_empty]). The array is the data to return, include is a TRUE/FALSE test with matching dimensions, and the optional third argument supplies a value if no records match.
A #REF! result points to an invalid reference somewhere in the formula’s dependency chain. For example, a source column may have been deleted, a defined name may point to a deleted range, or a linked workbook may be unavailable. The error does not mean that FILTER found zero matching rows. With no third argument, no matches normally produce #CALC!; with an if_empty value, FILTER returns that value instead.
Common causes of #REF! in a FILTER formula
- A referenced row, column, or sheet was deleted. Excel may replace the affected part of a formula with
#REF!. A formula can then visibly contain the error token as an argument. - A defined name has a broken “Refers to” range. FILTER may use a name rather than a visible A1-style reference, so the source of the error is less obvious.
- An external workbook is closed or unavailable. Dynamic-array formulas that link to another workbook have limitations when the source workbook is closed; the linked result may return
#REF!after recalculation or refresh. - A reference inside the include test is broken. Even if the array range is valid, a deleted column or broken named reference used to decide which rows qualify can invalidate the formula.
- A cell or formula feeding FILTER already contains an error. Errors in source data or in a calculated include condition can flow into the returned result. Inspect the referenced cells, not just the FILTER cell.
By contrast, a mismatch between the size of the array and the include range generally produces #VALUE!, and a blocked spill area produces #SPILL!. These errors can look related because they appear while working with dynamic arrays, but they call for different fixes.
Trace the broken reference step by step
- Select the cell showing
#REF!and read the formula bar from left to right. Look for the literal#REF!, but also note any sheet names, workbook names, or defined names in the formula. - Check each range in the array argument and the include argument. Click or select references in the formula bar where practical; Excel highlights valid referenced cells. A range that cannot be highlighted or that displays
#REF!needs attention. - Inspect the source cells for errors. If the include test uses a formula, check that formula too; an error in a condition can prevent FILTER from evaluating the requested rows.
- Open the Formulas tab and choose Name Manager. Select names used by the formula and review the Refers to field. Repair a broken name or replace it in the FILTER formula with the correct range.
- If the formula links to another workbook, open that workbook and confirm the file path and source sheet. Then recalculate or refresh the workbook as appropriate.
For a simple test, temporarily select the array range and the include range in the formula bar and verify that both exist and cover corresponding records. Avoid replacing every reference by guesswork: a formula may intentionally filter a larger range, and changing it to a shorter range can silently omit data.
Repair a deleted or incorrect range
Suppose a worksheet has product names in A2:A20 and regions in B2:B20, and you want products from the East. If the product column was deleted or moved, edit the formula to point to the column that now contains product names. The array and include ranges must describe the intended records, with the same number of rows.
For a working example, assume A1:C5 contains headers Product, Region, and Units, with Mug / East / 12 in row 2, Pen / West / 30 in row 3, Folder / East / 8 in row 4, and Pad / South / 15 in row 5. With the complete records in A2:C5 and regions in B2:B5, enter this formula in an empty cell:
=FILTER(A2:C5,B2:B5="East","No matches")
The result is two rows: Mug, East, 12 and Folder, East, 8. If a column was removed and the formula now contains a broken array reference, substitute the correct current range for A2:C5. If the region field moved, update B2:B5 as well. The third argument only changes the result when no row qualifies; it cannot make an invalid range valid.
When rows are added regularly, an Excel Table can make references easier to maintain because its structured references expand with table data. Select the data and use Insert > Table, then write the formula using the table’s actual name and column headers. Be careful when renaming or deleting table columns: formulas that depend on a deleted column may still need to be repaired.
Check external workbook links and named ranges
A FILTER formula can refer to data in another workbook. Dynamic-array formulas linked across workbooks are supported only while both workbooks are open; if the source workbook is closed, refreshing or recalculating can result in #REF!. Open the source file first and verify that the linked sheet and range still exist. If the source has moved, use Excel’s workbook-link controls to update the source rather than simply changing the visible formula text without checking the data.
For a name-based formula, use Formulas > Name Manager and inspect the name’s scope and Refers to value. A name can refer to a workbook-level or worksheet-level range. If a sheet was renamed, Excel often adjusts references automatically, but deleted cells or columns can leave a name pointing at #REF!. Update the name to the intended range or remove it only after confirming that other formulas do not rely on it.
Separate #REF! from other FILTER problems
Not every failed FILTER formula is a reference error. Identifying the exact error code helps avoid fixing the wrong thing:
#CALC!: No records matched, and the formula did not provide an if_empty result. Decide whether an empty result should show a message or another suitable value.#SPILL!: Excel cannot place the returned array because cells in its destination area are occupied, merged, or otherwise block the spill. Clear the obstructing cells or move the formula.#VALUE!: The dimensions of the array and include arguments do not align, or another incompatible value is involved. Check that the condition returns one result for each relevant row or column.#NAME?: Excel may not recognize the function name, which can happen in an unsupported Excel version or when a function name is misspelled. FILTER is available in Microsoft 365 and Excel 2021 or later.
Filtering with the ribbon is a different feature. To filter a list in place, select the data and choose Data > Filter. That AutoFilter feature hides rows that do not meet the selected criteria; it does not use the worksheet FILTER function or create a spilled result. A ribbon filter problem is therefore not the same as a FILTER formula returning #REF!.
Prevent the error in future work
There is no setting that makes FILTER ignore invalid references: the formula needs valid source data. A few habits can reduce the likelihood of breaking it:
- Use Excel Tables for changing datasets and structured column references where suitable, rather than relying on fixed ranges that must be extended by hand.
- Before deleting a source column or sheet, search for formulas and defined names that depend on it. Excel’s formula search and Name Manager can help reveal dependencies.
- Keep linked workbooks in stable locations and open the source workbook when recalculating formulas that depend on its dynamic-array results.
- Test a formula on a small known set of records. Confirm both which rows should match and whether an empty result is expected.
- Distinguish source-data errors from formula errors. A valid FILTER formula can return an error that was already present in a matching source cell.
These practices do not prevent every broken link—for example, a file may still be moved—but they make the source of an error easier to find and reduce accidental reference changes.
Frequently Asked Questions
Does adding an if_empty value fix FILTER #REF!?
No. The optional third argument handles the case where the include test finds no rows. It does not repair a deleted range, invalid name, or unavailable link. Correct the broken reference first.
Why does FILTER show #REF! after I close another workbook?
The formula may rely on a dynamic-array result from that external workbook. Excel’s dynamic-array links require both workbooks to be open for supported operation. Reopen the source workbook and confirm its location and referenced range.
Why does my FILTER formula return #CALC! instead?
Usually, no entries met the condition and no if_empty value was supplied. Check that the criteria are spelled and typed as intended, then decide how the formula should display when there are no matches.
Is #SPILL! the same as #REF!?
No. #SPILL! means Excel cannot place the dynamic result in the required cells, commonly because something blocks the output area. #REF! means a reference is invalid. Clear the spill area for the former; repair the source reference for the latter.
Can I use FILTER in older Excel versions?
The FILTER function is available in Microsoft 365 and Excel 2021 or later, not older perpetual versions such as Excel 2019. In an older version, use Data > Filter for an interactive filter, or use a compatible alternative such as Advanced Filter; these do not behave exactly like a dynamic-array formula.
Related
- Excel Dependent Drop-Down Lists with Dynamic Ranges
- Excel Dependent Drop-Down Lists in Tables
- Excel Dependent Drop-Down List: 3 Levels
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Data Validation Error Alert Not Working?If an Excel data validation error alert is not appearing, open Data > Data Validation > Data Validation,…
- Excel Cell Drop-Down Options: Create, Edit, and FixTo add Excel cell drop-down options in Microsoft 365 for Windows, select the cell, choose Data > Data…
- Outlook Spam Filter Not Working? Fix Common CausesIf Outlook’s spam filter is not working, first check the Junk Email folder, your safe and blocked sender…
- Excel Filter Button Not Working? Fix Common CausesIf the Excel filter button is missing, greyed out, or not filtering the rows you expect, check that…
- How to Fix Borders in Excel: Quick, Reliable StepsTo fix a border in Excel, select the affected cell or range, go to Home > Borders, and…
- Excel Error Message When Opening a File: Causes and FixesAn Excel error message when opening a file usually means Excel cannot read the file as presented, the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.