Introduction to Advance Filter

 

Advance Filter is the most powerful feature of Excel. The advanced filtering feature in Excel allows you to quickly copy unique information from one data list to another. It allows the person to quickly remove duplicates, extract records that meet certain criteria. It works great when we use wildcards, within 2 date criteria.

Filtering is a simple, however, amazing & powerful way to analyze data. Advance filter are quite easy to use. Here’s how you can use Excel’s advanced filtering capabilities.
 
img1
 
Let us start with creating “Advanced Filter”
 
Below shown picture is the example of data that we will use in this example
 
img2

 

  • Click on any cell in the above mentioned data
  • Click on Data ribbon
  • From Sort & Filter group, click on Advanced

img3
 

  • Advanced Filter dialog box will pop up
  • or use ALT + A + Q, ashortcut key

img4
 

  • The List range box will automatically takes the data range (eliminates the step for user to select whole data)
  • In Criteria range; select the criteria range as I1:N2

img5
 

  • Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.

img6
 

  • Click on OK button will give us the filtered data.Refer below snapshot.

img7
 
All the rows which contain Manager as Susan will get extracted.
 
ADVANCE FILTER INDEX:

Introduction to Advance Filter
1. Using “And”, “Or” and Multiple Criteria
2. How to Filter Unique Records?
3. How to Use Wildcards?
4. Extract records after specific date criteria
5. Extract items between 2 Date’s criteria
6. How to extract data with specific text?
7. How to extract data to another worksheet using VBA?
Download E-Book



10 thoughts on “Introduction to Advance Filter

    • Are you setting up the Criteria Range? The post doesn’t explain it well. Where it says “In Criteria range; select the criteria range as I1:N2″, see that it shows Susan in the appropriate cell (the 5th column over from the beginning of the range, just as manager in the data is the 5th column of that range). The post doesn’t really explain doing this, just shows an image.

      • Hi Slightly Less Confused,

        You need to download the excel file & follow below steps:

        • In Excel file you will find Raw Data sheet.
        • Copy the header range A1:F1 & paste to range I1:N1
        • In Manager column i.e. in cell M2 type “Susan”
        • Then press shortcut keys ALT + A + Q & follow the steps mention in the book.

        I follow the same & got the filter data.

        @AfroDaby: Hope this will help you.

        Regards,
        Ashish

  1. Advance Filters are very useful, but it’s a big call to say “Advance Filter is the most powerful feature of Excel.” I doubt you’ll find many agreeing with you that they’re more powerful than Pivot Tables, or the new PowerPivot in 2013.

    • Completely agree with you but do you have idea how many users use 2013???

      I wonder they will be many who are still using 2007 or 2010. So this is good for them and specially for beginners.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>