Preview of The Filter Mate Add-in for Excel - Excel Campus
22

Preview of The Filter Mate Add-in for Excel

In the last webinar I mentioned a new add-in that I have been working on with Randy, a student of The VBA Pro Course.  The add-in is called Filter Mate.

The goal of Filter Mate is to help make it easier to work with filtered ranges and Tables in Excel.  I just recorded a quick video that gives a preview of how Filter Mate works.

Video: Preview of the Filter Mate Add-in



Double-click video to watch in full screen.

As you can see, Filter Mate helps eliminate the horizontal scrolling we do to find these tiny buttons in Excel.

horizontal-scrolling-to-find-applied-filter-icon-button-in-excel

Filter Mate is Now Available!

The Filter Mate Add-in is now available.  I have also created an online course on Filters.  It’s called Filters 101 and includes training on all the filtering techniques and tools I have used in my career to help me prepare and analyze data.

filter-mate-and-filters-101-logos

Both of these products will help you become an Excel filtering superstar! 🙂

Please click here to learn more about Filter Mate and the Filters 101 Course

What Filtering Tasks Do You Dread?

I’m curious to know what filtering tasks you spend too much time on.  Please leave a comment below with any filtering related tasks that you find frustrating or time consuming in Excel.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 22 comments
Marcos - November 22, 2016

I liked the Filter Hop shortcut, because it is more accurate at the moment of search for what filter criteria was use on an unknown table. I only need to research how to include that Tab on my ribbon since I believe it might be related to the excel version.

Reply
Francis - November 22, 2016

I am working with a lot of file using filter it is great to see this new filter mate. I am excited to use it. I have matrix table using 100 of column it is takes time to scroll.

Reply
    Jon Acampora - November 23, 2016

    Awesome! Thanks Francis! That is exactly what Filter Mate was built for. To eliminate all that horizontal scrolling. It should be available next week.

    Reply
Roger Chessell - November 9, 2016

Just thinking out loud here.

Is there anyway that you can set several filters to true, select the item to be filtered and then for the results to be shown on a new spreadsheet that contains only the information in the filtered lists.

Reply
Dan Franceski - November 8, 2016

Pam said on Nov 4 (above): “When I click the Filter in Quick Access (or Ctl+Shift+L) it sends me to the top far left or right of my sheet even if I have selected a cell.”

My solution to this is to copy the code below and create a Macro named “AutoFilterStay”. Then add an icon (with a funnel) to your QAT to launch the macro. Problem solved.

Sub AutoFilterStay()

‘ AutoFilterStay Macro
‘ Turns on Autofilter without jumping away from the Active Cell
‘ (Filter icon in Excel 2010 jumps)
‘ Dan Franceski 07/01/15


Dim CellLocation As String
CellLocation = ActiveCell.Address
Range(CellLocation).Select
Selection.AutoFilter
Range(CellLocation).Select
End Sub

Reply
    Jon Acampora - November 21, 2016

    Hey Dan,
    This is a great macro that solves a common problem. Thank you so much for sharing. I love how we can use VBA to help enhance or improve our experience with Excel to save time. Thanks again!

    Reply
saeed53 - November 7, 2016

thank you mr jon Acampora
this site is very interesting for me

Reply
Pam - November 4, 2016

Jon,

It’s likely there are settings/tricks I don’t know about or may resolve themselves as I get more into PowerQuery/Pivot or take the time to set up macros, but here are some things on my filtering wish list.

1A) Some of my data dumps are 60+ columns. I might start a quick review, be scrolled halfway horizontally, decide I want to filter & realize I neglected to turn it on. When I click the Filter in Quick Access (or Ctl+Shift+L) it sends me to the top far left or right of my sheet even if I have selected a cell. I’m not sure the purpose of the screen moving. It does not move when you remove the filter.

1B) A big thanks to Bruce – I did not know about the “right click filter on a cell value”. This will come in handy BUT it has the same issue as 1A. Screen moves to the top right or left and you have to get back over to columns you were using.

2) I want to use my mouse to scroll the dropdown list in a filter. It’s hard to drag that scrollbar when there is a long list.

3) How cool would it be to hold the Shift key to select a contiguous group in the filter drop down?

4) A big thumbs up to the filter recall you’re working on. I have monthly dumps that I’ll be using to create a Dashboard(first attempt with PowerQuery etc). I’m sure PowerQuery has some ways to clean data and saves the recipe, but I think I’m going to have to clean up some of the data before it can run through the PowerQuery. It would save time if I had a series of saved filters to run through. Might also be useful for people who get more than their info. A saved filter to pull their data would be very helpful.

I love what you’ve done so far. Can’t wait to try it!
Pam

Reply
    Jon Acampora - November 14, 2016

    Hi Pam,
    Thank you so much for the suggestions. Dan just posted a comment with a macro that helps with your 1A request to keep the cell selection the same after turning filters on.

    We probably won’t be able to do much about 2 & 3 with the add-in. That would be something Microsoft has to fix/update. But those are fantastic suggestions for the Excel uservoice site.

    Thanks again Pam! 🙂

    Reply
