Excel: Quickly Filter a List to Certain Records

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. Find records within this data set.

    Strategy: You can find records that match a criterion by using the Filter feature.

    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.

  2. 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.

  3. Filter dropdowns.
    1. Select the Product dropdown. Before you can select ABC, you have to first uncheck (Select All).

  4. Uncheck Select All, then choose ABC.
    1. Click the ABC check box. Click OK. You will now see just the ABC records.
    2. 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.

  5. 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 Date filters 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.

  6. Date columns offer many new choices.

    Numeric columns offer a Top 10 filter, plus new choices such as Above Average.

  7. 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.

  8. 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.