New Perspectives on Microsoft Excel 2016 Instructor’s Manual 1 of 8
Microsoft Excel 2016
Module 5: Working with Excel Tables,
PivotTables, and PivotCharts
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.
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 a Structured Range of Data
Freezing Rows and Columns
2
2
Creating an Excel Table
2
Maintaining Data in an Excel Table
2
Sorting Data
3
Filtering Data
3
Splitting the Worksheet Window into Panes
4
Inserting Subtotals
4
Analyzing Data with PivotTables
4
Filtering a PivotTable
5
Refreshing a PivotTable
5
Creating a Recommended PivotTable
6
Creating a PivotChart
6
End of Module Material
6
Module Objectives
Students will have mastered the material in this module when they can:
• Explore a structured range of data
• Add, edit, and delete records in an Excel
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 2 of 8
• Insert a Total row to summarize an Excel
table
• Create and modify a PivotTable
• Apply PivotTable styles and formatting
Planning a Structured Range of Data
LECTURE NOTES
• Discuss the importance of planning a structured range of data.
TEACHER TIP
Be sure students spend time looking at the June cash receipts data (Figure 5-1) and also the Data definition
CLASSROOM ACTIVITIES
Group Activity: Divide the class into groups of two or three. Have each group look at the June cash
receipts data worksheet (Figure 5-1). Ask each group to make a list of ten different ways they might
Freezing Rows and Columns
LECTURE NOTES
• Show how to freeze and unfreeze rows.
CLASSROOM ACTIVITIES
Quick Quiz:
1. The Freeze Panes button is located on the _______ tab. (Answer: View)
Creating an Excel Table
LECTURE NOTES
• Demonstrate how to create an Excel table.
• Show how to rename and modify an Excel table.
TEACHER TIP
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 3 of 8
CLASSROOM ACTIVITIES
Class Discussion: What are the benefits of creating an Excel table? (Answer: Benefits include that you
can: format the Excel table quickly using a table style; add new rows and columns to the Excel
Maintaining Data in an Excel Table
LECTURE NOTES
• Demonstrate how to add records to an Excel table.
• Show how to find, edit, and delete records.
TEACHER TIP
CLASSROOM ACTIVITIES
Quick Quiz:
1. What key do you press to move from field to field? (Answer: Tab)
2. You can use the _______ dialog box to locate and remove records that have the same data in
selected columns. (Answer: Remove Duplicates)
Sorting Data
LECTURE NOTES
• Demonstrate how to sort one column using the Sort buttons.
CLASSROOM ACTIVITIES
Quick Quiz:
1. A(n) _______ indicates the sequence in which you want data ordered. (Answer: custom list)
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 4 of 8
In addition to the examples in the book, have students sort the data on other fields to observe the results.
Explain that by sorting they can provide several useful views of the data with little effort.
Filtering Data
LECTURE NOTES
• Demonstrate how to filter using one column.
TEACHER TIP
Be sure to go over Figure 5-18, which shows the filtering options. Explain that the options would be
different depending on the field on which they want to apply a filter.
CLASSROOM ACTIVITIES
Quick Quiz:
1. When you create an Excel table, _______ appear in each of the column headers. (Answer: filter
arrows)
Using the Total Row to Calculate Summary Statistics
LECTURE NOTES
• Demonstrate how to add a total row and select summary statistics.
• Show how to split the worksheet window into panes.
TEACHER TIP
CLASSROOM ACTIVITIES
Quick Quiz:
1. The _______ is displayed at the end of the table. (Answer: Total row)
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 5 of 8
• Demonstrate how to split the worksheet window into panes.
CLASSROOM ACTIVITIES
Quick Quiz:
Inserting Subtotals
LECTURE NOTES
• Demonstrate how to calculate subtotals for a range of data.
• Show how to use the Outline buttons to control the level of detail that is displayed.
CLASSROOM ACTIVITIES
Quick Quiz:
1. The _______ command includes many kinds of summary information. (Subtotal)
2. What does the Subtotal command do? (Answer: The Subtotal command inserts a subtotal row into
the range for each group of data and adds a grand total row below the last row of data.)
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 6 of 8
CLASSROOM ACTIVITIES
Class Discussion:
• What is a PivotTable? (Answer: A PivotTable is an interactive table that enables you to group
and summarize either a range of data or an Excel table into a concise, tabular format for easier
reporting and analysis.)
Creating a PivotTable
LECTURE NOTES
• Demonstrate how to create a PivotTable.
• Show how to add fields to a PivotTable.
CLASSROOM ACTIVITIES
Quick Quiz:
1. Where can you find the PivotTable button? (Answer: in the Tables group on the Insert tab)
2. Before you create a PivotTable, what should you do first? (Answer: click in the Excel table or select
Filtering a PivotTable
LECTURE NOTES
• Demonstrate how to add a field to the FILTERS area.
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 7 of 8
CLASSROOM ACTIVITIES
Quick Quiz:
Refreshing a PivotTable
LECTURE NOTES
• Show how to edit an Excel table to refresh the PivotTable.
TEACHER TIP
You cannot change the data in a PivotTable because the table is based on the data in a list. Instead you must
CLASSROOM ACTIVITIES
Quick Quiz:
1. What is the first step in updating a PivotTable? (Answer: Change the data that you want updated in
a PivotTable.)
Creating a Recommended PivotTable
LECTURE NOTES
• Demonstrate how to use the Recommended PivotTables dialog box.
Creating a PivotChart
LECTURE NOTES
• Demonstrate how to create a PivotChart.
• Show how to filter items in the PivotChart.
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 8 of 8
TEACHER TIP
CLASSROOM ACTIVITIES
Critical Thinking: Imagine the massive amount of information that might be involved in a sales data
table. The data would include the salesman, the customer, the product, the quantity, and the price.
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.
• Case Problems: A typical NP module has four Case Problems following the Review Assignments. Short
modules can have fewer Case Problems (or none at all); other modules may have five Case Problems.
The Case Problems provide further hands-on assessment of the skills and topics presented in the