You can use AutoFilters in Excel to select the items you want, and display data just for those items. In Excel 2010, AutoFilter contains a Search box where you can enter text to help you filter the items you want. This is helpful when you want to display only necessary information from a worksheet with a large amount of data.
Here, let's display only the expenses for "Meals" and then only for "Lunch" from the household expense data used in "Using PivotTables for Managing Household Expenses." Finally, let's organize "Category" in alphabetic order and change the order according to the amount in "Expense."
This article consists of the following 2 pages.
- Using AutoFilters to Display Only Necessary Data (This page)
- Sorting Data
|
Using AutoFilters to Display Only Necessary Data
Here, let's use the AutoFilter function to select only "Meals" from the "Category" column in the household expense worksheet.
- Click Sheet1 to display the household expense data.
- From the "Category" column, click
.
The items included in the "Category” column are displayed.
- Click (Select All) to clear the check box.
All check boxes in the "Category" column are cleared.
- Now, select the "Meals" check box, and click OK.
Only the expenses for "Meals" are displayed.
Top of Page
Using the AutoFilter Search Feature to Filter Data
Let's use the search feature in AutoFilter to narrow down the items in the "Content" column to "Lunch" so that only lunch expenses are displayed.
- Click
in the "Category" column, and then click Clear Filter From "Category."
The filter is cancelled and all items are displayed.
- From the "Content" column, click.
The items included in the "Content" column are displayed.
- Enter "L" into the Search box.
The list is narrowed down to items containing the letter "L."
- "Clear the "Clothes" check box, and click OK.
Only the expenses for "Lunch" are displayed.
Top of Page
Specifying Search Conditions
You can set the search condition so that only the data you want to view is displayed.
Here, let's filter the "Expense" column so that only items over $100 are displayed.
- Click from the "Expense" column, and then move your mouse pointer to Number Filters and click Greater Than.
The Custom AutoFilter dialog appears.
- Enter "100" in the first condition box, and then click OK.
Only items over $100 are displayed.
|
Tip: What other search conditions are available besides Greater Than? |
 |
In addition to Greater Than, you can also use the following search conditions.
- Equals: Use this to display data that is the same as the specified value.
- Does Not Equal: Use this to display data other than the specified value.
- Greater Than Or Equal To: Use this to display data that is equal to or greater than the specified value.
- Less Than: Use this to display data that is less than the specified value.
- Less Than Or Equal To: Use this to display data that is less than or equal to the specified value.
- Between: Use this to display data that is between the two specified values.
- Top 10: Use this to display the top ten or bottom ten values.
- Above Average: Use this to display data that is greater than the average.
- Below Average: Use this to display data that is less than the average.
* You can also change the search conditions from the Custom AutoFilter dialog box.
* You can specify multiple search conditions in a row to filter down the data even more.
|
Top of Page
“Sorting Data” is next.
