Shakil Ahmed
Circumstances may demand different types of filter operations; advanced, dynamic, and so on. Our agenda for today is to show you how to use the dynamic advanced filter in Excel. For conducting the session we are using Excel 2019, you can choose your version.
First things first, let’s get to know about the workbook which is the base of our examples.
We have a basic table with the salesperson from the different regions along with their selling products and brands. Using this table we are going to show you how to use the dynamic advanced filter.
Note that this is a basic table with a simple scenario. practically you may encounter a much larger and complex dataset and scenario.
Practice Workbook
You are welcome to download the practice workbook from the link below.
Basic of Advanced Filter
To comfort your surfing, Excel provides different advanced tools and features. One of these is Advanced Filter.
Advanced Filter will be found in the Sort & Filter section of the Data tab.
To explore the feature, click on Advanced here. A dialog box will appear in front of you.
You can see there are various options, you can filter in place or can copy the filtered data to another location.
There is an option Unique records only, that will produce only the distinctive data.
Generally, the Advanced Filter is used for copying the filtered data to another location. You need to select the Copy to another location option.
To show you examples we have introduced two tables with identical columns.
You can see after selecting the option the Copy to field has been enabled. You need to insert the destination location here.
In the Criteria range, we will provide the filter criteria.
Here we have selected the entire table as our List range. And in the Criteria range provided $G$3:$J$4. Do you understand why?
You can see within our Criteria range we have inserted the Region value so far. If we don’t tell the US is value for Region, how will Excel know with which column it needs to match this? We need to specify the column, so we entered $G$3:$J$4.
Then in the Copy to, we have inserted $G$8:$J$8, only the column name, to make it understand which value in which column.
We have found the value from the US region.
Now let’s insert another criteria value.
We have chosen Mobile as Product Type, still, the filtered data has Laptop and Keyboard. To make the new criteria count we need to repeat the entire process.
Dynamic Advanced Filter
We have understood from the previous section that we need to execute the entire Advanced Filter process from the beginning if we change our filter criteria. It’s not an ideal approach for most real-life cases.
To make the Advanced Filter dynamic you need to use Macros. Depending upon your convenience, you can select different options to execute the Macros.
1. Macros with Keyboard Shortcut
You can use a keyboard shortcut to trigger your macros. As per our agenda, our Macros will be on the basis of the Advanced Filter.
We have rolled-backed a bit. We have the data and criteria but no filtered value yet. Now let’s start.
In the Code section from the Developer tab, you will find an icon for Record Macro.
Click that.
A new dialog box will pop up.
In this dialog box, you need to provide the Macro a name, your desired shortcut key, and the scope of the Macro.
We have provided the macro name as Advanced_Filter. And have chosen CTRL + SHIFT + J as the shortcut key.
Please keep in mind the CTRL key is mandatory & Excel will automatically start the shortcut with this key.
Now we need to repeat the process of what we have done in the Basic of Advanced Filter section.
Choose the Advanced option from the Sort & Filter.
Select the List range, Criteria range, and Copy to. And then click OK.
So far we did the same as previous and our result is also the same.
Now from the Developer tab click Stop Recording. This will stop recording and save the Macro.
Once we started recording the Macro, the icon changed to stop.
Insert new criteria value and press CTRL + SHIFT + J (our chosen shortcut)
We no longer repeat the process from the beginning. Just press the key. And this will provide the desired outcome.
2. Macros with Button
You can insert a Button to execute your Macro. Sometimes buttons can be more useful than a keyboard shortcut.
In the Controls section of the Developer tab, there is an option called Insert. Click that.
Now from here select the Button. A dialog box will appear where you need to assign a Macro for the Button.
This dialog box will open when you click Button. Now you need to name your Macro or create a new Macro.
We have a Macro Advanced_Filter, we have inserted that here. You can do that or you can record a new Macro. Then click OK
We have named our Button as Filter. Feel free to set your preferred name.
Let us filter only by the product Mobile. Write the criteria value in the respective field. And then click the button.
We have inserted Mobile in the Product Type column and clicked the button Filter. Then we have found all the rows that contain Mobile irrespective of their region or brand.
Dynamic Filter
Apart from the Advanced Filter, you may also need any other form of dynamic filter. Where you want to explore the changes in real-time. Let’s see how can we apply that.
We will make the dynamic filter using the VBA code.
First of all, we need to format our table by using the Format as Table.
Select any of the formats from here.
Make sure to provide your table a name. Here we have provided the table name as Data.
Now from the Insert option of Controls section within the Developer tab, select Text Box.
Insert the box at your convenience.
You will be in the Design Mode similar to the image above. Double click on the box. Microsoft Visual Basic for Application window will appear.
Copy and Paste the code there.
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Data").Range.AutoFilter Field:=2, Criteria1:= [G3] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub
In our code we have Criteria1:= [G3] & “*”, this is because what input we provide in the G3 cell will be our criteria. See we have set G3 as our LinkedCell.
[G3] & “*” means that our input should be at the first and then any value can be there.
See we have inserted Ca in the box and it returned values from the Canada region.
Using Range.AutoFilter Field:=2 we have chosen the column Range. If you need other columns change the number accordingly.
Conclusion
That’s all for today. We have tried listing ways to use the dynamic advanced filter. We have used Macros and VBA codes for reaching our target. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other ways of what we have missed here.
