Page 621 - Excel 2013 All-in-One For Dummies

Filtering Data 603
unwanted records and leave behind only the records that you want to see. Much of the time, the capabilities of the AutoFilter feature are all that you need, especially when your main concern is simply displaying just the infor- mation of interest in the data list.
You will encounter situations, however, in which the AutoFilter feature is not sufficient, and you must do what Microsoft refers to as advanced filtering in your data list. You need to use advanced filtering to filter the data list when you use computed criteria (such as when you want to see all the records where the entry in the Sales column is twice the amount in the Owed column) and when you need to save a copy of the filtered data in a different part of the worksheet (Excel’s version of querying the data in a data list).
Using AutoFilter
Excel’s AutoFilter feature makes filtering out unwanted data in a data list as easy as clicking the AutoFilter button on the column on which you want to filter the data and then choosing the appropriate filtering criteria from that column’s drop-down menu.
If you open a worksheet with a data list and you don’t find AutoFilter buttons attached to each of the field names at the top of the list, you can display them simply by positioning the cell pointer in one of the cells with the field names and then clicking the Filter command button on the Ribbon’s Data tab or pressing Ctrl+Shift+L or Alt+AT.
The filter options on a column’s AutoFilter drop-down menu depend on the type of entries in the field. On the drop-down menu in a column that contains only date entries, the menu contains a Date Filters option to which a submenu of the actual filters is attached. On the drop-down menu in a column that contains only numeric entries (besides dates) or a mixture of dates with other types of numeric entries, the menu contains a Number Filters option. On the drop-down menu in a column that contains only text entries or a mixture of text, date, and other numeric entries, the menu contains a Text Filters option.
Doing basic filtering by selecting specific field entries
In addition to the Date Filters, Text Filters, or Number Filters options (depending on the type of field), the AutoFilter drop-down menu for each field in the data list contains a list box with a complete listing of all entries made in that column, each with its own check box. At the most basic level, you can filter the data list by clearing the check box for all the entries whose records you don’t want to see in the list.
This kind of basic filtering works best in fields such as City, State, or Country, which contain many duplicates, so you can see a subset of the data list that contains only the cities, states, or countries you want to work with at the time.
Book VI Chapter 2
Filtering and Querying a Data List