Using AutoFilter to filter data is a quick and easy way to find and work with a subset of data in a range of cells or table column.What do you want to do?

Learn more about filtering

Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.

You can also filter by more than one column. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.

Using AutoFilter, you can create three types of filters: by a list values, by a format, or by criteria. Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by cell color or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both.

Important For best results, do not mix storage formats, such as text and number or number and date, in the same column because only one type of filter command is available for each column. If there is a mix of storage formats, the command that is displayed is the storage format that occurs the most. For example, if the column contains three values stored as number and four as text, the filter command that is displayed is Text Filters. For more information, see and .

Filter text

  1. Do one of the following:

    Range of cells

    1. Select a range of cells containing alphanumeric data.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

      outlook ribbon image

    Table

    • Make sure that the active cell is in a table column that contains alphanumeric data.
  2. Click the arrow filter drop-down arrow in the column header.
  3. Do one of the following:

    Select from a list of text values

    • In the list of text values, select or clear one or more text values to filter by.

      The list of text values can be up to 10,000. If the list is large, clear (Select All) at the top, and then select the specific text values to filter by.

      Tip To make the AutoFilter menu wider or longer, click and drag the grip handle at the bottom.

    Create criteria

    1. Point to Text Filters and then click one of the comparison operator commands, or click Custom Filter.

      For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.

    2. In the Custom AutoFilter dialog box, in the box on the right, enter text or select the text value from the list.

      For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell.

      If you need to find text that shares some characters but not others, use a wildcard character.

      How to use wildcard characters

      The following wildcard characters can be used as comparison criteria for text filters.

      Use To find
      ? (question mark) Any single character
      For example, sm?th finds "smith" and "smyth"
      * (asterisk) Any number of characters
      For example, *east finds "Northeast" and "Southeast"
      ~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
      For example, fy06~? finds "fy06?"
    3. Optionally, filter by one more criteria.

      How to add one more criteria

      1. Do one of the following:
        • To filter the table column or selection so that both criteria must be true, select And.
        • To filter the table column or selection so that either or both criteria can be true, select Or.
      2. In the second entry, select a comparison operator, and then in the box on the right, enter text or select a text value from the list.

Filter numbers

  1. Do one of the following:

    Range of cells

    1. Select a range of cells containing numeric data.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

      outlook ribbon image

    Table

    • Make sure that the active cell is in a table column that contains numeric data.
  2. Click the arrow filter drop-down arrow in the column header.
  3. Do one of the following:

    Select from a list of numbers

    • In the list of numbers, select or clear one or more numbers to filter by.

      The list of numbers can be up to 10,000. If the list is large, clear (Select All) at the top, and then select the specific numbers to filter by.

      Tip To make the AutoFilter menu wider or longer, click and drag the grip handle at the bottom.

    Create criteria

    1. Point to Number Filters and then click one of the comparison operator commands or click Custom Filter.

      For example, to filter by a lower and upper number limit, select Between.

    2. In the Custom AutoFilter dialog box, in the box or boxes on the right, enter numbers or select numbers from the list.

      For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.

    3. Optionally, filter by one more criteria.

      How to add one more criteria

      1. Do one of the following:
        • To filter the table column or selection so that both criteria must be true, select And.
        • To filter the table column or selection so that either or both criteria can be true, select Or.
      2. In the second entry, select a comparison operator, and then in the box on the right, enter a number or select a number from the list.

Filter dates or times

  1. Do one of the following:

    Range of cells

    1. Select a range of cells containing numeric data.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

      outlook ribbon image

    Table

    • Make sure that the active cell is in a table column that contains dates or times.
  2. Click the arrow filter drop-down arrow in the column header.
  3. Do one of the following:

    Select from a list of dates or times

    • In the list of dates or times, select or clear one or more dates or times to filter by.

      By default, all dates in the range of cells or table column are grouped by a hierarchy of years, months, and days. Selecting or clearing a higher level in the hierarchy selects or clears all nested dates below that level. For example, if you select 2006, months are listed below 2006, and days are listed below each month.

      The list of values can be up to 10,000. If the list of values is large, clear (Select All) at the top, and then select the values to filter by.

      Tip To make the AutoFilter menu wider or longer, click and drag the grip handle at the bottom.

    Create criteria

    1. Point to Date Filters and then do one of the following:

      Common filter

      Note A common filter is one based on a comparison operator .

      1. Click one of the comparison operator commands (Equals, Before, After, or Between) or click Custom Filter.
      2. In the Custom AutoFilter dialog box, in the box on the right, enter a date or time, select a date or time from the list, or click the Calendar button to find and enter a date.

        For example, to filter by a lower and upper date or time, select Between.

      3. In the Custom AutoFilter dialog box, in the box or boxes on the right, enter a date or time, select dates or times from the list, or click the Calendar button to find and enter a date.

        For example, to filter by an earlier date of "3/1/2006" and a later date of "6/1/2006", enter 3/1/2006 and 6/1/2006. Or, to filter by an earlier time of "8:00 AM" and a later time of "12:00 PM", enter 8:00 AM and 12:00 PM.

      Dynamic filter

      Note A dynamic filter is one where the criteria can change when you reapply the filter.

      1. Click one of the pre-defined date commands.

        For example, to filter all dates by the current date, select Today, or by the following month, select Next Month.

      2. Click OK.

        Notes:

        • The commands under the All Dates in the Period menu, such as January or Quarter 2, filter by the period no matter what the year. This can be useful, for example, to compare sales by a period across several years.
        • This Year and Year to Date are different in the way that future dates are handled. This Year can return dates in the future for the current year, whereas Year to Date only returns dates up to and including the current date.
    2. Optionally, filter by one more criteria.

      How to add one more criteria

      1. Do one of the following:
        • To filter the table column or selection so that both criteria must be true, select And.
        • To filter the table column or selection so that either or both criteria can be true, select Or.
      2. In the second entry, select a comparison operator, and then in the box on the right, enter a date or time, select a date or time from the list, or click the Calendar button to find and enter a date.

