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. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in 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 more complex dataset and scenario.
Download Practice Workbook
You can download the practice workbook from here:
Basics of Advanced Filter in Excel
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 for Unique records only, which 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 the 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$4:$J$5. 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 the value for Region, how will Excel know with which column it needs to match this?- By specifying the column.
- So we entered $G$4:$J$5.
- Then in the Copy to, we have inserted $G$9:$J$9, only the column name, to make it understand which value in which column.
We have found the value in the US region.
- Now let’s insert another criteria value.
We have chosen Mobile as the Product Type, still, the filtered data has Laptop and Keyboard. To make the new criteria count we need to repeat the entire process.
2 Examples of Dynamic Advanced Filter in Excel
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. Dynamic Advanced Filter with a Keyboard Shortcut Created by Using Excel Macros
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 back a bit. We have the data and criteria but no filtered value yet. Now let’s start.
Steps:
- 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 + P 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 previously 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 + P (our chosen shortcut)
- We no longer repeat the process from the beginning. Just press the key. And this will provide the desired outcome.
2. Dynamic Advanced Filter Button Created by Using Macros
You can insert a Button to execute your Macro. Sometimes buttons can be more useful than a keyboard shortcut.
Steps:
- 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 found all the rows that contain Mobile irrespective of their region or brand.
How to Apply a Dynamic Filter in Excel
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.
Steps:
- 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 with a name. Here we have provided the table name as Data.
- Now from the Insert option of the 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 in Excel. 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.
