Microsoft Office Excel without any doubt is one of the most used program when we need to do Data Presentations such as charts and graphs.
When Data are showed in tables, a feature also commonly used are the filters, to limit the records of the table based on certain criteria.
The Excel Filter options are already good, but we can always do a little better, especially in the issue of improving the the user experience and interactivity.
For example: we can quickly filter rows of a table based on the value entered in a given cell!
But custom filters as this example above are not accessible through the Excel UI, and we need to write some VBA code for it to work.
We'll now see an easy way to create an 'Customized Filter'.
Note: All telecomHall articles are originally written in Portuguese. Following we translate to English and Spanish. As our time is short, maybe you find some typos (sometimes we just use the automatic translator, with only a final and 'quick' review). We apologize and we have an understanding of our effort. If you want to contribute translating / correcting of these languages, or even creating and publishing your tutorials, please contact us: contact.
Download
To download the sample files of this tutorial, click here.
To make a donation (or know more about donating) and receive ALL Hunter System files, the including source code, click here.
Custom Filter in Excel
To create a 'Custom Filter' in an Excel Spreadsheet we must use VBA, and through manipulating the properties of the 'Auto Filter' we can do what we need.
To create an 'Auto Filter' in Excel, select the desired data (1), and then go to the Menu: 'Editing' -> 'Sort & Filter' (2) -> 'Filter' (3).
Clicking the arrows next to each field, you have a list of options with the existing values in that column.
As already mentioned, this function is very good and functional, and widely used. But it's not very 'friendly', or rather: we can improve it.
For example, if we want a particular column to be filtered according to some value we type, let's say, in the cell just above its header?
There's no way: we need to write a little code that do the work for us, that is, turn the filter according to what we want.
VBA Code
The VBA code for handling auto filter is very simple and has been sent to Hunter Users (donors), ie, those who once contributed to the telecomhall.
If you are not a donor, and have your own macros and codes, insert a code in any spreadsheet, for example right-clicking on it (1) and choosing 'View Code' (2).
So, write your own code to manipulate your data. Don't worry, you don't need to be an expert, and also don't to write too much code: notice that in the case of the demonstration of this code (Hunter xFilter) we use less than 50 lines in total (except for comments).
Basically, you just need to manipulate the 'AutoFilter' object properties, and its parameters can be found through the Excel Help for 'AutoFilter', including a complete example of its use.
Applying Hunter xFilter
Now let's see our application in action, or in other words, what our custom filter do.
Note: the example filter (file) is of type 'XLS', or Excel 2003 format, and is therefore compatible with the newer versions - 2007 and 2010. Moreover, as usual, we provided some sample data so you can practice.
First, we adjust the way our filter presents the data. In this case, we inserted a row above the header, paint it with yellow color (1). This is the line where we enter the values or criteria for the filter.
And to get a little more interesting, we insert one more line, writing the formula '= SUBTOTAL (3,A4:A65536)' at first cell on that line (2), which therefore will contain information on how many rows are filtered.
When typing a value in the yellow line above the headers, the macro uses this value and applies the 'Auto Filter' for the corresponding column!
For example, typing 'Leo' and pressing 'Enter' (1), 'Auto Filter' is automatically applied (2), and we have only three filtered values (3) - only for lines with 'Leo' in the 'Text' column.
If we type another value, such as the number '35' in the 'Number' column (1), the macro continues by filtering the data further.
Selecting yellow line (1), and clearing its contents - for example pressing the 'Delete' key when the row is selected - we have all table data (all original data) without any filter criteria applied.
To finish, the macro has a special option to filter 'Empty' values by typing '=' in the desired column. And also an option to identify 'Not Empty' values, in this case while typing '<>'.
For example, see what happens when we type '=' (1) immediately above the 'Number' column: the filter is applied, and we show only the rows (2) of our table where 'Number' field is blank (or empty).
These were some examples of the use of xFilter Hunter, or an example of Customized Filters in Excel for Hunter Users.
Needless to say that you can extend the example shown here, the way you find necessary, just manipulating and changing the properties of the 'Auto Filter' in accordance with its values and criteria. Simply change the VBA code according to your needs.
Anyway, we hope it was useful at least as a starting point for those wishing to write their own code.
Conclusion
Today we saw another good practice using the tools available to us, like Microsoft Excel.
Using the Hunter Methodology, we always seek to improve efficiency in our work, as creating a custom filter in Microsoft Excel, facilitating user interactivity with traditional ways of applying filters and criteria in a list.
Thank you for visiting, and once again thank those who recognize our efforts, and contribute with one time donation to get all Hunter System.
Always remember: the knowledge that you acquire reading each tutorial can represent your differential!
