星期一, 二月 06, 2012

Excel AutoFilter By Typing Criteria

from contextures.com
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.


____________


RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Excel AutoFilter By Typing Criteria

Please visit the Contextures page on Facebook


没有评论: