Clearly and Simply

Filter Excel Tables by Double Clicking

Written by

Robert

in

,

How to set the active cell’s value as the filter criteria in Microsoft Excel Tables by double clicking

Filter - Photographer jercraigs (flickr.com)In a post on the Excel Hero LinkedIn Group Charles asked today:

“[…] I wish to click on a cell with a purchase order number and have that action filter the list based on that purchase order number.
What could be a strategy please? […]”

In one of the answers to Charles’ question, Craig pointed to the most obvious solution: right click and select Filter | Filter By Selected Cell’s Value. Well, I know this is available in Excel 2010, but I can’t tell you no lies: I don’t even remember if this feature was available already in 2003 and earlier.

Anyway. Although at least Excel 2010 provides a built-in feature for filtering by the value of the active cell, Charles' idea is very interesting and reminded me of a post we had here last year: Sort Excel Tables by Double Clicking.

The article provided a small VBA code snippet to sort data tables by double clicking into any given column. The technique used back then for sorting can easily be adjusted to implement Charles’ original idea: filter data tables by the value of the active cell after double clicking and remove the filter criteria with another double click.

The implementation is simple: all you need is to define a name for the range where your data is (e.g. “myData”) and a very small piece of VBA code (24 lines of code) in the event driven Worksheet_BeforeDoubleClick event procedure.

The code

  • checks if the the user double clicked inside of the data
  • detects the active column
  • checks if the auto filter is set and sets it, if not
  • checks if a criteria is defined already and if so, removes the criteria or otherwise sets the criteria to the value of active cell

If you are interested in the details, have a look at the workbook provided for download below. If you have any questions, please leave me a comment.

A simple and effective way of setting / removing filter criteria by double clicking. The technique is not limited to one column. You can also select several columns to filter your data.

Here is the workbook for free download:

Download Filter Excel Tables by Double Clicking (Microsoft Excel 2003, 93.5K)

You can even combine the filtering by double clicking with the sorting by double clicking technique, if you change the VBA to sort only if the user double clicks on the header of a column. A simple IF clause and a combination of the two code snippets will do the job.

Stay tuned.

Comments

