Filter a list of data

When you filter a list, you temporarily hide some of your content. Filters provide a quick way to find and work with a subset of data in a range or table.

Do any of the following:

ShowFilter for the top or bottom set of values

  1. Click a cell in the range or table that you want to filter.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. Under Filter, click Choose One, and then in the pop-up menu, do one of the following:
To filter for the Click
Top numbers by value Top 10.
Bottom numbers by value Bottom 10.
Top numbers by percentage Top 10, click Items, and then on the pop-up menu, click Percent.
Bottom numbers by percentage Bottom 10, click Items, and then on the pop-up menu, click Percent.

 Notes 

  • You can apply filters to only one range of cells on a sheet at a time.
  • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.
  • Only the first 10,000 unique entries in a list appear in the filter window.
  • Instead of filtering, you can use conditional formatting to make the top or bottom numbers stand out clearly in your data. For more information about conditional formatting, see Highlight data points with conditional formatting.

ShowFilter for a specific number or a number range

  1. Click a cell in the range or table that you want to filter.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. Under Filter, click Choose One, and then in the pop-up menu, do one of the following:
To filter for Click
Numbers greater than or less than a particular number Greater Than or Less Than.
Numbers equal to or not equal to a particular number Equals or Does Not Equal.
  1. In the box next to the pop-up menu, enter the number that you want to use.
  2. Depending on your choice, you may be offered additional criteria to select:

Filter criteria

To Click
Filter the table column or selection so that both criteria must be true And.
Filter the table column or selection so that either or both criteria can be true Or.

 Notes 

  • You can apply filters to only one range of cells on a sheet at a time.
  • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.
  • Only the first 10,000 unique entries in a list appear in the filter window.
  • Instead of filtering, you can use conditional formatting to make the top or bottom numbers stand out clearly in your data. For more information about conditional formatting, see Highlight data points with conditional formatting.

ShowFilter by font color, cell color, or icon sets

You can quickly filter data based on visual criteria, such as font color, cell color, or icon sets. And you can filter whether you have formatted cells, applied cell styles, or used conditional formatting.

  1. In a range of cells or a table column, click a cell that contains the cell color, font color, or icon that you want to filter by.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click a color.

ShowFilter blank cells

This option is available only if the column that you want to filter contains a blank cell.

  1. Click a cell in the range or table that you want to filter.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. In the (Select All) area, scroll down and select the (Blanks) check box.

 Notes 

  • You can apply filters to only one range of cells on a sheet at a time.
  • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.
  • Only the first 10,000 unique entries in a list appear in the filter window.

ShowFilter for specific text

  1. Click a cell in the range or table that you want to filter.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. Under Filter, click Choose One, and then in the pop-up menu, do one of the following:
To filter the range for Click
Rows that contain specific text Contains or Equals.
Rows that do not contain specific text Does Not contain or Does Not equal.
  1. In the box next to the pop-up menu, enter the text that you want to use.
  2. Depending on your choice, you may be offered additional criteria to select:

Filter criteria

To Click
Filter the table column or selection so that both criteria must be true And.
Filter the table column or selection so that either or both criteria can be true Or.

ShowFilter for the beginning or end of a line of text

  1. Click a cell in the range or table that you want to filter.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. Under Filter, click Choose One, and then in the pop-up menu, do one of the following:
To filter for Click
The beginning of a line of text Begins With.
The end of a line of text Ends With.
Cells that contain text but do not begin with letters Does Not Begin With.
Cells that contain text but do not end with letters Does Not End With.
  1. In the box next to the pop-up menu, enter the text that you want to use.
  2. Depending on your choice, you may be offered additional criteria to select:

Filter criteria

To Click
Filter the table column or selection so that both criteria must be true And.
Filter the table column or selection so that either or both criteria can be true Or.

ShowUse wildcard characters to filter

Wildcard characters can be used to help you build criteria.

  1. Click a cell in the range or table that you want to filter.
  2. On the Standard toolbar, click Filter Filter button, standard toolbar.
  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.
  4. Under Filter, click Choose One, and select any option.
  5. In the text box, type your criteria and include a wildcard character.

For example, if you wanted your filter to catch both the word "seat" and "seam", type sea?.

  1. Do one of the following:
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)

A question mark or an asterisk

For example, there~? finds "there?"

ShowRemove or reapply a filter

  • Do any of the following:
To Do this
Remove a filter that is applied to one column in a range or table Click the arrow AutoFilter arrow in the column that contains the content that you want to filter, and then click Clear Filter.
Remove all filters that are applied to a range From the Data menu, click Clear Filters.
Remove all filters that are applied to a table Select the columns of the table that has filters applied, then from the Data menu, click Clear Filters.
Remove filter arrows from or reapply filter arrows to a range or table On the Standard toolbar, click Filter Filter button, standard toolbar.

See also

About sorting

Sort a list of data

Highlight data points with conditional formatting

About filtering

Highlight patterns and trends with conditional formatting

Use data bars, color scales, and icon sets to highlight data

 
 
Applies to:
Excel for Mac 2011