# How to Filter in Excel (Step by Step) Filtering in Excel hides the rows you do not need and shows only the rows that match criteria you set. You turn on AutoFilter from the Data tab, click the drop-down arrow in a column header, and choose the values or conditions you want. This guide shows how to filter in Excel on a real table, how to combine conditions across columns, and how to clear the filters when you are done. ## Quick Answer - Select any cell inside your data range, then go to **Data > Filter**. Drop-down arrows appear in the header row. - Click a column's drop-down arrow to filter by specific values, or open **Number Filters** and **Text Filters** for conditions like greater than, contains, or between. - Filters on different columns combine with AND, so Region = North and Amount > 500 returns only rows that satisfy both. - The status bar shows how many records match, which helps you confirm the filter worked. - To remove all filters, go to **Data > Clear**. The full table comes back. ## Before You Start A few things make filtering predictable. Your data should be in a rectangular range with a single header row and no blank rows or blank columns inside the block. Excel uses the header labels as the filter menu names, so duplicate headers cause confusion. If a header cell is empty, Excel may treat the row below it as part of the header. Check that each column holds one kind of value. A column that mixes text and numbers, such as "450" stored as text next to 720 stored as a number, will sort and filter in ways that look wrong. Numbers should be right-aligned by default and text left-aligned, which is a quick visual check. Dates are the most common source of trouble. A date that Excel stores as text will not respond to date filters the way you expect. In the example below, dates are built with the `DATE` function, so they are true date values. If you plan to add rows later, consider converting the range to an Excel Table with **Insert > Table**. Tables expand automatically and keep the filter arrows attached to the data. If you want to pull filtered results into another area instead of hiding rows, the dynamic array approach is covered in [Excel FILTER Function: Syntax and Examples](/blog/data-analysis/excel-filter-function-syntax-examples). ## Step by Step 1. **Select a cell in the data.** Click any cell inside the range, for example A1. You do not need to select the whole table. 2. **Turn on the filter.** Go to **Data > Filter**. Drop-down arrows appear in the header row of each column. 3. **Open a column menu.** Click the drop-down arrow in the Region header. A checklist of the distinct values in that column appears. 4. **Choose values.** Uncheck **(Select All)**, then check **North**, then click **OK**. Only North rows remain visible. 5. **Add a condition on another column.** Click the drop-down arrow in the Amount header, then choose **Number Filters > Greater Than**. Type 500 and click **OK**. 6. **Read the result.** The table now shows rows where Region is North and Amount is greater than 500. The row numbers on the left skip the hidden rows, which confirms the filter is active. 7. **Clear the filters.** Go to **Data > Clear** to remove every filter at once. To remove one column's filter, open that column's menu and choose **Clear Filter from "ColumnName"**. The order of steps 4 and 5 does not matter. Excel keeps every active filter and applies them together. ## Worked Example The table below is a 14-order sales log with columns for order ID, region, amount, date and sales representative. It is the dataset used throughout this section. | | A | B | C | D | E | |---|---|---|---|---|---| | 1 | OrderID | Region | Amount | Date | SalesRep | | 2 | 1001 | North | 450 | `=DATE(2024,1,15)` -> displays 01/15/2024 | Alice | | 3 | 1002 | South | 720 | `=DATE(2024,1,18)` -> displays 01/18/2024 | Bob | | 4 | 1003 | North | 610 | `=DATE(2024,1,20)` -> displays 01/20/2024 | Carol | | 5 | 1004 | East | 300 | `=DATE(2024,1,22)` -> displays 01/22/2024 | Dave | | 6 | 1005 | North | 890 | `=DATE(2024,1,25)` -> displays 01/25/2024 | Eve | | 7 | 1006 | West | 540 | `=DATE(2024,1,28)` -> displays 01/28/2024 | Frank | | 8 | 1007 | North | 320 | `=DATE(2024,2,1)` -> displays 02/01/2024 | Grace | | 9 | 1008 | South | 780 | `=DATE(2024,2,3)` -> displays 02/03/2024 | Heidi | | 10 | 1009 | North | 950 | `=DATE(2024,2,5)` -> displays 02/05/2024 | Ivan | | 11 | 1010 | East | 410 | `=DATE(2024,2,7)` -> displays 02/07/2024 | Judy | | 12 | 1011 | North | 670 | `=DATE(2024,2,10)` -> displays 02/10/2024 | Mallory | | 13 | 1012 | West | 830 | `=DATE(2024,2,12)` -> displays 02/12/2024 | Niaj | | 14 | 1013 | North | 510 | `=DATE(2024,2,15)` -> displays 02/15/2024 | Olivia | | 15 | 1014 | South | 600 | `=DATE(2024,2,18)` -> displays 02/18/2024 | Peggy | Apply the filter for Region = North and Amount > 500. The visible rows are: | OrderID | Region | Amount | Date | SalesRep | |---|---|---|---|---| | 1003 | North | 610 | 01/20/2024 | Carol | | 1005 | North | 890 | 01/25/2024 | Eve | | 1009 | North | 950 | 02/05/2024 | Ivan | | 1011 | North | 670 | 02/10/2024 | Mallory | | 1013 | North | 510 | 02/15/2024 | Olivia | Five of the fourteen orders match. Notice that order 1001 (North, 450) and order 1007 (North, 320) are hidden because their amounts are not greater than 500, and order 1002 (South, 720) is hidden because its region is not North. The two conditions act together, which you can write as $$\text{Region} = \text{North} \;\text{AND}\; \text{Amount} > 500$$ The caption for this result: the 15-row sales table after applying filters for Region = North and Amount > 500, showing only the matching rows. ## Other Ways to Do It **Number Filters and Text Filters.** The Amount column menu offers **Number Filters** with options such as Equals, Does Not Equal, Greater Than, Less Than, Between and Top 10. The Region and SalesRep columns offer **Text Filters** with Equals, Begins With, Ends With and Contains. The Date column offers **Date Filters** with presets such as Before, After, Between and relative periods. **Search box in the value list.** When a column has many distinct values, type into the search box at the top of the drop-down menu to narrow the checklist before you tick items. **Filter by color or icon.** If you have applied cell colors or conditional formatting icons, the column menu includes **Filter by Color**, which shows only cells with that fill or icon. **Criteria across columns.** Filters on separate columns combine with AND. To get OR behavior within one column, use the search box or a custom filter with two conditions joined by **Or**, for example Amount greater than 900 Or Amount less than 400. **Sorting alongside filtering.** The same drop-down menus include sort commands, so you can order the visible rows at the same time. See [How to Sort a Column in Excel (Step by Step)](/blog/data-analysis/how-to-sort-column-in-excel) for the sort options. **Finding values instead of hiding rows.** If you only need to locate a value, [How to Find and Search in Excel (Step by Step)](/blog/data-analysis/how-to-find-and-search-in-excel) covers Find and Go To. ## Troubleshooting **The filter arrows are missing.** The range may not be recognized as a table. Click a cell inside the data and try **Data > Filter** again. If arrows still do not appear, check for a blank row or column splitting the data into two blocks. **A number filter returns nothing.** The column probably contains text that looks like numbers. Select the column, then use **Data > Text to Columns** and click **Finish** to convert, or retype the values as numbers. **Dates will not filter by period.** Text dates do not group into months and years. Convert them to real dates, or rebuild them with the `DATE` function as in the example table. **New rows are excluded.** A plain range does not expand. Add rows inside the existing range, or convert the range to a Table so it grows with your data. **Filtered rows still appear in totals.** Functions like `SUM` and `AVERAGE` include hidden rows. Use `SUBTOTAL` or `AGGREGATE` to total only the visible rows. [How to Sum a Column in Excel (Step by Step)](/blog/data-analysis/how-to-sum-a-column-excel) explains the difference. **The row count looks wrong.** Check the status bar. It reports how many of the total records are showing, which is the fastest way to confirm a filter is doing what you think. ## Common Mistakes - **Filtering a range with blank rows inside it.** Excel stops at the blank row, so later rows never appear in the menu. Fix: delete the blank rows first, as shown in [How to Delete Blank Rows in Excel (Step by Step)](/blog/data-analysis/delete-blank-rows-in-excel). - **Expecting OR across columns.** Two column filters always combine with AND. Fix: put the OR conditions inside one column's custom filter, or use a helper column with a formula. - **Copying filtered data and pasting it into the same sheet.** Excel pastes into visible cells only in some cases and into hidden rows in others, which scrambles the layout. Fix: copy the visible rows to a new location, or use the FILTER function. - **Deleting rows while a filter is active.** You may delete hidden rows by accident. Fix: clear the filter with **Data > Clear** before deleting anything. - **Leaving a filter on and forgetting it.** A hidden filter makes totals and row counts look wrong to anyone opening the file later. Fix: clear filters before sharing, or note them in a cell. - **Treating duplicate values as errors.** Repeated entries in a column are normal and appear once in the value list. If you actually need to remove them, see [How to Remove Duplicates in Excel (Step by Step)](/blog/data-analysis/how-to-remove-duplicates-in-excel). ## Limitations AutoFilter hides rows in place. It does not create a new dataset, and the hidden rows still exist in the file. Anyone who clears the filter sees the full table again, and formulas that ignore visibility, such as `SUM`, will include the hidden values. If you need a separate, self-updating result set, a dynamic array formula or a PivotTable is the better tool. Filtering also depends on clean data types. Mixed text and numbers, text dates, and stray spaces cause filters to miss rows that look like matches. Spreadsheet software has a documented history of statistical accuracy issues in some functions [1], so when a filter feeds a calculation, verify the result against the visible rows rather than trusting the total. Filters are a view, not a data-cleaning step. ## Frequently Asked Questions ### How do I filter multiple columns at once in Excel? Turn on the filter, then set a condition in each column you care about. Excel keeps every active filter and applies them together with AND logic. The column menus show a funnel icon on any column that currently has a filter, so you can see at a glance which conditions are active. ### Why is my Excel filter not showing all the values? The most common cause is a blank row or blank cell inside the data range, which makes Excel treat the block as ending early. Another cause is a value stored as text when the rest of the column is numeric. Remove the blanks and unify the data type, then reopen the menu. ### How do I clear a filter in Excel? Go to **Data > Clear** to remove every filter in the sheet at once. To remove just one column's filter, open that column's drop-down menu and choose **Clear Filter from "ColumnName"**. The arrows stay in place either way, so you can filter again without re-enabling the feature. ### Can I filter by more than one value in the same column? Yes. Open the column menu, uncheck **(Select All)**, then tick each value you want. For conditions instead of exact values, choose a filter such as **Text Filters > Contains** and join two conditions with **Or** in the custom filter dialog. ### Does filtering delete the hidden rows? No. Filtering only hides rows. The data stays in the worksheet and returns as soon as you clear the filter. If you copy a filtered range and paste it elsewhere, only the visible rows are pasted, which is a common way to extract a subset without deleting anything. ## References 1. [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004) ## Further Reading - [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989) - [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7) - [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel) - [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984) - [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510) ## Related Articles - [How to Find and Search in Excel (Step by Step)](/blog/data-analysis/how-to-find-and-search-in-excel) - [Excel FILTER Function: Syntax and Examples](/blog/data-analysis/excel-filter-function-syntax-examples) - [How to Remove Duplicates in Excel (Step by Step)](/blog/data-analysis/how-to-remove-duplicates-in-excel) - [How to Alphabetize in Excel (Step by Step)](/blog/data-analysis/how-to-alphabetize-in-excel) - [How to Use the IF Function in Excel (Step by Step)](/blog/data-analysis/excel-if-function-step-by-step)