Notes:

Filter for top or bottom numbers

  1. Do one of the following:

    Range of cells

    1. Select a range of cells containing numeric data.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

      outlook ribbon image

    Table

    • Make sure that the active cell is in a table column that contains numeric data.
  2. Click the arrow filter drop-down arrow in the column header.
  3. Point to Number Filters and then select Top 10.
  4. In the Top 10 AutoFilter dialog box, do the following.
    1. In the box on the left, click Top or Bottom.
    2. In the box in the middle, enter a number.
    3. In the box on the right, do one of the following:
      • To filter by number, click Items.
      • To filter by percentage, click Percent.

Note Top and bottom values are based on the original range of cells or table column and not the filtered subset of data.

Filter for above or below average numbers

  1. Do one of the following:

    Range of cells

    1. Select a range of cells containing numeric data.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

      outlook ribbon image

    Table

    • Make sure that the active cell is in a table column that contains numeric data.
  2. Click the arrow filter drop-down arrow in the column header.
  3. Point to Number Filters and then do one or more of the following:
    • To filter by numbers that are above the average, click Above Average.
    • To filter by numbers that are below the average, click Below Average.

Note Above and below average numbers are based on the original range of cells or table column and not the filtered subset of data.

Filter for blanks or nonblanks

  1. Do one of the following:

    Range of cells

    1. Select a range of cells.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter. outlook ribbon image

    Table

    • Make sure that the active cell is in a table column.
  2. Click the arrow filter drop-down arrow in the column header.
  3. Do one of the following:
    • To filter for nonblanks, in the AutoFilter menu at the top of the list of values, select (Select All), and then at the bottom of the list of values, clear (Blanks).
    • To filter for blanks, in the AutoFilter menu at the top of the list of values, clear (Select All), and then at the bottom of the list of values, select (Blanks).

    Note The (Blanks) check box is available only if the range of cells or table column contains at least one blank cell.

Filter by cell color, font color, or icon set

If you have manually or conditionally formatted a range of cells, by cell color or font color, you can also filter by these colors. You can also filter by an icon set created through a conditional format.

  1. Do one of the following:

    Range of cells

    1. Select a range of cells containing formatted by cell color, font color, or an icon set.
    2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

      outlook ribbon image

    Table

    • Make sure that the table column contains data formatted by cell color, font color, or an icon set (No selection is required).
  2. Click the arrow filter drop-down arrow in the column header.
  3. Select Filter by Color, and then depending on the type of format, select Filter by Cell Color, Filter by Font Color, or Filter by Cell Icon.
  4. Depending on the type of format, select a color, font color, or cell icon.

Filter by selection

You can quickly filter data with criteria that is equal to the contents of the active cell.

  1. In a range of cells or table column, right click a cell containing the value, color, font color, or icon you want to filter by.
  2. Click Filter, and then do one of the following:
    • To filter by text, number, or date or time, click Filter by Selected Cell's Value.
    • To filter by cell color, click Filter by Selected Cell's Color.
    • To filter by font color, click Filter by Selected Cell's Font Color.
    • To filter by icon, click Filter by Selected Cell's Icon.

Ungroup the hierarchy of dates in the AutoFilter menu

For the list of dates at the bottom of the AutoFilter menu in a date filter, you can change the hierarchical grouping of dates to a nonhierarchical list of dates. For example, you can filter for just two-digit years by manually selecting them from a nonhierarchical list.

  1. Click the Microsoft Office Button button image, click Excel Options, and then click the Advanced category.
  2. In the Display options for this workbook section, select a workbook, and then clear the Group dates in the AutoFilter menu check box.

See also: