Excel's AutoFilter shows and hides rows based on what you choose, without deleting anything

Filtering in Excel means telling the program to display only the rows that match what you're looking for, while hiding the rest. The data stays in your spreadsheet — nothing is deleted or moved. You can turn the filter off anytime and see all your rows again. This is different from sorting, which rearranges your data into a new order.

The most common way to filter is AutoFilter, which adds dropdown arrows to your column headers. You click the arrow, choose which values you want to see, and Excel hides the rows that don't match. If you have a list of sales by region and you only want to see the Northeast, AutoFilter lets you do that in seconds.

Key Takeaways

  • AutoFilter adds dropdown arrows to your headers and lets you pick which values to display by clicking checkboxes.
  • You turn AutoFilter on by selecting any cell in your data and clicking the Filter button in the Data tab on the ribbon.
  • You can filter by multiple columns at once — for example, show only Northeast sales from January.
  • Clearing a filter shows all rows again without changing your original data.
  • Custom filters let you set conditions like "greater than 100" or "contains the word urgent" instead of picking from a list.

How to turn AutoFilter on and use the dropdown arrows

Click any cell inside your data table. Then go to the Data tab on the ribbon at the top of Excel and click Filter. Excel will add a small dropdown arrow to the right of each column header. These arrows are your filter controls.

Click the dropdown arrow in the column you want to filter. A menu appears with a list of every unique value in that column, each with a checkbox next to it. By default, all boxes are checked, meaning all rows are visible. Uncheck the values you want to hide. For example, if your column lists regions and you uncheck "West" and "South", only rows with "Northeast" and "Midwest" will show. Click OK and Excel hides the rows that don't match.

The dropdown arrow turns blue when a filter is active on that column, so you can see at a glance which columns are filtered. You can filter multiple columns at the same time — click the arrow in a second column and uncheck more values. Excel will show only rows that match all your filters.

Clearing a filter to see all your data again

To remove a filter from one column, click its dropdown arrow and click Clear Filter From [Column Name] at the top of the menu. All rows in that column become visible again, but your other filters stay active.

To remove all filters at once and see your entire spreadsheet, go to the Data tab and click Filter again. This turns off AutoFilter completely and removes all the dropdown arrows. Your data returns to normal view.

Using custom filters for conditions like "greater than" or "contains"

Sometimes you don't want to pick from a list of values — you want to set a condition. For example, you might want to see only sales over $5,000, or only entries that contain the word "urgent". This is where custom filters come in.

Click the dropdown arrow in your column and select Filter by Condition or Number Filters (the exact name depends on what type of data is in the column). A submenu appears with options like "Greater Than", "Less Than", "Equals", "Contains", and others. Click the condition you want. A dialog box opens where you type the value you're comparing to — for example, 5000 or "urgent". Click OK and Excel shows only rows that meet that condition.

You can combine custom filters with regular filters. For instance, filter one column to show only "Northeast" and another to show only sales greater than $5,000. Excel displays rows that match both conditions.

Filtering dates and times

Date columns have their own filter options. Click the dropdown arrow in a date column and you'll see options like "Before", "After", "Between", and "Equals". You can also expand the list to see dates grouped by year, month, or week — click the arrow next to a year to see the months inside it, then click the arrow next to a month to see individual dates. Uncheck the dates or date ranges you want to hide.

This is useful when you have a long list of transactions and you only want to see what happened in a specific month or year. You can filter to show only entries from January 2024, for example, without typing anything — just expand the year and month and uncheck the dates you don't need.

What happens when you sort filtered data

If you sort a filtered spreadsheet, Excel sorts only the visible rows. The hidden rows stay hidden and don't move. This is usually what you want — if you've filtered to show only Northeast sales, sorting by amount will sort only those Northeast rows. But it's worth knowing so you don't accidentally think your entire dataset has been sorted.

If you clear the filter afterward, the rows that were hidden will reappear, but they won't be in the sorted order — they'll be back in their original positions. If you need to sort your entire dataset, clear all filters first, then sort.

Copying filtered data to another location

When you copy rows from a filtered spreadsheet, Excel copies only the visible rows — not the hidden ones. This is useful if you want to extract a subset of your data. Select the filtered rows you want (including headers), copy them with Ctrl+C, click a new location, and paste with Ctrl+V. Only the visible data will paste.

If you want to copy the hidden rows too, you have to clear the filter first, then select and copy. There's no way to copy only the hidden rows without unhiding them.

Frequently Asked Questions

Can I filter by more than one value in the same column?

Yes. Click the dropdown arrow, and you'll see checkboxes next to each value. Check only the values you want to see. For example, if your column lists colors, you can check only "Red" and "Blue" to hide all other colors. Excel shows rows where the column contains either Red or Blue.

What's the difference between filtering and sorting?

Filtering hides rows without moving them — your data stays in its original order, just with some rows hidden. Sorting rearranges all your rows into a new order, like A to Z or smallest to largest. You can do both: filter first to show only the rows you care about, then sort those rows.

If I filter my data and save the file, will the filter stay when I open it again?

Yes. Excel remembers which filters you set. When you reopen the file, the same rows will be hidden. If you want someone else to see all the data, clear the filters before you send them the file.

Can I filter text that's only part of a cell?

Yes, using a custom filter. Click the dropdown arrow and choose "Text Filters" or "Contains". Type the text you're looking for — Excel will show rows where the cell contains that text anywhere in it, not just at the beginning.

What if I filter a column and see no results?

This means no rows match the values you selected. Check your filter settings by clicking the dropdown arrow again — you may have unchecked everything by mistake, or the value you're looking for might be spelled differently than you thought. If you're using a custom filter, double-check the condition and the value you typed.