Microsoft Office XP Step by Step

( 1 )

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 ...
See more details below
Paperback (REV)
$40.18
BN.com price
(Save 10%)$44.99 List Price
Other sellers (Paperback)
  • All (46) from $1.99   
  • New (10) from $1.99   
  • Used (36) from $1.99   
Sending request ...

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

Quickly teach yourself how to use the complete suite of Office XP applications. This easy-to-follow book features step-by-step lessons in full color, plus practice files on CD-ROM. Take just the lessons you need, or work from cover to cover.

Read More Show Less

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)
Read More Show Less

Product Details

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

Meet the Author

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.
Read More Show Less

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....

Read More Show Less

Table of Contents

What's New in Microsoft Office XP

Getting Help

Using the Book's CD-ROM

Conventions and Features

Part I: Microsoft Word

Chapter 1: Creating a Document

Chapter 2: Changing the Look of Your Document

Chapter 3: Presenting Information in Tables and Columns

Chapter 4: Proofreading and Printing a Document

Part II: Microsoft Excel

Chapter 5: Getting to Know Excel

Chapter 6: Making Changes to Your Workbook

Chapter 7: Performing Calculations on Data

Chapter 8: Focusing on Specific Data Using Filters

Part III: Microsoft Access

Chapter 9: Getting to Know Access

Chapter 10: Creating a New Database

Chapter 11: Simplifying Data Entry with Forms

Chapter 12: Locating Specific Information

Part IV: Microsoft PowerPoint

Chapter 13: Creating a Presentation

Chapter 14: Working with a Presentation

Chapter 15: Adding and Modifying Slide Text

Chapter 16: Printing a Presentation

Part V: Microsoft FrontPage

Chapter 17: Understanding How FrontPage Works

Chapter 18: Creating a Web Site to Promote Yourself or Your Company

Chapter 19: Presenting Information in Lists and Tables

Chapter 20: Enhancing Your Web Site with Graphics

Chapter 21: Publishing Your Web Site

Part VI: Microsoft Publisher

Chapter 22: Creating a Document

Chapter 23: Printing Your Documents

Part VII: Microsoft Outlook

Chapter 24: Working with E-Mail

Chapter 25: Managing E-Mail Messages

Chapter 26: Customizing and Organizing E-Mail Messages

Chapter 27: Managing Your Calendar

Chapter 28: Scheduling and Managing Meetings

Quick Reference

Glossary

appendix

Read More Show Less

First Chapter

Chapter 8.|Focusing on Specific Data Using Filters
  • Limiting the Data That Appears on the Screen
  • Performing Calculations on Filtered Data
  • Defining a Valid Set of Values for a Range of Cells

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.

(Image Unavalilable)


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.

(Image Unavalilable)

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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

(Image Unavalilable)

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.

(Image Unavalilable)

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.

(Image Unavalilable)

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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

    (Image Unavalilable)

  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.

(Image Unavalilable)

You can use the Data Validation dialog box to define the type of data that Excel should allow in the cell and then, depending on the data type you choose, to set the conditions data must meet to be accepted in the cell. In the following graphic, Excel knows to look for a whole number value between 1000 and 2000.

(Image Unavalilable)

Setting accurate validation rules can help you and your colleagues avoid entering a customer’s name in the cell designated to hold their phone number or setting a credit limit above a certain level. To require a user to enter a numeric value in a cell, display the Settings page of the Data Validation dialog box, in the Allow box, click the down arrow, and depending on your needs, choose either Whole Number or Decimal from the list that appears.

Of course, it’s frustrating if you want to enter data into a cell and, when a message box appears telling you the data you tried to enter isn’t acceptable, you aren’t given the rules you need to follow. Excel lets you create messages that tell the user what values are expected before the data is entered and then, if the conditions aren’t met, reiterate the conditions in a custom error message.

You can turn off data validation in a cell by displaying the Settings page of the Data Validation dialog box and clicking the Clear All button in the lower left corner of the dialog box.

In this exercise, you create a data validation rule limiting the credit line of The Garden Company customers to $2,500, add an input message mentioning the limitation, and then create an error message should someone enter a value greater than $2,500. After you’ve created your rule and messages, you test them.

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

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

  5. Click cell J4.
  6. On the Data menu, click Validation.
  7. The Data Validation dialog box appears with the Settings tab page in front.

    (Image Unavalilable)

  8. In the Allow box, click the down arrow and, from the list that appears, click Decimal.
  9. Boxes labeled Minimum and Maximum appear below the Data box.

    (Image Unavalilable)

  10. In the Data box, click the down arrow and, from the list that appears, click less than or equal to.
  11. The Minimum box disappears.

  12. In the Maximum box, type 2500.
  13. Clear the Ignore blank check box.
  14. Click the Input Message tab.
  15. The Input Message tab page appears.

    (Image Unavalilable)

  16. In the Title box, type Enter Limit.
  17. In the Input Message box, type Please enter the customer’s credit limit, omitting the dollar sign.
  18. Click the Error Alert tab.
  19. The Error Alert tab page appears.

    (Image Unavalilable)

  20. In the Style box, click the down arrow and, from the list that appears, choose Warning.
  21. The icon that will appear on your message box changes to the Warning icon.

  22. In the Title box, type Error.
  23. Click OK.

  24. TIP:
    Leaving the Error Message box blank causes Excel to use its default message: The value you entered is not valid. A user has restricted values that can be entered into this cell.

    A ScreenTip with the title Enter Limit and the text Please enter the customer’s credit limit, omitting the dollar sign appears near cell J4.

  25. Type 2501, and press Enter.
  26. A warning box with the title Error and default text appears.

    (Image Unavalilable)

  27. Click Yes.
  28. The warning box disappears.


    IMPORTANT:
    Clicking No lets you edit the bad value, while clicking Cancel deletes the entry.
  29. Click cell J4.
  30. Cell J4 becomes the active cell, and the ScreenTip reappears.

  31. Type 2500, and press Enter.
  32. Excel accepts your input.

  33. Click cell J4.
  34. On the Data menu, click Validation.
  35. The Data Validation dialog box appears.

  36. Click the Settings tab.
  37. The Settings tab page appears.

    (Image Unavalilable)

  38. In the Allow box, click the down arrow and, from the list that appears, click Whole number.
  39. Click OK.
  40. In cell J4, type 2499.95 and press Enter.
  41. A warning box with the title Error and default text appears.

    (Image Unavalilable)

  42. Click No.
  43. The warning box disappears, cell J4 becomes the active cell, and the ScreenTip reappears.

  44. Type 2500, and press Enter.
  45. Excel accepts your input.
  46. On the Standard toolbar, click the Save button.
  47. Click the Close button.
  48. Validate.xls closes.

Read More Show Less

Customer Reviews

Average Rating 5
( 1 )
Rating Distribution

5 Star

(1)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted August 8, 2009

    Skill Building Book

    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.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)