To filter one Excel column for several allowed values, select the column’s filter arrow and check each value you want to keep; Excel treats the selected values as alternatives (OR). For more complex conditions, use the column’s Custom Filter, Data > Advanced, or the FILTER function in Microsoft 365.
What multiple criteria in one column means
A filter criterion is a rule that determines whether a row remains visible. When several criteria apply to the same column, you may want to show rows that match any one of several values, such as East or West, or rows that satisfy a condition such as a date after one day and before another. These are different kinds of requests, and the best filtering method depends on which one you mean.
For a list of exact values, Excel’s filter checklist is usually the simplest choice. Selecting East and West in a Region column displays rows where the region is either East or West. The row does not have to match both values; one cell cannot equal both distinct values at once. This is an OR test.
By contrast, a request such as “show amounts greater than 100 and less than 500” asks for two conditions to be true together. That is an AND test. Excel’s Custom Filter dialog lets you specify two conditions and choose whether they are joined by And or Or. For more than two conditions, or a more elaborate mix of AND and OR, use Advanced Filter or a formula.
Filter for several exact values with the checklist
For ordinary lists and Excel tables, the checkbox list is the quickest way to select several distinct items from one column. It is useful for categories, names, statuses, locations, and other fields where the desired values are already present in the data.
- Click a cell in the data range. If the range has headers, Excel usually detects it automatically; converting it to a table with Insert > Table also gives the headers filter arrows.
- If filter arrows are not visible, choose Data > Filter. Excel adds a drop-down arrow to each header in the selected range.
- Open the arrow on the column you want to filter. Clear Select All, then check each value to include. Use the search box in the menu to find an item in a long list.
- Choose OK. Excel hides rows that do not match any checked value, while leaving the source data unchanged.
For example, selecting Open and Pending in a Status column keeps rows whose status is Open or Pending. The filter affects the displayed rows, not the stored values. Other column filters can remain in effect at the same time, so choosing East or West and then Open shows rows that meet the region selection and the status selection.
To remove just this column’s filter, open its arrow and choose Clear Filter From [column name]. To remove all active filters while keeping the filter arrows, use Data > Clear. To turn the arrows off, choose Data > Filter again.
Use Custom Filter for two conditions
The checklist is for choosing existing values. Custom Filter is better when you want rules such as begins with, contains, is greater than, or falls between, including values that may not be convenient to select from a long list. Open the column’s filter arrow, choose Text Filters for text, Number Filters for numbers, or Date Filters for dates, and select Custom Filter or the relevant condition command. The available choices depend on the kind of data Excel recognizes.
In the Custom AutoFilter dialog, enter the first condition, choose And or Or, and enter the second condition. For instance, a text column can be set to “equals East” OR “equals West.” A numeric column can be set to “is greater than 100” AND “is less than 500.” The first example keeps rows matching either region; the second keeps values inside the stated range, excluding 100 and 500 because the conditions are strictly greater than and less than.
This dialog provides two condition slots for a column, not an unlimited list. For three or more exact alternatives, use the checkbox list. For three or more rule-based conditions or a more complex arrangement, use Advanced Filter or a formula. Text filtering is generally not case-sensitive, so East and EAST are normally treated as the same text for these filters.
Understand AND and OR across columns
A frequent source of confusion is the difference between multiple selections in one column and filters applied to different columns. Within one column, selecting several checklist values means “match this value or that value.” Across columns, active filters are combined: a row must pass the filter in each filtered column. In practical terms, filtering Region for East and West, then Status for Open, means “Region is East or West, and Status is Open.”
For conditions in a Custom Filter, choose And only when both rules must be true for the same cell. Choose Or when either rule is enough. For example, “contains urgent” OR “contains overdue” can match a cell containing either word. “is greater than 100” AND “is less than 500” selects values strictly between those bounds. A condition cannot be both alternatives and a range unless the logic is expressed in a more capable tool.
Keep the logic in mind when you design criteria. A request like “Region is East and Status is Open, or Region is West and Status is Closed” combines conditions across columns in two alternative groups. Applying two ordinary column filters would instead require both selected columns’ rules to pass together. Advanced Filter’s criteria grid or a formula can represent the intended grouped logic more explicitly.
Use Advanced Filter for longer criteria lists
Advanced Filter is built into desktop Excel and can apply criteria from a separate range. It is useful when a checklist is cumbersome, when you need more than two rule conditions, or when criteria involve multiple columns. Arrange the source data with a single header row, then create a criteria area with headers that exactly match the source column names.
For example, if the source header is Region, put Region above a list of alternatives in the criteria area, with East and West on separate rows. In an Advanced Filter criteria range, conditions on the same row are combined with AND; separate criteria rows are combined with OR. Thus the two Region entries represent Region equals East OR Region equals West. If you put Region and Status headers side by side on one criteria row, both conditions must be true for a matching row.
Click in the source list and choose Data > Advanced. In the dialog, check that List range refers to the complete source table, including headers, and set Criteria range to the criteria cells, also including their headers. Choose Filter the list, in-place to hide nonmatching rows in the source, or Copy to another location to produce a separate result. Be careful not to include unrelated cells in either range. Advanced Filter is available in desktop Excel; if the command is not available in Excel for the web, open the workbook in the desktop app.
Return matching rows with the FILTER function
When you want a live, separate result that updates as the source data changes, the FILTER function is an alternative to hiding rows. It is available in Microsoft 365 and Excel 2021 or later. The formula returns matching rows in a spill range, so leave enough empty cells below and to the right of the formula for the results.
With headers in A1:C1, records in A2:C6, and Region in B2:B6, enter this formula in an empty cell to return records from East or West:
=FILTER(A2:C6,(B2:B6="East")+(B2:B6="West"),"No matches")
The plus sign combines the two tests as OR: a nonzero result means that at least one test is true. For example, if the five regions in B2:B6 are East, West, North, East, and South, the result includes the first, second, and fourth records. The third argument displays No matches if there are no qualifying rows. The formula returns the matching rows; it does not hide the original rows.
With the same layout, to return records only when Region is East and Status in C2:C6 is Open, use:
=FILTER(A2:C6,(B2:B6="East")*(C2:C6="Open"),"No matches")
The multiplication sign combines the tests as AND. If the example data has only one East row with Open status, the formula returns that one record. The source range and each test range must have the same number of rows. If the result shows a spill error, check whether cells in the intended output area are occupied or merged.
Choose the method that fits the task
- Choose the filter checklist to keep several known, existing values in one column. It is fast and easy to adjust interactively.
- Choose Custom Filter for two text, number, or date conditions joined by AND or OR.
- Choose Advanced Filter for longer criteria lists, more involved AND/OR logic, or a copied result in desktop Excel.
- Choose FILTER when you want a formula-driven result that recalculates as source values change. It requires a supported Excel version and clear spill space.
These methods serve different purposes. AutoFilter is convenient for inspecting a working list; it hides rows but does not create a separate extract. FILTER creates a separate dynamic result but requires a formula. Advanced Filter can copy results but is less immediately intuitive because its criteria range has specific header and row logic. Pick based on whether you need an interactive view, an automatically updating output, or a reusable criteria setup.
Common problems and how to fix them
- The filter arrow is missing: Click in the data and use Data > Filter. Check that the range has a clear header row and that there are no blank rows splitting the data into separate blocks. Tables normally show filter arrows in their header row.
- Some expected values are absent from the checklist: Clear other active filters first; the visible checklist can reflect the currently filtered records. Also check for spelling differences, leading or trailing spaces, and cells that look alike but contain different underlying values.
- Numbers or dates behave like text: Excel may have imported values as text, which can change the filter options and comparisons. Check the cells’ actual contents and data type; applying a number format alone does not convert text into numeric values.
- Filters seem to contradict each other: Filters on different columns are combined with AND, while multiple selected values in one checklist are alternatives. Clear filters and apply them one at a time to confirm the intended logic.
- Advanced Filter returns no rows: Make sure criteria headers exactly match source headers, include headers in both ranges, and put OR alternatives on separate criteria rows. A typo in a header can prevent the criteria from being applied as intended.
- The formula returns an error or incomplete output: Confirm that each range covers the same number of rows, use straight quotation marks around text criteria, and clear cells where the result needs to spill. FILTER requires a version that supports dynamic arrays.
Also remember that filtering only changes which rows are displayed. It does not delete the excluded records. If you copy visible rows elsewhere, inspect the copied result before sharing or using it, especially when hidden rows or multiple active filters could affect what is visible.
Frequently Asked Questions
Can I select more than two values in one Excel column?
Yes. Open the column’s filter arrow, clear Select All, and check as many listed values as needed. The two-condition limit applies to the Custom AutoFilter dialog, not to selecting several existing values from the checklist.
Does selecting multiple values mean AND or OR?
It means OR within that column: a row remains visible if its cell matches any selected value. Filters on separate columns are combined with AND, so a row must also meet each other active column filter.
Can I filter for three conditions with Custom Filter?
Not in one Custom AutoFilter dialog, which provides two condition fields. For three or more existing values, use the checklist; for multiple rule conditions, use Advanced Filter or a FILTER formula.
How do I filter one column for several words it contains?
Custom Filter can combine two text conditions, such as contains one word OR contains another. If you need more alternatives or a different grouped logic, create a criteria range for Advanced Filter or use a formula suited to the data.
Does Excel filtering delete rows that do not match?
No. AutoFilter hides nonmatching rows; it does not remove them from the worksheet. Clear the filter to display the full list again. The FILTER function also leaves the source data intact and returns matches separately.
Can I use these filters in Excel for Mac or the web?
Excel for Mac has filter controls on the Data tab, though the ribbon layout can differ from Windows. Excel for the web supports ordinary filtering and, in supported versions, FILTER formulas; use desktop Excel if you need the Advanced Filter command.
Related
- Excel Data Validation List From Another Sheet: Steps
- Excel Data Validation List From a Table Column Explained
- Excel Data Validation List Based on Selection: How-To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX Function Examples: Useful Formulas and UsesExcel’s INDEX function returns a value—or a reference to a value—from a range based on its row and…
- Excel MAX IF: Find the Highest Number by ConditionTo find the largest number in Excel that meets a condition, use MAXIFS, such as =MAXIFS(C2:C7,B2:B7,"East"). If you…
- Excel Standard Deviation Formula: Which Function to UseIn Excel for Microsoft 365, use STDEV.S to calculate standard deviation from a sample, or STDEV.P when your…
- Combine HLOOKUP and VLOOKUP in ExcelYes. You can nest VLOOKUP inside HLOOKUP so VLOOKUP finds the row number and HLOOKUP finds the column…
- Average If Excel Date Range? Formulas That WorkTo average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a…
- Excel SEQUENCE Function Examples for Dates and NumbersExcel’s SEQUENCE function creates a spill range of consecutive numbers, dates, or other values from one formula. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.