AutoFilter

AutoFilter (found under the Data > Filter menu) provides an easy way to limit the data displayed in a list. It’s less flexible than its cousin Advanced Filter, but what it lacks in flexibility, it makes up in ease of use.

Select the range you want to filter and choose Data > Filter > AutoFilter. You can select one cell in the range, and as long as the range is contiguous, Excel will guess which range you want to filter. Note that the first row of your range must contain headings. If it doesn’t, Excel will assume that the first row is headings, which may be undesirable. Once you’ve selected the menu item, Excel inserts dropdown boxes into cells of the header row.

autofilter1

Clicking on the dropdown will present a list of possible values. Most of the values will likely be actual values from the list. The other values are All, Top 10, and Custom.

autofilter2

In this example, I select 3 to only show those rows whose SupplierID equals 3.

autofilter3

The All choice is used to clear the filter for that column. Top 10 is used to show the Top or Bottom 10 items. A dialog appears when you select that choice that allows you to pick Top or Bottom and Items or Percent. Happily, the 10 isn’t fixed either; you can select a number from 1 to 500, although I’m not sure what use 500% would be.

The Custom choice gives a little more flexibilty to your filter in that you can choose greater than, less than, and all those comparison operators that you know and love.

Debra at Contextures
says the limit on the number of choices the dropdown will show is 1000, and I have no reason to doubt her. She also has some lovely pages about AutoFilter, so if you’re interested in learning more, I recommend them.

You can filter on more than one column also. Just set the filter for each column separately. From the example above, once you’ve limited the list to SupplierID=3, you can further limit the list, for instance, with the Custom choice and only show those items with SupplierID=3 AND UnitPrice > 26.

autofilter4

Filtering on multiple columns is an AND proposition. If you need to use an OR between multiple columns, you’ll either need a helper column or the Advanced Filter, also under the Data > Filter menu.

Finally, to clear the dropdowns, select AutoFilter from the Data > Filter menu again and it will toggle off.

Posted in Uncategorized

8 thoughts on “AutoFilter

  1. In my view, autofilters are one of the key features of Excel but they become much faster and even easier to use if you control them with VB.
    I have a filter row at the top of my data lists and type entries such as ‘fred’ in a cell to automatically activate the autofilter with fred*, rather than using the drop downs which are particularly slow to use with wild cards.
    I’ve also implemented the AND/OR facility so that simply typing in my filter cell fred/o/barn will set the autofilter to use fred* OR barn*.
    And also various other enhancements.
    I’ve been surprised that other better programmers haven’t made such a utility widely available for the Excel community.

  2. i have data in month wise and there are spaces in the rows after every month . when i filter i get only custom all and top 10 why dont i get the rest of the data. when spaces are ther in rows after every month

  3. One word of warning… one of the common ways to find the last column in a spreadsheet is to use the .find method (I don’t want to go into the detail, search the Google if you need the full code). It can throw up errors when a filter is active, when there are rows outside the filter range.

    In a procedure I’ve got, an the dimensions of an array of a worksheet was created using the .Find method, as the .UsedRange method can be unreliable at the best of times. But because the sheet contained a filtered list, it produce a few “whacky” results (eg it thought the sheet had 10 columns instead of 18). Gave me a lovely “Subscript out of range” error.

    I still think the .Find method is the most efficient, but if you’re likely to be working with filtered lists, you may want to consider an alternative approach.

    Great blog. If I’m ever in the States, I will try to schedule a visit to yourself and Mr Walkenbach – for an Excel geek such as myself, photos with the two of you on my wall would be like a photo of a Beatles nut with Paul and Ringo… :o)

  4. How to write the vba script that return the list of the filter data for example, get the list of product where unit price

  5. I am trying to filter out all rows in a sheet that Contain an error #N/A. There are like 2400 rows every time so the comuter generally just freezes. Does anyone know a way around this, in excel or with VB code?

  6. I like to use the filter count at the bottom left corner of the screen. That does not show up any more. Any idea why?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.