Excel AutoFilter

< Back to Search results

 Category: [Excel]  Demo Available 

Excel AutoFilter


Excel AutoFilter for Filtering a List of Excel Data Within a Criteria


Excel AutoFilters See Also: Display AutoFilter Criteria

One of the most useful functions in Excel is the AutoFilter. The AutoFilter allows a user to filter items in a list according to a set criteria. You can filter text, numbers or dates with AutoFilter. When you apply AutoFilters to a worksheet, filter switches (black drop down arrows) will appear to the right of your column headings. To activate AutoFilter, click in your heading row and go to Data>Filter>AutoFilter. Once the drop arrows appear, click on one of the arrows to the right of a column heading and you will see a list of options.

Data Table with AutoFilters Selected

We will take a look at the two most common options, All and Custom.  The other options are self-explanatory:

Sort Ascending Sorts the list in an ascending order (only in Excel 2003)
Sort Descending Sorts the list in an descending order (only in Excel 2003)
All Shows all items in the list once. 
Top 10 Shows the top 10 items in the list once
Custom Used to customise filter criteria
Blanks Will filter by blank cells.  Option only appears if you have a blank cell in your list
Non-Blanks Non-blanks will only appear if you have blanks in your list.  Will hide rows containing blank cells and show only non-blanks.

AutoFilter All

When you select the All option, the records for that column appear only once in the Filter list, even if they appear many times in your column.  Click on one of the records in the list, and you will see all the records for only the items selected displayed in your spreadsheet.  The drop down arrow will turn blue, and all other records not meeting the criteria you selected are hidden (as indicated by your row numbers which are now blue).  If a filter is no longer needed, click the All option from the drop down and your filter arrows will turn black again and all your records will be displayed.  To remove the AutoFilter arrows, go to Data>Filter>AutoFilter.

AutoFilter Compounding Filters

You can apply a compound filter to newly filtered data.  In other words, you can apply a filter upon a filter.  This is useful for restricting records even further. Say you have filtered your list (similar to the one in the screen shot) to see all people who work in the Administration Department.  We then want to know, that of all the people who work in the Administration Department, who are Personal Assistants.  To do this, all we do is select the drop down arrow to the right of the heading Role in column F, select Personal Assistant from the list, and all records meeting both criteria (those who work in Administration and who are Personal Assistants) will be displayed.

Compounding Filters

TIP! Excel's AutoFilter will only show 1000 unique items from a list. If you have more this and the value you want is not in the drop-down list, you can click Custom and type the value in.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

Array Formulas - Excel Array Formulas
Assign Macros in Excel
AutoComplete for Data Validation List


Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)