Click a Cell to Filter Excel Column

Click on a cell in an Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.

.

To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.

FilterClick Sample File

This filtering trick is accomplished with a bit of programming. There is an event procedure – Worksheet_SelectionChange – that automatically runs when you select a cell on the worksheet . You can turn the FilterClick feature on or off, by clicking on a cell that’s named FilterStatus. When you click on that cell, its value automatically toggles between On and Off.

The SelectionChange procedure checks the value in that cell, and only filters the column if the FilterStatus cell says “On”.

Update for Working With Excel Tables

I created the sample file a few years ago, and have just updated it to include a worksheet that has the data in a formatted Excel table. The code is almost identical – only one line is different if you’re using a table.

In the sample code shown below, there are two versions of the line that sets the range rngF. For formatted tables, use the first line, and for non-table AutoFilters, use the second line.

Set rngF = ActiveSheet.ListObjects(1).Range 'for tables

'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges

Type an apostrophe at the start of the line that you don’t need, and remove the apostrophe at the start of the line that you do need.

The FilterClick Code

Here is the code, and you can copy it to the worksheet module for the sheet that your list is on. Remember to fix the Set rngF lines, so one has an apostrophe, and one doesn’t – or delete the line that you don’t need.

Download the Sample File

You can download the sample file, with the code for both versions – table and non-table – on my Contextures website. Go to the Excel Sample Files page, and in the Filters section, look for FL0021 - FilterClick.

6 Responses

I have a excel 2007 spreadsheet that has 7 row and 10 colums. 5 of those columns has a multiple select dropdown list and the dropdown list has the ability to select multiple items. Thank you Contextures! The problem I am having is that, when I protect the spreadsheet and lock down column C, where I have the (=TODAY())CODE, the spreadsheet looses the ability to multi-select from the dropdown lists.

@Wanda, the code should work, even if the sheet is protected. Is there other code on the worksheet module? Maybe there’s another line that isn’t able to run, and it’s exiting the procedure before it gets to the multi-selection code.