Excel AutoFilter By Typing Criteria

Someone emailed me for help with an Excel AutoFilter last week. He wanted to type the criteria onto a worksheet, and have the filtered results shown automatically. There are some built-in options for filtering by text, and keep reading to see a worksheet version that Roger Govier designed.

AutoFilter Search in Excel 2010

There is a new feature in Excel 2010 that provides easy searching, though not on the worksheet. You can see an example here, for the Excel 2010 AutoFilter search feature.

image

AutoFilter Search in Earlier Versions

In earlier versions of Excel, you can filter for text, but it’s a bit more work. In Excel 2007 you can use a text filter, which opens the Custom AutoFilter dialog box

image

In Excel 2003, use the Custom option on the AutoFilter drop down.

image

Roger Govier’s FastFilter

If you’d like to enter the AutoFilter criteria on the worksheet, instead of a search box or dialog box, you can use Roger Govier’s FastFilter sample Excel file.

He has set up a table on the worksheet, with an empty row above the table. In that row, you can type one or more criteria, and when you press the Enter key, the table is automatically filtered.

For a simple filter, type an exact match for a value, and press Enter. In the screen shot below, the table is showing only the items from category 2.

fastfilter01

You can also use operators, and in the next screen shot I’ve added a ">20" criterion in the Unit Price column.

fastfilter02

Use WildCard Characters

If you’re trying to find a specific string of characters in a column, you can use the * and ? wildcard characters. In the next screen shot, I used *b* in the product name column, to find any products that have a "b" somewhere in the name.

fastfilter03

Use Multiple Criteria in a Column

You can use special characters for OR (^^) and AND (^), to combine multiple criteria in a single heading cell. In the Category ID column, I used the ^^ characters to find category 2 OR 4. In the Unit Price column, the ^ character limits the price to >20 AND <35.

fastfilter04

Remove the Criteria

To clear the filter from a column, just click on the criteria cell, and press the Delete key on your keyboard. If you want to clear all the filters, select all the criteria cells, and press Delete.

Download the Sample File

To download the sample file, you can visit Roger’s Sample Files page on the Contextures website. In the Filters section, look for FL0001 – Fast Filter. There is a download link for the FastFilter zipped file.

The file is in Excel 2003 format, and will work in later versions too. After you open the file, enable macros, so you can test the automatic filter feature.

____________

You may also like...

3 Responses

  1. Pasi says:

    Hi, i tested this in Excel 2010 and notice that vba need to be altered littlebit. I found instructions here so its not my idea: http://social.msdn.microsoft.com/Forums/da-DK/exceldev/thread/608c32c1-3d47-47f4-a6ff-893b4695db20

    “In newer versions of Office, replace trim by strings.trim (same for Left, Right etc) and it should work fine.”

  2. Philippe BÉRARD says:

    Hello,

    Just to share something very usefull for me, it is also possible to include a toggle button at the top of the column with a little code attached (including a conditional formatting), like this one :

    Private Sub ECART_Click()
    Colonne = 29
    If ECART.Value = True Then
    Filtre (Colonne)
    Else
    Defiltre (Colonne)
    End If
    End Sub

    Private Sub Filtre(Colonne As Integer)
    ActiveSheet.Range(“$A$3:$AE$244”).AutoFilter Field:=Colonne
    ActiveSheet.Range(“$A$3:$AE$244″).AutoFilter Field:=Colonne, Criteria1:=”0″, _
    Operator:=xlAnd, Criteria2:=””

    With Range(Cells(1, Colonne), Cells(3, Colonne)).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -4.99893185216834E-02
    .PatternTintAndShade = 0
    End With
    End Sub

    Private Sub Defiltre(Colonne As Integer)
    ActiveSheet.Range(“$A$3:$AE$244”).AutoFilter Field:=Colonne
    With Range(Cells(1, Colonne), Cells(3, Colonne)).Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    End Sub

  3. FJ says:

    Can someone help me in “countif” formula.I am working on an excel sheet having an annual plan with dates (ddmm), I want to count the months with date i.e. in column A1:A11 there are different dates with different months.

Leave a Reply to Philippe BÉRARD Cancel reply

Your email address will not be published. Required fields are marked *