Today, we want to learn how advanced filters work in Excel, especially between conditions.
Advanced Filters and between condition
Let’s start. When we have our dataset, we write our advanced filter conditions in cells. We need to have a header and a condition for this header. If we write conditions in one row, all of them are connected with the final criteria. It means that each condition should be met in order to take out the whole row from our dataset (1). When we add conditions to further rows, it meas that it’s an ‘or’ condition (2). In this case, we will look for strawberries or plums. If we want to create a between condition, we need to write the header two times (3) in order to create a common part. In our case, we are looking for strawberries that are equal or greater than 500 and smaller or equal to 750, and in the case of plums we want the plums that are equal or greater than 300 and smaller or equal to 500. We have to remember that those conditions should make a common part (Fig. 1)
Fig. 1 Dataset with two headers
When we want to go to advanced filters, we need to select one cell in our dataset (1), then go to the Data tab (2), then Advanced filters (3). In the Advanced filter window press the Copy to another location radio button (4), and select the criteria range (5). The last step is selecting the target cell, which will be cell E6 (6) (Fig. 2)
Fig. 2 Creating a table
Remember that if conditions are written in one row, they are combined as an ‘and’ condition, and when they are in separate rows, they are combined as an ‘or’ condition. Moreover, we can add an empty condition for different headers. But, since there isn’t any condition in the Date column, we can select the range without it. In figure 3, we can see the final table with sales of plums that are between 300 and 500 and sales of strawberries that are between 500 and 750, together with all possible dates for those conditions (Fig. 3)
Today, we want to learn how wildcards work with advanced filters.
Advanced Filters and wildcards
First of all, we have to remember that an asterisk replaces any text string, even an empty one. A question mark replaces any single character. A tilde disables a wildcard.
Let’s start with finding ‘Red’ in the Product column. To do that, we have to select one cell in our data, then go to the Data tab (1) and click on the Advanced filter command (2). In the Advanced filter window, we have to select the Copy to another location radio button (3), then write the Criteria range (4). In the Copy to bar, we select our target cell (5).
Fig. 1 Advanced filter window
After pressing OK, we have our results. In advanced filters, we don’t have a simple condition with text. This condition is not an equal condition, but a ‘starts with’ condition. In our target column, we have the ‘red’ word, however, advanced filters chose whole cells that start with ‘red’. If we want only the exact word, we have to write it with a single quote, then equals, then our word. Let’s write it. We should make another advanced filter, so let’s make the same steps as in the previous example, starting with the Data tab and changing the Criteria range to another column (1) (Fig. 2)
Fig. 2 Advanced filters with an exact word
In the target cell, we can see only one cell with DragonFly, even though there are three cells with DragonFly in our data.
Now, let’s try the asterisk option. We write an asterisk before ‘beetle’, but we have to remember that it is always connected with the ‘starts with’ option. We are working with wildcards as well as with the ‘starts with’ criteria. It works as if an asterisk was also at the end of ‘beetle’. Let’s make a proper advanced filter for this case staring with the Data tab and changing the Criteria range into another column (1) (Fig. 3)
Fig. 3 Advanced filters with an asterisk
As we can see, ‘beetle’ can be at the end, at the beginning or even in the middle of our cell.
Now, let’s work with question marks. We need to remember that one question mark represents only one sign. It can be a digit, a letter or any other sign. For example ‘Ant ? — ? mm’ cannot represent ‘Ant 6 — 10 mm’, because there are too many signs in 10. Let’s make an advanced filter for that. We start with the Data tab, and we change the Criteria range (1) and the target cell (2) (Fig. 4)
Fig. 4 Advanced filters with an asterisk
We can see that we found two cells where a question mark represented only one sign.
Now, let’s move on to a tilde. We want to find the exact phrase of ‘*Ant’. That’s why we put a tilde at the beginning. If we put only an asterisk and an Ant, like this ‘*Ant’, Excel would find any Ant, regardless of its position. The tilde makes the asterisk stop being a wildcard. We do the same as in previous cases, changing only the Criteria range and the target cell (Fig. 5)
Fig. 5 Advanced filters with a tilde
We can see that Excel found only one Ant with an asterisk at the beginning. It’s because we turned off wildcards.
