AutoFilter in Excel

Overview

Filtering data in Excel can be quite advantageous, but you must build a spreadsheet with good data structure in order to take maximum advantage of the built in AutoFilter. In addition, there are certain aspects of this tool which require attention. Some things must be done ritualistically, while others are not so important. I’ll go into each of the major areas which affect AutoFilter and give examples along the way.
In the examples given in this write-up, I’ll show how to navigate while using Excel 2003. Some added functionality was introduced in Excel 2007, which will lightly be covered as well.

From the Menu

Getting to AutoFilter is very simple. In Excel 2003 – 2007 it is found under the Data menu.

In Excel 2007, it is found differently as Office 2007 delivers the Ribbon and no more traditional menus.

You can also apply AutoFilter from the Home tab in the Editing group (far right), but you have additional similar options from the Data tab (i.e. sorting and Advanced Filter).
For the Office 2007 users, as you may well know, all of your 2003 keyboard shortcuts are still valid, so you can use the keyboard combination Alt + D, F, F. This will apply the filter and not activate the Ribbon. To activate the Ribbon controls, navigate with the Alt key as well, but start with only a single click of the button, resulting with Alt, T, A. This is non-traditional in the fact that it is less keyboard strokes in 2007 as compared to 2003; in most cases it is the other way around.

Structuring Data

To start out, your data needs to be conducive for using AutoFilter. Specifically, you should always give preference to one-line headers. In addition, there are some things that you should never have in your data. They are:

1. Blank rows
2. Blank cells
3. Blank columns

One-Line Headers

The advantage of practicing this is it’s never a question of where to place the filter. The filter itself should be on the bottom-most header row, not on the data itself. It can be misleading when attempting to apply the filter.

In this case, the filter should be applied to the second row and not with the first row. This can be confusing for newcomers to AutoFilter and sometimes might even catch the power user. For general purposes, it’s better to use one-line headers.

Blank Rows

Good data structuring is by far the most important aspect of using AutoFilter. For example, if you have a filter set on your header rows followed by a blank row, you will not have anything to filter! This can be annoying as many people like to setup their spreadsheet how they would like to view it, which may or may not be proper data structuring. We won’t get into details on proper structuring, but only how it would relate to AutoFilter itself.

Putting a blank row between different areas in your data can lead to unwanted results as well. Excel is not a database, but when working with data, sometimes it’s best to structure it as one. Here is an example of data formatted conducive to using AutoFilter:

Here is the same data with a structure that will make it difficult – even impossible – to use AutoFilter:

Blank Cells

Having blank cells might look nice, but it doesn’t help when trying to use AutoFilter. Here is an example of bad structuring with blank cells:

Notice the ‘GeoArea’ column. It can be more pleasing to the eye, but for data manipulation it shouldn’t be done. If it’s a nice reporting format you’re looking for, creating a Pivot Table of your data can help with that. Keeping data and formatted data reports separate will save you big headaches.
There is the option to filter for Blank Cells, however. If your data structure has blank cells and you cannot get around using them, or perhaps you inherited a workbook with blank cells, you can use this option to isolate these rows. Vice-versa you can filter for non-blanks. While these options are native and available on every filter (non-blanks not an option in Excel 2007, based on the idea that you can de-select the Blanks field and you’ll have the same functionality), this shouldn’t be an indicator to structure data with these.
I use blank cells only when I need to manipulate bad [spreadsheet] design into good design. What I mean by that is I can isolate blank rows and then manipulate them to fill with data, thus eliminating the blanks. Let’s take the example pictured above. Typically what I would do is fill in each blank cell in column A with its preceding value. So A3:A7 should have the value of “North” and A9:A14 should have “South”.
There is an easy way to do this! First, select A2:A25. The next step is easy, but not well known. We want to select only the blank cells, which we can do by using SpecialCells. To go to this menu, hit F5 (or Edit menu, Goto), then click the Special button.

This will open a separate dialog box that allows you to go to what Excel refers to as SpecialCells. There are various types of SpecialCells you can go to. By default, this will work on the entire Selection; if only one cell is selected, it will work on the entire active sheet. We would be looking for the Blanks. Select that radio button and click Ok.

