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.

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.

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.

Gallery

stars (0 Reviews)

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

Website designed by Astrava Solutions Ltd.