In Excel for Microsoft 365, use the FILTER function with a table name and a structured column reference, such as =FILTER(tblOrders,tblOrders[Region]="West","No matching orders"). The formula returns the rows of the table whose Region is West; enter it in a normal worksheet cell outside a table so the results can spill.
What a table reference means in FILTER
An Excel table is a formatted data range with a name and named columns. Instead of referring to fixed addresses such as A2:D100, a formula can use the table name and its column headings. These are called structured references. For example, tblOrders[Region] refers to the Region data in the table named tblOrders.
FILTER has the syntax =FILTER(array,include,[if_empty]). The array is the material to return, include is a same-height or same-width set of TRUE/FALSE tests, and the optional if_empty value appears if no records match. When the array is the table name, the returned values are its data rows and columns, not the table’s header row or total row.
Structured references are useful because they adjust as table data changes. If you add another order to tblOrders, a formula that filters the table can include that new order without changing a range endpoint. The column heading is also easier to interpret than a column letter, particularly in a wide workbook.
Set up the table and its name
For a concrete example, suppose the worksheet has four columns—OrderID, Customer, Region, and Amount—and these records:
- 1001, Ana, East, 240
- 1002, Ben, West, 180
- 1003, Cam, West, 320
- 1004, Dev, East, 150
Select the data, including its headings, and choose Insert > Table. Confirm that the table has headers. Click a cell in the table and use the Table Design tab’s Table Name box to give it a clear name, such as tblOrders. Table names cannot contain spaces, so use an underscore or capitalization if needed. The Table Design tab is also available in current Excel for Mac, though the ribbon layout can differ; Excel for the web has table tools as well.
Use the exact column heading inside square brackets. If a heading contains spaces, the structured reference preserves them, for example tblOrders[Order Amount]. If a heading changes, Excel generally updates references that point to it, but formulas are easier to maintain when headings are stable and descriptive.
Return matching rows with a basic formula
With the four example records in a table named tblOrders, enter this formula in an empty cell outside the table to return every West order:
=FILTER(tblOrders,tblOrders[Region]="West","No matching orders")
The condition compares every value in tblOrders[Region] with West. FILTER returns the matching complete rows: order 1002 for Ben and order 1003 for Cam. The third argument is optional, but it gives a readable result instead of an empty-result error when nothing matches. Text values in formulas need straight double quotation marks.
To return just one field, make the first argument that column rather than the whole table. With the same tblOrders data, this returns the amounts for West orders:
=FILTER(tblOrders[Amount],tblOrders[Region]="West","No matching orders")
The results are 180 and 320. The include condition still refers to the Region column, while the array being returned is Amount. The two references must describe corresponding rows; using columns from unrelated ranges or mismatched-sized arrays can produce an error.
Combine conditions for AND and OR results
For multiple conditions that must all be true, multiply the logical tests. In the example table, this formula returns West orders with an amount of at least 200:
=FILTER(tblOrders,(tblOrders[Region]="West")*(tblOrders[Amount]>=200),"No matching orders")
The layout is still the four-column tblOrders table described above. The result is one row: order 1003 for Cam, with an amount of 320. In this pattern, multiplication acts like AND: a row is included only when both tests are true. Parentheses make each test clear and prevent mistakes when a formula becomes longer.
For conditions where either test can be true, add the logical tests. With the same data, this example returns orders from the East or orders of at least 300:
=FILTER(tblOrders,(tblOrders[Region]="East")+(tblOrders[Amount]>=300),"No matching orders")
It returns orders 1001, 1003, and 1004. Addition acts like OR because any nonzero result is treated as included. A row that meets both conditions is still returned once, not duplicated. These multiplication and addition patterns are common ways to combine Boolean criteria in dynamic-array formulas.
Understand spilled results and table behavior
FILTER is a dynamic-array function. Excel places its results in the formula cell and spills the remaining rows and columns into adjacent cells automatically. Only the top-left cell contains the formula; the other displayed values are part of the spill range. Do not type into cells where results need to spill, and make sure there is enough blank space to the right and below the formula.
A key limitation is that a dynamic array formula cannot spill from inside an Excel table. Put the FILTER formula in an ordinary worksheet area outside the table, or on a separate results sheet. The results do not become another expandable table automatically; they remain a spill range that recalculates when the source table or criteria change.
If the formula is in H2, other formulas can refer to its entire current result using the spill operator, as in =COUNTA(H2#). For that formula, assume the FILTER output begins in H2 and its first spilled column contains nonblank order IDs; the result is the number of returned orders. The # reference expands or contracts with the spill. Avoid putting other content inside that spill area, because Excel will show a #SPILL! error if something blocks the output.
In Excel for Mac and Excel for the web, FILTER also returns a dynamic array and requires room to spill. The exact ribbon appearance and some workbook interactions may vary, but the structured-reference formula syntax is the same.
Common errors and how to fix them
- #SPILL! Usually means a cell in the expected output area is occupied, merged cells obstruct the output, or the formula was entered inside a table. Clear the obstruction or move the formula to an open area outside the table.
- #CALC! FILTER can return this when there are no matches and the optional if_empty argument is omitted. Add a message such as “No matching orders” as the third argument, or check whether the criteria actually match the source values.
- #VALUE! A size mismatch between the array and include condition can cause an error. Check that the table or column references cover corresponding rows and that the condition is not based on an unrelated range.
- Unexpectedly missing matches Check spelling, spaces, and data types. A number stored as text may not behave like a numeric value in comparisons, and a trailing space in a text value can make it differ from the criterion.
- Formula not recognized FILTER is available in Microsoft 365 and Excel 2021 or later. Older Excel versions may not support it. Also check whether the workbook uses a regional list separator other than commas; the examples here use standard comma separators.
Table structured references themselves are not usually the problem when a formula is entered outside the table. If Excel inserts an @ symbol or changes a reference while editing, inspect the formula bar and confirm that the first argument is the intended table or column reference, not a single-row reference.
Choose between FILTER and ordinary table filters
The FILTER function is best when you want a formula-driven result elsewhere in the workbook. It can combine criteria, return selected columns, and update the visible result as data changes. It also leaves the source table intact, which is valuable when several reports need different views of the same records.
The table’s built-in filter buttons are better for temporarily hiding rows in the source view. Click a header’s filter arrow to select values or apply conditions; this changes which rows are displayed in that table rather than creating a separate result set. For a repeatable summary with grouping or aggregation, a PivotTable may be more appropriate. These tools solve related but different problems: FILTER returns matching values, a table filter hides nonmatching rows, and a PivotTable summarizes data.
Frequently Asked Questions
Can I use a table name directly as FILTER’s first argument?
Yes. A reference such as tblOrders can be used as the array to return matching table data rows. Use a column reference such as tblOrders[Amount] instead when you want only that field.
Does FILTER include the table headers?
No. Filtering the table name returns matching data rows, not the header row. Add or display headings separately if the results need their own labels.
Why can’t my FILTER formula go inside the table?
FILTER needs to spill its result into neighboring cells, while table cells are managed as part of the table structure. Enter the formula in a normal worksheet cell outside the table.
Will new table rows appear in the FILTER results?
Yes, when the formula references the table or its structured column references, adding records to that table extends the source reference. The result recalculates, provided the spill area is clear.
Can FILTER return several columns but only selected rows?
Yes. Use the table name as the first argument to return all its data columns, and use a column-based logical test as the second argument. Use a single structured column reference as the first argument when only one output column is needed.
Practical checks before sharing the workbook
Before relying on a FILTER report, verify that the table has the expected name and headings, test a criterion that should return a known row, and check the no-match case. Keep the results area clear and label the report so readers know it is a live view of the source rather than a separately maintained copy. If a workbook is shared with someone using an Excel release older than Excel 2021, tell them that FILTER may not calculate in that version; a conventional table filter or another compatible reporting approach may be needed.
Related
- Excel Data Validation List With a Blank Option
- Excel Data Validation List Without a Cell Reference?
- Excel Data Validation List: Can You Select Multiple?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: Count Values Less Than a Set NumberTo count values below a number in Excel, use =COUNTIF(A2:A20,"<10"). Replace A2:A20 with your range and 10 with…
- Excel FILTER Formula for Multiple CriteriaTo filter rows in Excel by multiple criteria, use FILTER with a condition for each test: multiply the…
- How to Subtract in Microsoft Excel: Formulas and TipsTo subtract in Microsoft Excel, enter a formula using the minus sign: with the starting value in A2…
- Excel Formula for Minus Amounts: Examples and TipsTo subtract one amount from another in Excel, enter a formula with the minus sign: if the first…
- Function in Function in JavaScript: Examples & UsesA function inside another function in JavaScript is a nested function: it is defined within the outer function’s…
- Excel Formula Due Date Calculator: How to Set One UpThe quickest way to calculate a due date in Excel is to add days to a start date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.