Limiting the Data That Appears on the Screen


Excel spreadsheets can hold as much data as you need them to, but you might not want to work with all of 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 in 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 use the Data menu to turn on AutoFilter . When you turn on AutoFilter, which is a built-in set of filtering capabilities, a down arrow button appears in the cell that Excel recognizes as the column s label.

Important  

When you turn on filtering, Excel 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 values and options. The first few items in the list are filtering options, such as whether you want to display the top 10 values in the column, create a custom filter, or display all values in the column (that is, remove the filter). The rest of the items in the list are the unique values in the column ”clicking one of those values displays the row or rows containing that value.

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 five percent of your customers.

When you choose Custom from the AutoFilter list, you can define a rule that Excel uses to decide which rows to show after the filter is applied. For instance, you can create a rule that only days with total sales of less than $2,500 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.

Two related things you can do in Excel are to choose rows at random from a list and to display the unique values in a column in the worksheet (not in the down arrow s list, which you can t normally work with). Generating a list of unique values in a column can give you important information, such as from which states you have customers or which categories of products sold in an hour .

Selecting rows randomly is useful for selecting customers to receive a special offer, deciding which days of the month to audit, or picking prize winners at an employee party. To choose rows, you can use the RAND function, which generates a random value between 0 and 1 and compares it with a test value included in the statement. A statement that returns a TRUE value 30 percent of the time would be RAND() < =30%; that is, whenever the random value was between 0 and .3, the result would be TRUE. You could use this statement to select each row in a list with a probability of 30 percent.

In this exercise, you create a filter to show the top five sales days in January, show sales figures for Mondays during the same month, display the days with sales of at least $3,000, pick random days from the month to audit, and then generate a list of unique values in one of the worksheet s columns .

USE the  Filter.xls document in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\UsingFilters folder, and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the  Filter.xls document.

  1. If necessary, click the January sheet tab.

  2. Click cell P5.

  3. On the Data menu, point to Filter , and then click AutoFilter .

    A down arrow appears in cell P5.

  4. In cell P5, click the down arrow and, from the list that appears, click (Top 10 ) .

    The Top 10 AutoFilter dialog box appears.

  5. Click in the middle box, delete 10 , type 5 , and click OK .

    Only the rows containing the five largest values in column P are shown.

    click to expand
  6. On the Data menu, point to Filter , and then click AutoFilter .

    The filtered rows reappear.

  7. Click cell B5.

  8. On the Data menu, point to Filter , and then click AutoFilter .

    A down arrow appears in cell B5.

  9. In cell B5, click the down arrow and, from the list of unique column values that appears, click Mon .

    Only rows with Mon in column B are shown in the worksheet.

  10. On the Data menu, point to Filter , and then click AutoFilter .

    The filtered rows reappear.

  11. Click cell P5, and then, on the Data menu, point to Filter , and then click AutoFilter .

    A down arrow appears in cell P5.

  12. In cell P5, click the down arrow and then, from the list that appears, click (Custom ) .

    The Custom AutoFilter dialog box appears.

    click to expand
  13. In the upper-left box, click the down arrow and, from the list that appears, click is greater than or equal to .

  14. In the upper-right box, type 3000 and then click OK .

    Only rows with totals of at least 3000 are shown in the worksheet.

    click to expand
  15. On the Data menu, point to Filter , and then click AutoFilter .

    The filtered rows reappear.

  16. On the Data menu, point to Filter , and then click AutoFilter .

    A down arrow appears in cell P5.

  17. In cell P5, click the down arrow and then, from the list of unique column values that appears, click 2236 .

    All rows except the row containing 2236 in column P disappear.

  18. On the Data menu, point to Filter , and then click AutoFilter .

    The filtered rows reappear.

  19. In cell Q5, type Audit .

  20. In cell Q6, type =RAND() < 17%.

    If the result of the RAND function is less than 17% , cell Q6 will display TRUE; otherwise , cell Q6 will display FALSE .

  21. Drag the AutoFill handle from cell Q6 to cell Q36.

    TRUE and FALSE values appear in the cells from Q6 to Q36 with a frequencyof approximately 16 percent and 84 percent, respectively.

    click to expand
  22. On the Data menu, point to Filter , and then click Advanced Filter .

    The Advanced Filter dialog box appears.

  23. Clear the List Range box, and then click cell B5 and drag to cell B36.

    $B$5:$B$36 appears in the List Range box.

  24. Select the Unique records only check box, and then click OK .

    Rows with the first occurrence of a value are displayed in the worksheet.

    click to expand
  25. On the Data menu, point to Filter , and then click Show All .

    The filtered rows reappear.

  26. On the Standard toolbar, click the Save button.

    Excel saves your changes.

CLOSE the  Filter.xls document.




Microsoft Office 2003 Step by Step
MicrosoftВ® Office ExcelВ® 2003 Step by Step (Step By Step (Microsoft))
ISBN: 0735615187
EAN: 2147483647
Year: 2005
Pages: 350
Authors: Curtis Frye

Similar book on Amazon

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