New Perspectives on Microsoft Excel 2016 Instructor’s Manual 1 of 9
Microsoft Excel 2016
Module 7: Developing an Excel Application
A Guide to this Instructor’s Manual:
We have designed this Instructor’s Manual to supplement and enhance your teaching experience through
classroom activities and a cohesive module summary.
within the software.
In addition to this Instructor’s Manual, our Instructor’s Resources also contains PowerPoint Presentations,
Test Banks, and other supplements to aid in your teaching experience.
Table of Contents
Module Objectives
1
Planning an Excel Application
2
Naming Cells and Ranges
Using Defined Names in Formulas
2
2
Validating Data Entry
3
Protecting a Worksheet and a Workbook
3
Inserting Comments
4
Automating Tasks with Macros
4
Protecting Against Macro Viruses
5
Recording a Macro
5
Working with the Visual Basic Editor
6
Creating Macro Buttons
6
Saving Workbooks with Macros
6
Opening a Workbook with Macros
7
7
End of Module Material
7
Module Objectives
Students will have mastered the material in this module when they can:
• Create an application
• Create, edit, and delete defined names for
• Add defined names to existing formulas
• Create validation rules for data entry
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 2 of 9
• Learn about macro viruses and Excel
• Assign a macro to a keyboard shortcut and a
Planning an Excel Application
LECTURE NOTES
• Discuss the benefits of planning an Excel application.
TEACHER TIP
Continue to stress the importance of planning. In this module, students will create applications. It is
extremely important to determine ahead of time what you want to do.
CLASSROOM ACTIVITIES
Classroom Discussion: Students will have learned that they can use WordArt to enhance a worksheet.
Ask
Naming Cells and Ranges
LECTURE NOTES
• Demonstrate how to use the Name box to create defined names.
• Show how to select cells and ranges by their defined names.
TEACHER TIP
Students are familiar with referring to cells by their cell references such as A5, C6, and so on. However,
sometimes it is useful to choose a cell or range of cells and assign a name to those cells. This is called a range
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False. A defined name is a word or string of characters associated with a single cell or a
range. (Answer: True)
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 3 of 9
Using Defined Names in Formulas
LECTURE NOTES
• Demonstrate how to use defined names in formulas.
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False. Range references in formulas are updated with their defined names. (Answer:
False)
Validating Data Entry
LECTURE NOTES
• Explain how to create validation rules for data entry.
• Demonstrate how to specify validation criteria.
TEACHER TIP
Excel provides features that will help validate data as it is entered into a worksheet. Students can specify the
type of data that is allowed and/or a range of acceptable values. If a value is entered that does not meet the
CLASSROOM ACTIVITIES
Class Discussion:
1. What is data validation? (Answer: To ensure that correct data is entered and stored in a
worksheet, you can use data validation to create a set of rules that determine what users can
enter in a specific cell or range.)
Protecting a Worksheet and a Workbook
LECTURE NOTES
• Demonstrate how to lock and unlock cells.
• Show how to protect and unprotect a worksheet and a workbook.
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 4 of 9
TEACHER TIP
Once students have a worksheet that they know is correct, they may want to protect the worksheet so that
users cannot make changes. Excel allows you to protect a portion of the worksheet or all of the worksheet.
Setting the locked property will disallow any changes to a particular cell. The worksheet will have to be
CLASSROOM ACTIVITIES
Class Discussion: How do you protect a worksheet? (Answer: In the Font group on the Home tab, click
the Dialog Box Launcher. In the Format Cells dialog box, click the Protection tab, click the Locked
check box to remove the check mark, and then click the OK button. In the Changes group on the
Review tab, click the Protect Sheet button. Enter a password (optional). Select all of the actions you
want to allow users to take when the worksheet is protected. Click the OK button.)
Quick Quiz:
1. True or False: Worksheet protection applies only to the contents of a worksheet, not to the
Inserting Comments
LECTURE NOTES
• Demonstrate how to insert a comment.
TEACHER TIP
Providing documentation is important for a successful application. Another source of documentation you
CLASSROOM ACTIVITIES
Class Discussion: Why would you add comments to a worksheet? (Answer: To (A) explain the contents
of a particular cell, such as a complex formula; (B) provide instructions to users; and (C) share ideas
and notes from several users collaborating on a project.)
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 5 of 9
• Show how to display the Developer tab on the ribbon.
TEACHER TIP
Remind students that when they create a worksheet, they are usually preparing it to be used by someone
CLASSROOM ACTIVITIES
Quick Quiz:
1. What is a macro? (Answer: A macro is a series of stored commands that can be run
Protecting Against Macro Viruses
LECTURE NOTES
• Discuss macro security settings.
• Show how to set the macro security level.
TEACHER TIP
Students can create a macro, a set of actions that will allow them to easily automate tasks. Because a macro
is actually a program, a virus can be attached to the macro. A macro is attached to a workbook and just
CLASSROOM ACTIVITIES
Quick Quiz:
1. The _______ is a central location for all the security settings in Office. (Answer: Trust
Center)
2. The _______ control what Excel will do about macros in a workbook when you open that
workbook. (Answer: macro security settings)
Recording a Macro
LECTURE NOTES
• Show how to record a macro.
TEACHER TIP
One way to create a macro is to use the Excel macro recorder. When students start the macro recorder, all
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 6 of 9
CLASSROOM ACTIVITIES
Critical Thinking: Think about the way that you create a macro with the macro recorder. What are the
Running a Macro
LECTURE NOTES
• Show how to run a macro.
CLASSROOM ACTIVITIES
Class Discussion: In this module, you learned about how to make edits in Excel. However, even small
Creating the TransferData Macro
LECTURE NOTES
• Explain how to create a macro to transfer data.
• Show how to fix macro errors.
CLASSROOM ACTIVITIES
Quick Quiz:
1. You can turn off the macro recorder by clicking the _________ button. (Answer: Step
Recording)
Working with the Visual Basic Editor
LECTURE NOTES
• Discuss the structure of macros.
• Show how to edit a macro using the Visual Basic Editor.
TEACHER TIP
All macros are actually small programs written in Visual Basic for Applications (VBA). Students can create a
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 7 of 9
CLASSROOM ACTIVITIES
Class Discussion: In this module, you altered some of the Visual Basic code in the Visual Basic Editor.
LAB ACTIVITY
Ask students (in groups if you prefer) to think of different macros that might be helpful in the
workbook. If you have time, have the students record the proposed macro. Have them explain whether
the macro seemed difficult or easy to create.
Creating Macro Buttons
LECTURE NOTES
• Show how to create a macro button.
TEACHER TIP
Students can invoke a macro by assigning a shortcut key to the macro. When the shortcut key is pressed the
macro is run. An additional option is to create a button on the workbook that will invoke a macro.
CLASSROOM ACTIVITIES
Class Discussion: How do you move or resize a macro button? (Answer: To move or resize a macro
Saving a Workbook with Macros
LECTURE NOTES
• Show how to save a workbook with a macro.
TEACHER TIP
Remind students that the default Excel workbook (.xlsx file extension) does not allow macros to be stored as
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 8 of 9
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False. The default Excel workbook (.xlsx file extension) allows macros to be stored as
part of the file. (Answer: False)
Opening a Workbook with Macros
LECTURE NOTES
• Show how to open a workbook with macros.
TEACHER TIP
When Excel opens a file with macros, it checks to see if it contains a macro. The response you see is based
CLASSROOM ACTIVITIES
Quick Quiz:
1. What happens when you open a file with macros? (Answer: Excel checks the opening
workbook to see if it contains a macro.)
LAB ACTIVITY
Divide the class into groups of two or three. Ask them to conduct a Web search about macro viruses.
TEACHER TIP
Excel 2016 allows users to change the ribbon. Explain to students that they can minimize the ribbon, create
new tabs and groups, hide tabs or commands, and rename tabs or commands.
CLASSROOM ACTIVITIES
Quick Quiz:
1. True or False: When the Ribbon is minimized, only the Quick Access Toolbar and the tab
End of Module Material
• Review Assignments: Review Assignments provide students with additional practice of the skills they
learned in the module using the same module case, with which they are already familiar. These
assignments are designed as straight practice and do not include anything of an exploratory nature.
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 9 of 9
The Case Problems provide further hands-on assessment of the skills and topics presented in the
module, but with new case scenarios. There are five types of Case Problems:
• Apply. In this type of Case Problem, students apply the skills that they have learned in the module to
solve a new problem.