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.

22 comments

Your email address will not be published. Required fields are marked *

  • 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.

  • 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.

  • 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.

  • 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

    • 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!

  • 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

    • 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! 🙂

  • 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.

    • 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!

  • 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.

    • 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!

  • 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

    • 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!

      • 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.

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly