To filter one Excel column by several values, open its filter dropdown and select each value you want to keep; Excel combines those selections as OR. For more specific rules, use Data > Filter > the column dropdown > Text Filters, Number Filters, or Date Filters, then choose Custom Filter and connect up to two conditions with AND or OR.
What multiple criteria in one column means
Filtering one column by multiple criteria means showing rows where that column satisfies more than one allowed value or rule. The key choice is whether you want an OR test or an AND test. OR means a row can match either condition; AND means it must meet both conditions at once.
For example, filtering a Region column for East or West is an OR test: keep a row if its region is East, or if it is West. Filtering a Units column for values greater than or equal to 10 and less than or equal to 15 is an AND test: the value must satisfy both limits, so the result is a range from 10 through 15.
In a filter dropdown, selecting several listed items usually handles the OR case most simply. Custom Filter is useful when the criteria are comparisons rather than a short list of exact values. Advanced Filter and the FILTER function are options when the rules are more involved or you need a reusable result.
Filter a column using its dropdown checklist
For a list of exact values, the dropdown checklist is generally the fastest method in Excel for Microsoft 365 on Windows:
- Click a cell in the data range. If the data is an Excel table, its header row already has filter arrows; otherwise choose Data > Filter.
- Open the arrow in the column you want to filter.
- Clear Select All, then check every value you want to keep. For example, check East and West in a Region column.
- Click OK. Excel hides rows whose value is not among the selected items.
The selected items work as alternatives: a row appears if its column value matches any selected entry. This is convenient for several categories, names, product codes, or dates that are already present in the list. To include more values later, reopen the dropdown and check the additional items without clearing the existing selections.
If the list is long, use the search box in the dropdown to find a value. Searching helps locate items in the checklist; check the desired results and confirm the filter. If you type a new value that is not in the data, it will not create a matching row. The checklist filters existing records, rather than adding or editing data.
Use Custom Filter for two conditions
Custom Filter is a better fit when you need comparisons such as begins with, contains, greater than, or before a date. Open the column dropdown, choose Text Filters, Number Filters, or Date Filters as appropriate, and select Custom Filter. The available choices depend on the data type Excel recognizes in that column.
In the Custom AutoFilter dialog, select an operator and enter a value for the first condition, then choose And or Or and set the second condition. Use And when both rules must be true. Use Or when either rule is enough. For instance, a Text Filter for “begins with E” OR “begins with W” can include East and West, but it may also include other values beginning with those letters.
For numbers, “greater than or equal to 10” AND “less than or equal to 15” returns values from 10 to 15 inclusive. If you select OR for those same limits, nearly every number will meet at least one condition: numbers below 10 satisfy the second comparison, and numbers above 15 satisfy the first. Choosing the connector is therefore just as important as choosing the operators.
The standard Custom Filter dialog provides two condition lines, not an unlimited set of rules. For three or more exact values, use the checklist. For several comparison rules that do not fit the dialog, consider Advanced Filter or a formula-based result.
Filter for three or more values
To keep three or more exact categories in an ordinary list, use the dropdown checklist rather than trying to add a condition for every value in Custom Filter. Check, for example, East, West, and South; Excel retains rows whose Region is any one of those values. This works even when the values are far apart in the checklist.
For many conditions or a criteria list you want to maintain separately, Advanced Filter can be more manageable. It uses a criteria range on the worksheet. In that range, repeated entries in separate rows represent alternatives (OR). Conditions written across the same criteria row represent rules that must all be met (AND). This makes the layout a useful way to express logic that is awkward in the two-line Custom Filter dialog.
Set up an Advanced Filter criteria range
Suppose the source table has a column headed Region. Create a small criteria range elsewhere on the sheet, using the exact header Region, with East and West on the two rows below it. Those separate rows tell Advanced Filter to return records where Region is East OR West. Keep the criteria range outside the source list, and make sure its heading matches the source heading.
To apply it on Windows, click in the source data and choose Data > Advanced in the Sort & Filter group. In the dialog, check that the List range covers the full table, set Criteria range to include the criteria heading and values, then choose Filter the list, in-place or Copy to another location. If you copy results, specify the destination in the dialog. The copied result is a separate output, not a live formula result; run Advanced Filter again after changing the source or criteria.
Advanced Filter can also express two conditions on the same field. For example, to keep Units from 10 through 15, place the field heading Units twice across the criteria range. Under the first heading enter >=10 and under the second enter <=15 on the same row. Since both conditions occupy one criteria row, they act as AND. Put another criteria row underneath if you want an alternative set of conditions. This duplicate-heading setup is specific to the criteria range, so ensure the headings are identical to the source field name.
Return matching rows with the FILTER function
Excel for Microsoft 365 and Excel 2021 also have the FILTER function, which returns matching rows in a separate area of the worksheet. Unlike a dropdown filter, it does not hide rows in the original table. It produces a dynamic array that updates when the referenced data changes, provided the output area has room to spill.
Assume A1:C7 contains a small table with Order in column A, Region in B, and Units in C; rows 2:7 contain orders 101 East 12, 102 West 7, 103 North 15, 104 East 20, 105 South 11, and 106 West 14. To return rows whose Region is East or West, use this formula in an empty cell outside the source data:
=FILTER(A2:C7,(B2:B7="East")+(B2:B7="West"),"No matches")
The plus sign combines the two tests as OR. This example returns four rows: orders 101, 102, 104, and 106. A matching row is returned if either test is true. The optional third argument displays No matches if there are no qualifying records.
With the same A1:C7 layout, to return rows where Units is between 10 and 15 inclusive, enter this formula in an empty cell:
=FILTER(A2:C7,(C2:C7>=10)*(C2:C7<=15),"No matches")
The multiplication sign combines the tests as AND. The result is orders 101, 103, 105, and 106, with Units values 12, 15, 11, and 14. The FILTER function requires Excel 2021 or Microsoft 365; it is not available in every older Excel release. Leave enough blank cells below and to the right for the returned rows, or Excel may show a spill error.
Choose the method that fits your task
- Dropdown checklist: Best for selecting several existing exact values quickly, such as three regions or a handful of product codes.
- Custom Filter: Best for one or two text, number, or date comparisons connected by AND or OR.
- Advanced Filter: Useful for larger criteria ranges, more elaborate combinations, or copying qualifying records to another location. It is a desktop worksheet feature, not a continuously updating formula.
- FILTER function: Useful when you want a separate result that recalculates as source cells change. It requires a supported Excel version and free spill space.
These approaches solve related but distinct problems. Dropdown and Custom Filter change which source rows are visible; they do not delete the hidden rows. Advanced Filter can filter in place or copy results. FILTER returns a new array and leaves the source display alone. If colleagues need to see a filtered view of the same table, use the built-in filter. If a report needs to show matching records elsewhere, a formula or copied Advanced Filter output may be more appropriate.
Common problems and how to fix them
If a value you expect is missing from the checklist, first clear the filter and check the source cells. A cell may contain a trailing space, a slightly different spelling, or a different underlying value that only looks the same because of formatting. For example, dates can display in the same format while representing different dates or times. Correcting inconsistent source data is often better than adding more filter rules.
If filtering seems to affect only part of the list, check that the data range is contiguous and that the filter includes all rows and columns. Blank rows or columns can cause Excel to interpret the data as separate regions. Converting the range to a table with Insert > Table can make the dataset easier to manage, especially as new rows are added. Confirm that each column has a distinct header.
If a Custom Filter result is unexpectedly broad or narrow, verify whether the connector should be And or Or and check the comparison operators. “Contains” is not the same as “equals,” and “greater than” excludes the boundary value. For date filters, ensure Excel recognizes the entries as dates rather than text. For FILTER formulas, check that the criteria ranges and returned range have the same number of rows, and that the output cells are not blocked.
Finally, filtering hides rows; it does not remove them. Copying only the visible cells or deleting filtered rows can have lasting effects, so inspect the selection before making edits. To clear a filter and show all records again, open the relevant dropdown and choose Clear Filter From, or use Data > Clear to clear filters from the range.
Frequently Asked Questions
Can I filter a column for multiple values in Excel?
Yes. Open the column’s filter dropdown, clear Select All, and check each value to keep. Excel displays rows matching any selected value.
How do I use AND instead of OR in an Excel filter?
In Custom Filter, select And between the two conditions. For an Advanced Filter criteria range, put conditions that must both apply on the same criteria row. With FILTER, multiply the logical tests.
Can Custom Filter handle more than two criteria?
The standard Custom AutoFilter dialog offers two condition lines. Use the dropdown checklist for multiple exact values, or use Advanced Filter or FILTER for more complex criteria.
Does filtering delete rows that do not match?
No. A normal Excel filter hides nonmatching rows; clearing the filter makes them visible again. Deleting rows is a separate action.
Why does Excel show the wrong rows after filtering?
Check for extra spaces, inconsistent data types, incomplete filter ranges, and an incorrect AND/OR choice. Also confirm that the comparison includes or excludes boundary values as intended.
Related
- Excel File Too Big? How to Reduce Workbook File Size Safely
- Excel File Too Large for No Reason? Find the Hidden Data
- Excel File Size Too Large? How to Reduce It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20.…
- Excel Formula Bar Not Showing Text? Fixes That WorkIf Excel’s formula bar is missing, turn it back on with View > Show > Formula Bar in…
- Excel Formula to Remove Duplicates From a List AutomaticallyWith values in A2:A20, enter =UNIQUE(A2:A20) in an empty cell to return one copy of each distinct value.…
- Excel Formula for Rounding Numbers: ROUND and MoreUse Excel’s ROUND function to round a number to a chosen number of decimal places: with the value…
- Excel Filters: How to Sort and Show the Rows You NeedTo filter data in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Excel IRR Monthly: Formula, Setup, and Common ErrorsTo calculate a monthly internal rate of return in Excel, put cash flows in chronological order at equal…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.