3 Hidden Tips & Tricks You Probably Don’t Know About ADVANCED Filter

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn about the 3 Hidden Tips & Tricks You Probably Don’t Know About ADVANCED Filter. Let’s get started

Video

Sample Dataset

In the screenshot below, we have our sample dataset with five columns. In addition, we have a drop down list in cell M6 that contains 1 to 12 representing the 12 months of the year

First Hidden Tips and Tricks

In the first example, we are going to look at how to use the ADVANCED filter to filter our dataset based month section of the date value in column A. For example, if we have 7 in cell M6, then we want to return all the transactions for the month of July. We want the result to be copied to columns G to K.

Therefore, to do this, we need to write a simple formula in M2 which will check whether the month number in cell A2 equals to the selected month number in cell M6.

• In cell M2, execute the formula: =MONTH(A2)=$M$6
• Click Enter
• In the Sort & Filter group of the Data tab, click on ADVANCED
• In the Advanced Filter dialogue box, check Copy to another location
• For the List Range, select the entire main dataset including the headings
• For the Criteria Range, select cells M1:M2
• For Copy to, select G1:K1

• Click OK

From the screenshot below, we can clearly see that all the transactions for the month of July which represent Month number 7 was result. If we select another month number from cell M6 and rerun the ADVANCED filter, the result will be updated accordingly

Second Hidden Tips & Tricks

In the second example, we are going to see how to add another criteria that will return filtered list for selected Month number and Payment that contains money 

• In cell N2, type in *money*
• In the Sort & Filter group of the Data tab, click on ADVANCED
• In the Advanced Filter dialogue box, check Copy to another location

The List Range and the Copy to references remains the same

• For the Criteria Range, select cells M1:N2

• Click OK

From the caption below, we can see that selected Month of July and all the Payment types that contains money were returned as the final result!

Third Hidden Tips & Tricks

The final hidden tips and ticks is such an interesting one – we are going to use the ADVANCED filter to reorder our columns and still retain our to criteria. To achieve this, I have renamed the columns from Date, Brand, Payment, Region and Total to Total, Region, Payment, Brand and Date respectively. This is necessary because we want the columns reordered with the criteria applied.

• In the Sort & Filter group of the Data tab, click on ADVANCED
• In the Advanced Filter dialogue box, check Copy to another location
• For the List Range, select the entire main dataset including the headings
• For the Criteria Range, select M1:N2
• For Copy to, select G1:K1

• Click OK

From the caption below, we can see that the columns has been reordered and the criteria specified – Month number 10 and Payment that contains money -were also applied

See you in the next tutorial