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.
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
In Excel 2003, use the Custom option on the AutoFilter drop down.
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.
You can also use operators, and in the next screen shot I’ve added a “>20” criterion in the Unit Price column.
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.
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.
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.
____________
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.”
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
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.