Excel Column Filter tools
Originally published: 23/04/2020 06:56
Last version published: 06/05/2021 07:20
Publication number: ELQ-68372-2
View all versions & Certificate
certified

Excel Column Filter tools

Three quick COLUMN FILTER utilities for Excel data tables (now also works with post-Excel 2007 ListObject 'Tables').

Description
UPDATE May 2021: I have previously worked with pre-Excel 2007 'data ranges', so I hadn't worked with ListObject Tables until recently. I've discovered that my utilities didn't work with these. So, I've now updated them so that they all work both on old-fashioned data ranges and the newer ListObject Tables. I've also added a handy utility called ResizeTable that resets the Table name to the exact data range (in case something gets out of sync). Everything else is as before: just download the VBA code from the revised MSWord file, 'Column Filter Macro text - works on Tables.docx'.

These 3 Excel VBA macro utilities enable you to:-
a) quickly apply a CUSTOM filter criterion to a column;
b) clear a single column filter; and
c) filter a column on its current value
without all the keystrokes these actions usually require.

If you frequently work with large Excel data tables (like I do) and have a recurring need to apply filters to different columns, to analyse or code up your data (particularly your own CUSTOM filter criteria), then you’ll know that it can be tedious using the standard Excel filter facilities.

The functionality within Excel's built-in ‘Custom filter…’ option is certainly handy, allowing various Boolean comparisons and nesting of criteria, for more complex queries. But if, most of the time, you just want to filter records that contain a single piece of text (that you specify), it can be very tedious, particularly if you want to keep doing it for various different values. You have to select the drop-down arrow at the top of the column, possibly have to click on another sub-menu (depending on the Excel version you’re using) then skim down to the ‘Custom filter…’ option. Then you still have to type in the text you’re searching on and, if it isn’t an exact match you’re after but, rather, any record that contains the text you type, you still have to click on the drop-down box then drag the slider down before you’re able to select the ‘contains’ option (or, alternatively, you have to type an asterisk both before and after your search text).

1. The first of these macros allows you to do that much quicker!
2. The second macro allows you to clear any column filter in a single click (instead of 3 or more).
3. The third allows you to filter on the current cell’s value (without having to type it).
[Earlier versions of Excel did this if you just clicked on the Auto-Filter icon, which was handy. The problem was, there was no easy way to clear all filters if you wanted to. In later versions, the Auto-Filter icon now works to toggle filters on and off, so it’s lost the facility to filter on the current cell’s value.]

The macro text is in the attached 'Column Filter Macro text.docx' file: you can just copy the text then paste it into your favourite place in your own VBA library (e.g. the Modules of your Personal.xlb file). The VBA macros ‘FilterOnColumn’, ‘ClearFilterOnColumn’ and ‘FilterOnCurrentValue’ will then be available to run.

This Best Practice includes
One MSWord document containing text of VBA macro; and one Excel demonstration table file (Demo.xls)

Michael Martin offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

Save time when querying or analysing data in tables.

When working with a data table (with field descriptions in the first row and no blank rows or columns within it).

Where there are blank rows or columns within the data (although it will still work – it’s just that it can be more confusing!)

Reviews

  • Be the first to review this Downloadable Best Practice

    Write a review

People using this Best Practice also downloaded


keyboard_arrow_leftkeyboard_arrow_right

Any questions on Excel Column Filter Tools?

The user community and author are here to help. Go ahead!


0.0 / 5 (0 votes)

please wait...