Excel FILTER: What the Include Argument Does

In Excel’s FILTER function, include is the test that decides which rows or columns to return: TRUE keeps an item, and FALSE leaves it out. For example, with records in A2:C5 and regions in B2:B5, the formula =FILTER(A2:C5,B2:B5="East","No matches") returns only the records whose Region value is East.

What the Include Argument Means

FILTER is a dynamic-array function in Microsoft 365 and Excel 2021 or later. Its include argument supplies a TRUE-or-FALSE result for each item in the data being filtered. Excel evaluates the test, keeps the items marked TRUE, and returns them together as a spilling array. The source data stays unchanged.

Include does not have to be a single cell or a manually typed list of TRUE and FALSE values. Usually it is a range comparison, such as checking whether each region equals East, whether each sales amount exceeds a threshold, or whether each date falls in a period. The comparison produces a logical array that FILTER can use.

The function’s other arguments provide the data to return and, optionally, a value to show if nothing qualifies. Include is the selection rule between those two pieces. It answers “which items match?” rather than “which columns should be displayed?”—although it can select columns when its shape is horizontal.

How Include Aligns With the Data

The include array must have the same number of rows as the source array when filtering rows, or the same number of columns when filtering columns. It does not need to have the same overall shape as the source. For instance, a 4-row list of TRUE/FALSE results can select rows from a 4-row, 3-column data range.

With records in A2:C20 and a test range in B2:B20, the 19 results in the include array line up with the 19 source rows. If the include range has 18 or 20 rows instead, Excel cannot match the tests to the records and returns a #VALUE! error. A common cause is including a header in one range but not the other.

For a horizontal example, assume quarterly labels are in A1:D1 and the related figures are in A2:D5. A horizontal include test can keep the column labeled Q1, returning that column’s label and values. This is useful when the data is arranged across columns, though many worksheets are easier to maintain when each record occupies a row.

A Basic Row-Filtering Example

Suppose A1:C5 has the headings Name, Region, and Sales. Rows 2–5 contain Ava / East / 120, Ben / West / 90, Chen / East / 150, and Dia / North / 110. With those records in A2:C5 and the regions in B2:B5, this formula returns Ava and Chen’s complete rows:

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

The expression B2:B5=”East” checks each cell in the Region column separately. It evaluates to TRUE for the first and third records and FALSE for the second and fourth. FILTER uses those results to return the matching rows in their original order. The answer includes all three columns because A2:C5 is the array being returned; the include test only determines which rows survive.

The third argument, shown here as No matches, is optional. It gives Excel a fallback to display when every test is FALSE. Without that argument, a result with no matches can produce #CALC!. The fallback is a single value, not a custom message for each failed row.

Combining Multiple Conditions

To require two conditions at once, multiply the logical tests. With the same A2:C5 records, the following formula keeps sales of at least 130 only among East-region records:

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

Both tests must be TRUE for their product to be 1, so only Chen’s row qualifies: Chen / East / 150. Parentheses make it clear where each comparison begins and ends. Multiplication is a common way to express AND in a FILTER include argument.

To accept either of two conditions, add the logical tests. With regions in B2:B5, this formula returns records from either East or North:

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

In this example, the returned rows are Ava, Chen, and Dia. Addition works like OR because a row is retained if at least one comparison is TRUE. When writing more elaborate criteria, use parentheses around each comparison and test the formula on a small sample so the intended AND and OR logic is clear.

Using Cell References and Other Tests

A criterion can refer to a worksheet cell, which makes a filtered result easier to change without editing its formula. Assume the same records are in A2:C5 and a user enters East in E2. The formula below uses E2 as the requested region:

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

Changing E2 to West makes Ben’s row appear instead. This pattern is useful for a report sheet with a drop-down selection or a clearly labeled criteria cell. If E2 is blank, the formula looks for blank region cells; it does not automatically mean “show every region.”

Include can also test for blank cells, numbers, dates, and text patterns. For example, to omit rows without a name, use a nonblank test. Assume names are in A2:A20 and the complete records are in A2:C20:

=FILTER(A2:C20,A2:A20<>"","No records")

