Filtering data is a quick and easy way to create a subset of data in a range of cells or in a table column. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want to display. You can also filter by more than one column. Filters are additive. Each additional filter that you apply works only the subset of data produced by the current filter, and therefore further reduces the subset of data. Thus, you might first filter on data from the year 2008, and then on data from France. The resulting information would contain only data that applied to France in 2008.
You can filter by a list of values or by criteria.
What do you want to do?
Filter text
-
Locate a column that contains alphanumeric data.
-
Click the arrow
in the column header. -
Do one of the following:
Select from a list of text values
-
Click Filter.
-
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 1,000 items. If the list is large, clear (Select All) at the top, and then select the specific text values to filter by.
Create criteria
-
Point to Text Filters and then click one of the comparison operator 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.
-
In the Custom Filter dialog box, in the box on the right, enter text.
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.
The following table describes the wildcard characters that you can use as comparison criteria for text filters.
-
| Use | To find |
| ? (question mark) | Any single character |
| * (asterisk) | Any number of characters |
| ~ (tilde) followed by ?, *, or ~ | A question mark, asterisk, or tilde |
Filter numbers
-
Locate a column that contains numeric data.
-
Click the arrow
in the column header. -
Do one of the following:
Select from a list of numbers
-
Click Filter.
-
In the list of numbers, select or clear one or more numbers to filter by.
The list of numbers can be up to 1,000 items. If the list is large, clear (Select All) at the top, and then select the specific numbers to filter by.
Create criteria
-
Point to Number Filters and then click one of the comparison operator commands or click Custom Filter.
For example, to filter by a lower and upper number limit, select Between.
-
In the Custom AutoFilter dialog box, in the box or boxes on the right, enter a number or numbers.
For example, to filter by a lower number of "25" and an upper number of "50", enter 25 and 50.
-
Filter dates or times
-
Locate a column that contains dates or times.
-
Click the arrow
in the column header. -
Do one of the following:
Select from a list of dates or times
-
Click Filter.
-
In the list of dates or times, select or clear one or more dates or times to filter by.
By default, the filter process groups all of the dates in the range in a hierarchy of years, months, and days. When you select or clear a level in the hierarchy, the filter automatically selects or clears all of the dates that are nested below that level. For example, if you select "2006", the Web viewer shows months below "2006", and then lists the days below the name of each month.
The list of values can be up to 1,000 items. If the list of values is large, clear (Select All) at the top, and then select the values to filter by.
Create criteria
-
Point to Date Filters and then do one of the following:
Common filter
A common filter is one based on a comparison operator. To use a common filter, follow this procedure:
-
Click one of the comparison operator commands (Equals, Before, After, or Between) or click Custom Filter.
-
In the Custom Filter dialog box, in the box on the right, enter a date or time.
For example, to filter by a lower and upper date or time, select Between.
-
In the Custom Filter dialog box, in the box or boxes on the right, enter a date or time.
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.
Dynamic filter
A dynamic filter is one where the criteria can change when you reapply the filter. To use a dynamic filter, click one of the predefined date commands.
For example, on the All Dates in the Period menu, to filter all dates by the current date, select Today, or to filter all dates by the following month, select Next Month.
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 is. This can be useful, for example, to compare sales by a period across several years.
-
The filter options This Year and Year to Date handle future dates in a different way. This Year can return dates in the future for the current year, whereas Year to Date returns only dates up to and including the current date.
-
-
-
Filter for top or bottom numbers
-
Locate a column that contains numeric data.
-
Click the arrow
in the column header. -
Point to Number Filters and then select Top 10.
-
In the Top 10 Filter dialog box, do the following.
-
In the box on the left, click Top or Bottom. The filter determines the top and bottom values based on the original range of cells. It does not use the filtered subset of data.
-
In the box in the middle, enter a number from 1 to 255 for Items or 0.00 to 100.00 for Percent.
-
In the box on the right, do one of the following:
-
To filter by number, click Items.
-
To filter by percentage, click Percent.
-
-
Clear a filter for a column
-
To clear the filter for a column, click the Filter button
on the column heading, and then click Clear Filter from <Name>.
Learn about possible issues with filtering data
The following are issues that you should be aware of that either limit filtering or prevent filtering.
Avoid mixing storage formats For best results, the workbook author should not mix 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 formats, the filter dialog box displays the format that occurs most frequently. For example, if the column contains three values stored as number and four as text, the filter process displays Text Filters.
There may be more filtered rows than the limit of the maximum number of rows displayed If the number of rows that match the filter exceeds the maximum number of rows displayed in the workbook, and then the Web-based viewer displays the first set of filtered rows up to the current maximum number. To see all of the filtered rows, you may need to page to the next scrolling region. If the workbook is in an Excel Web Access Web Part, it might be possible to increase the number of rows that the Web Part displays.
Nonprinting characters can prevent filtering The Web-based viewer cannot filter data in a column that contains characters with an ANSI value of 0 to 32, which are nonprinting characters. In an Excel workbook, these characters display a rectangular box as a placeholder character. To filter data, the workbook author must remove these nonprinting characters or replace them with a printing character.
Calculated members in PivotTable reports You cannot apply a filter that combines a calculated member (indicated by a blue color) with any other item, including other calculated members. To use a filter with a calculated member, make sure that you select only one calculated member. If you are selecting two or more items, remove any calculated members.
PivotTable metadata may be visible If you are a workbook author and you publish a workbook containing an OLAP PivotTable report with filtering applied, it's possible for a user to see the metadata of a hidden field. If you want to keep this information confidential, do not enable filtering.
No comments:
Post a Comment