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.
Let us start with creating “Advanced Filter”
Below shown picture is the example of data that we will use in this example
- Click on any cell in the above mentioned data
- Click on Data ribbon
- From Sort & Filter group, click on Advanced
- Advanced Filter dialog box will pop up
- or use ALT + A + Q, ashortcut key
- 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
- Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.
- Click on OK button will give us the filtered data.Refer below snapshot.
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?