Yes. In Excel, use a column’s filter checklist to show several selected values (OR), or apply filters to different columns to require all those conditions (AND). For more complex rules, use Data > Advanced or the FILTER function in Microsoft 365; the best method depends on whether you want to filter the existing table or return matching rows elsewhere.
How Excel combines filter criteria
Multiple criteria can mean different things. You might want rows for either East or West, rows that are both East and Open, or rows that meet one of several combinations. Excel supports each pattern, but the filter interface combines conditions differently depending on where you enter them.
- OR within one column: Show rows whose Region is East or West.
- AND across columns: Show rows whose Region is East and whose Status is Open.
- More involved logic: Show rows that are (East and Open) or (West and Open), using Advanced Filter or a formula.
In ordinary AutoFilter, conditions applied to separate columns are combined with AND. Within a column, selecting multiple items in the checklist means that a row can match any selected item. The Custom Filter dialog lets you specify two conditions for one column and choose whether to join them with AND or OR.
Filter for several values in one column
This is the quickest option when you want to keep a few distinct values and hide the rest. For example, suppose a table has a Region column and you want to see both East and West. Turn on the filter with Data > Filter, open the Region dropdown, clear Select All, select East and West, and choose OK. Excel displays rows where Region is either value.
- Click a cell inside the data range or Excel table.
- Choose Data > Filter. Filter arrows appear in the header row.
- Open the arrow for the column you want to filter.
- Clear Select All, check each value to include, and select OK.
The checklist is convenient for a small number of exact values, such as two departments or several order statuses. Use its search box to locate an item in a long list, then check it. Selecting multiple checkboxes does not require the row to contain all selected values; one matching value is enough. This is an OR condition.
To restore the full list, open the same dropdown and choose Clear Filter From [column name], or use Data > Clear to remove filters from the range. Clearing a filter does not delete hidden rows; it changes which rows are currently displayed.
Use Custom Filter for ranges and text rules
When you need a comparison such as greater than a number, before a date, or text that begins with certain letters, use Custom Filter. Open the relevant column dropdown, point to Number Filters, Text Filters, or Date Filters as appropriate, and choose Custom Filter. Available labels depend on the kind of data Excel recognizes in that column.
In the Custom Filter dialog, set the first condition, choose And or Or, then set the second condition. For example, a Sales column can be filtered to show values greater than 100 and less than or equal to 150. A Name column could be set to begin with A or begin with B. The dialog provides operators such as equals, does not equal, contains, and is greater than; the available choices vary by data type.
Custom Filter handles two conditions for one field. If you need three or more separate tests on that same field, the checklist may be simpler for exact values. For more complex combinations across multiple fields, use Advanced Filter or a formula rather than trying to stack repeated custom dialogs.
Combine filters across different columns
Filters on separate columns naturally narrow the results together. Imagine a table with Region, Rep, Status, and Sales columns. If you select East in Region and Open in Status, Excel shows only rows that meet both conditions. Adding a Sales filter of greater than 100 narrows the displayed rows again: a row must pass every active column filter.
This is useful for routine exploration because each filter can be changed independently. You can select several statuses in the Status checklist, then select one region in Region. The resulting rule is “one of these statuses” AND “this region.” If the result is unexpectedly empty, check whether each condition is individually represented in the source data and whether the combination actually occurs.
AutoFilter is a display mechanism, not a separate copy of the data. Sorting or editing the underlying records still affects the same range or table. If you need a separate results area that updates as the source changes, a dynamic-array formula may be a better fit.
Use FILTER for a live results range
In Microsoft 365 for Windows, the FILTER function returns matching rows in another part of the worksheet. It is available in Microsoft 365 and Excel 2021 or later; dynamic-array behavior also works in Excel for the web. It does not hide nonmatching records in the original table. Instead, it spills the matching records into cells below and to the right of the formula.
Assume A1:D6 contains headings Region, Rep, Status, and Sales, with the five records in rows 2 through 6: East/Ana/Open/120, West/Ben/Open/90, East/Chen/Closed/150, North/Dia/Open/75, and West/Eli/Closed/110. With those values in A2:D6, enter this formula in an empty cell to return rows where Region is East and Status is Open:
=FILTER(A2:D6,(A2:A6="East")*(C2:C6="Open"),"No matches")
The asterisk combines the two TRUE/FALSE tests as AND. With the sample records, the returned row is East, Ana, Open, 120. The third argument supplies a message if nothing meets the conditions. The output area must be clear, or Excel may show a spill error instead of the results.
With the same layout, use addition to express OR between two Region tests:
=FILTER(A2:D6,(A2:A6="East")+(A2:A6="West"),"No matches")
This returns the four East or West records and leaves out North. You can combine OR and AND by grouping the tests. To return East or West rows that are also Open, use:
=FILTER(A2:D6,((A2:A6="East")+(A2:A6="West"))*(C2:C6="Open"),"No matches")
That returns Ana and Ben from the example. Keep the source and criteria ranges the same height so each test corresponds to the correct record. If the source grows regularly, convert it to an Excel table and use structured references, or adjust the ranges to include the new records.
Use Advanced Filter for multi-part logic
Advanced Filter is a built-in desktop Excel feature for criteria that are awkward to express with dropdowns. It can filter the source list in place or copy matching rows to another location. Choose Data > Advanced in the Sort & Filter group, then specify the list range and criteria range in the dialog. Depending on your selection, Excel may fill the list range automatically.
A criteria range is a small worksheet area with column headings that match the source headings. Conditions on the same criteria row are AND conditions; separate criteria rows are OR conditions. For example, place Region and Status as headers, then put East and Open on the first criteria row and West and Open on the next. That means (East AND Open) OR (West AND Open). With the sample data above, the matching records are Ana and Ben.
Advanced Filter is useful when a filter must be repeatable or when you want results copied elsewhere without writing a formula. Take care to use headings that match the source columns and to leave a blank row between the criteria area and other data where practical. Advanced Filter is available in desktop Excel; Excel for the web has standard filtering but does not provide the same Advanced Filter dialog.
Choose the right method
- Choose the checklist for several exact values in one column, such as multiple regions or statuses.
- Choose Custom Filter for two comparisons on one field, such as a numeric interval or two text conditions.
- Filter separate columns when every selected column condition must be true.
- Choose Advanced Filter for multiple AND/OR combinations or a copied extract in desktop Excel.
- Choose FILTER when you want a formula-driven results area that recalculates as the source data changes.
There is no single best method for every workbook. AutoFilter is easy for interactive review; Advanced Filter makes criteria logic visible in worksheet cells; FILTER is flexible and automatically returns a changing result set. A formula result is not the same as an editable filtered copy: changes to the spilled output are not a way to modify the source records.
Common problems and useful checks
If a filter omits records you expected, first check the data itself. Values that look identical can differ because of leading or trailing spaces, inconsistent spelling, or text-versus-number storage. Dates can also be stored as text rather than recognized Excel dates, which can prevent date comparisons from behaving as expected. Standardize the source values before relying on a strict match.
- Filter arrows are missing: Click inside the range and choose Data > Filter. Confirm that the data has a clear header row.
- New rows are not included: A range-based filter may not cover appended records. Convert the range to a table with Insert > Table, or extend the filtered range.
- FILTER returns an error: Check that the include array is the same height as the data array and that the output cells are empty.
- There are no visible rows: Review all active column filters; several valid conditions can still produce no matching combination.
- Values appear duplicated: Filtering does not remove duplicates. Use a separate deduplication tool or a suitable formula if unique results are required.
Excel’s standard text filters are generally not case-sensitive, so a case-only difference is not a dependable way to distinguish values. Also, filtering hides rows from view; it does not securely remove or conceal data from someone who can access the workbook. Use appropriate file permissions when data confidentiality matters.
Frequently Asked Questions
Can I select multiple items in an Excel filter?
Yes. Open the column dropdown, clear Select All, check each value to include, and select OK. Excel shows rows matching any checked value in that column.
How do I filter for two criteria in the same column?
Open the column dropdown, choose the appropriate Text, Number, or Date Filters menu, and select Custom Filter. Enter both conditions and choose And or Or to define how they are combined.
How do I filter for criteria in two different columns?
Apply a filter to each column. Excel combines the active column filters with AND, so each displayed row must satisfy the conditions in both columns.
Can Excel filter with more than two criteria?
Yes. Use checklist selections for multiple exact values, Advanced Filter for more complex AND/OR rules, or FILTER with a logical include expression in Microsoft 365 or Excel 2021 and later.
Does Excel’s filter delete the rows that do not match?
No. AutoFilter hides nonmatching rows; the records remain in the worksheet. If you need a separate list of matches, copy filtered rows or use Advanced Filter’s copy option or the FILTER function.
Related
- Excel Data Validation List Based on Selection: How-To
- Excel Data Validation List Character Limit Explained
- Excel Data Validation List: Build a Dynamic Source
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Merge Data From 2 Cells: Formulas and MethodsTo merge the contents of two cells in Excel, combine them with a formula such as =A2&" "&B2.…
- Round Excel Numbers to the Nearest Ten With a FormulaTo round a number to the nearest ten in Excel, use the ROUND function with -1 as its…
- Excel: How to Sort Dates in Order?To sort dates in Excel, select a cell in the date column, then choose Data > Sort Oldest…
- Excel Formula List in Hindi: Meanings, Uses and ExamplesAn Excel formula list in Hindi is a guide to common spreadsheet functions, their Hindi meanings, and examples…
- Excel UNIQUE Function Not Showing? Causes and FixesIf Excel does not recognize or display UNIQUE, first check your version: the function is available in Microsoft…
- Excel Trim a Range: Remove Extra Spaces from CellsTo trim a range in Excel, enter =TRIM(A2) beside the first value and fill the formula down; in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.