To filter Excel using multiple criteria, select a cell in your data and choose Data > Filter, then apply a filter to each relevant column; Excel combines filters on different columns with AND logic. For more complex conditions, use Advanced Filter or, in Microsoft 365, the FILTER function.
What multiple criteria mean in Excel
A criterion is a condition that a value must meet, such as Region equals West, Sales is greater than 1,000, or Date is in a particular month. Applying several criteria lets you narrow a list to records that meet the conditions you need. The important detail is how Excel combines those conditions: it may require all of them, or allow a record to match any one of them.
For example, suppose a sales list has columns for Order, Region, Product, and Sales. Filtering Region to West and Sales to at least 1,000 returns only West orders whose sales also meet the threshold. This is AND logic: a row must satisfy both conditions. By contrast, Region equals West or East is OR logic: either region is acceptable.
Excel’s standard column filters are often the simplest option because they hide rows that do not match while leaving the original data in place. A filter does not delete excluded records. Clearing the filter makes them visible again.
Apply filters to several columns
In Excel for Microsoft 365 on Windows, click a cell in the data range and choose Data > Filter. Excel adds a drop-down arrow to each header. Open a column’s arrow and select the values or conditions to keep. Repeat for other columns. The visible rows must satisfy the active filter in every filtered column.
- Make sure the first row contains clear, unique column headings and that there are no completely blank rows or columns inside the list.
- Choose Data > Filter. If Excel has not identified the full range correctly, select the whole data range first.
- Open a header’s drop-down and select the desired items, or choose a text, number, or date filter such as Equals, Greater Than, or Between.
- Apply filters to the other columns. Check the results against the conditions you intended.
For instance, select West in the Region filter and set the Sales filter to Number Filters > Greater Than Or Equal To, entering 1000. Excel shows rows that meet both conditions. The filter arrows indicate which columns are currently filtered. To remove one condition, open that column’s menu and choose Clear Filter From; to remove all filters, use Data > Clear.
When the data is an Excel table, filters are usually available in the header row automatically. You can create a table by selecting the data and choosing Insert > Table, making sure My table has headers is selected when appropriate. Tables expand more reliably when new rows are added, which helps keep later filtering and sorting within the intended range.
Combine AND and OR conditions
Filters on separate columns work as AND conditions. If you filter Region to West and Product to Chairs, a visible row must be both in the West region and for Chairs. This is useful for the common request to match several independent requirements, such as a particular department, a date period, and a minimum amount.
For alternatives within a single column, select multiple values in that column’s filter list. Selecting West and East means a row may match either region. Text Filters and Number Filters also provide Custom Filter dialogs where you can combine two conditions with And or Or. For example, a Sales custom filter can show amounts greater than 1,000 or less than 200. The result can include both high and low values.
Be careful not to confuse these patterns. Filtering Region to West and then applying a second Region filter for East does not create an either-region condition; the second selection changes the first filter. Instead, select both values together in the Region list or use the Custom Filter dialog when the alternatives are comparisons rather than exact values.
Use Advanced Filter for complex criteria
Advanced Filter is useful when conditions span multiple fields or need a clear criteria grid. It can filter the list in place or copy matching records to another location. On Windows desktop Excel, select a cell in the list and choose Data > Advanced. Depending on the ribbon layout, the command appears in the Sort & Filter group. Excel for Mac desktop also offers Advanced Filter from the Data tab, though the ribbon layout can differ. Excel for the web has basic filtering but does not provide the full desktop Advanced Filter workflow.
To set up the criteria range, enter the relevant column headings exactly as they appear in the source list. Put conditions that must all be true on the same criteria row; put alternative sets of conditions on separate rows. For example, with source headers Order, Region, Product, and Sales in A1:D7, place Region and Sales in F1:G1. Enter West and >1000 in F2:G2, then East and >1000 in F3:G3. The first criteria row means West AND sales above 1,000; the second means East AND sales above 1,000. Because the rows are alternatives, the overall condition is (West AND above 1,000) OR (East AND above 1,000).
In the Advanced Filter dialog, confirm the List range and Criteria range. Choose Filter the list, in-place to hide nonmatching rows, or Copy to another location and specify a destination. Keep the criteria range outside the source list so it is not mistaken for data. If a condition seems to be ignored, first check that the criteria heading exactly matches a source heading and that the criteria cells are in the intended row.
Return matching rows with the FILTER function
In Microsoft 365, FILTER creates a separate, dynamically updated result rather than hiding rows in the source list. This is useful for reports, dashboards, and views that should remain visible alongside the complete source data. FILTER is also available in Excel 2021 and Excel 2024; it is not available in older perpetual versions such as Excel 2019. Enter the formula in a blank area with enough room for the results to spill.
Assume the sample data has headers in A1:D1 and six records in A2:D7: Order, Region, Product, and Sales. The rows include order 1001, West, Chairs, 1200; order 1003, West, Desks, 1500; and other orders from East and North. With the complete records in A2:D7, Region in B2:B7, and Sales in D2:D7, this formula returns the West records with sales of at least 1,000:
=FILTER(A2:D7,(B2:B7="West")*(D2:D7>=1000),"No matches")
The multiplication combines the two tests as AND. The sample returns orders 1001 and 1003: both are West orders and both have sales at least 1,000. For OR, addition can combine tests. With the same layout and sample, this formula returns qualifying West or East orders:
=FILTER(A2:D7,((B2:B7="West")+(B2:B7="East"))*(D2:D7>=1000),"No matches")
That result contains orders 1001, 1003, and 1005: order 1005 is East with sales of 1,300, while the West results remain. The addition represents either region; multiplication still requires sales to meet the threshold. The optional third argument displays No matches if no rows qualify. If the result shows a spill error, check that the cells where results would appear are empty and not blocked by merged cells.
Choose the right filtering method
Use the built-in drop-down filters when you want to inspect the original list, apply a few straightforward conditions, and quickly clear or change them. They are usually the best choice for interactive exploration. Use Advanced Filter when the rule needs several combinations of AND and OR conditions, or when you want a copied extract instead of hidden rows.
Choose FILTER when the output should update automatically as source values change and should sit in a separate area. It requires a supported Excel version and space for a spill range. FILTER returns values, not an interactive filter state on the original list. If your workbook will be shared with people using older Excel versions, confirm compatibility before relying on newer functions.
For recurring reports with large or multi-step data preparation needs, Power Query can load, transform, and filter data through a repeatable process. It is more setup than a quick column filter, but can be useful when you regularly import and clean the same kind of data. It is not necessary just to filter a small, tidy list.
Common problems and how to fix them
- Some records are missing: Check whether another column still has an active filter. Excel combines separate column filters, so one restrictive condition can exclude otherwise relevant rows.
- New rows are not included: The filter range may not have expanded. Convert the list to a table, or clear the filter and reselect the full range before applying it again.
- Numbers sort or filter strangely: Some entries may be stored as text, especially after importing data. Check for inconsistent formatting, spaces, or apostrophes, and convert the values to numbers if appropriate.
- A date condition misses records: Confirm that the cells contain real Excel dates rather than text that looks like a date. Mixed date formats can also make imported data behave unexpectedly.
- Advanced Filter returns nothing: Verify the criteria heading, the criteria range, and whether conditions intended as alternatives are on separate rows. A condition placed on the same row as another condition is combined with AND.
- FILTER shows an error: Confirm that the include ranges are the same height as the data range and that the result area is not blocked. Check that the workbook is opened in an Excel version that supports FILTER.
Also check for hidden rows, blank headings, and duplicate-looking values that contain extra spaces. If the workbook is shared, a filter view can affect what another person sees in the same file, depending on the Excel environment and collaboration state. Communicate which filters are active when sharing a filtered result.
Frequently Asked Questions
How do I filter Excel for two values in the same column? Open that column’s filter arrow and select both values, such as West and East. Excel displays rows matching either selected value. For comparisons, use Text Filters or Number Filters and choose the Custom Filter option to set two conditions with And or Or.
How do I filter by multiple columns in Excel? Apply a filter to each column in turn. Excel combines conditions from different columns with AND, so a row must meet every active column filter. For example, selecting West in Region and Chairs in Product shows only West chair orders.
Can Excel filter with OR across different columns? The standard column menus do not directly express every cross-column OR rule. Use Advanced Filter with alternative criteria on separate rows, or use FILTER with plus signs to combine tests as OR. Keep any additional required condition, such as a sales threshold, as a separate AND test.
Does filtering delete rows that do not match? No. A normal filter hides nonmatching rows; it does not remove them from the worksheet. Clear the filter to show the full list again. Advanced Filter can copy matching rows elsewhere, but that also leaves the source list intact.
Why is the Filter button unavailable or not filtering all my data? Check that the worksheet is not protected in a way that prevents filtering, that the selected range includes the intended records, and that the data has a single header row without blank separator rows. If the list is already filtered, clear existing filters before setting up a new range.
Related
- Extract Data from Google Maps to Excel: Practical Options
- Export Excel to Word: Methods and Formatting Tips
- Expand Data Validation Lists in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Protected Sheet Won’t Let You Sort? FixesExcel usually blocks sorting on a protected sheet because sorting is not allowed in the sheet’s protection settings,…
- Calculate Age in Excel with DATEDIF: Formula and TipsTo calculate someone’s age in completed years in Excel, enter their birth date in A2 and use =DATEDIF(A2,TODAY(),"Y").…
- Shuffle a List in Excel: Formulas, Sorting, and TipsYes. In Microsoft 365, you can shuffle a list with a formula using SORTBY and RANDARRAY; in any…
- How to Subtract in Microsoft Excel: Formulas and TipsTo subtract in Microsoft Excel, enter a formula using the minus sign: with the starting value in A2…
- Conditional Formatting Only If a Cell Has DataYes. In Excel for Microsoft 365, select the cells to format, choose Home > Conditional Formatting > New…
- Excel Formula Enum: How to Create Enum-Like Lists in ExcelExcel has no built-in ENUM worksheet function or enum data type, so there is no single “Excel formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.