Limiting Data That Appears on Your Screen


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.

Important

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.

Note

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.


1.

On the ByRoute worksheet, click any cell in the cell range B2:F27.

2.

On the Home tab, in the Editing group, click Sort & Filter and then click Filter.

A filter arrow appears in each column's header cell.

3.

Click the Date column filter arrow and then, from the list of options that appears, clear the March check box.

4.

Click OK.

Excel 2007 hides all rows that contain a date from the month of March.

5.

Click the Center column filter arrow and then, from the list of options that appears, clear the Select All check box.

Excel 2007 clears all the check boxes in the list.

6.

Select the Midwest check box and then click OK.

Excel 2007 displays only those exceptions that occurred in the Midwest distribution center during the month of April.

7.

On the Home tab, in the Editing group, click Sort & Filter and then click Clear.

Excel 2007 clears all active filters but leaves the filter arrows in place.

8.

Click the MarchDailyCount sheet tab.

The MarchDailyCount worksheet appears.

9.

Click any cell in the data table.

10.

Click the Exceptions column filter arrow, point to Number Filters, and then click Top 10.

The Top 10 AutoFilter dialog box appears.

11.

In the middle field, type 5.

12.

Click OK.

Excel 2007 displays the table rows that contain the five highest values in the Exceptions column.

13.

Click the Exceptions column filter arrow, and then click Clear Filter from "Exceptions".

Excel 2007 removes the filter.

14.

Click the Date column filter arrow, point to Date Filters, and then click Custom Filter.

The Custom AutoFilter dialog box appears.

15.

Click the upper-left field down arrow and then click is after or equal to.

16.

Click the upper-right field down arrow and then click 3/8/2007.

17.

Click the lower-left field down arrow and then click is before or equal to.

18.

Click the upper-right field down arrow and then click 3/14/2007.

19.

Click OK.

Because you left the And option button selected, Excel 2007 displays all table rows that contain a date from 3/8/2007 to 3/14/2007, inclusive.

20.

On the Quick Access Toolbar, click the Undo button to remove your filter.

Excel 2007 restores the table to its unfiltered state.

CLOSE the PackageExceptions workbook.





MicrosoftR Office ExcelR 2007 Step by Step
MicrosoftВ® Office ExcelВ® 2007 Step by Step (Step By Step (Microsoft))
ISBN: 073562304X
EAN: 2147483647
Year: 2004
Pages: 143
Authors: Curtis Frye

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net