Excel 2007 spreadsheets can hold as much data as you need them to, but you might not want to work with all the data in a worksheet at the same time. For example, you might want to see the sales figures for your company during the first third, second third, and final third of a month. You can limit the data shown on a worksheet by creating a filter, which is a rule that selects rows to be shown in a worksheet.
To create a filter, you click the cell in the group you want to filter and then, on the Home tab, in the Editing group, click Sort & Filter and then click Filter. When you do, Excel 2007 displays a filter arrow at the right edge of the top cell in each column of the selected range. The arrow indicates that the Excel 2007 AutoFilter capability is active.
When you turn on filtering, Excel 2007 treats the cells in the active cell's column as a range. To ensure that the filtering works properly, you should always add a label to the column you want to filter.
Clicking the down arrow displays a list of filtering options (greatly expanded over Excel 2003) and a list of the unique values in the column. The first few items in the list are sorting options, followed by the Clear Filter item. The next option that appears on the list depends on the type of data in the column. For example, if the column contains a set of dates, the item will be Date Filters. Clicking the item displays a list of options specific to that data type.
When a column contains several types of data, the filter option becomes Number Filters.
When you click a filtering option, Excel 2007 displays a dialog box that enables you to define the filter's criteria. As an example, you could create a filter that displays only dates after 3/31/2007.
If you want to see the highest or lowest values in a data column, you can create a Top 10 filter. Choosing the Top 10 option from the list doesn't just limit the display to the top 10 values. Instead, it opens the Top 10 AutoFilter dialog box. From within this dialog box, you can choose whether to show values from the top or bottom of the list, define the number of items you want to see, and choose whether the number in the middle box indicates the number of items or the percentage of items to be shown when the filter is applied. Using the Top 10 AutoFilter dialog box, you can find your top 10 salespeople or identify the top 5 percent of your customers.
When you choose Custom Filter from the AutoFilter list, you can define a rule that Excel 2007 uses to decide which rows to show after the filter is applied. For instance, you can create a rule that determines only days with package volumes of greater than 100,000 should be shown in your worksheet. With those results in front of you, you might be able to determine whether the weather or another factor resulted in slower business on those days.
Excel 2007 indicates that a column has a filter applied by changing the column's filter arrow to include an icon that looks like a funnel. After you finish examining your data by using a filter, you can remove the filter by clicking the column's filter arrow and then clicking Clear Filter. To turn off filtering, display the Data tab of the user interface and then, in the Sort & Filter group, click Filter.
In this exercise, you will filter a data list using a series of AutoFilter items, create a filter showing the five days with the highest delivery exception counts in a month, and create a custom filter.
USE the PackageExceptions workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Focusing folder.
BE SURE TO start Excel 2007 before beginning this exercise.
OPEN the PackageExceptions workbook.
CLOSE the PackageExceptions workbook.