The formula keeps rows where the name cell is not equal to an empty string. Date tests work the same way: compare the date range to a cutoff or use two comparisons for a date interval. Make sure the worksheet values are actual Excel dates, not text that merely looks like dates; text dates may not compare as expected.

For a text search that finds a word within a cell, SEARCH can produce a position when it finds the text, and ISNUMBER turns that result into a TRUE/FALSE test. SEARCH is not case-sensitive. For large datasets, however, simpler direct comparisons are generally easier to read and maintain.

Where the Filtered Results Appear

Enter the formula in a cell with enough empty space to its right and below. Excel returns a dynamic array that spills into neighboring cells; those results are not separate formulas copied down. If any cell in the required spill area is occupied, Excel shows a #SPILL! error. Clear the blocking cell or move the formula to an open area.

Do not put a spilling FILTER formula inside an Excel table. Dynamic-array results spill in the worksheet grid, while table columns are designed to hold row-by-row entries. Put the formula in a normal cell outside the table; it can still refer to table columns in its arguments, and table references usually expand as table data grows.

When a source range changes size, ordinary fixed references such as A2:C20 may need to be adjusted. Converting the source data to an Excel table can make references more durable as records are added. For a shared workbook, remember that the formula’s output occupies cells: another user cannot type into the middle of the spill range without causing a conflict.

Common Errors and Misunderstandings

#VALUE!: Check that include has a matching number of rows or columns for the direction being filtered. Confirm that headers have not been included in only one range.
#CALC!: Usually, no item matched and no if-empty value was supplied. Add a suitable fallback if an empty result is expected.
#SPILL!: Look for content, merged cells, or another obstruction in the cells where the results need to appear. The formula cell itself must also be outside a table.
Unexpected matches: Check for extra spaces, inconsistent spelling, or values stored as text. A cell containing East followed by a space is not an exact match for East.
Wrong logic: Multiplying conditions requires all of them to pass; adding conditions allows any one to pass. Swap the operators only if that matches the intended rule.

Another misconception is that include must be typed as a list of TRUE and FALSE values. It can be, but a comparison is usually more practical because it updates when the source data changes. Also, FILTER does not automatically remove duplicate records: if several matching rows are identical, they are all returned.

FILTER Versus Excel’s Built-In Filter

The FILTER function creates a formula-driven result in another part of the sheet. The source data remains visible, and the returned array updates when the source values or criteria change. This is useful for dashboards, summaries, and clean output ranges that need to feed other formulas.

The regular AutoFilter feature instead hides rows in the original data view according to selected column criteria. It is convenient for inspecting or sorting a list interactively, without creating a second result range. A formula result is better when you need a separate live extract; AutoFilter is often better when you simply want to inspect the original list.

These tools can be used for different purposes in the same workbook. Be aware that a FILTER formula returns matching records based on its include logic; it does not reproduce every interactive feature of AutoFilter, such as the filter drop-down menus on the source headers.

Frequently Asked Questions

What does include mean in Excel FILTER?
It is the logical test or array of tests that tells FILTER which rows or columns to return. TRUE keeps an item, and FALSE excludes it.

Can FILTER include multiple criteria?
Yes. Multiply logical tests to require all conditions, or add them to accept any condition. Put each comparison in parentheses so the intended logic is easy to follow.

Why does my FILTER formula return #VALUE!?
The include array may not have the right number of rows or columns for the source array. Check the start and end cells in both ranges, especially whether one range includes a heading.

How do I show a message when there are no matches?
Supply a value for FILTER’s optional third argument, often a short message such as No matches. If you leave it out and nothing qualifies, Excel can return #CALC!.

Does FILTER change or delete the original data?
No. It returns a separate dynamic-array result and leaves the source records in place. The result updates when its referenced data or criteria change.

Is the FILTER function available in every version of Excel?
No. FILTER is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for the web. Older versions may not calculate it and may show an unsupported-function error.

Choosing the Right Include Test

Start by deciding what one result row must satisfy: an exact category, a numeric threshold, a date interval, or a combination. Then make the include range line up with the same source records and decide what should happen if nothing matches. A direct comparison is usually the clearest choice for a single rule; multiplication and addition express multi-condition logic. Once the formula works, confirm that the spill area is clear and that the returned rows match the source data you intended to select.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.