Using operators to refine filters
We can further broaden our filters using comparison operators. Previously in this book, we concentrated on the greater than and less than operators. Now, let’s look at a few scenarios where we can encompass the use of two operators, as well as the NOT criteria. We will also explore how to use blank cells when applying Advanced Filters:
- Open the workbook named MattsWinery.xlsx to expand on our criteria operators.
- In cells M1:R3, we will build a data table that will include all the Cases Sold values that are greater than or equal to 300, and less than or equal to 600, for Prominent Wines 1992 OR Matts Winery 1991. The result should include dates greater than or equal to January 1, 2021, as well as any profit:
Figure 9.26 – Data table
- Prepare the data table as per the preceding screenshot. Criteria on the same row in a data table automatically assume the AND criteria. If on different rows, they would assume the OR criteria. The result we are filtering should contain the fields as per the data table. Copy and paste the field headings from the data table of the WINE SALES sheet to cell A1 of the Results worksheet.
- Let’s build the argument. Click on the Results worksheet and make sure that your mouse pointer is not positioned within the field headings.
- Navigate to Data | Advanced Filter and enter the following criteria:
- List range: A1:K145
- Criterial range: M1:R3
- Copy to: A1:E1
- List range: A1:K145
-
Click the OK button to apply the filter and display the result.
The next condition we will explore will exclude certain results from the filter result using the <> operator.
-
Enter the criteria into cells N7:08 to filter the results so that they exclude the North
region for the Merlat Label value (Matts Winery OR Prominent Wines):
Figure 9.27 – Data table for the exclude condition
-
Copy the Winery, Label, Region, and Revenue field headings to cell I1 of the Results
sheet as this is the data we would like to extract.
- Click Data | Advanced Filter, then enter the following criteria:
- List range: A1:K145
- Criteria range: M6:P8
- Copy to: I1:L1
- List range: A1:K145
- Click the OK button to display the results of the filter.
Let’s look at another example:
-
Here, we would like to exclude the North AND South regions for Merlat
(Matts Winery):
Figure 9.28 – Data table for excluding the North and South regions
-
Copy the Winery, Label, Region, and Revenue field headings to cell P1 of the Results
sheet as this is the data we would like to extract.
- Click Data | Advanced Filter, then enter the following criteria:
- List range: A1:K145
- Criteria range: M12:Q13
- Copy to: P1:S1
- List range: A1:K145
- Click the OK button to display the results of the filter.
In the next section, we will learn how to use wildcard characters when filtering data.
