Focusing on Specific Data Using Filters

Core Training for Microsoft Office XP

Limiting the Data That Appears on the Screen

1	To create a filter so that of rows 6 to 36, only rows containing the five largest values in cells P5 to P36 will display, click the Data menu, point to Filter, and then click AutoFilter.
2	In cell P5, click the down arrow and, from the list that appears, click (Top 10...).
3	Press the DELETE key, type 5, and click OK.
4	To remove the filter and display all rows again, click the Data menu, point to Filter, and then click AutoFilter.
5	To scroll left in the worksheet, click once to the left of the scroll box in the horizontal scroll bar.
6	To create a filter so that of rows 6 through 36, only the rows with Mon in column B will display, click cell B5, click the Data menu, point to Filter, and click AutoFilter.
7	In cell B5, click the down arrow and, from the list of unique column values that appears, click Mon.
8	To display all rows again, click the Data menu, point to Filter, and then click AutoFilter.
9	To scroll right again, click once to the right of the scroll box in the horizontal scroll bar.
10	To create a filter so that of rows 6 through 36, only the rows with totals in column P of at least 3000 will be displayed, click cell P5, click the Data menu, point to Filter, and click AutoFilter.
11	In cell P5, click the down arrow and then, from the list that appears, click (Custom…).
12	In the upper-left box, click the down arrow and, from the list that appears, click is greater than or equal to.
13	Click in the upper-right box, type 3000, and then click OK.
14	To change the filter so that of rows 6 through 36, only the row containing 2236 in column P will be displayed, click the down arrow in cell P5, and from the list of unique column values that appears, click 2236.
15	To remove the filter and view all rows again, click the Data menu again, point to Filter, and click AutoFilter.
16	To ask Excel to pick rows at random from rows 5 through 36 in order to perform an audit on data from 16% of the days listed, click cell Q5, type Audit, and press ENTER.
17	In cell Q6, type =RAND()<17% and then press ENTER.
18	Click cell Q6, point to the Fill handle at the lower-right corner of the cell, hold down the left mouse button, drag the Fill handle down to cell Q36, and release the mouse button.
19	To scroll back to the upper-left corner of the worksheet, while holding down the CONTROL key on your keyboard, and press the HOME key.
20	To create a filter that allows only the rows containing the first occurrence of each value in cells B5 to B36 to display, point to cell B5, hold down the left mouse button, drag to cell B36, and release the mouse button.
21	Click the Data menu, point to Filter, and then click Advanced Filter.
22	At the bottom of the Advanced Filter dialog box, click the Unique records only check box and then click OK.
23	To view all rows again, click the Data menu, point to Filter, and then click Show All.

Performing Calculations on Filtered Data

1	To create a filter so that of rows 5 to 36, only rows containing the ten highest values in column P will be displayed, click the Data menu, point to Filter, and then click AutoFilter.
2	In cell P5, click the down arrow button and then, from the list that appears, click (Top 10...).
3	To accept the default setting in the Top 10 AutoFilter dialog box (to show the top 10 values in the selected cells), click OK.
4	To view a subtotal for the data now displayed in cells P6 to P27, point to cell P6, hold down the left mouse button, drag to cell P27, and release the mouse button.
5	To insert the subtotal of the selected cells into cell P37, click cell P37, and then, on the Standard toolbar, click the AutoSum button.
6	Press the ENTER key on your keyboard.
7	To edit the formula in cell P38 so that it does not include P37 in its total, in the Formula bar, click between the number 37 and the closing parenthesis, press the BACKSPACE key on your keyboard, type 6, and press ENTER.
8	To change the formula in cell P37 so that it calculates the average of the top 10 values in cells P6 through P36 (instead of the sum), click cell P37.
9	In the Formula bar, click between the number 9 and the comma that follows it.
10	Nine is the function number for calculating a sum in a SUBTOTAL formula. The function number for calculating an average is 1, so press the BACKSPACE key, type 1, and then press the ENTER key.

Defining a Valid Set of Values

1	To create a validation rule for entering a customer credit limit into cell J4, click the Data menu and then click Validation.
2	In the Data Validation dialog box, on the Settings tab, in the Allow box, click the down arrow and, from the list that appears, click Decimal.
3	In the Data box, click the down arrow and, from the list that appears, click less than or equal to.
4	Click in the Maximum box, and type 2500.
5	Click the Ignore blank check box in order to clear the check mark.
6	Click the Input Message tab.
7	On the Input Message tab, click in the Title box and type Enter Limit.
8	Click in the Input Message box, type Please enter the customer's credit limit, omitting the dollar sign.
9	To specify the appearance of the error alert, click the Error Alert tab.
10	To choose the icon that will appear on the error alert, on the Error Alert tab, in the Style box, click the down arrow and, from the list that appears, choose Warning.
11	To specify the title to appear at the top of the error alert, click in the Title box, type Error, and then click OK.
12	To test the new validation rule, type 2501, and press the ENTER key on your keyboard.
13	To edit the value in J4, click No.
14	Type 2500, and press ENTER.
15	To change the validation rule so that it only accepts whole numbers, click cell J4, click the Data menu, and click Validation.
16	In the Data Validation dialog box, click the Settings tab.
17	In the Allow box, click the down arrow, and from the list that appears, click Whole number, and then click OK.
18	To test the change, in cell J4, type 2499.95 and press ENTER.
19	In the Error Alert window, click No.
20	Type 2500, and press ENTER.