Do you ever use the Excel Advanced Filter feature? Or is all your filtering done with an AutoFilter?
For ease of use, it’s hard to beat the Excel AutoFilter feature. Just click the Filter command on the Excel Ribbon’s Data tab, and the filter is ready to go.
You can click the drop down arrows in the column headings, and use the check boxes to select the items you want in the filter. Or, use the Text, Date or Number Filters commands, for variations on the filters.
Another quick way to filter is to right-click on a cell in the list, then click Filter, and click Filter by Selected Cell’s Value.
The Excel Advanced Filter isn’t as easy to use (I guess that’s why they call it Advanced 😉 ), but it does have some benefits that make it worth the effort.
After you click the Advanced command on the Excel Ribbon’s Data tab, the Advanced Filter dialog box opens. Fill in the details, and then click the OK button to filter the data.
The main reason that I use an Advanced Filter, especially when programming in Excel, is that it’s a quick and efficient way to copy specific data to a different worksheet.
In the screen shot above, the option is selected to copy the filtered data to another location. You can use that option to quickly create a report for each department, or salesperson, and email the results.
NOTE: You have to start the Advanced Filter from the destination sheet. Click this link for detailed instructions and a video.
Excel 2007 introduced the Remove Duplicates feature, but I still use an Advanced Filter to create lists of unique items. The Remove Duplicates feature strips the duplicates out of the list that is selected, so you have to remember to work on a copy of the list, if you want to keep the original list intact.
With an Advanced Filter, no items are removed from the list, so you don’t risk losing any of your original data.
With an Advanced Filter, you can create a criteria range on the worksheet, and see at a glance what the filter settings are. You can also create complex filters with AND/OR settings that go beyond what an AutoFilter can do.
For example, with an AutoFilter, you can select two specific customers, and two products for those selected customers.
With an Advanced Filter, you can create OR conditions between columns, such as Customer A OR Product B — you can’t do that in an AutoFilter!
_________