With records in A2:C6 and the text to match in B2:B6, use =FILTER(A2:C6, B2:B6="North", "No matching rows") to return every row whose region is North. Excel’s FILTER function can also return rows when a text field contains a word, starts with a prefix, or meets more than one text condition.
What FILTER does with text
FILTER extracts matching rows or columns from a range; it does not change or hide the source data. Its syntax is FILTER(array, include, [if_empty]): array is the data to return, include is a TRUE/FALSE test for each row or column, and the optional if_empty value appears when nothing matches.
In the example above, A2:C6 is the three-column result range, and B2:B6 tests each corresponding region. If the sample rows have IDs 1001 through 1005, with North in rows 2, 4, and 6, the formula returns those three complete records. The test range must line up with the result range: a five-row array needs a five-row include test. FILTER is useful when you want a live result that updates as the source values change.
FILTER is a dynamic-array function in Microsoft 365 and Excel 2021 or later, including Excel 2024. Enter it in one cell with room for results to spill into adjacent cells; the returned rows are not typed into separate formulas. Excel for the web also supports dynamic arrays. Excel 2019 and earlier do not include the FILTER function.
Return rows for an exact text match
Equality is the simplest text test. The opening formula checks whether each cell in column B equals North and returns the corresponding cells from columns A through C. Excel’s ordinary equality comparison for text is not case-sensitive, so North and NORTH match the same way. It is an exact-value check, though: a cell containing North Region is not equal to North.
For a different exact value, replace North with the text you need. Keep text in straight double quotes, and use a range of the same height as the result array. If the text criterion is in a worksheet cell, such as E1, you can refer to that cell instead of typing a fixed word. With records in A2:C6 and the lookup text in E1, =FILTER(A2:C6, B2:B6=E1, "No matching rows") returns rows whose region equals the value currently in E1. For the sample data, if E1 contains North, it returns the same three North rows.
Leading or trailing spaces are part of a cell’s contents and can prevent an apparent exact match. For example, a cell containing North followed by a space may not equal North. If values come from copied or imported data, inspect suspicious cells or clean the source before relying on exact comparisons.
Find text that contains a word or phrase
To find a word anywhere within a cell, combine FILTER with SEARCH and ISNUMBER. With IDs in A2:A6, regions in B2:B6, and item descriptions in C2:C6, use =FILTER(A2:C6, ISNUMBER(SEARCH("pen", C2:C6)), "No matching rows"). SEARCH returns a position when it finds the text, and ISNUMBER turns that position into TRUE; a failed search becomes FALSE. If the sample descriptions are Blue pen, Notebook, Red pencil, Pen case, and Marker, the formula returns the rows for Blue pen, Red pencil, and Pen case. The match in pencil is expected because it contains the letters pen.
SEARCH is case-insensitive, so the criterion pen can match Pen. For a case-sensitive search, use FIND instead. With the same A2:C6 layout, =FILTER(A2:C6, ISNUMBER(FIND("Pen", C2:C6)), "No matching rows") returns only the row with Pen case in the sample because FIND distinguishes uppercase P from lowercase p. Both approaches look for a sequence of characters, not a separate whole word; a search for cat can also match a longer word that contains those letters.
For a prefix match, test the first characters of each description. With item descriptions in C2:C6 and the full rows in A2:C6, =FILTER(A2:C6, LEFT(C2:C6, 3)="Pen", "No matching rows") returns the Pen case row in the sample. LEFT extracts the first three characters, and the comparison is case-insensitive. If the prefix length changes, update both the number in LEFT and the text being compared.
Combine multiple text conditions
You can apply more than one test to each row. Multiplying Boolean tests acts like AND: both conditions must be TRUE for the row to be included. With records in A2:C6, regions in B2:B6, and descriptions in C2:C6, use =FILTER(A2:C6, (B2:B6="North")*ISNUMBER(SEARCH("pen", C2:C6)), "No matching rows"). In the sample, this returns the North rows whose descriptions contain pen: Blue pen and Red pencil. The North row for Marker is excluded because its description does not contain the search text.
Adding Boolean tests acts like OR: either condition can include a row. For example, with the same layout, =FILTER(A2:C6, (B2:B6="North")+(B2:B6="West"), "No matching rows") returns rows from either region. Parentheses make each comparison clear. When combining several conditions, check whether the intended logic is “all conditions” or “any condition”; mixing multiplication and addition can create more complex logic and should be grouped deliberately.
Set up the source and results
A reliable FILTER formula starts with a rectangular source range and aligned tests. In this example, row 1 contains headings and rows 2 through 6 contain records, so the formula uses A2:C6 rather than including the headings. If you add records later, a fixed range such as A2:C6 will not automatically include rows beyond 6. You can extend the references or use a source that expands as you add data.
- Put the FILTER formula in a blank cell outside the source data, with enough empty cells below and to the right for the results.
- Keep the include range the same height as the source range. For a horizontal source, the include test must have the matching width.
- Use the optional third argument to show a clear message rather than an error when there are no matches.
- Leave the source headings out of the data range unless you specifically want them included in the filtered output.
A FILTER result spills into neighboring cells, so avoid typing into the cells where results need to appear. The formula can sit beside an Excel Table, but dynamic-array results cannot spill from inside a table’s data area. If the source is a table, place the formula in the worksheet grid outside the table and refer to the table’s columns as appropriate.
Common errors and how to fix them
A #SPILL! error usually means Excel cannot place the full result. Look for existing values, merged cells, or another obstruction in the intended spill area; clear the obstruction or move the formula. The result may grow when the source changes, so leave more room than the current output requires.
If there are no matches and you omit if_empty, FILTER returns #CALC! because Excel has no matching rows to return. Adding a message such as No matching rows makes the outcome easier to interpret. Remember that the message is a single cell, not a row with the same number of columns as the data.
A #VALUE! error can result when the include array has the wrong dimensions. For example, using A2:C6 as the array but B2:B10 as the test range does not create a valid row-by-row match. Confirm that both ranges begin and end on corresponding rows. Also check for errors already present in the cells used by the include test: an error in a criterion calculation can prevent FILTER from evaluating the result as expected.
If a contains search seems to miss a value, check for unexpected spaces, nonprinting characters, spelling differences, and whether you meant a whole word rather than a character sequence. SEARCH is not a whole-word search. If the source is inconsistent, cleaning or standardizing it may be more dependable than making a complex condition for every variation.
FILTER versus Excel’s built-in text filters
FILTER formulas and the regular column filters solve related but different problems. A FILTER formula creates a separate, automatically recalculated result range while leaving the source visible. The standard filter hides nonmatching rows in the original list, which is convenient for browsing or editing the source without creating another output area.
To use the built-in filter in Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > Filter. Open a column’s dropdown, then choose Text Filters and a condition such as Contains, Begins With, or Equals. On Mac and Excel for the web, the Data tab also provides the Filter command; the exact placement and menu presentation can vary by interface. These controls are useful for interactive inspection, while FILTER is better when another formula, report, or worksheet needs a live extracted list.
Use FILTER when you want the results to update in place as criteria or source values change, or when you need to combine conditions in a formula. Use the built-in dropdown when you want to quickly show or hide rows in the original list. Neither method changes the underlying text values, and applying a visual filter is not the same as deleting unwanted records.
Frequently Asked Questions
Can Excel FILTER search for text that contains a word?
Yes. Combine FILTER with ISNUMBER and SEARCH to test whether each cell contains the text. SEARCH is case-insensitive; use FIND inside ISNUMBER when the match must be case-sensitive.
Does FILTER support wildcards such as an asterisk?
FILTER itself evaluates a TRUE/FALSE include array rather than accepting a wildcard criterion argument like some other Excel features. SEARCH recognizes wildcard characters in its search text, but for many straightforward searches, SEARCH with ISNUMBER is clearer. If you need literal wildcard characters, account for SEARCH’s wildcard behavior rather than assuming they are ordinary characters.
Why does FILTER return #SPILL!?
Excel cannot write the complete result into the cells needed for the spill range. Clear cells or merged areas blocking the output, or move the formula to a location with enough space.
Can I use FILTER in Excel 2019?
No. FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024, and in Excel for the web. In an older desktop version, use the column dropdown filters or another supported method to display matching rows.
Does FILTER change or delete the original records?
No. It returns a separate result and leaves the source range intact. Editing the output cells is not a way to edit the original records; make changes in the source data if you want those records themselves to change.
Related
- Excel Desktop Keyboard Shortcuts: A Practical Guide
- Excel Dependent Drop-Down Lists in Microsoft 365
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Volume of a Right Cylinder: Formula, Steps and ExamplesThe volume of a right cylinder is V = πr²h: multiply pi by the radius squared, then by…
- COUNTIF Excel Formula Examples and How to Use ThemIn Excel, COUNTIF counts cells in a range that meet one condition. Its basic pattern is =COUNTIF(range,criteria); for…
- Excel Filter Merged Cells: Why Only the First Row ShowsYes—when a cell is merged vertically across several rows, Excel stores its value only in the top-left cell,…
- Sort Excel Oldest to Newest: Dates, Data, and FixesTo sort dates in Excel from oldest to newest, select a cell in the date column, then choose…
- Excel Data Validation Custom Formula Examples That WorkExcel custom data validation lets you accept an entry only when a formula returns TRUE. In Microsoft 365…
- Excel Formula to Pick Randomly From a ListWith a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.