Evert - November 4, 2016

Nice work!!!

Do you share this code as well or the add-in?

Cheers!

Reply
Larry Roberts - November 4, 2016

I would love to be able to filter on multiple font or fill colors without resorting to a helper column.

Reply
David Walton - November 4, 2016

Hi, totally agree with the other posts on needing a simpler or quicker way to filter on selected value and also the add to selection is one I use a bit but can often end up resetting by mistake as it’s location is not ideal.
One feature I would like to see is a simple summary to show me what filters are applied and if it was possible a way to store and reset those filters like a memory recall. We often work with shared documents and invariably when it’s opened its filtered to suit someone else’s preference. Resetting is easy enough but being able to then set a specific set of filters would be useful.
Thanks.

Reply
    Jon Acampora - November 4, 2016

    Hi Dave,
    Thanks for the suggestions! As I just mentioned to Bruce’s comment, you can use the menu key on the keyboard shortcut to filter for a cell’s value. The keyboard shortcut is: Menu Key, E, V

    We have plans to add a filter memory recall feature to Filter Mate. It is proving to be a bit of a challenge, mostly due to how date columns are filtered, but should be possible. Thanks again!

    Reply
Dick Kusleika - November 3, 2016

When I type Alt+A+C to clear all the filters, and there were not filters applied, the tooltips in the Ribbon stay visible and I have to hit escape a couple of times to get back to work. I want Alt+A+C to clear all filters and return even if Clear is disabled.

When I use Alt+A+C to clear filters on a calculation intensive workbook, it takes longer than if I clear the filters individually. It must do something to the calculation tree.

I wish it was less cumbersome to “Add selection to current filter”. I don’t do it often, but when I need it I usually need it a bunch of times. Maybe if I could say “add to current until I say otherwise”.

When the column is dates, there are a couple of things I want. I press Alt+{Down} to expand the filter box, then “e” to get to the search box. In a non-date column, I can use the down arrow to get the individual items. But in a date column, it gets stuck in the side menu. Although I just realized that I can Alt+{Down} and then use the up arrow as long as I don’t go too far.

The other thing about dates is that I should be able to type any valid date in the search box and have it filter. I want to type 11/3/2016, 11/03/2016, 2016-11-3, Nov 3 2016 and any other valid combination and have it know what I mean.

And while I’m at, it should be able to type any valid filter in the search box and have it interpret it as such. So “11/3/2016 – 11/10/2016” would filter on the right range. And “Utah and Nevada and Oregon” or “In(Utah, nevada, oregon)” should do a multiple selection filter.

OK, that’s all I can think of for now.

Reply
    Jon Acampora - November 4, 2016

    Hey Dick,
    Wow, these are all awesome suggestions! There are a lot of little quirks with the filter drop-down menus, as you described, that could be improved.

    Add selection to current filter is definitely a hidden feature that not many people know about. A toggle mode for it is a great idea.

    I wish the date tree in the filter drop-down menu was available in VBA. It’s impossible to determine the filter criteria with VBA if the user filters with the date tree (listbox).

    That Alt+Down, then 3 up arrows is a nice trick to get into the date tree. The “+” and “-” keys expand and collapse the groups within the date tree.

    Thanks again for all the suggestions! Great stuff!

    Reply
Bruce L - November 3, 2016

The one autofilter feature I found most useful in XL2007+ is the “right click > filter > filter by selected cell’s value”. I used it so often that I ended up writing a macro to speed it up with a shortcut key; three clicks to reach a frequently-used command is three clicks too many.

At the same time, I wrote a macro to quickly clear all autofilters without removing the filter sort settings (because the “Clear” command in the Sort & Filter ribbon group is too powerful and removes everything including sort settings).

These macros quickly grew arms and legs however, since with the advent of the possibility of multiple autofilters on one sheet (thanks to the introduction of tables) I wanted them to be able to “intelligently” detect the intended target.

In the end I got it all working. The only issue I have is that the “filter by selected cell’s value” macro doesn’t seem to work properly with numeric values (iirc nothing happens). I haven’t gotten round to figuring out a fix for this yet, mostly because it hasn’t annoyed me enough yet to make it worth my while.

Code here if anyone wants to play with it: http://pastebin.com/v2gpa3mz

Reply
    Jon Acampora - November 4, 2016

    Hey Bruce,

    Thanks for sharing the code and suggestions!

    If your keyboard has a menu key then the keyboard shortcut to filter for a cell’s value is: Menu Key, E, V

    You’re right that multiple tables and a filtered range on the same sheet can be tricky to work with. Thanks again for sharing!

    Reply
      Bruce - November 7, 2016

      Hi Jon, thanks. Yeah I make a lot of use of the menu key, but I’m not a touch-typer so even the menu-E-V key combo was slowing me down, hence the code to allow a faster, two-handed kb shortcut.

      Reply
Jon Acampora - November 3, 2016

What tasks do you find to be time consuming when working with filters? Please leave a comment here with your answer. Thanks!

Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x