Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel AutoFilter

| | Information Helpful? Why Not Donate.

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.

Learn About Custom AutoFilters

See Also: Display AutoFilter Criteria

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

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