Filter data in Excel Services
Filtering data is a quick and easy way to find and work with 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, which means that each additional filter is based on the current filter and further reduces the subset of data. You can create two types of filters: by a list of values or by criteria.
What do you want to do?
Filter text
To filter data, a workbook author must first save the Microsoft Office Excel workbook with a filter applied. For more information, see Microsoft Office Excel 2007 Help.
-
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.
To make the Filter menu wider or longer, click and drag the bottom corner.
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.
How to use wildcard characters
The following wildcard characters can be used as comparison criteria for text filters.
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, fy06~? finds "fy06?"
-
Filter numbers
To filter data, a workbook author must first save the Excel workbook with an AutoFilter applied. For more information, see the Microsoft Office Excel 2007 Help system.
-
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.
To make the Filter menu wider or longer, click and drag the bottom corner.
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
To filter data, a workbook author must first save the Excel workbook with an AutoFilter applied. For more information, see the Microsoft Office Excel 2007 Help system.
-
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, all of the dates in the range of cells or table column are grouped by a hierarchy of years, months, and days. Selecting or clearing a higher level in the hierarchy selects or clears all of the nested dates below that level. For example, if you select 2006, months are listed below 2006, and days are listed below 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.
To make the Filter menu wider or longer, click and drag the bottom corner.
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.
-
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. Or, to filter by an earlier time of "8:00 AM" and a later time of "12:00 PM", enter 8:00 AM and 12:00 PM.
Dynamic filter
A dynamic filter is one where the criteria can change when you reapply the 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.
-
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.
-
This Year and Year to Date are different in the way that future dates are handled. 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.
-
All date filters are based on the Gregorian calendar.
-
Fiscal years and fiscal quarters always start in January of the calendar year.
-
-
-
Filter for top or bottom numbers
To filter data, a workbook author must first save the Excel workbook with an AutoFilter applied. For more information, see the Microsoft Office Excel 2007 Help system.
-
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.
-
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.
-
-
Top and bottom values are based on the original range of cells or table column and not the filtered subset of data.
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 more about 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 storage 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 storage formats, the command that is displayed is the storage format that occurs most frequently. For example, if the column contains three values stored as number and four as text, the filter command that is displayed is Text Filters. For more information, see Microsoft Office Excel 2007 Help.
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 Microsoft Office Excel Web Access, then Microsoft Office Excel Web Access 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. You may be able to see all of the rows that are returned by that filter in one scrolling region by increasing the number of rows to display in Excel Web Access. For more information, see Excel Web Access Web Part custom properties.
Nonprinting characters can prevent filtering Excel Web Access 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. For more information, see Microsoft Office Excel 2007 Help.
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. Change the filter so that only one calculated member is selected, or if you are selecting two or more items, remove the 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