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

Users are saying about us...

    • Slightly Less Confused

      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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube