Microsoft Office XP Step by Step

Microsoft Office XP Step by Step

5.0 1
by Curtis Frye D., Perspection Inc., Online Training Solutions, Kristen Crupi
     
 

Experience learning made easy—and quickly teach yourself how to use the complete suite of Microsoft® Office XP applications. With STEP BY STEP, you can take just the lessons you need or work from cover to cover. Either way, you drive the instruction—building and practicing the skills you need, just when you need them!

  • WORD: Create and

Overview

Experience learning made easy—and quickly teach yourself how to use the complete suite of Microsoft® Office XP applications. With STEP BY STEP, you can take just the lessons you need or work from cover to cover. Either way, you drive the instruction—building and practicing the skills you need, just when you need them!

  • WORD: Create and publish great-looking documents quickly and easily
  • EXCEL: Develop easy-to-use spreadsheets and perform calculations
  • ACCESS: Build your own databases for better decision making
  • POWERPOINT®: Produce and deliver compelling presentations
  • FRONTPAGE®: Get yourself—or your company—on the Web fast by constructing your own site
  • PUBLISHER: Create professional-quality marketing materials—without being a designer
  • OUTLOOK®: Manage your e-mail communications and calendar

Editorial Reviews

Booknews
An overview listing of Microsoft Office XP suite's new features referenced to color-coded chapters launches this self-paced guide to using the latest versions of Word, Excel, Access, PowerPoint, FrontPage, Publisher, and Outlook for e-mail. Includes highlighted general and troubleshooting tips, a quick reference guide, and a glossary of terms. The companion CD-ROM, requiring a minimum of Windows 98, contains practice files. A supporting Web site is available. Lacks references. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Product Details

ISBN-13:
9780735612945
Publisher:
Microsoft Press
Publication date:
07/28/2001
Series:
Step by Step Series
Edition description:
REV
Pages:
784
Product dimensions:
7.42(w) x 9.22(h) x 1.78(d)

Read an Excerpt

Chapter 8: Focusing on Specific Data Using Filters

After completing this chapter, you will be able to:

  • Limit the data that appears on the screen.
  • Perform calculations on filtered data.
  • Define a valid set of values for a range of cells.

An important aspect of working with large amounts of data is the ability to zero in on the most important data in a worksheet, whether that data represents the best ten days of sales in a month or slow-selling product lines that you may need to reevaluate. In Excel, you have a number of powerful, flexible tools with which you can limit the data displayed in your worksheet. Once your worksheet displays the subset of your data that you need to make a decision, you can perform calculations on that data. You can discover what percentage of monthly sales were made up by the ten best days in the month, find your total sales for particular days of the week, or locate the slowest business day of the month.

Just as you can limit the data displayed by your worksheets, you can limit the data entered into them as well. Setting rules for data entered into cells lets you catch many of the most common data entry errors, such as entering values that are too small or too large, or attempting to enter a word in a cell that requires a number.

In this chapter, you'll learn how to limit the data that appears in your worksheets, perform calculations on the remaining data, and limit the data that can be entered into specific cells.

This chapter uses the practice files Filter, Calculations, and Validate that you installed from this book's CD-ROM. For details about installing the practice files, see "Using the Book's CD-ROM" at the beginning of this book.

Limiting the Data That Appears on the Screen

Excel spreadsheets can hold as much data as you need them to, but you may 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 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 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.


Click to view graphic


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 ten 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 on 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 ten values. Instead, it opens the Top 10 AutoFilter dialog box.


Click to view graphic

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 ten 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 choose rows at random from a list and 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 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%; 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.

  1. On the Standard toolbar, click the Open button.
  2. The Open dialog box appears.

  3. Navigate to the Office XP SBS\Excel\Chap08 folder, and double-click the Filter.xls file.
  4. Filter.xls opens.

  5. If necessary, click the January sheet tab.
  6. Click cell P5.
  7. On the Data menu, point to Filter, and then click AutoFilter.
  8. A down arrow appears in cell P5.

  9. In cell P5, click the down arrow and, from the list that appears, click (Top 10...).
  10. The Top 10 AutoFilter dialog box appears.


    Click to view graphic

  11. Click in the middle box, delete 10, type 5, and click OK.
  12. Only the rows containing the five largest values in column P are shown.


    Click to view graphic

  13. On the Data menu, point to Filter, and then click AutoFilter.
  14. The filtered rows reappear.

  15. Click cell B5.
  16. On the Data menu, point to Filter, and then click AutoFilter.
  17. A down arrow appears in cell B5.

  18. In cell B5, click the down arrow and, from the list of unique column values that appears, click Mon.
  19. Only rows with Mon in column B are shown in the worksheet.


    Click to view graphic

  20. On the Data menu, point to Filter, and then click AutoFilter.
  21. The filtered rows reappear.

  22. Click cell P5, and then, on the Data menu, point to Filter, and then click AutoFilter.
  23. A down arrow appears in cell P5.

  24. In cell P5, click the down arrow and then, from the list that appears, click (Custom…).
  25. The Custom AutoFilter dialog box appears.


    Click to view graphic

  26. In the upper left box, click the down arrow and, from the list that appears, click is greater than or equal to.
  27. In the upper right box, type 3000 and then click OK.
  28. Only rows with totals of at least 3000 are shown in the worksheet.


    Click to view graphic

  29. On the Data menu, point to Filter, and then click AutoFilter.
  30. The filtered rows reappear.

  31. On the Data menu, point to Filter, and then click AutoFilter.
  32. A down arrow appears in cell P5.

  33. In cell P5, click the down arrow and then, from the list of unique column values that appears, click 2236.
  34. All rows except the row containing 2236 in column P disappear.


    Click to view graphic

  35. On the Data menu, point to Filter, and then click AutoFilter.
  36. The filtered rows reappear.

  37. In cell Q5, type Audit.
  38. In cell Q6, type =RAND()<17%.
  39. If the result of the RAND function is less than 17%, cell Q6 will display TRUE; otherwise, cell Q6 will display FALSE.

  40. Drag the AutoFill handle from cell Q6 to cell Q36.
  41. TRUE and FALSE values appear in the cells from Q6 to Q36 with a frequency of 16 percent and 84 percent, respectively.


    Click to view graphic

  42. Click cell B5 and drag to cell B36.
  43. Cells B5 through B36 are highlighted.

  44. On the Data menu, point to Filter, and then click Advanced Filter.
  45. The Advanced Filter dialog box appears.


    Click to view graphic

  46. Select the Unique records only check box, and then click OK.
  47. Rows with the first occurrence of a value are displayed in the worksheet.


    Click to view graphic

  48. On the Data menu, point to Filter, and then click Show All.
  49. The filtered rows reappear.

  50. On the Standard toolbar, click the Save button.
  51. Excel saves your changes.

  52. Click the Close button.
  53. Filter.xls closes.

Performing Calculations on Filtered Data

When you filter your worksheet, you limit the data that appears. The ability to focus on the data that's most important to your current needs is important, but there are a few limitations. One limitation is that any formulas you have created don't change their calculations, even if some of the rows used in the formula are hidden by the filter. The following graphic shows a filtered worksheet in which the total at the bottom of the filtered column hasn't changed.


Click to view graphic

There are two ways you can find the total of a group of filtered cells. The first method is to use AutoCalculate. To use AutoCalculate, you select the cells you want to find the total for. When you do, the total for the cells appears on the status bar, at the lower edge of the Excel window.


Click to view graphic

When you use AutoCalculate, you aren't limited to finding the sum of the selected cells. To display the other functions you can use, you right-click the AutoCalculate pane and select the function you want from the shortcut menu that appears.


Click to view graphic

AutoCalculate is great for finding a quick total or average for filtered cells, but it doesn't make the result available in the worksheet. To make the value available in your worksheet, you can create a SUBTOTAL function. As with AutoCalculate, you can choose the type of calculation the function performs.

In this exercise, you use AutoCalculate to find the total of a group of cells in a filtered worksheet, create a SUBTOTAL function to make the same value available in the worksheet, and then edit the SUBTOTAL function so that it calculates an average instead of a sum.

  1. On the Standard toolbar, click the Open button.
  2. The Open dialog box appears.

  3. Double-click the Calculations.xls file.
  4. Calculations.xls opens.

  5. If necessary, click the January sheet tab.
  6. Click cell P5.
  7. On the Data menu, point to Filter, and then click AutoFilter.
  8. A down arrow button appears in cell P5.

  9. In cell P5, click the down arrow button and then, from the list that appears, click (Top 10...).
  10. The Top 10 AutoFilter dialog box appears.


    Click to view graphic

  11. Click OK.

  12. TIP:
    Clicking OK here accepts the default setting of the Top 10 AutoFilter dialog box, which is to show the top ten values in the selected cells.

    The Top 10 AutoFilter dialog box disappears, and the rows with the ten highest values in column P are displayed.

    Click to view graphic

  13. Click cell P6 and drag to cell P27.
  14. The cells are selected, and on the status bar, in the middle of the Excel window, SUM=36781 appears in the AutoCalculate pane.


    Click to view graphic

  15. Click cell P37, and then, on the Standard toolbar, click the AutoSum button.
  16. The formula =SUBTOTAL(9,P6:P36) appears on the formula bar.

  17. Press Enter.
  18. The value 36781 appears in cell P37. The value in cell P38 also changes to 134706, but that calculation includes the subtotal of the filtered cells in the column.

  19. Click cell P37, and then, on the formula bar, edit the formula so that it reads =SUBTOTAL(1,P6:P36) and then press Enter.
  20. By changing the 9 to a 1 in the SUBTOTAL function, the function now calculates an average instead of a sum. The average of the top ten values in cells P6 through P36, 3678.1, appears in cell P37. The value in cell P38 also changes to 101603.1, but that calculation includes the average of the filtered cells in the column.


    Click to view graphic

  21. If necessary, click cell P37 and then press Del.
  22. Excel deletes the SUBTOTAL formula from cell P37, and the total in cell P38 changes to 97925.

  23. On the Standard toolbar, click the Save button.
  24. Excel saves your changes.

  25. Click the Close button.
  26. Calculations.xls closes.

Defining a Valid Set of Values for a Range of Cells

Part of creating efficient and easy-to-use worksheets is to do what you can to ensure that the data entered into your worksheets is as accurate as possible. While it isn't possible to catch every typographical or transcription error, you can set up a validation rule to make sure the data entered into a cell meets certain standards.

To create a validation rule, you open the Data Validation dialog box....

Meet the Author

Perspection, Inc. is a technology training company committed to providing information to help people communicate, make decisions, and solve problems.

Online Training Solutions, Inc. (OTSI) is a traditional and electronic publishing company with years of experience in the creation, production, and delivery of computer software training. OTSI is also the author of several Step by Step books about Office System applications.

Curtis Frye is the author of numerous books on Excel and other Office products, most recently the Excel 2007 Pocket Guide. He graduated from Syracuse in 1990 with a degree in political science, and then moved to Washington, DC, where he worked as a defense trade analyst for four years and as the director of sales and marketing for an ISP for one year. He moved to Portland, Oregon, in 1995 to launch his freelance writing career. When Curt's not writing, and often while he is writing, he is a keynote speaker, mentalist, and professional improvisational comedian.

With more than five years of experience as a technical writer and information architect, Kristen Crupi brings her expertise in user assistance to Microsoft Outlook Version 2002 Step by Step. Kristen served as a technical resource on the Windows 95 Resource Kit team and contributed to the Microsoft Excel 2000 Step by Step courseware. Her background includes extensive online help and training development, and most recently, information architecture for We-based applications.

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >

Microsoft Office XP Step by Step 5 out of 5 based on 0 ratings. 1 reviews.
Anonymous More than 1 year ago
I purchased this book to build up my skills to return to the workforce. The book is very informative and has opened up on how to work the software. I had a beginner knowledge now I will say that I at least have a high intermediated understanding of the software.