Excel AutoFilter or Advanced Filter?

filterclog Do you ever use the Excel Advanced Filter feature? Or is all your filtering done with an AutoFilter?

AutoFilter Benefits

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.

FilterAdvAuto01

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.

filteradvauto02

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.

filteradvauto03

Advanced Filter Benefits

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.

advancedfilter04

Filter Data to Anther Sheet

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.

Create a List of Unique Items

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.

Complex Filters

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.

filteradvauto04

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!

filteradvauto05

_________

Save

You may also like...

17 Responses

  1. Jon says:

    I’ve developed a whole class around the advanced filter. It makes it much easier to use and the class figures out how to order everything for me. All I tell the class is, I want you to AND these in this column and OR it with these from another column, I want them to be for the whole selection, or match case, or use wild cards, or spit out unique values after the filter, etc. It took me three weeks (someone skilled probably could have done it in a couple of days) to write but it is so nice to have a piece of code that can make filter super easy. Many times I’ll opt for the class over using the find function.

  2. Gregory says:

    When I try and use the Advanced Filter to copy filtered data to another worksheet a pop-up box appears telling me that you can only copy filtered data to the active sheet. Are you able to copy filtered data to another worksheet using the Advanced Filter via VBA or does VBA have it’s own functions?

    Oops, never mind I just used the macro recorder to copy filtered data to the same sheet, then modified the CopyToRange in the VBA code to include another worksheet and it worked like a champ.

  3. Dominik says:

    Gregory,
    you have to open the Advanced Filter dialog box from the destination(!) sheet.
    Regards,
    Dominik.

  4. Gregory says:

    @Dominik Thanks for that tip. It seems weird we have to do that just to copy data to another worksheet.

  5. @Jon, sounds like all your programming work was worth the effort!

  6. @Dominik, thanks for answering the question.

    @Gregory, glad you figured out how to make it work in your code, and there is a short video here showing the steps to filter to a different sheet manually:

    http://www.contextures.com/xlVideos04.html#AdvFiltSheet

  7. Jon says:

    I know this was from a long time ago. But since I created that class, I’ve actually developed a userform in .NET that makes it super simple to use AdvancedFilter with an auto complete drop down menu. It wasn’t too bad to implement. But it sure is nice to just get a quick report.

  8. Hugo says:

    @Jon
    Nice to hear that your effort resulted in valuable solutions.
    Do you think it’s worth to share your results with us? Hopefully both variants – VBA and .NET.
    We’d like to learn from you.

  9. Jon says:

    Hugo,

    I can do some blog posts on my site with the .NET information – I’ve posted some on it, but not as much as I should have. I don’t know if Debra does cross blogging or not (and I’m not even sure if it was within her comment policy to allow me to comment, but she hasn’t deleted anything, so I guess it was fine).

    My VBA solution is buggy and not written out very well – it was written when I was still learning quite a bit. So, if you want the code I could send it to you, you can post your e-mail here, or comment over at my blog, or just e-mail me directly at jon@spreadsheetbudget.com.

    The actual userform is a paid product (I figured it cost me about $15k to write it (included the time I spent developing it), so it would be nice to recoup that money).

  10. Hugo says:

    @Jon
    For your commercial product, I agree it’s bad to offer the code.
    The VBA solution, I understand, is not in a state to be published for direct use by others.
    But -thanks for your answer.

  11. Jon says:

    @Hugo,

    I was thinking about it and I’ll go ahead and try and convert it to VBA and I’ll post it to my blog. I usually work on that kind of stuff on Mondays and then post on Wednesdays. I’ll do the RPN class today. I think that should be relatively easy! After that is just the actual “Search” class and I imagine that shouldn’t be too terribly difficult to convert over either. The worse part is just that .NET makes it so easy to program that when you go back to VBA you feel like you’ve gone back to the stone ages :)

  12. Hugo says:

    @Jon
    We are used to use stone-age tools! :-)
    Greatly appreciate your approach.

  13. Jon says:

    OK Hugo, just posted the Search class (AdvancedFilter Wrapper) VBA code. Hope it all works well. I think I got most of the errors!

    http://www.spreadsheetbudget.com/2012/12/18/advancedfilter-wrapper-engine-for-vba-enthusiasts/

  14. vinay bajpai says:

    Before this blog i was knowing that the basic difference between auto filter and advanced filter that auto filter is working on the same date from where we want to get result and advanced filed gives an option to filter data at another place. But today i got the difference why it is called advanced filter.

    thanks for this post

  15. SUNDER JB says:

    Hi, It was very useful for me to under stand the concept of filter and two types of filter can be done. But you
    have to open the advanced filter dialog box from the destination sheet.
    Regards,
    thank-you.

  1. March 11, 2013

    […] A pivot table would work if you group the dates by month in the pivot table and then filter only for, say April. An easier way is to turn on Autofilters (Select all the cells in your database, then click Data-Filter) and filter the dates for just April. In case you're not familiar with filters this link may be helpful: Excel AutoFilter or Advanced Filter? | Contextures Blog […]

Leave a Reply to SUNDER JB Cancel reply

Your email address will not be published. Required fields are marked *