Filter a range

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

You can apply filters to only one range on a worksheet at a time.

  1. Click a cell in the range you want to filter.
  2. On the Data menu, point to Filter, and then click AutoFilter.

Filter for the smallest or largest number

  1. Click the arrow Field arrow in the column that contains the numbers, and click (Top 10...).
  2. In the box on the left, click Top, or Bottom.
  3. In the box in the middle, enter a number.
  4. In the box on the right, click Items.

Filter a range for rows that contain specific text

  1. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
  2. In the box on the left, click equals, or does not equal, contains, or does not contain.
  3. In the box on the right, enter the text you want.
  4. If you need to find text values that share some characters but not others, use a wildcard character.

How?

The following wildcard characters can be used as comparison criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for filters, and when searching and replacing content.

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, fy91~? finds "fy91?"
  1. To add another criteria, click And or Or, and repeat the previous step.

Filter for blank or nonblank cells

Click the arrow Field arrow in the column that contains the numbers, then click (Blanks) or (NonBlanks).

 Note   The Blanks and NonBlanks options are available only if the column you want to filter contains a blank cell.

Filter for numbers greater than or less than another number

  1. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
  2. In the box on the left, click is greater than, is less than, is greater than or equal to, or is less than or equal to.
  3. In the box on the right, enter a number.
  4. To add another criteria, click And or Or, and repeat the previous step.

Filter for a number equal to or not equal to another number

  1. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
  2. In the box on the left, click equals, or does not equal.
  3. In the box on the right, enter a number.
  4. To add another criteria, click And or Or, and repeat the previous step.

Filter for the beginning or end of a text string

  1. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
  2. In the box on the left, click begins with, or does not begin with, or ends with, or does not end with.
  3. In the box on the right, enter the text you want.
  4. If you need to find text values that share some characters but not others, use a wildcard character.

How?

The following wildcard characters can be used as comparison criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for filters, and when searching and replacing content.

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, fy91~? finds "fy91?"
  1. To add another criteria, click And or Or, and repeat the previous step.

Filter for the top or bottom numbers by percent

  1. Click the arrow Field arrow in the column that contains the numbers, and click (Top 10...).
  2. In the box on the left, click Top or Bottom.
  3. In the box in the middle, enter a number.
  4. In the box on the right, click Percent.

 Notes 

  • 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 1000 unique entries in a list appear when you click the arrow Field arrow .
 
 
Applies to:
Excel 2003