Filters in Excel is an amazing feature. I almost cannot recall a time when I have opened an Excel workbook and have not applied a filter. For me, Excel and and filter is almost synonymous. But, when I look back and ask myself the question - "what is the type of AutoFilter that I use mostly?" It is AutoFilter actually, not the Advanced one. I think it would be case for most of us as well,. Of course, there would be exceptions!
There is no learning required for start using AutoFilter. You open Excel Workbook and apply a Filter. Then from the dropdowns in different columns, you select the desired criteria. There are several options in standard Filter, that you can apply. Infact, you can narrow your criteria by filtering in different columns and with different conditions in AutoFilter and achieve the same result as Advanced Filter. In other words, AutoFilter can give similar results as Advanced Filter for most of the scenario.
AutoFilter can give similar results as Advanced Filter, with few exceptions
AutoFilter can be done anyone acquainted with Excel and can be self learned. All options can be explored visually with mouse. Following are some visual examples.
AutoFilter is selected by individual values. Values can be searched and the results can be appended by selecting Add current selection to filter. This Add current selection to filter only visible when search is used.
Advanced criteria in AutoFilter are contextual. This means that Excel understands the data that you have in each column. When you try to filter data in that column, depending on the data type, you will be given a options like Text Filter, Number Filter, Date Filter . These are the options, which you can use to create more complex criterias using greater than, less than etc.. Please refer to the following images of the options. Each of this options have Custom Search and are the key to create Search filter equivalent to the Advanced Search Filter
The Custom filters gives you option to create different conditions. One such sample is as follows:
Well, if autofilter can do all of the things that a Advanced Filter can do, then why do we need Advanced Filter?
The main problem with AutoFilter is that you cannot save the criteria or the filters that you applied to a sheet separately. This means that if you have a similar data on another sheet or you have received similar data from multiple source, you have to manually set the criteria in all those sheets. This is where the Advanced Filter comes Handy. You Create the conditions one time and use it again and again