Next, we want to enter a formula in these blank cells, but we only want to enter it once for the entire range of cells. This means that it must be confirmed with Ctrl + Enter instead of just Enter. The formula is basic, just hit ‘=’ and your up arrow. If A2 was the activecell when you selected the blank cells, A3 should be the first activecell in the non-contiguous range. Your formula bar should read “=A2” and A2 should have the dancing ants around it (CutCopyMode). Confirm this with Ctrl + Enter.
You will then have all of the blanks filled with the value from the cell above it. (We can thank relative referencing for copying the formula down to our advantage.) Our next task is to leave these as static values and not formulas. To do so, we’ll use PasteSpecial. With this method, however, we cannot use a non-contiguous range when we paste. So we’ll need to select the entire range (A2:A25) all over again, or select the entire column if you’d like. Once selected, press Ctrl + C, Alt + E, S, V, Enter (the keyboard shortcuts for Copy, Edit {menu}, PasteSpecial, Values, Enter.
The other option in SpecialCells that is used mostly with AutoFilter is the Visible cells only. We won’t cover this option, but suffice to say it will let you select a non-contiguous range of cells that have been filtered and the only cells selected will be the non-filtered cells.
Note that if there are no blanks in your data range, the AutoFilter drop down arrow will not show Blanks or Non-Blanks at the end of the unique items list (this is in Excel 2003, it will only be a ‘Blanks’ checkbox in Excel 2007).

Blank Columns

Blank columns can be quite the pain. When applying AutoFilter, get there by going to the Data menu and selecting Filter, then AutoFilter. If you only have a single cell selected, for example B2, and apply AutoFilter, Excel will only filter those column headers until a break (blank column) is met.

Using AutoFilter

The method itself is fairly straightforward. Apply your criteria to the filter and let Excel do the data manipulation. A couple key points to note:

• One filter is allowed per column
• Two criteria is allowed per filter

You can filter data over multiple columns, using a different criteria (up to 2) in each field (column). This gives you great flexibility with this feature.
In Excel 2003, the filter menu is fairly straightforward. You have two sort options (ascending and descending), All, Top 10 & Custom. The remainder of the list is constructed of all of the unique values in that field. Beware, you are only allowed 1000 unique items in this list.

So using the example at the beginning of this paper, say we want to see all of the ‘GeoAreas’ from the North who have more than 20 employees. We would need column A and D with filters. Apply the filter to the first row (assuming proper data structuring), select the filter on ‘GeoAreas’ and click North. Select the filter on ‘NumEmployees’ and click Custom, is greater than, enter 20, hit OK.
Now if you’re using Excel 2007, the filter menu appears a little differently. The unique limit was also increased from 1000 to 10,000 unique items. You also have more options to filter by, in more of a tree view.

Not only do you have the familiar sort options, but you can also sort by color now! Previous to 2007 you could only do this via VBA. You’ll also notice the checkbox’s. Previously it was only a list selection (much like a data validation list) where now you can customize your filter in any way you choose.
If you have colors applied to your data table, you can also filter by color (also previously unavailable except via VBA). In the examples here, the colors were applied via Table, which is different than traditional coloring. The Text Filters drop down allows you quite a variety of textual filter options as well. If your field contains only numeric values, you’ll see a Numbers Filter instead which adds more options for filtering your numbers. The same goes for filtering Dates, which received a much needed overhaul.
From the example we walked through, you now see only 3 records showing. If you use Pivot Tables, think of this as a reverse drill down. For thousands of records, this can be quite the powerful tool!

AutoFilter with VBA

Using AutoFilter programmatically can be quite advantageous, but quite the hair-puller as well. As VBA is a subset of Visual Basic (VB), it is based on Object Oriented Programming (OOP). So in the Excel Object Model (OM), AutoFilter is a method based on the worksheet object.

The Syntax

AutoFilter via VBA has a very basic syntax structure:

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

What it all means:

• Field

This is one of the most commonly mistaken pieces of code with AutoFilter, and it is a required field. The Field is referring to the column which the AutoFilter range is applied. It is like everything in Excel, such as Conditional Formatting, it is relevant to where the selection (or range in this case) is. Lets say you are working with range A:D, the AutoFilter Field 3 is column C. Although if you’re using a range of C:E and use Field 3, you will be filtering column E. Let’s look at some examples:

This filters column D for values greater than 10.

This filters column A for values equal to “North”.

This filters column B for values equal to “New York”.
As you can tell, the field can be used for multiple purposes. If a Field is used that is greater than the number of columns in the range you will receive an error.

• Criteria1

This is a required field. You must have at least one criterion set for AutoFilter to work. The only time you’ll not see a criteria is if AutoFilter is being turned off, then you will not see any of the syntax’s.
You can set the criteria to anything you want, but it would be best if it was something actually in your column/field.

• Operator

This is generally only used if you are going to use Criteria2. When using 2 criteria together, you must specify how you want them to relate to each other; that is where the Operator syntax comes into play.

• Criteria2

Used for your second criteria. Much like Criteria1, this is only used to set the second criteria for the specified Field.

• VisibleDropDown

Not used very often, this syntax is completely optional, but can give you a very nice looking spreadsheet report. This will also make it manually difficult for users to change the AutoFilter.

General Guidelines

• Unless AutoFilter is taken off, assume it is still in place. Excel OM has a built-in property for this called AutoFilterMode which is a worksheet property. This is a Boolean property and is read/write capable, so it can be read or set with True or False. Alternatively you can apply the filter without any fields or criteria specified to turn AutoFilter off.
• Apply one filter at a time. Remember, each field can have two criteria applied to it. To apply multiple field filters perform them as separate operations.
• When you have completed using the AutoFilter method, unless otherwise required, turn it off.

Applying a Single Filter

To apply a filter to our example on column A for the criteria “North”, the code will look like the following:

Breaking this down, logically we are filtering the specified range, set as a variable ‘rngFilter’, in the first field (column A) for the criteria of “North”. Manipulation from that point will be shown in a moment.
After the variables are set, we ensure that AutoFilter is turned off. This is mostly important if a user has previously applied a filter before running the code. If AutoFilter is applied, setting a new filter will not replace the original filter(s), but rather add to them. Generally this is not desired.
We could use a simple For/Next loop to iterate an AutoFilter through different values in the specified range:

The same routine, with comments explaining the logic:

Applying Multiple Filters

When applying filters to multiple columns/fields, you will need to run them as separate lines of operation. In the following example we will apply a filter to column A for “North” and column D for greater than 20 employees:

The above code will give us the same example shown earlier. To add more filtered fields, add additional lines of filtering code.
For two criteria in one field, you have the option of using a logical AND operation or an OR operation. The Excel constants for these are xlAnd and xlOr respectively. Here is a line of code showing both ways:

One thing to note about the first line of code above is that you will get all of the values between 10 and 30. This is because of the xlAnd operator used. The second line of code will get you everything above 10 and everything equal to 1 in that field. Knowing which one to use in the right situation is entirely dependent on what you are filtering.
In Excel 2007 there are many more operators you can use. This article will not cover them completely, but will be looked at for a future update of the article.