To filter data by more than one condition in Excel, turn on filters for the full data range, then choose a condition in each relevant column. Filters in different columns work together, so a row stays visible only when it meets every selected column’s conditions. For more complex combinations—such as one condition or another across different columns—use the Advanced Filter tool in Excel for Windows.
What You’ll Need
- An Excel worksheet with a single, clearly labeled header row and one record per row.
- Microsoft Excel for Microsoft 365 on Windows for the steps below. Basic column filters are also available in Excel for Mac and Excel for the web; Advanced Filter is a desktop feature and is not available in Excel for the web.
- A saved copy of the original data if you plan to edit, delete, or rearrange the filtered records.
The example below uses a sales list with columns for Region, Rep, Product, Date, Units, and Status. The same process works with other kinds of lists, such as inventory, contact records, or project tasks.
Step-by-Step
-
Check and prepare the data. Confirm that each column has a unique, descriptive heading, such as Region or Status. Keep the headings in one row, with each record directly below them. Remove completely blank rows or columns inside the list, and avoid merged cells in the data area. A blank row can make Excel treat the records above and below it as separate ranges, which means some rows may not get filter controls.
Make sure values that should match are consistent. For example, East and East with a trailing space can be treated as different entries. Dates should be real Excel dates, not text that only looks like a date. Select a cell in the list and check that the entire range is a single continuous block before applying a filter.
-
Turn on filter controls. Click any cell in the data range. On the ribbon, select Data > Filter. Excel adds a drop-down arrow to each header cell. If it selects only part of the list, undo the command, remove any blank separators, and select the complete range—including the header row—before choosing Data > Filter again.
For a list you will keep updating, you can convert the range to a table instead: select a cell in the data and choose Insert > Table. Check that My table has headers is selected, then choose OK. Tables include filter arrows by default and generally expand to include new adjacent rows. On a Mac, use the same ribbon commands; in Excel for the web, the Filter command is on the Data tab, though the ribbon layout can differ slightly.
-
Apply the first condition. Open the arrow in a column heading, such as Region. Clear Select All, select East, and choose OK. Only rows with East in the Region column should remain visible. The hidden rows have not been deleted; Excel is temporarily hiding records that do not match.
You can also filter by a value search or a category. In the drop-down, type part of a value in the search box to find it, or select one or more entries from the checklist. Search helps with long lists, but check the selected items before applying it: a search can narrow the checklist without selecting the value you intend.
-
Add a condition in another column. Open the Status arrow and select Shipped. The visible rows must now have both Region set to East and Status set to Shipped. In ordinary column filtering, conditions on separate columns are combined as AND: a row must pass the Region condition and the Status condition to remain visible.
Continue in other columns as needed. For example, select a product in Product and a date range in Date. Excel combines all active column filters, so a row has to meet every active condition. The filter arrows on columns with an active filter usually show a filter icon, which helps you identify where conditions have been applied.
-
Choose multiple values or set a custom condition. If you want either East or West in the same Region column, open its arrow, clear Select All, and check both East and West. Multiple checked values in one column work as OR: a row can match East or West. This is useful for choosing several named products, representatives, or statuses.
For number, date, or text comparisons, open the column arrow and choose the relevant filter option, such as Number Filters, Date Filters, or Text Filters. Choose a condition such as Greater Than, Between, or Contains, enter the value or values, and select OK. Some custom filter dialogs let you join two conditions with AND or OR. Use AND when both conditions must be true—for example, units are at least 10 and fewer than 50. Use OR when either one is enough—for example, a status is Pending or Backordered.
-
Use Advanced Filter for more complex combinations. If regular filters cannot express the logic you need, create a criteria range in empty worksheet cells. Copy the exact relevant column headings from the data into the criteria range. Put criteria that must both be true on the same row; put alternative criteria on separate rows. For example, to find East-region records with Shipped status, put Region and Status as headings in adjacent cells, then enter East and Shipped beneath them on the same row. To include East and Shipped records or West and Pending records, place those two pairs on separate rows.
Click a cell in the source list, then choose Data > Advanced in Excel for Windows. In the dialog, check that the List range covers the full data including its headers, then select the Criteria range including its headings and criteria. Choose Filter the list, in-place to hide nonmatching rows, or Copy to another location to produce a separate result. If copying, specify a destination outside the source list and criteria range, then choose OK. Advanced Filter is available in desktop Excel, but not Excel for the web; use regular filters there or open the workbook in desktop Excel.
-
Review, adjust, or remove the filters. Check the visible rows and the filter icons to confirm the conditions you meant to apply. To change one condition, open that column’s drop-down and select different values or criteria. To clear only one column’s filter, open its drop-down and choose Clear Filter From followed by the column name. To clear filters across the list, select Data > Clear. To remove the arrows entirely, select Data > Filter again. Clearing filters reveals hidden records; it does not restore deleted data or undo edits.
Common Mistakes
- Filtering only part of the list: If rows are separated by blank lines or the selection excludes the bottom of the data, some records may not be included. Use one continuous range or convert it to a table.
- Expecting conditions in different columns to act as OR: Regular filters across columns require a row to match every column’s active filter. Use Advanced Filter criteria on separate rows when you need alternative combinations across columns.
- Leaving old filters active: A filter in another column may still be restricting the results. Look for active filter icons and clear conditions you no longer need.
- Mixing data types or inconsistent entries: Text-formatted dates, extra spaces, and spelling variations can keep expected records out of the results. Standardize the source values before filtering.
- Assuming hidden rows were removed: Filtering hides nonmatching rows, but they remain in the worksheet. Be careful when copying, deleting, or editing while a filter is active, and confirm the selected rows before making changes.
Tips
- Turn the range into an Excel table if you regularly add records. Tables make the data easier to manage and usually extend the filterable area as you add rows.
- Use the search box in a filter menu for long lists, then verify the checked values before you select OK.
- Give each heading a clear name and avoid duplicate header labels. Exact headings are especially important when setting up an Advanced Filter criteria range.
- Before sharing a workbook, consider clearing filters so other readers can see the full list. A filter can make records appear missing even though they are still present.
- For a complicated Advanced Filter, write the logic in plain language first. Mark conditions that must all be true on one criteria row, and put alternative combinations on separate rows.
Troubleshooting
The filter arrows do not appear: Click within the data and choose Data > Filter. If Excel detects only a small section, check for blank rows or columns dividing the list. Select the entire range manually, including headers, and apply the command again. If the worksheet is protected, check whether filtering is allowed in the sheet protection settings.
Expected rows are missing: Open each column’s filter menu and inspect its selected values and conditions. Clear all filters with Data > Clear, then reapply them one at a time. Check for leading or trailing spaces, different spellings, and values stored as text instead of numbers or dates. For a date filter, verify that the cells contain actual dates and not text strings.
The results include records you did not expect: Confirm the logic. Selecting several values in one column allows any selected value, while filters in separate columns narrow the list together. If you used Advanced Filter, confirm that the criteria headers exactly match the source headers, that AND conditions share a row, and that OR alternatives are on separate rows. Also check that the selected List range includes all source records.
You cannot find Advanced Filter: Advanced Filter is not available in Excel for the web. Open the workbook in the desktop Excel app and look under Data > Advanced. If you are using Excel for Mac, use the Data tab and look for Advanced in the filtering or sort-and-filter controls; ribbon placement can vary by version. Regular column filters are available in all three environments.
Frequently Asked Questions
Can I filter several columns at once in Excel?
Yes. Turn on Data > Filter, then apply a condition in each column. Excel keeps rows that meet all the active column conditions.
How do I show two values from the same column?
Open that column’s filter menu, clear Select All, and check both values. Excel displays rows matching either selected value in that column.
Can I use AND and OR conditions together?
Yes. Regular filters combine separate columns as AND, and a custom filter can join two conditions with AND or OR. For more complex combinations, use Advanced Filter and arrange criteria on the same or separate rows as needed.
Does filtering delete the rows that do not match?
No. Filtering hides nonmatching rows. Use Data > Clear to reveal them again, or remove the filter arrows with Data > Filter.
Why is my filter not finding a date or number?
The values may be stored as text, or may contain inconsistent spaces or formats. Correct the source data so Excel recognizes dates as dates and numbers as numbers, then clear and reapply the filter.
Related
- How to Divide in Excel Without Getting a Date Result
- How to Divide Every Cell in Excel by a Number: Steps
- How to Exit Group Edit Mode in Excel and Ungroup Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel ROUNDUP Examples: 10 Practical Ways to RoundExcel’s ROUNDUP function always rounds a number away from zero, which makes it useful when a decimal result…
- Excel Filter Duplicates: Keep the First RecordTo keep the first occurrence of each value in Excel, select the full data range and choose Data…
- 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…
- Average Days Between Dates in Excel: Formulas & TipsIf your dates are listed in chronological order in A2:A10, enter =AVERAGE(A3:A10-A2:A9) to calculate the average number of…
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- Excel Formula for Finding Matches: A Practical GuideTo find the position of a value, use MATCH with exact-match mode. With the value to find in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.