What a drop-down filter does and why you'd use one
A drop-down filter in Excel lets you show or hide rows based on what you pick from a list. Instead of scrolling through hundreds of rows to find what you need, you click a small arrow next to a column header and select the values you want to see. Excel then hides everything else.
This is different from sorting, which rearranges your data. A filter leaves your data in place and just shows you a subset. If you have a spreadsheet of sales by month, a filter lets you see only January without moving the February rows anywhere — they just disappear from view until you clear the filter.
The technical name is AutoFilter, and it works the same way in Excel for Windows, Excel for Mac, and Excel online. Once you turn it on, you can filter by text, by number, by date, or by custom rules you set yourself.
Key Takeaways
- Click any cell in your data table, then go to the Data tab and click AutoFilter to add filter arrows to your column headers.
- Click the arrow next to any column header, uncheck the values you want to hide, and click OK to filter that column.
- You can filter by multiple columns at once — each filter narrows down what the others show.
- Filtering hides rows but does not delete them; clear the filter to see all your data again.
- If your data has no header row, Excel will treat your first row of data as headers, so add a real header row before you filter.
How to turn on AutoFilter in three steps
Start by clicking any cell inside your data table — it does not matter which one. Excel will recognize the whole connected block of data as your table.
Go to the Data tab at the top of the ribbon. On the left side, you will see a button labeled AutoFilter. Click it. Small dropdown arrows will appear in the header row of your table, one in each column.
If no arrows appear, check that your data has a header row (the first row with column names). If your first row is data instead of headers, add a header row above it, then try AutoFilter again.
How to filter by a single column
Click the dropdown arrow next to the column header you want to filter. A menu will open showing every unique value in that column, with a checkbox next to each one.
By default, all values are checked. Uncheck the ones you want to hide. For example, if the column shows months and you want to see only January and February, uncheck March through December. Then click OK at the bottom of the menu. Excel will hide all rows where the month is not January or February.
The arrow in that column header will turn blue to show a filter is active. To remove the filter and see all rows again, click the arrow and click Clear Filter.
How to filter by multiple columns at once
You can explore filters to more than one column, and they work together. If you filter the Month column to show only January and the Region column to show only North, Excel will show only rows that are both January and North.
Click the dropdown arrow in the second column you want to filter. Uncheck the values you want to hide, just as you did for the first column. Click OK. Now both columns have blue arrows, and your table shows only rows that match both filters.
Each filter narrows down what the others show. If you have 1,000 rows and filter to January (100 rows) and then to North (20 of those January rows), you will see only 20 rows. Remove either filter to see the larger set again.
How to use number and date filters for ranges
If your column contains numbers or dates, the filter menu has an extra option called Number Filters or Date Filters (depending on what type of data is in the column). Click the dropdown arrow, then look for this option near the top of the menu.
Click Number Filters or Date Filters and you will see choices like "Greater Than", "Less Than", "Between", and "Equals". These let you set a range instead of picking individual values. For example, you can filter a sales column to show only amounts greater than $5,000, or a date column to show only dates after January 1, 2024.
Choose the condition you want, enter the number or date, and click OK. This is faster than unchecking hundreds of individual values when you want to see a range.
What happens to your data when you filter
Filtering hides rows, but it does not delete them. Your original data is still there. Row numbers on the left side will skip (you might see rows 1, 2, 3, then jump to 7, 8, 9) to show you that some rows are hidden. If you copy and paste filtered data, you will copy only what is visible, not the hidden rows.
To see all your data again, click the dropdown arrow in any filtered column and click Clear Filter. Or go to the Data tab and click AutoFilter again to turn off all filters at once and remove the dropdown arrows.
If you want to delete the hidden rows permanently, you have to unhide them first, select them, and delete them manually. Filtering alone will not remove data from your file.
Common mistakes and how to avoid them
The most common mistake is forgetting that you have a filter on. You might think data is missing when it is just hidden. Check the column headers — if any arrow is blue, a filter is active. Look at the row numbers on the left; if they skip, rows are hidden.
Another mistake is explore AutoFilter to data that has no header row. Excel will treat your first row of data as a header, which means you will not be able to filter that row's values. Before you filter, make sure your first row contains column names, not data.
If you add new data below your table after you turn on AutoFilter, the new rows will not have filter arrows. You can either turn AutoFilter off and back on to include the new data, or manually add the new rows to your table range.
Frequently Asked Questions
Can I filter text that contains certain letters or words?
Yes. Click the dropdown arrow in the text column, then click Text Filters near the top of the menu. Choose Contains, type the letters or words you want to find, and click OK. Excel will show only rows where that column contains your search term.
What if I want to filter by color or by the cell format?
Click the dropdown arrow in the column, then look for Filter by Color near the bottom of the menu. You can filter to show only cells with a specific background color or font color. This works if you have manually colored cells or if your spreadsheet uses conditional formatting.
Can I save a filter so it stays the same when I reopen the file?
Yes. Excel saves your filter settings with the file. When you reopen the spreadsheet, the same filters will be active and the same rows will be hidden. If you want to remove the filters before saving, clear them first, then save the file.
Does filtering work the same way in Excel online as in the desktop version?
Yes, the steps are identical. Click any cell in your data, go to the Data tab, click AutoFilter, and use the dropdown arrows to filter. Excel online has all the same filter options as the desktop version.
What if my table has merged cells in the header row?
AutoFilter does not work well with merged cells. Unmerge the header cells first, then turn on AutoFilter. If you need a title above your table, put it in a separate row above the header row, and do not merge cells in the actual header row.