Use AutoFilter or built-in comparison operators like "greater than" and "top 10" in Excel to show the data you want and hide the rest. Once you filter data in a range of cells or table, you can either reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.
Use filters to temporarily hide some of the data in a table, so you can focus on the data you want to see.
When you put your data in a table, filter controls are automatically added to the table headers.
Excel Training: Filter data in a table Guidelines and examples for sorting and filtering data by color Filter data in a PivotTable Filter by using advanced criteria Remove a filter
Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it. 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.
Note: When you use the Find dialog box to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. To search all the data, clear all filters.
The two types of filters Using AutoFilter, you can create two types of filters: by a list value or by criteria. Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by a list of numbers, or a criteria, but not by both; you can filter by icon or by a custom filter, but not by both. Reapplying a filter To determine if a filter is applied, note the icon in the column heading:
When you reapply a filter, different results appear for the following reasons:
Do not mix data types For best results, do not mix data types, 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 data types, the command that is displayed is the data type that occurs the most. For example, if the column contains three values stored as number and four as text, the Text Filters command is displayed . When you put your data in a table, filtering controls are added to the table headers automatically.
If you don't want to format your data as a table, you can also apply filters to a range of data.
You can either apply a general Filter option or a custom filter specific to the data type. For example, when filtering numbers, you'll see Number Filters, for dates you'll see Date Filters, and for text you'll see Text Filters. The general filter option lets you select the data you want to see from a list of existing data like this:
Number Filters lets you apply a custom filter:
In this example, if you want to see the regions that had sales below $6,000 in March, you can apply a custom filter:
Here's how:
You can apply custom Date Filters and Text Filters in a similar manner.
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice. Page 2
|