Extracting data in Microsoft Excel - Advance Filter

Before starting this article, I thank to all members of CAclubindia for appreciating my articles on Advance Excel.

This article is in continuation with my Advance Excel tips and inspired by one of my friend facing problems in extracting relevant data in Microsoft Excel from Huge Raw Data, dumped from ERP software.

In This Article you will see how to relevant data get extracted from properly set up Excel Data Table, under basic to advance scenarios.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER

For this you first need to download Sales Workbook, click here. 

Scenario -1 in this scenario, from sales data, our aim is to extract records related to sales of "Hardware Model C" from the Product column.

Step - 1 Write down Initial Maintenance under heading name Product, anywhere near with main data. I just mentioned this over the main data for ease of understanding.

Step - 2 go to data tab and select Advanced or you may use combination of short cut keys Alt+A+Q

Step - 3 In Next screen we have to select under Action to either Filter the list, in-place or to extract a Copy to another location of the selected data. I Select Copy to another location, since it leaves the original data untouched and all the rows remain visible and make possible to review/compare the result of different queries for future review.

4. The List range field specifies the Excel range containing the source data.  In this example it is $C$5:$E$288 as List range.

5. Select $B$2:$b$2 as Criteria range

6. Select $H$5 as Copy to location and click OK

Note: the Unique records only checkbox indicates that the result should have no duplicate results, where duplicates are defined as having the same value in every output column. I leave it as unchecked.

Instantly will get your relevant data information related to product

Hopefully you got the basic understanding of Advance Filter option. But if you are regularly using basic Auto Filter, then you might ask where is advance excel function. Now let me explain.

Scenario-2 In first instance our Criteria is limited only to "Hardware Model C", but now, suppose, we also wants to extract south region as addition criteria i.e. all sales records of "Hardware Model C" made in "East" region.

For this we just need to expand our criteria by adding Region as Heading and East as additional criteria.  

And redo the mentioned in step 2 to 6 in this way    

Instantly, your data get extracted with additional criteria.

Scenario - 3 Now our criteria is not limited only to the "Hardware Model C" with region "East". We also wants to extract the sales of "Hardware Model A" in "South" i.e. Sales records related to "Hardware Model C" and "Hardware Model A" in "East" and "South" respectively.

For this, we just need to expand our criteria by adding "Hardware Model A" and "South" under Product and "Region" respectively.     

And repeat step 2 to 6 in this way   

Instantly, your data get extracted with additional criteria of "Hardware Model C" with "East" Region.

Scenario -4 Now we wants to extract the data related to sale made during the period February 2016 to June 2016 and February 2015 to June 2015 for "Hardware Model C" and "Hardware Model A".

But if we use Start Date and End Date as additional criteria, Microsoft Excel not able understand what we what we want to extract, as Advance Filter works only when Heading of Criteria and Heading of range list remain same. Therefore, in this case we need to use special excel Operation symbols (Greater than ">", Greater than or equal to ">=", Less than ">", Less than or equal to "<=", Not "<>") in our date criteria.  

Since we are extracting data related to February 2016 to June 2016 for Product "Hardware Model C", need to use first use Greater than or equal to >= 01-02-2016 (Starting Date) and Less than or equal to <=30-06-2016.

And, we need to repeat step 2 to 7 once again

Instantly, your data get extracted with all complicated conditions.

In short I can say, from simple Auto filter we can extract data one by one only, but if we have an understanding of Advance Filter function we can accurately Extract Data with complex criteria when a simple Auto Filter can't do what we want.

I hope you will be delighted with my article, and if you have any query related to this article or anything related to Microsoft Excel, FEEL FREE TO GET IN TOUCH.

The author can also be reached at Mail ID camukeshkumar@outlook.com

You may also make reference of my other articles on Advance Excel Functions 

• Ageing report from pivot table - Advance Excel
• Vlookup's Advance Function (Advance Excel)
• Data Consolidation in Microsoft Excel Part-2 (Advance Excel)
• Data Consolidation in Microsoft Excel (Advance Excel)
• Linking Tally with Excel Steps (Advance Excel)
• Scenario Manager in Excel (Revised)
• Data Protection In Microsoft-Office