This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Quickly Filter a List to Certain Records
Problem: I have 10,000 records in the worksheet. I need to be able to quickly find records that match a criterion, such as all East ABC records.
Figure 614 Find records within this data set.
Strategy: You can find records that match a criterion by using the Filter feature.
Before Excel 2007, the Filter was known as AutoFilter. Filter has improved in every version of Excel in the last decade.
Toggle on the Filter command by using either Home, Sort & Filter, Filter or selecting Data, Filter icon. As you can see below, the Filter button is three times larger than the Advanced Filter icon, which I take as evidence that Microsoft someday hopes to add enough power to Filter to eliminate the need for the Advanced Filter.
Figure 615 AutoFilter is now just Filter.
To filter your data set, follow these steps:
1. Make sure your data has a heading row. Select one cell within the data. Select Data, Filter. Excel will add a dropdown to each heading.
Figure 616 Filter dropdowns.
2. Select the Product dropdown. You will see several new elements that were not available in the AutoFilter. With these new choices is an annoying side-effect. Before you can select ABC, you have to first uncheck (Select All).
Figure 617 Uncheck Select All, then choose ABC.
3. Click the ABC check box. Click OK. You will now see just the ABC records.
4. Open the Region dropdown. Uncheck (Select All). Check East. Click OK.
You will now have only the East, ABC records. Notice the Funnel icon appears on all columns that have a filter applied.
Figure 618 Excel hides the other rows.
To clear a filter, open the dropdown and choose Clear Filter from Field.
Additional Details: Excel will detect if your column is text, numeric, or dates. Each column type includes a flyout with new options.
The new Date filters offer the most dramatic changes. The dates appear in a tree view, so you can turn on/off entire months rather than clicking all 30 dates that fall in a month. The Date Filter flyout menu offers many choices that seem like they were borrowed from Quickbooks.
Figure 619 Date columns offer many new choices.
Numeric columns offer a Top 10 filter, plus new choices such as Above Average.
Figure 620 New number filters.
The Top 10 Filter option allows you to specify the top or bottom “n" items or “n%" of items. The Top 10 feature was in previous versions of Excel, but all the other value filters in the figure above are new in Excel 2007.
If you have used cell colors, font colors, or icon sets, you can use the Filter by Color fly-out menu to show records that have a certain color.
Figure 621 Filter by color.
Gotcha:In order for the Date Filters or Number Filters options to appear, your data needs to be predominantly dates or numbers. If you have too many blank cells or too many text cells, Excel will treat the column as text and not offer these filter options in the dropdown.