How to apply an advanced filter in Excel ?

  Excel Interview Q&A

Today we will read on this tutorial that how to apply an advanced filter in Excel to display only those records that meet complex criteria.

When you use advanced filters, you must enter criteria on the worksheet. Create a criterion range (blue border for illustration only) above your data set. Use the same column header. Make sure that there is at least one empty row between your criteria range and the data set.

Download the excel demo data file here

AND

To display sales in the United States and Qtr 4, perform the following steps.

Click on one of the cells inside the data set -> On the Data tab, in the Sort and Filter group, click Advanced  -> Click in the Criteria Range box and select the range A1: D2 (blue)  -> Click on OK.

Follow the steps given in snapshot 

 

And Criteria in excel filter

And Criteria in excel filter

Or Criteria in excel filter

To display sales in Qtr 4 in the United States or in Qtr 1 in the UK, perform the following steps.

Enter the criteria below on the worksheet ->  On the Data tab, in the Sort and Filter group, click Advanced, and adjust the criteria for the A1: D3 (blue) range -> Click OK.

Follow the steps given in snapshot 

Or Criteria in excel filter

Or Criteria in excel filter

Formula as Criteria in excel filter

To display sales in Qtr 4 in the USA for over $ 10.000 or in Qtr 1 in the UK, follow the steps below.

Enter the criteria (+ formula) shown below on the worksheet -> On the Data tab, in the Sort and Filter group, click Advanced, and adjust the criteria range to A1: E3 (blue)  -> Click OK.

Follow the steps given in snapshot 

Formula as Criteria in excel filter

Formula as Criteria in excel filter

Always put a formula in a new column. Do not use column labels or use column labels that are not in your data set. Make a relative reference to the first cell in column (B6). The formula should evaluate to TRUE or FALSE.

LEAVE A COMMENT