Succeeding In Business With Microsoft Excel 2013 1st Edition By Debra Gross – Test Bank

Learning Objectives

Students will have mastered the material in Chapter 10 when they can:

 

Level 1

• Plan an Excel application
• Control data-entry errors
• Protect worksheets and workbooks
• Document workbooks

 

Level 2

• Audit formulas for accuracy and validity
• Evaluate and trace formula errors
• Identify, analyze, and correct errors
• Set error-checking options

 

Level 3

• Plan and record macros
• Run and test macros
• Customize ribbon tabs
• Add macro buttons to a ribbon tab
• Add custom buttons to a worksheet
• View macro code using the Visual Basic Editor

EX 606: Planning an Excel Application

LECTURE NOTES

• Define the term application.
• Use Figure 10.1 to illustrate Kiola’s plan for the Income Template in the Sunglasses
• Use Figure 10.2 to discuss planning a more automated model to calculate EPS.

 

FIGURES

• Figure 10.1, Figure 10.2

 

CLASSROOM ACTIVITIES

1. Assign a Project: Ask students to complete questions 1 and 2 from the Conceptual Review.
2. Quick Quiz:
3. An Excel ____ is usually a decision support system, a tool that helps managers or other users make decisions or solve problems. (Answer: application)
4. True or False: To guide users to enter accurate data, Excel applications control the type of values users enter and where they can enter them. (Answer: True)

 

EX 609: Controlling Data-Entry Errors Using the Data Validation Tool

LECTURE NOTES

• Introduce the terms data validation, validation rule, input message, and error alert.
• Use Figure 10.3 to illustrate the Projected Income worksheet in the Sunglasses workbook.
• Show students how to validate data using the data validation tool.
• Use Figure 10.4 to illustrate the Data Validation dialog box.
• Use Table 10.1 to discuss data validation rules.
• Use Figures 10.5 through 10.7 to illustrate the creation of a validation rule.
• Use Figure 10.8 to illustrate the Input Message tab in the Data Validation dialog box.
• Use Figure 10.9 to illustrate the input message for cell B12.
• Use Table 10.2 to discuss error alert messages.
• Use Figure 10.10 to illustrate how to set an error alert.
• Introduce the Circle Invalid Data
• Use Figure 10.11 to discuss how to circle invalid data.
• Use Figure 10.12 to illustrate a worksheet with all known errors corrected.

 

FIGURES

• Figure 10.3, Figure 10.4, Figure 10.5, Figure 10.6, Figure 10.7, Figure 10.8, Figure 10.9, Figure 10.10, Figure 10.11, Figure 10.12

 

TABLES

• Table 10.1, Table 10.2

 

BOXES

1. How To: Create a Data Validation Rule. Discuss the steps involved in creating a data validation rule. (EX 611)
2. How To: Create an Input Message. Discuss the steps involved in creating an input message. (EX 614)
3. How To: Specify an Error Alert Style and Message. Discuss the steps involved in specifying an error alert style and message. (EX 616)
4. Best Practice: Using the Data Validation Tool Effectively. Discuss the use of the data validation tool. (EX 617)
5. How To: Circle Invalid Data. Discuss the steps involved in circling invalid data. (EX 617)

 

TEACHER TIP

Note that the data validation tool restricts data being entered, not data already stored in the worksheet.

 

CLASSROOM ACTIVITIES

1. Assign a Project: Ask students to complete questions 3 and 4 from the Conceptual Review.
2. Quick Quiz:

1. A(n) ____ can specify the type of data that is appropriate, such as whole numbers, decimal numbers, dates, times, and text. (Answer: validation rule)
2. True or False: By default, input messages appear as comments next to the cell when the cell is selected. (Answer: True)

3. Critical Thinking: For what types of problems should you use Excel’s error-checking tools? For what types of problems should you use other techniques, including your own common sense?

 

EX 619: Protecting Workbooks

LECTURE NOTES

• Discuss the techniques used to protect workbooks.
• Show students how to lock and unlock cells (see Figure 10.13).
• Show students how to protect a worksheet.
• Use Figure 10.14 to illustrate the Protect Sheet dialog box.
• Show students how to hide and unhide rows and columns (see Figure 10.15).
• Ensure that students understand how to protect a workbook.
• Use Figure 10.16 to illustrate the Protect Structure and Windows dialog box.

 

FIGURES

• Figure 10.13, Figure 10.14, Figure 10.15, Figure 10.16

 

BOXES

1. Best Practice: Selecting an Appropriate Level of Protection in Excel. Discuss the three levels of security and protection used to control who can access and change your Excel data. (EX 620)
2. How To: Unlock Cells. Discuss the steps involved in unlocking cells. (EX 621)
3. How To: Protect and Unprotect a Worksheet. Discuss the steps involved in protecting and unprotecting a worksheet. (EX 622)
4. How To: Hide and Unhide Rows or Columns. Discuss the steps involved in hiding and displaying a row or column. (EX 623)
5. How To: Protect and Unprotect a Workbook. Discuss the steps involved in protecting and unprotecting a workbook. (EX 625)

 

TEACHER TIP

Note that after you unlock cells to allow users to enter data in those cells, you can protect the worksheet so that users cannot change the contents of the rest of the cells, which are locked by default.

 

CLASSROOM ACTIVITIES

1. Assign a Project: Ask students to complete questions 5 through 7 from the Conceptual Review.
2. Quick Quiz:

1. True or False: Hiding a row or column will affect the data or any formulas that reference a hidden cell. (Answer: False)
2. True or False: By default, every cell in a worksheet is locked. (Answer: True)

EX 626: Documenting Workbooks

LECTURE NOTES

