To filter by two criteria in Excel, turn on filters with Data > Filter, then set a condition in each relevant column; Excel keeps rows that meet both column filters. If both criteria apply to the same column, open that column’s filter menu and choose a custom filter with two conditions, or select two values in its checklist.
What “two criteria” can mean
The phrase can describe a few different filtering tasks. You might want rows from the West region and with sales of at least $500, or records whose status is either Open or Pending. You might also need to find names that begin with a particular letter and contain a particular word. The right method depends on whether the criteria apply to separate columns, the same column, or an OR condition.
In a normal Excel filter, applying a condition in one column and another condition in a different column combines them with AND. For example, filtering Region to West and Sales to greater than or equal to 500 shows only rows that satisfy both. Within one column, the custom-filter dialog lets you choose whether its two conditions are joined with AND or OR. Selecting multiple distinct values from a column’s checklist is also an OR-style selection: show this value or that value.
These filters hide rows that do not match; they do not delete the underlying data. Clearing a filter restores the hidden rows, which makes filtering suitable for exploring a list without changing its contents.
Filter two different columns with the ribbon
For the common case—one criterion in each of two columns—use Excel’s built-in AutoFilter. The source list should have one header row, with a distinct heading for each field. Avoid completely blank rows or columns inside the list, since they can make Excel identify the data range incorrectly.
- Click a cell inside the data range. If the data is already an Excel table, click inside the table instead.
- On the ribbon, select Data > Filter. Filter arrows appear beside the column headings. In Excel for Microsoft 365 on Windows, Ctrl+Shift+L is a shortcut to turn filters on or off.
- Open the arrow for the first column, clear (Select All) if needed, select the value or set the condition, and choose OK.
- Open the arrow for the second column and apply its condition. The first filter remains active, so the visible rows must pass both.
For example, imagine a table with columns named Region, Product, and Sales. Filter Region to West, then filter Sales using Number Filters > Greater Than Or Equal To and enter 500. The result contains West rows with sales of at least 500; East rows and West rows below 500 are hidden. The headings and data are just an example—the same approach works for dates, categories, names, and other fields.
Excel’s filter menus vary according to the data type it detects. A numeric column offers number conditions, a date column offers date conditions, and text offers text conditions. If Excel treats numbers as text, or dates as text, the expected category of filter options may not appear.
Apply two conditions to the same column
When both tests concern one field, use a custom filter. Open that column’s drop-down arrow and choose the relevant category, such as Text Filters > Custom Filter or Number Filters > Custom Filter. For dates, open Date Filters and select a suitable option. The available labels depend on the column type and Excel build.
The Custom AutoFilter dialog provides two condition rows and an AND/OR choice between them. Choose AND when a value must satisfy both tests—for example, a number must be greater than 10 and less than 50. Choose OR when either test is enough—for example, a category can equal Hardware or Supplies. Pick the comparison operators from the menus, enter the values, select the correct AND or OR option, and confirm.
There is a useful distinction between a pair of conditions and a pair of exact values. If you simply want to show the categories Open and Pending, use the column’s checklist and tick both values. Excel treats those selections as alternatives: a row can match Open or Pending. If the task instead says that a text entry must begin with Open and also meet another text test, use a custom filter and choose the appropriate relationship.
Text conditions can include options such as Equals, Begins With, Contains, and Does Not Contain. Number filters include comparisons such as greater than, less than, and between. Dates can be filtered by particular dates or calendar periods, depending on the menu options. Use the condition that matches the actual requirement rather than manually hiding rows.
Use the FILTER function for a live result
If you want matching rows to appear in a separate area and update as the source changes, use the FILTER function. FILTER is available in Microsoft 365 and Excel 2021 or later. Unlike a dropdown filter, it returns a dynamic array of results in worksheet cells, leaving the source list visible. The output range needs room to spill; cells in its way can cause a spill error.
With Region in A2:A100, Product in B2:B100, and Sales in C2:C100, enter this formula in an empty cell to return complete matching rows where Region is West and Sales is at least 500: =FILTER(A2:C100,(A2:A100="West")*(C2:C100>=500),"No matches"). The formula returns the matching rows from columns A through C, or the text No matches if none meet both conditions.
The asterisk combines the two TRUE/FALSE tests as AND in this array calculation. To return rows that meet either condition, use addition between the tests instead. With Region in A2:A100, Product in B2:B100, and Sales in C2:C100, this formula returns rows where Region is West or Sales is at least 500: =FILTER(A2:C100,(A2:A100="West")+(C2:C100>=500),"No matches"). A row meeting both conditions is still returned once. Keep the ranges the same size so each row’s tests line up with the corresponding source row.
FILTER is useful for reports, dashboards, and repeatable views, but it does not replace every filter-menu feature. The source and criteria need to be expressed in the formula, and the returned results update when the source data changes. FILTER is not available in older perpetual versions such as Excel 2019 and Excel 2016; use the ribbon filter or Advanced Filter there.
Use Advanced Filter for a criteria range
Advanced Filter is useful when the conditions are more elaborate or you want to copy matching records to another location. It is a desktop Excel feature. Put the criteria in a small range above or beside the data, making sure the criteria headings exactly match the source column headings.
In an Advanced Filter criteria range, conditions on the same row are combined with AND; criteria on separate rows are combined with OR. For instance, to find Region West and Sales at least 500, put the Region and Sales headings on one criteria row and their respective values or comparisons on the row beneath. To find West records or records with sales at least 500, put the two tests on separate criteria rows. This row arrangement is different from the way the ordinary column filter menus are used.
To run it, select a cell in the list and go to Data > Advanced. In the dialog, check the list range, set the criteria range, and choose either to filter the list in place or copy the results to another location. The list needs headers, and the criteria range must include its matching header row. If you choose to copy results, specify a destination outside the source list. Read the dialog carefully before confirming so you do not overwrite existing data.
Choose the right approach
- Use column filter arrows for a quick, temporary view, especially when the conditions are in different columns.
- Use a custom filter for two comparisons on the same column, such as dates in a range or text matching one of two patterns.
- Use the checklist when you want to include several specific values from one field.
- Use FILTER when you need a formula-driven output that updates as the source changes and you have a supported Excel version.
- Use Advanced Filter when you need a criteria range, more complex AND/OR combinations, or a copied result set in desktop Excel.
For occasional analysis, the standard dropdowns are usually simplest. For a recurring report, a table with filter arrows or a FILTER formula may be easier to maintain. Advanced Filter can express criteria in a compact grid, but it requires more careful setup. The best option is the one that makes the intended AND/OR logic clear to the next person who uses the workbook.
Common problems and how to fix them
Excel filters only one column. If applying the second filter seems to replace the first, check whether the first column’s filter indicator is still active and whether you applied the second filter to the same field by mistake. Reopen the first column’s menu to confirm its selected values or condition.
The filter arrow is missing. Click within the list and use Data > Filter. If the list has blank rows or columns, select the intended range before turning on the filter. Converting a clean range to a table with Insert > Table can also make the data easier to manage; confirm that the table has headers.
A value seems to be missing. Check for leading or trailing spaces, inconsistent spelling, mixed data types, and active filters in other columns. A cell containing the text “500” may not behave like a numeric 500, and a date stored as text may not be recognized as a date. Clear other filters temporarily to see whether a second condition is excluding the record.
Nothing matches. Verify the comparison operator and the entered value. For a custom filter, confirm that AND is not selected where OR is intended, or vice versa. In a FILTER formula, ensure the source and test ranges cover the same rows, and check that matching text is spelled and spaced as it appears in the data. The formula’s third argument can provide a readable result when no rows match.
The FILTER result cannot spill. Clear cells in the intended output area, including cells that look empty but contain spaces or formulas. A dynamic array cannot overwrite existing content. Also check that the formula is not placed inside an Excel table when its result needs to spill into surrounding cells.
Frequently Asked Questions
How do I filter two columns at once in Excel?
Turn on Data > Filter, apply a condition from the first column’s arrow, then apply a condition from the second column’s arrow. Excel displays rows that meet both column filters.
How do I filter for two values in one Excel column?
Open the column arrow, clear (Select All) if necessary, and select both values in the checklist. Excel shows rows matching either selected value. For two comparison rules rather than two exact values, use the column’s Custom Filter dialog.
Can Excel filter with AND and OR criteria?
Yes. Separate column filters combine as AND. The Custom AutoFilter dialog lets you choose AND or OR for two conditions on one column. In Advanced Filter, same-row criteria mean AND, while criteria on separate rows mean OR.
Can I filter two criteria without hiding rows in the original list?
Yes. Use FILTER in Microsoft 365 or Excel 2021 and later to return matching rows in a separate, empty area. In desktop Excel, Advanced Filter can also copy matching records to another location.
Does Excel for Mac have the same filtering options?
Excel for Mac includes ordinary column filters; use the Data tab and the filter menus, though ribbon layout and keyboard shortcuts can differ from Windows. If a particular command, such as Advanced Filter, is not visible in your build, use the available filter menus or a supported FILTER formula. Excel for the web also provides basic filtering, but its available commands can differ from desktop Excel.
Related
- Excel Shuffle List of Names: 10 Easy Methods
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: How It Works and ExamplesIn Excel, COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria); for…
- 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 for Quarter From Date: 3 Easy MethodsIf the date is in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from 1…
- Conditional Formatting Only If a Cell Has DataYes. In Excel for Microsoft 365, select the cells to format, choose Home > Conditional Formatting > New…
- SUMIF Excel Formula: Examples, Syntax, and Common FixesTo add values that match one condition, use SUMIF: with regions in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"East",B2:B6)…
- Excel Named Range Not Appearing in Formula? FixesIf an Excel named range is not appearing as you type a formula, first confirm that the name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.