D
Don Guillett
Please DO put the question in the body of your posting along with a
meaningful subject line.
When all else fails look in the help index for FILTER
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
a.. When you apply a filter to a column, the only filters available for
other columns are the values visible in the currently filtered range.
b.. Only the first 1000 unique entries in a list appear when you click the
arrow .
meaningful subject line.
When all else fails look in the help index for FILTER
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
a.. When you apply a filter to a column, the only filters available for
other columns are the values visible in the currently filtered range.
b.. Only the first 1000 unique entries in a list appear when you click the
arrow .