New Perspectives on Microsoft Excel 2016 Instructor’s Manual 1 of 9
Microsoft Excel 2016
Module 2: Formatting Workbook Text and Data
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.
This document is organized chronologically, using the same headings in blue that you see in the
textbook. Under each heading you will find (in order): Lecture Notes that summarize the section,
Table of Contents
Module Objectives
1
Formatting Cell Text
1
Working with Fill Colors and Backgrounds
2
Using Functions and Formulas to Calculate Sales Data
2
Formatting Numbers
Formatting Worksheet Cells
3
3
Applying Cell Styles
5
Copying and Pasting Formats
5
Finding and Replacing Text and Formats
6
Working with Themes
6
Highlighting Cells with Conditional Formats
6
Formatting a Worksheet for Printing
7
End of Module Material
8
Exploring the Format Cells Dialog Box
4
Module Objectives
Students will have mastered the material in this module when they can:
• Change fonts, font style, and font color
• Add fill colors and a background image
• Create formulas to calculate sales data
• Apply cell styles
• Copy and paste formats with the
Format Painter
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 2 of 9
• Set the print area, insert page breaks,
add print titles, create headers and
footers, and set margins
Formatting Cell Text
LECTURE NOTES
• Explain how to use themes to format data
TEACHER TIP
Working with font types, sizes, and colors can make a dramatic difference in the appearance and
presentation of a spreadsheet. Be sure to point out the differences in serif and sans serif fonts and
show examples. Serif means strokes or tails; sans means without. In addition, discuss the difference
between theme and non-theme fonts.
CLASSROOM ACTIVITIES
Class Discussion: Show the students different fonts. Ask them to determine if the print is serif or sans
serif.
Class Discussion: How many colors are always available regardless of the theme selected? What are
Working with Fill Colors and Backgrounds
LECTURE NOTES
• Demonstrate how to change a fill color
• Show how to add a background image to the Documentation sheet
TEACHER TIP
Color can enhance or detract from the content. Caution students to choose color based on the
audience and the information contained in the worksheet.
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 3 of 9
color combinations. Print the workbook both in color and black-and-white. Understand your
printer’s limitations. Be sensitive to your audience.)
Quick Quiz:
Using Functions and Formulas to Calculate Sales Data
LECTURE NOTES
• Demonstrate how to use functions and formulas to calculate sales values
CLASSROOM ACTIVITIES
Quick Quiz:
1. What are gross sales? (Answer: The total amount of sales.)
Formatting Numbers
LECTURE NOTES
TEACHER TIP
By selecting the Number group on the Home tab you can select a number format, apply accounting or
other currency formats, change a number to a percentage, insert a comma as a thousands separator,
and increase or decrease the number of digits displayed to the right of the decimal point.
CLASSROOM ACTIVITIES
Class Discussion: What might the General number format be good for? (Answer: Simple calculations.)
What are some other ways to format numbers? (Answer: Set the number of digits displayed to the
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 4 of 9
right of the decimal point. Add commas. Apply currency or accounting symbols. Display %
symbols.)
Formatting Worksheet Cells
LECTURE NOTES
• Demonstrate how to align cell content
TEACHER TIP
When you enter numbers and formulas into a cell, Excel automatically aligns them with the cell’s
right edge and bottom border, while text entries are aligned with the left edge and bottom border.
You can control the alignment of data within a cell both horizontally and vertically. You can also
have Excel shrink the text to fit within the given column width you have chosen or even rotate text
from -90 to +90 degrees.
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: Combining several cells into one cell is called aligning. (Answer: False)
2. True/False: Text is oriented within a cell horizontally from left to right. (Answer: True)
Class Discussion:
What are the three steps to indent text in a cell? (Answer: 1. Select the range. 2. In the
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 5 of 9
worksheet open all the time and periodically pause long enough for them to try the different
formats that you are illustrating.
Exploring the Format Cells Dialog Box
LECTURE NOTES
• Show how to open the Format Cells dialog box and review the options
TEACHER TIP
Point out to students that the buttons on the Home tab provide quick access to the most common
formatting, but you can also use the Format Cells dialog box.
CLASSROOM ACTIVITIES
Creative Thinking Activity: Discuss the six formatting tabs and how they are used.
(Answer: Number: Provides options for formatting the appearance of numbers, including dates and
numbers treated as text (for example, telephone or Social Security numbers)
• Alignment: Provides options for how data is aligned within a cell
Quick Quiz:
1. You can also open the Format Cells dialog box by _______ a cell or range, and then
clicking Format Cells on the shortcut menu. (Answer: right-clicking)
Calculating Averages
LECTURE NOTES
CLASSROOM ACTIVITIES
Quick Quiz:
1. What value does this function return: =AVERAGE (3, 6, 7, 8)? (Answer: C)
A. 3
B. 5
C. 6
D. 7
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 6 of 9
2. What is the syntax of the Average function? Answer: AVERAGE (number1, number2,
number3, …)
Applying Cell Styles
LECTURE NOTES
• Review how to apply built-in styles
TEACHER TIP
Whenever several cells need to use the same format, you can create a style for those cells. A style is a
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: A style is a collection of formatting. (Answer: True)
2. True/False: Excel has built-in styles to format worksheet titles. (Answer: True)
LAB ACTIVITIES
Have the students practice applying styles. First have them select the cell or range. In the
Styles group on the Home tab, click the Cell Styles button. Have them point to each style in
the Cell Styles gallery to see a Live Preview of that style on the selected cell or range. Have
them click the style they want to apply to the selected cell or range.
Copying and Pasting Formats
LECTURE NOTES
TEACHER TIP
Your students can create their own cell styles by clicking the Cell Style button from the Styles group
on the Home tab and clicking New Cell Style. Excel will open a dialog box from which students can
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 7 of 9
along with its contents. With the Paste Special dialog box, you can specify exactly what you want to
paste.
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: The Format Painter copies data as well as formatting. (Answer: False)
2. True/False: You can click the Transpose button to paste the column data into a row or to
paste the row data into a column. (Answer: True)
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: You cannot replace text and a format simultaneously. (Answer: False)
2. True/False: The shortcut key for the Replace command is Ctrl+H. (Answer: True)
TEACHER TIP
Styles and themes allow consistency across the Microsoft Office 2016 software.
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: The appearance of non-theme fonts, colors, and effects changes based on
which theme is applied to the workbook. (Answer: False)
Highlighting Cells with Conditional Formats
LECTURE NOTES
• Explain how to apply conditional formatting
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 8 of 9
TEACHER TIP
Discuss how conditional formatting in a worksheet is special formatting applied only to certain cells
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False: Conditional formatting in a worksheet is special formatting applied when
certain cell values meet one or more conditions. (Answer: True)
Formatting a Worksheet for Printing
LECTURE NOTES
• Show how to use Page Break Preview to view a worksheet
TEACHER TIP
By default, Excel prints all of the active worksheets that contain text, formulas, or values. You can
define a print area that contains only the content that you want to print.
CLASSROOM ACTIVITIES
Quick Quiz:
1. A page break is indicated by a(n) _______. (Answer: C)
A. red solid line
B. green solid line
C. dotted blue border
D. dotted yellow border
2. A(n) _______is the space between the page content and the edges of the page. (Answer:
margin)
Class Discussion: What is a header? What is a footer? Discuss how they can improve a worksheet’s
appearance. (Answer: A header is text printed within the top margin of every worksheet page and a
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 9 of 9
LAB ACTIVITIES
Have the students practice creating custom headers and footers in class. This is a feature they
will use repeatedly when creating workbooks. Excel provides several formatting buttons to
customize headers or footers. There is a left, center, and right section in which to enter data.
End of Module Material
• Review Assignments: Review Assignments provide students with additional practice of the skills
• 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 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.
Top of Document