Easy AutoFilter Basics for Beginners

Download Article

Filtering and sorting data in Excel just got much easier

Written by Megaera Lorenz, PhD

Last Updated: February 26, 2026 Fact Checked

Download Article

Getting Started with AutoFilter

|

Using Advanced Filters

|

FAQ

|

Video

|

Q&A

|

Tips

|

Warnings

Excel's AutoFilter feature makes it quick and easy to sort through large quantities of data. We'll show you how to use both pre-set and custom filters to boost your productivity in Excel in no time! We’ll also explain how to filter for more complex criteria using the Advanced Filters feature.

Quick Steps to Use AutoFilter in Excel

Add a descriptive header to the top of each column of data.
Select the data range you want to filter.
Click the Data tab and select Filter.
Click the dropdown arrow on the header of the column you want to filter.
Select your desired sort and filter criteria in the dialog.
Click Apply Filter.

Section 1 of 3:

Getting Started with AutoFilter

Download Article

1
Create a spreadsheet. Make sure that your sheet has column headings that describe the type of data in each column. The heading is where the filter will be placed, and it will not be included in the data that is sorted. Each column can have a unique dataset (e.g., date, quantity, name, etc.) and contain as many entries as you wish to sort through.
- You can freeze your headings in place by selecting the containing row and going to the View tab, then selecting Freeze Panes. This will help keep track of filtered categories on large data sets.
2
Select all the data you wish to filter. Click and drag to select all of the cells you wish to be included in the filter. Since AutoFilter is, as the name implies, an automatic process, you cannot use it to filter non-contiguous columns. All columns in between will be set to filter with them.
- To select the entire sheet, press Ctrl + A (Windows) or Command + A (Mac).
Advertisement
The Filter button looks like a funnel, and it’s located roughly in the middle of the Data menu. Clicking it will turn on AutoFilter. Once it’s activated, the column headers will have drop-down buttons. Using these buttons, you can set your filter options.^{[1]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
4
Click a header dropdown menu to select filter criteria. Filters options can vary based upon the type of data within the cells. Text cells will filter by the textual content, while number cells will have mathematical filters. There are a few filters that are shared by both. When a filter is active, a small filter icon will appear in the column header. The available filter criteria include:
- Sort Ascending: Sorts data in ascending order based on the data in that column; numbers are sorted from low to high (1, 2, 3, 4, 5, etc.), and words are sorted alphabetically starting with a, b, c, d, e, etc.
- Sort Descending: Sorts data in descending order based on the data in that column; numbers are sorted in reverse order (5, 4, 3, 2, 1, etc.), and words are sorted in reverse alphabetical order (e, d, c, b, a, etc.).
- Sort/Filter by color: These options allow you to sort or filter cells by cell color, font color, or cell icon.
- Specific conditions (in the Choose One menu): Some filter parameters can be set using value logic, like filtering values greater than, less than, equal to, before, after, between, containing, etc. You can see these options by clicking the Choose One dropdown under the Filter header. After selecting one of these, you will be prompted to enter the parameter limits (e.g. After 1/1/2011 or greater than 1000).
  - Once you’ve set one condition, you’ll have the opportunity to set more. Select the “and” or “or” radio button to choose how to apply multiple filters.
- Note: the filtered data is hidden from view, NOT deleted. You will not lose any data by filtering.
5
Manually filter specific data with Search or the checklist. In the Filter menu, you should see a list of all the data in the selected column. Uncheck any items you want to filter out. Check Select All to fill or clear the entire checklist. If you want to search for items to filter out, type a keyword into the Search field. For example:^{[2]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- To find all entries relating to soup, type soup into the Search bar. Add * to your keyword to represent any series of characters or ? to represent a single character.
6
Click Apply Filter or OK to apply your filter. Depending on your version of Excel, you might be able to check the Auto Apply box in the Filter menu to apply changes instantly as you adjust the sort/filter settings.
- Click Clear Filter (if it’s available) to clear all filter settings before closing the menu.
7
Click the Filter button again to deactivate AutoFilter. As soon as you click the Filter button in the Data menu, all your filters will turn off. To clear filters on a single column, click the dropdown menu on the header and click the Clear Filter button.
- You can also click the Clear and Reapply buttons in the Data menu next to the main Filter button to remove or reapply filters without deactivating AutoFilter.

Section 2 of 3:

Using Advanced Filters

Download Article

1
Put your data in a table. Excel also has an option to use advanced filters if you want to filter using more complex criteria. In order to do this, you’ll need to take a few extra steps. First, select the data in your spreadsheet, then go to the Insert tab and click Table. Click OK in the window that pops up.
- Your table should have headers with names that describe the contents of each column. Make sure there are already headers in the first row, and check the My table has headers box in the Create Table dialog window.
2
Create a separate table defining your criteria. Now you’ll need to set aside another range of cells with the criteria you want to use for your advanced filter. Place this table above or next to the table containing your data. The information you put in this table depends on how you want to filter your data.^{[3]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- For example, say you want your criteria to include data from columns in your table called “Product Type” and “Price,” and you want to filter your data to show only cookies that cost more than $3.00. Your second table would have the headers Product Type and Price, and the columns would contain the criteria cookie and >$3.00, respectively.
- If you want to use “OR” rather than “AND” logic, place your data in separate (staggered) columns.
3
Use operators to further define your criteria. Operators are symbols you can add to the data in your criteria table to tweak the way your list data is filtered. For example, you can use these symbols to specify that you want to see numbers that are greater than, less than, or equal to a particular value, or that you want to match a word exactly. Here are some numerical and text operators you can use:^{[4]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- = : Filters for values equal to a specified number or word. To avoid filtering issues caused by the use of = to start Excel formulas, use this format when filtering for text: =”=cookie”
- > or < : Filters for values greater than or less than a specified number. When applied to text, these filter out values that are alphabetically ordered after or before your specified text.
- >= or <= : Filters for values greater than or equal to, or less than or equal to, a specified number.
- <> : Filters for values not equal to a specified number or word.
- *[text]* : Use on either side of a text string to filter for cells that contain the specified text (e.g., *cookie* would include cells with data like “chocolate chip cookie”).
- =”=*[text]” : Filters for cells ending in the specified text.
- You can also use * and ? as wildcards to filter for text strings. * stands for any number of characters, while ? stands for a single character. For example, cook* would filter for cells containing “cooks”, “cookie,” or “cookies”, while cook? might filter for cells containing “cooks”, but not “cookie” or “cookies.”
  - If you want to include a literal ? or * in your filter criteria, put a tilde (~) in front of it (e.g., ~* or ~?). For a literal tilde, write ~~.
4
Go to the Data tab and click Advanced. The Advanced button is in the Sort & Filter section next to the main Filter button. It looks like the filter button with a gear superimposed on it. Click it to open the Advanced Filter dialog.
5
Define the ranges of your data list and your criteria. Click the List range field and enter the range for your main data list. You can simply select the entire table to automatically fill in the range. Do the same thing in the Criteria range field.
6
Click OK to apply the filter. Any data not matching your criteria will be hidden. Click Clear in the Short & Filter section of the Data menu to unhide the filtered data.

Section 3 of 3:

Frequently Asked Questions

Download Article

1
How can I tell when filters are active? If a filter is active in a column, you’ll see a filter symbol next to the dropdown arrow at the top of the column.
2
Can I filter non-adjacent columns? Not with AutoFilter, although there are ways to do it using the Visual Basic (VBA) editor or the Advanced Filters option.
3
Why isn’t my filter detecting all my data? If there are any blank rows or columns in your dataset, AutoFilter will stop detecting data after the blank. Either manually delete any blank rows or columns, or convert your data into a table to remove them automatically.

Community Q&A

Search

Add New Question

Question

How do you know when the autofilter is turned on?

Community Answer

Dropdown menus will appear at the top of each column. Those with active filters will display a small filter icon next to the dropdown menu arrow.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 7
Question

Not all the variables in my field of data are appearing in my filter selection. How do I correct this?

Community Answer

It's possible that you have set conflicting filters. Try clearing some of your filters. If you are using a custom filter, check your "and/or" logic.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 4
Question

How can I set Excel 2007 so that upon opening, AutoFilter will have the "select all" box to be unchecked?

Community Answer

This checkbox cannot be deselected by default. If you use Advanced Filter instead of AutoFilter, you can select which ranges you want filtered from the start.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 1 Helpful 2

See more answers

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

Tips

AutoFilter arranges data vertically, meaning filter options can only be applied to column headings but not to rows. However, by entering categories for each row and then filtering that column only, you can get the same effect.

Thanks
Helpful 0 Not Helpful 0
The filter won't work beyond any blank cells if you leave any cells blank.

Thanks
Helpful 0 Not Helpful 0
Backup your data before using AutoFilter. While AutoFilter can be turned off, any changes made to the data may overwrite your existing information.

Thanks
Helpful 0 Not Helpful 0

Submit a Tip

All tip submissions are carefully reviewed before being published

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!

Warnings

Save your changes frequently unless you have backed up your data and are not planning to overwrite your data.

Thanks
Helpful 5 Not Helpful 2
By filtering your data, you are not deleting rows; you are hiding them. Hidden rows can be unhidden by selecting the row above and below the hidden row, right-clicking on them, and selecting "Unhide".

Thanks
Helpful 0 Not Helpful 0