Filtering shows only the rows that match what you're looking for
Filtering in Excel hides rows that don't meet your criteria, so you can focus on specific data without deleting anything. The original data stays intact — you're just temporarily hiding rows. When you remove the filter, all rows reappear. This is different from sorting, which rearranges rows; filtering just conceals them.
The most common way to filter is using AutoFilter, which adds dropdown arrows to your column headers. You click the arrow, choose what to show, and Excel hides everything else. You can filter by text, numbers, dates, or custom conditions.
Filtering is useful when you have a large spreadsheet and need to see only sales from a certain month, only items in a specific category, or only rows where a number falls within a range you set.
Key Takeaways
- AutoFilter adds dropdown arrows to your headers and is the fastest way to hide rows that don't match your criteria.
- Click the dropdown arrow in any column header, uncheck the items you want to hide, and click OK to apply the filter.
- You can filter multiple columns at once, and each filter narrows down what the others show.
- The row numbers turn blue when a filter is active, reminding you that some rows are hidden, not deleted.
Turn on AutoFilter with one click
Start by selecting any cell in your data table — it doesn't matter which one. Then go to the Data tab at the top of the ribbon and click AutoFilter. Excel automatically detects where your data starts and ends, and adds a dropdown arrow to the header of every column.
If your data doesn't have headers in the first row, Excel will treat the first row as headers anyway. If that's wrong, you can manually select the exact range you want to filter before clicking AutoFilter, and Excel will respect that selection instead.
Once AutoFilter is on, you'll see small dropdown arrows next to each column name. The arrows stay there until you turn AutoFilter off again by clicking the AutoFilter button a second time.
Click the dropdown arrow and choose what to show
Click the dropdown arrow in the column you want to filter. A menu appears with a list of every unique value in that column. By default, all values are checked, meaning all rows are visible.
Uncheck the items you want to hide. For example, if the column contains product names and you only want to see "Widgets," uncheck everything except "Widgets." Then click OK. Excel hides all rows where the product is not a Widget.
The row numbers on the left turn blue to show you a filter is active. The dropdown arrow in that column also turns blue. This reminds you that you're looking at filtered data, not the complete table.
Filter by numbers and dates with comparison operators
For columns with numbers or dates, you can do more than just pick individual values. Click the dropdown arrow and select Number Filters (for numbers) or Date Filters (for dates). A submenu opens with options like "Greater Than," "Less Than," "Between," and "Equals."
Choose the condition you want. For example, select "Greater Than" if you want to see only sales over $500. A dialog box opens where you type the number you're comparing to. Click OK, and Excel hides all rows that don't meet that condition.
You can combine conditions too. If you want to see dates between January 1 and March 31, select "Between," then enter both dates. Excel shows only the rows in that date range.
Filter multiple columns at the same time
You can apply a filter to more than one column, and each filter narrows down what the others show. For example, filter the "Region" column to show only "North," then filter the "Product" column to show only "Widgets." Excel displays only rows where the region is North AND the product is Widgets.
Each column you filter gets a blue dropdown arrow. If you want to remove one filter but keep the others, click its dropdown arrow and select "Clear Filter." The other filters stay in place. To remove all filters at once, go to the Data tab and click AutoFilter again to turn it off.
The order you apply filters doesn't matter — the result is the same. Filtering by Region first then Product gives the same rows as filtering by Product first then Region.
Search within a filter dropdown when the list is long
If a column has hundreds of unique values, scrolling through the dropdown list is slow. Instead, use the search box at the top of the filter menu. Type part of the value you're looking for, and the list narrows to only matching items.
For example, if you're filtering a "City" column and you want to see only cities starting with "San," type "San" in the search box. The list shrinks to show only San Francisco, San Diego, Santa Fe, and so on. Uncheck the ones you don't want and click OK.
The search is not case-sensitive, so typing "san" finds the same results as "San." Once you click OK, the search box clears and the full list returns for next time.
Remove a filter or turn off filtering entirely
To remove the filter from one column only, click its dropdown arrow and select Clear Filter From [Column Name]. That column shows all values again, but filters on other columns stay active.
To remove all filters and show every row, go to the Data tab and click AutoFilter again. The dropdown arrows disappear, and all hidden rows reappear. Your data is unchanged — filtering never deletes anything.
If you want to see the filtered data again later, turn AutoFilter back on. Excel remembers your last filter settings and can reapply them, though you'll need to set them up again if you close and reopen the file.
Frequently Asked Questions
Does filtering delete rows I hide?
No. Filtering only hides rows temporarily. All your data stays in the spreadsheet. When you remove the filter, the hidden rows reappear exactly as they were. You can copy filtered data, and only the visible rows copy — the hidden ones stay behind.
Can I filter text that contains certain words?
Yes. Click the dropdown arrow in the text column and select Text Filters, then choose Contains. Type the word or phrase you're looking for, and Excel shows only rows where that column contains those characters anywhere in the text.
What if I filter a column and then add new data below?
The new rows won't be included in the filter automatically. You need to turn AutoFilter off and back on, or manually extend the filter range. The safest approach is to turn off the filter, add your new data, then turn AutoFilter back on so it includes everything.
Can I filter by color or formatting?
Yes, but only if you're using the Data menu's advanced filter options. Click the dropdown arrow, select Filter by Color, and choose the cell color or font color you want to show. This is useful when you've color-coded your data by status or category.
How do I show only rows where two columns both meet different conditions?
Filter the first column to your criteria, then filter the second column to its criteria. Excel applies both filters at once, showing only rows that satisfy both conditions. You can add a third, fourth, or more filters the same way.