Easy Office
LCI Learning

Extracting data in Microsoft Excel - Advance Filter

CA Kumar Mukesh , Last updated: 26 April 2021  
  Share


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.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER

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.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 1

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 2

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.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 3

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.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 4

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 5

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 6

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 7

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.  

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 8

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 9

Instantly, your data get extracted with additional criteria.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 10

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.     

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 11

And repeat step 2 to 6 in this way   

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 12

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 13

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".

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 14

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.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 15

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

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 16

Instantly, your data get extracted with all complicated conditions.

EXTRACTING DATA IN MICROSOFT EXCEL - ADVANCE FILTER Step 17

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 


Published by

CA Kumar Mukesh
(CA, CMA FINAL and Advance Excel Trainer)
Category Info Technology   Report

17 Likes   25387 Views

Comments


Related Articles


Loading