• Define the term documentation.
• Discuss the types of documentation that are helpful in an Excel application.
• Use Figure 10.17 to illustrate a documentation worksheet.
• Introduce the term comment.
• Use Figure 10.18 to illustrate the use of a comment as documentation.
• Use Figure 19 to illustrate the projected income worksheet for the helmet product line.

 

FIGURES

• Figure 10.17, Figure 10.18, Figure 10.19

 

BOXES

1. Best Practice: Selecting Print Options When Printing Worksheet Formulas. Discuss the importance of setting print options. (EX 627)
2. How to: Add a Comment to a Cell. Discuss the steps involved in adding a comment to a cell. (EX 629)
3. Steps To Success: Level 1: Ask students to complete the assignments. (EX 630)

 

CLASSROOM ACTIVITIES

1. Group Activity: Ask students to print worksheet formulas for any worksheet they’ve created. Also ask them to create complete documentation for at least one workbook.
2. Quick Quiz:

1. A(n) ____ worksheet usually contains the workbook title, its creation date, the name of the person who created the workbook, and any other information that describes the purpose and content of the workbook. (Answer: documentation)
2. A(n) ____ is a note that you attach to a cell, separate from other cell content.
   (Answer: comment)

 

LAB ACTIVITIES

1. Ask students to examine a worksheet they’ve created for this book and identify cells where a comment would be helpful.

EX 631: Types of Formula Errors

LECTURE NOTES

• Present an overview of the various types of formula errors that may be encountered, and the tools available to find and address them.

 

CLASSROOM ACTIVITIES

1. Quick Quiz:
2. True or False: All errors in a workbook result in error messages, making certain problems even more difficult to find. (Answer: False)
3. True or False: Formulas originally constructed incorrectly will cause error messages.
   (Answer: True)

EX 632: Tools for Solving Formula Errors

LECTURE NOTES

• Discuss how to use error messages to understand formula errors.
• Use Figure 10.20 to illustrate errors on the Selling Expenses worksheet.
• Use Figure 10.21 to illustrate a formula with color-coded cell references and borders.
• Discuss how to use auditing tools to troubleshoot formula errors.
• Use Figure 10.22 to illustrate the Formula Auditing group on the FORMULAS tab.
• Use Table 3 to discuss formula auditing tools.
• Introduce the terms tracer arrows, precedent cell, and dependent cell.

 

FIGURES

• Figure 10.20, Figure 10.21, Figure 10.22

 

TABLES

• Table 10.3

 

CLASSROOM ACTIVITIES

1. Assign a Project: Ask students to complete questions 8 and 9 from the Conceptual Review.
2. Quick Quiz:

1. True or False: If a cell contains an error-indicator triangle in its upper-left corner, you can click the cell to display the Error Alert button. (Answer: True)
2. A(n) ____ uses the value in the active cell in its formula. (Answer: dependent cell)

3. Critical Thinking: What makes an Excel application different from other types of workbooks?

EX 634: Tracing and Solving Formula Errors

LECTURE NOTES

• Show students how to trace errors.
• Use Figure 10.23 to illustrate the Selling Expenses worksheet and the Error Checking dialog box.
• Use Figures 10.24 and 10.25 to illustrate how to trace an error.
• Use Figure 10.26 to illustrate the Evaluate Formula dialog boxes.
• Use Figure 10.27 to illustrate the Selling Expenses worksheet with #VALUE! errors corrected.
• Show students how to trace precedent cells (see Figure 10.28).
• Use Figure 10.29 to illustrate tracing dependent cells.
• Introduce the terms false positives and false negatives.
• Use Figure 10.30 to show students how to use the Error Alert option to copy a formula.
• Use Figure 10.31 to illustrate the corrected Selling Expenses worksheet.
• Show students how to set error checking options (see Figure 10.32).
• Use Figure 10.33 to illustrate the completed Selling Expenses worksheet.
• Use Figure 10.34 to illustrate the Cost of Goods Sold worksheet.

 

FIGURES

• Figure 10.23, Figure 10.24, Figure 10.25, Figure 10.26, Figure 10.27, Figure 10.28, Figure 10.29, Figure 10.30, Figure 10.31, Figure 10.32, Figure 10.33, Figure 10.34

 

BOXES

1. How To: Trace Errors. Discuss the steps involved in tracing errors. (EX 635)
2. Best Practice: Relying on Business Knowledge. Discuss the use of common sense and business knowledge to identify errors. (EX 638)
3. How To: Trace Precedent Cells. Discuss the steps involved in tracing precedent cells. (EX 638)
4. How To: Trace Dependent Cells. Discuss the steps involved in tracing dependent cells. (EX 640)
5. How To: Set Error Checking Rules. Discuss the steps involved in setting error checking rules. (EX 643)
6. Best Practice: Debugging Your Excel Workbook. Discuss the process known as debugging. (EX 644)
7. Steps To Success: Level 2: Ask students to complete the assignments. (EX 646)

 

CLASSROOM ACTIVITIES

1. Assign a Project: Ask students to complete questions 10 and 11 from the Conceptual Review.
2. Quick Quiz:

1. ____ are items that are flagged as incorrect but are, in fact, correct. (Answer: False positives)
2. ____ are items that are, in fact, incorrect but are not flagged as such. (Answer: False negatives)

 

LAB ACTIVITIES

1. Ask students to use any or all of the techniques covered in this level to troubleshoot the worksheets they’ve created for this book.

EX 647: Defining Macros

LECTURE NOTES

• Introduce the terms macro and Visual Basic for Applications (VBA).
• Explain that macros make a workbook easier to use by automating repetitive or difficult tasks.