Filter data even faster with a custom filter technique in Excel
Last month, I showed you a custom find method using Data Validation in an easy custom find technique in Excel. In this article, we’ll do something similar, but with filtering. Excel’s Filter feature is helpful, but the interface is awkward to use and requires a bit of training to use efficiently. We’ll use an Active X control to capture an input string; as a user types, Excel will filter the records right before his or her eyes, as shown in Figure A. The user doesn’t have to do anything but type the string. This custom filtering control isn’t superior to Excel’s built-in interface, but some users will find it easier to use. In addition, it might help you in those instances when the built-in options aren’t flexible enough.
Filtering on the fly.
I’m using Excel 2013 and Windows 7. You can work with any simple data set or download the demonstration .xlsm or .xls file (which also contains the Data Validation find example from last month). We’ll use the Table object, which isn’t supported by the .xls format, in the first solution. If you’re using the .xls format or if you’re using .xlsx but don’t want to use the Table object (because of inherent limitations), you can use the second technique, which relies on a dynamic range.
The Table method
Using Excel’s Table object provides the easiest solution, because everything updates as users enter data. You convert the data set to a Table, put the controls in place, add a short VBA procedure, and users can delete records, modify existing records, and even add new records—and it all works. On the other hand, Excel’s Table object has limitations. You can’t share a workbook that contains a Table object, which is a serious limitation in my opinion. Other features, such as Subtotal, won’t work on a Table. If its limitations won’t impact use, use the Table solution. It’s easy to implement and maintain.
The first thing you need is a Table, so let’s generate one from a simple data set as follows:
- Click anywhere inside the data set.
- Click the Data tab.
- Click Table in the Tables group.
- In the resulting dialog (Figure B), click OK. If you’re working with a data set that doesn’t have headers, be sure to uncheck that option before clicking OK.
The resulting Table has filtering drop-downs for each column. Now, let’s see what your users might be facing by filtering the data set for Jones:
- Click the drop-down in the Name header cell.
- In the resulting filter dialog, uncheck (Select All).
- Check Jo… wait a minute… you can’t filter for Jones (Figure C).This is one of the limitations you run into with the filtering interface. Most users might not know what to do at this point. Would yours?
- Instead of using the values list, select Text Filters, and then choose Contains (Figure D).
- Enter Jones to the right of the contains filter (Figure E).
- Click OK to see the filtered results (Figure F).
Admittedly, the example is a bit contrived; the name values aren’t autonomous (on purpose). In a perfect world, your data is always perfect, and your users know what to do with it. In the real world, we’re lucky if we have one or the other; sometimes, we don’t have either! Most of us end up supporting at least a few legacy workbooks that are far from perfect.
In this case, someone should split the Name values into first and last name fields. Then, you could filter for Jones from the supplied list. That doesn’t negate the number of clicks and the specialized knowledge your users will need to acquire before they can filter the records.
Now that you’ve seen the possible issues users might not have the expertise to work around, let’s build that custom filtering control. I recommend that you put the filtering control above the column users will be filtering. (I inserted a few rows above the Table, but you might not need to do so.) Next, name the Table ZIPCodeTable. To do so, select the Table, click the contextual Design tab and enter a more meaningful name in the Table Name control, as shown in Figure G. Naming the Table isn’t critical to this technique, but working with a meaningful name is easier. Finally, because this technique relies on a VBA sub procedure (short, so don’t panic), save the workbook as a macro-enabled workbook if you’re using the .xlsx format.
Name the Table.
Now, you’re ready to add the filtering controls, as follows:
- Click the Developer tab.
- Click Insert in the Controls group and select the Active X (bottom section) Text Box control. Use drag-and-drop to embed a text box control above the Name field in the Table (Figure H).
- Click Properties in the Controls group.
- Enter B5 as the LinkedCell setting (Figure I), and close the Properties window.
- Double-click the control to open its module and enter the code shown in Listing A. Don’t try to copy and paste from the web page, as VBA might complain. Instead, download the example demo file or enter the code manually.
- Save the module and return to the sheet.
- Add an ActiveX Command Button to the right of the text box control (Figure J).
- Click Properties and enter Clear as the button’s Caption setting, and close the Properties window.
- Double-click the Clear button and enter the procedure shown in Listing B.
- Save the module and return to the sheet.
- Click Design View in the Controls group so you can use the controls.
Private Sub TextBox1_Change() 'Filter Name field in ZIPCodeTable Table. Dim strFilter As String strFilter = "*" & [B5] & "*" Debug.Print strFilter ActiveSheet.ListObjects("ZIPCodeTable").Range.AutoFilter _ Field:=1, _ Criteria1:=strFilter, _ Operator:=xlFilterValues End Sub
Private Sub CommandButton1_Click() 'Clear ZIPCodeTable custom filter. [B5] = "" End Sub
All the pieces are in place and you can start filtering. For example, you’d start a filter for Mattingly by entering M. As a result, the filter string is *M* and the filter returns any record that contains an M in the Name field, as shown in Figure K.
Start entering the search string.
Continue typing until the filter has enough characters to find the right record. In this case, you must enter Matti before the filter isolates the Mattingly record, as shown in Figure L. Click the Clear button to remove the filter and display all of the records.
The filter finds the record.
The AutoFilter method triggers the filtering feature, as if you’d clicked the drop-down and chosen a value yourself. The Field argument specifies the first column in the Table. The second argument species the filtering string, which you supply by entering characters into the text box control. The Debug statement isn’t necessary for the code to work, but I always include them when concatenating values for easy debugging.
As is, this feature isn’t case sensitive. Clicking Clear sets the filter to an empty string. The example data is simple, but you can see how easily your users will adjust to using this custom filtering control.
If you’re still using Excel 2003 or you can’t use a Table object, you’ll need a dynamic name range. They’re a bit complicated and frankly, can be difficult to maintain, so I recommend the Table solution whenever possible.
First, insert a few rows above your data set to make room for the controls, if necessary, and then create the named ranged as follows:
- Click the Formulas tab.
- Click Name Manager in the Defined Names group.
- Click New in the resulting dialog.
- Enter Name and choose ZipFilter for the Scope.
- The first range in the OFFSET() function begins with $B$7, the header cell, because Excel requires the header cell in the Filter range. Replace the Refers to reference with the following formula (Figure M), where FilterZIP is the sheet name: =OFFSET(FilterZIP!$B$7,0,0,COUNTA(FilterZIP!$B:$B)-2,1)
- Click OK and then Close.
Check the range by pressing [F5] and entering Name in the Reference control. If you entered the OFFSET() function correctly, Excel will select the B7:B12. Similar to the Table solution, the dynamic named range also comes with limitations:
You can’t enter anything but data below the data set, because the function uses the absolute column reference structure ($B$B).
For the same reason, you must account for cells that contain text above the range, but aren’t part of the data set. That’s where the -2 comes in. In the existing data set, there are three cells above B8 that contain values. So, why not -3? We’ve already accounted for the header cell text (B7) in the OFFSET() range.
Once you put this solution into place, don’t insert data above the data set. If you must insert new data above the named range, update the OFFSET() function accordingly.
Add the text box and command button controls using the instructions from the Table section. Link the text box to cell B5 and change the button’s Caption setting to Clear. Add the code in Listings C and D to the text box control and the command button, respectively. Save the module and return to the sheet. Be sure to disable Design Mode by clicking the Design Mode option in the Controls group before trying to use the controls.
Private Sub TextBox1_Change() 'Filter Name field. Dim strFilter As String strFilter = "*" & [B5] & "*" Debug.Print strFilter ActiveSheet.Range("Name").AutoFilter _ Field:=1, _ Criteria1:=strFilter, _ Operator:=xlFilterValues End Sub
Private Sub CommandButton1_Click() 'Clear custom filter for Name field. [B5] = "" End Sub
Now, let’s filter the records to find Mattingly by entering Matti to filter the records to Mattingly. Changing the value in the text box executes the control’s sub procedure, which uses the contents of B5 to create a filter based on the Name range.
To see how the dynamically named range works, enter a new record, as shown in Figure N.
Enter a new record.
To find the record, enter Mar or Andr, as shown in Figure O.
Filter for the new record.
This solution is harder to implement than the Table solution because of the OFFSET() function. Be sure to accommodate the data set’s structure when entering all those arguments. If even one argument is wrong, this solution won’t work.
Knowing how OFFSET() works will help you troubleshoot this technique when applying it to your own work. This function uses the following syntax:
OFFSET(reference, rows, cols, [height], [width])
Refer to Table A for an explanation of each argument.
The easiest way to understand how this function works in regards to the dynamic name range is to evaluate the function using our example. The COUNTA() function counts the number of text values in column B. Initially, this function returns 8. By subtracting 2 (we’re not including the header text in the count), the COUNTA() function supplies a Height argument of 6. The selection begins with B7 and selects six cells, B7:B12. When you add a record, the COUNTA() function returns 9. Subtracting 2 returns 7, so OFFSET() selects seven cells.
Table or dynamic range
The choice is simple: choose the Table solution unless that solution can’t accommodate other requirements. If push comes to shove, then try the dynamic range name solution. Be careful when writing your OFFSET() function. If it doesn’t work, revisit the function and check all the arguments. Using GoTo to select the named range will help you troubleshoot this function if you’re unable to determine the right arguments.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers that I help. You can contact me at [email protected]