Filter data in a range or table

Try Office 2010 In Excel 2010, search capability has been added to the AutoFilter.
Read a blog post or try Office 2010!

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. Once you have filtered data in a range of cells or table, you can either reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.

What do you want to do?


Learn more about filtering

Filtered data displays only the rows that meet criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) 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.

 Note   When you use the Find dialog box to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. To search all the data, clear all filters.

The three types of filters

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.

Reapplying a filter

To determine if a filter is applied, note the icon in the column heading:

  • A drop-down arrow Filter drop-down arrow means that filtering is enabled but not applied.

 Tip   When you hover over the heading of a column with filtering enabled but not applied, a screen tip displays "(Showing All)".

  • A Filter button Applied filter icon means that a filter is applied.

 Tip   When you hover over the heading of a filtered column, a screen tip displays the filter applied to that column, such as "Equals a red cell color" or "Larger than 150".

When you reapply a filter, different results appear for the following reasons:

  • Data has been added, modified, or deleted to the range of cells or table column.
  • The filter is a dynamic date and time filter, such as Today, This Week, or Year to Date.
  • Values returned by a formula have changed and the worksheet has been recalculated.

Do not mix storage formats

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 Convert numbers stored as text to numbers and Convert dates stored as text to dates.

Top of Page Top of Page

Filter text

  1. Do one of the following:

Range of cells    

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

Excel Ribbon Image

Table    

  • Make sure that the active cell is in a table column that contains alphanumeric data.
  1. Click the arrow Filter drop-down arrow in the column header.
  2. 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 (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) 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.

  1. 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?"
  1. 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.
  1. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

Top of Page Top of Page

Filter numbers

  1. Do one of the following:

Range of cells    

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

Excel Ribbon Image

Table    

  • Make sure that the active cell is in a table column that contains numeric data.
  1. Click the arrow Filter drop-down arrow in the column header.
  2. 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 (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands or click Custom Filter.

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

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

  1. 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.
  1. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

Top of Page Top of Page

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 Data tab, in the Sort & Filter group, click Filter.

Excel Ribbon Image

Table    

  • Make sure that the active cell is in a table column that contains dates or times.
  1. Click the arrow Filter drop-down arrow in the column header.
  2. 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 (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.).

  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.

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

  1. 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.
  1. 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.
  1. 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.
  1. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

 Notes 

  • All date filters are based on the Gregorian calendar.
  • Fiscal years and fiscal quarters always start in January of the calendar year.
  • If you want to filter by days of the week, format the cells to show the day of the week. If you want to filter by the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, and so the filter command that is displayed would be Text Filters, not Date Filters. For more information, see Show dates as days of the week.
  • 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.
  1. In the Display options for this workbook section, select a workbook, and then clear the Group dates in the AutoFilter menu check box.

Top of Page Top of Page

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 Data tab, in the Sort & Filter group, click Filter.

Excel Ribbon Image

Table    

  • Make sure that the active cell is in a table column that contains numeric data.
  1. Click the arrow Filter drop-down arrow in the column header.
  2. Point to Number Filters and then select Top 10.
  3. 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.
  1. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

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

Top of Page Top of Page

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 Data tab, in the Sort & Filter group, click Filter.

Excel Ribbon Image

Table    

  • Make sure that the active cell is in a table column that contains numeric data.
  1. Click the arrow Filter drop-down arrow in the column header.
  2. 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.
  1. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

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

Top of Page Top of Page

Filter for blanks or nonblanks

  1. Do one of the following:

Range of cells    

  1. Select a range of cells.
  2. On the Data tab, in the Sort & Filter group, click Filter.

Excel Ribbon Image

Table    

  • Make sure that the active cell is in a table column.
  1. Click the arrow Filter drop-down arrow in the column header.
  2. 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.

  1. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

Top of Page Top of Page

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 Data tab, in the Sort & Filter group, click Filter.

Excel 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).
  1. Click the arrow Filter drop-down arrow in the column header.
  2. 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.
  3. Depending on the type of format, select a color, font color, or cell icon.
  4. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

Top of Page Top of Page

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.
  3. To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

Top of Page Top of Page

Clear a filter

You can clear a filter for a specific column or clear all filters.

Clear a filter for a column

  • To clear a filter for one column in a multicolumn range of cells or table, click the Filter button Applied filter icon on the heading, and then click Clear Filter from <Column Name>.

Clear all filters in a worksheet and redisplay all rows

  • On the Data tab, in the Sort & Filter group, click Clear.

Excel Ribbon Image

Top of Page Top of Page

 
 
Applies to:
Excel 2007