Filter Mate Overview
Filter Mate is a navigation tool that will help you find columns that have filters applied to them, and also find columns that you want to apply filters to. We will still use the built-in filter drop-down menus to apply and modify filters. Filter just makes the process of finding these columns faster and easier.
Filter Hop
Filter Hop is a simple feature that will select the next filtered column in the range or Table.
You can continue to press the Filter Hop button to select the next filtered column, moving from left to right across the range. Once the last column is selected, Filter Hop will loop back and select the first filtered column in the range.
You can also setup a keyboard shortcut to press Filter Hop by adding the Filter Hop button to the Quick Access Toolbar.
Filter Mate Field List
The Filter Mate Window contains a list (Field List) of all the column names in the filtered range or Table. This is a great way to quickly view all the columns in your data range in a vertical list, and is much faster than horizontal scrolling through the sheet.
Most of the features in the Filter Mate Window are based around working with the Field List. This includes the search box, filtered column, and filter criteria box.
View Filtered Columns
The Filed List contains a Filtered column that tells us which columns have filters applied. If the column name contains a “True” in the Filtered column then the column has a filtered applied to it.
The Field List can be filtered down to only display the columns that have filters applied. Press the Filtered button to display a list of all columns that have filters applied. Press the Filtered button again to display all column names.
This makes it very easy to see all the filtered columns in one place. You can use this list to navigate between columns, view the filter criteria, or clear filters.
Filter Criteria Box
The Filter Criteria Box displays the filter criteria for the selected column in the Field List.
This is a quick way to see the filter criteria for a column without having to navigate to the filter drop-down menu.
Limitation with date columns: Currently the filter criteria box cannot display filter criteria for date filters that are applied in the filter drop-down menu's tree view. The tree view is the checkbox list at the bottom of the filter drop-down menu. Here is a screenshot.
This is a limitation of Excel. The filter criteria for these types of filters is not exposed in VBA, and there is no good way to determine the filter criteria. We are working on some workarounds, but it is a big challenge.
One way to avoid this is to use the Date Filters menu. This will apply a filter that can be read by VBA and Filter Mate.
The Search Box
The Search Box in the Filter Mate Window allows you to type a search to filter down the Field List. The list is filtered as you type.
The list will be filtered to return any column names that match the search criteria. The column name does NOT have to start with the search criteria, it just has to contain the search criteria.
You can press the up and down arrows on the keyboard to select an item in the list, then press Enter to navigate to the selected column. The focus will be set to Excel and you can press Alt+Down Arrow to open the filter drop-down menu.
Checkout my video on keyboard shortcuts for the filter drop-down menus to learn more about these time saving shortcuts.
Press the Escape button or the “X” button to clear the search box and start over.
The Refresh Button
If the Filter Mate Window is open when you apply a filter with the filter drop-down menu, then you will have to press the Refresh button to update the Field List and see the filter criteria.
This is due to a limitation in Excel and VBA. There is no event that is triggered when a filter is applied or modified, so there is no way for VBA to tell if a change has been made. We are looking into workarounds, but for now you will need to press the Refresh button or close and re-open the Filter Mate Window.
The Refresh button ONLY refreshes the field list in Filter Mate. It does NOT refresh or change anything in the workbook.
Filtered Ranges Dropdown
The dropdown box at the top of the Filter Mate window contains a list of all filtered tables and ranges in the workbook. This makes it easy to quickly navigate to any one of these sheets and ranges and view the filter properties.
Please leave a comment in the Hero Tools Community Forum if you have any questions.