Show All

Filter a range

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 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 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 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?"

   5. To add another criteria, click And or Or, and repeat the previous step.

   Filter for blank or nonblank cells

   Click the 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 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 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 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 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?"

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