24 responses to “Filter Excel Tables by Double Clicking”

  1. John Avatar
    John

    Very interesting post. I use Excel XP. The simple way I do it is to drag the “Auto Filter” command button into a toolbar. This command seems to do more than its name suggest. It does not simply turn on the auto filter when you click it, it also filters the list according to the value of the cell you selected. Selected another cell in another column and click the button again, the list will be filtered further. Very useful command.

    Reply
  2. Robert Avatar
    Robert

    John,
    thanks for your comment. Very interesting. This feature was new to me. Excel 2010 also offers the option to add the Auto Filter command button to the Quick Access Toolbar or to a customized group on a ribbon. Thanks for pointing this out.
    Still, in my humble opinion the technique described above is a nice little alternative to Excel’s built-in features.

    Reply
  3. Charles Avatar
    Charles

    I feel so honored being credited for inspiring this post. I am thrilled with the result. Thanks so much Robert.

    Reply
  4. ksm Avatar
    ksm

    if range data (mydata) in format table not working any solution?

    Reply
  5. Robert Avatar
    Robert

    ksm,
    I am not sure I understand your question, but if you have your data in an Excel table (formerly known as Excel list), you have to delete the IF-clause in the code which sets the Autofilter.
    Simply delete or comment out the following part of the code and it should work:
    If ActiveSheet.AutoFilterMode = False Then
    rngTable.AutoFilter
    End If

    Reply
  6. ksm Avatar
    ksm

    ty work perfect, sorry for my english

    Reply
  7. Jason Avatar
    Jason

    Hi Robert
    This works perfectly but I’m trying to find another implementation if possible.
    I have a worksheet full of quotes that we’ve provided customers with and it is set up as an Excel Table. On a separate worksheet, I have another table that provides analysis of the quotes. The analysis is split up like this:
    Single Pallets
    2-5 Pallets
    6-9 Pallets
    10-13 Pallets
    14-17 Pallets
    Full Loads (18-26 Pallets)
    Single Cartons
    2+ Cartons
    Other
    And each one provides counts and revenues for each category. Each category is totalled using countifs and sumifs of one of the columns in the table that specifies what the goods are e.g. 1 plt, 2 plts, 3 plts etc.
    I’d really love it if I could double click on say 2-5 pallets and it takes me to the other sheet and filters the table for all 2 plts, 3 plts, 4 plts, 5 plts, jobs.
    Do you think this would be possible?

    Reply
  8. Robert Avatar
    Robert

    Jason,
    have a look at this:

    Drill Down by Double Clicking (36.3K)

    Something like this?

    Reply
  9. Andrew Avatar
    Andrew

    I’ve combined this with the sort by double clicking code and it all works except that after it’s filtered it will only sort in one direction on each column. Anyway to get it to sort the other direction when I doubleclick on the header?

    Reply
  10. Robert Avatar
    Robert

    Andrew,
    if you sort filtered data, only the visible rows will be sorted, not all the rows. The check of the sort order in the code, however, refers to the entire range, i.e. the visible and invisible rows. If you sorted by one column (descending) and double click again to reverse the sort order, the check will most likely return that the data is not sorted (since only the visible rows have been sorted). I.e. it will sort the data again descending by default and you never get to an ascending sort order.
    How could you solve this? There is more than one way, I guess, but you could write additional code to check if a filter is applied, remove the filter before sorting, sort the data and then set the filter again. I never tried, but I think this should work.

    Reply
  11. Shivaprasad Avatar
    Shivaprasad

    Hi Robert,
    I would like Design the Excel Work book. which will have master sheet
    where on if i Double click on Country Column.it should open a additional sheet and bring the data from MSSQL/MS access using Country filter .show the country detail report in Sheet 2

    Reply
  12. Robert Avatar
    Robert

    Shivaprasad,
    this is possible with some extra coding.
    First you have to check if the user clicked in the country column using e.g. ActiveCell.Column.
    If this is the case, you would create an SQL string like “SELECT […] FROM […] WHERE Country =”& ActiveCell.Value. You fire this SQL statement per VBA to the database and write the results to the second worksheet.
    Have a look at the last workbook of the article

    Motion Chart Excel Template

    where I used this technique to retrieve filtered data from an MS Access database.

    Reply
  13. Gonzalo Avatar
    Gonzalo

    Hi Robert,
    This is very interesting and useful.
    The only thing (and not less important) is:
    For example if you click Argentina on the 1930 line you get all the lines were Argentina is included, but if you click for example Argentina 1962 you get Argentina from 1958 onward, but not previous years (you always get previous year and onward)
    Is this possible to be ‘repaired’?

    Reply
  14. Robert Avatar
    Robert

    Gonzalo,
    I think it is working as it should. If you click on Argentina 1962, you just have to scroll up to see all of Argentina’s rows. They are there, they just aren’t visible after double clicking because of the freezed pane (column headers).

    Reply
  15. Gonzalo Avatar
    Gonzalo

    Robert please ignore my question above, it works perfect, you are a genius.

    Reply
  16. Marie Roffey Avatar
    Marie Roffey

    I realise I’m pretty late to the party but if anyone could help. I don’t do too bad with excel but don’t really know VBA. With this spreadsheet (amazing work by the way) – I would like to turn the range into a table but still have the same double-click filter functionality but it seems as soon as I change it to a table that is lost. Can anyone help. thanks in advance. Marie

    Reply
  17. Robert Avatar
    Robert

    Marie,
    delete or comment out the following part of the code and it should work:
    If ActiveSheet.AutoFilterMode = False Then
    rngTable.AutoFilter
    End If

    Reply
  18. Mohammad Hossain Avatar
    Mohammad Hossain

    If I wish to use in only three header rows,what I need to change. Please tell me details. Thanks

    Reply
  19. Robert Avatar
    Robert

    Mohammad,
    have a look at this file:

    Download fil-ter_table_by_double_clicking_selected_columns (39K)

    You can specify in the row above the column headers, which columns shall be viable for filtering by double clicking. If you set the value above a column header to TRUE, you can filter by double clicking, if the cell value is FALSE, the double click feature is not available in this column.

    Reply
  20. Ajay Avatar
    Ajay

    Hi Mr. Robert,
    I cannot start any writing before saying How fantastic your tool is. really amazing!!!
    I am trying to implement a double click mode in my files as i can use this code in all workbook i Open like enable macro will automatically name the used range as myData and and after doing filters another macro delete the name like that.
    for that i need to add this in a Module, Please help me to do so

    Reply
  21. Robert Avatar
    Robert

    Ajay,
    let me start with one important point for clarification: if you want to use the code in all of your workbooks, you either have to copy the code to all the workbooks, define the name “myData” (manually or by VBA code, see below) and save them as macro enabled workbooks or you have to transfer the code to an add-in (probably using a new class module providing the Worksheet_BeforeDoubleClick event).
    As per your question how to programmatically add and delete a name: the following line adds the name “myData” referring to the used range of the first worksheet:
    ThisWorkbook.Names.Add Name:=”myData”, RefersTo:=Worksheets(1).UsedRange
    The following line deletes the name “myData”:
    ThisWorkbook.Names(“myData”).Delete
    I hope this helps.

    Reply
  22. Sharaz.ul.hassan@hotmail.com Avatar
    Sharaz.ul.hassan@hotmail.com

    What if i like to add a short key rather than double clicking like pressing Ctr + F ?

    Reply
  23. Robert Avatar
    Robert

    Sharaz,
    this is possible.
    Here are the steps you have to do:
    1. Go to the VBE, cut (or copy and delete) the entire code from the worksheet module
    2. Insert a new module
    3. Insert the code you copied in step 1 into the new module
    4. Change the name of the sub, i.e. replace
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    by e.g.
    Sub FilterData()
    5. Go to the worksheet, click on the Developer tab and on Macros. In the upcoming dialogue window, select FilterData, click on Options and define the shortcut you like. Since CTRL-F is taken already by Excel (Search), you would have to use e.g. CTRL-Shift-F (or whatever you choose).

    Reply
  24. hassan_kech Avatar
    hassan_kech

    Nice work my friend, i appreciate your efforts

    Reply

Leave a Reply to Sharaz.ul.hassan@hotmail.com Cancel reply

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

More posts