For your
students:
Our latest online feature, CourseCasts, is a
library of weekly podcasts designed to keep your students up to date with the
latest in technology news. Direct your students to http://coursecasts.course.com, where they can download the most recent CourseCast onto their mp3
player. Ken Baldauf, host of CourseCasts, is a faculty member of the Florida
State University Computer Science Department where he is responsible for
teaching technology classes to thousands of FSU students each year. Ken is an expert
in the latest technology and sorts through and aggregates the most pertinent
news and information for CourseCasts so your students can spend their time
enjoying technology, rather than trying to figure it out. Open or close your
lecture with a discussion based on the latest CourseCast.
Table
of Contents
Students
will have mastered the material in Chapter Six when they can:
l Create and use a
template
l Use the ROUND function
l Utilize custom format
codes
l Define, apply, and
remove a style
l Add a worksheet to a
workbook
l Create formulas that use
3-D cell references
l Draw a Clustered Cone
chart
l Use WordArt to create a
title and create and modify shapes
l Add a header or footer,
change margins, and insert and move a page break
l Save a workbook as a PDF
or XPS file
l Use the Find and Replace
commands
l Create a workspace
l Consolidate data by
linking workbooks
LECTURE
NOTES
CLASSROOM
ACTIVITIES
1.
Group Activity: Templates are used in a wide range of applications. Ask
students to cite various situations in which templates are used. Discuss when
worksheet templates might be useful.
2.
Assign a Project: Business-type applications sometimes need to summarize data
from several worksheets into one worksheet. Ask students to research consolidation
and to list some business applications that summarize data from several sources
into one source, such as a worksheet.
EX 418:
Project — Profit Potential Worksheets with Cone Chart
LECTURE
NOTES
FIGURES
and TABLES: Figures — 6-1a, 6-1b, 6-1c, 6-1d, 6-1e, 6-2, 6-3a, 6-3b
BOXES:
1.
BTW: Workbook Survival. Explain that
workbooks must be well-documented to survive.
2. BTW: Templates. Describe how templates ensure
consistency and reduce work.
CLASSROOM
ACTIVITIES
1.
Group Activity: Review the requirements document together with the students. In
the Calculations section, ask if any of the calculations can be handled by a
function. Ask why the chart in Figure 6-3b should be placed on a separate
sheet.
2.
Quick Quiz:
1) What is the purpose of the ROUND function,
which was described in a previous chapter but is used in this chapter’s
project? (Answer: To eliminate errors caused by decimal numbers in calculations)
EX 421: Plan
Ahead Box (Critical Thinking): General Project Decisions
LECTURE
NOTES
·
Discuss
the general decisions that determine the worksheet’s characteristics as shown
in Figures 6-3a and 6-3b
·
Review
the general guidelines such as the template, identifying additional worksheets,
adding the chart, printing options, and consolidating workbooks
CLASSROOM
ACTIVITIES
1.
Quick Quiz:
1) The purpose of the _____ is to help visualize
the worksheet design. (Answer: Sketch)
2) True or False: Margins and page breaks also
can be adjusted to provide professional looking printed worksheets. (Answer:
True)
2.
Assign a Project: In class, ask students to read through the guidelines. Then,
have the students close the books and list as many of the guidelines as they
can remember and put them in order.
LECTURE
NOTES
·
Explain
that the first step in creating a workbook is to create and save a template
TEACHER TIPS
Dummy numbers serve two purposes — first, to
check the accuracy of formulas, and second, to test the limits of the formulas.
To check the accuracy of formulas, dummy data should be simple enough so that
results can be anticipated. To test the limits of formulas, dummy data should
include the different types of values expected.
FIGURES
and TABLES: Figures — 6-4, 6-5, 6-6, 6-7, 6-8, 6-9, 6-10, 6-11, 6-12, 6-13,
6-14, 6-15, 6-16; Table — 6-1
BOXES:
1.
BTW: Selecting a
2.
BTW: Displaying Future Dates. Describe
how to add a number to NOW and TODAY functions.
3.
BTW: Manipulating Dates. Describe how
to use the DATE function to change the date.
4.
BTW: Dummy Numbers. Emphasize the
importance of using good test data.
5.
BTW: Accuracy. Explain the level of
accuracy of arithmetic operations.
6.
BTW: Fractions. Describe how to use
the slash (/) character to enter fractions.
7.
BTW: Changing Modes. Describe the use
of the Equals Sign (=) to switch between Point and Enter or Edit modes.
8.
Other Ways: Encourage other ways to enter dummy data.
9.
Other Ways: Encourage using key sequences for saving the template.
CLASSROOM
ACTIVITIES
1.
Group Activity: Templates are a tool for reducing work and promoting
consistency. Ask students how templates accomplish this.
2.
Critical Thinking: Pyramids were built with blocks. Do you think a template was
used to create the blocks so they were consistently sized and shaped? Or, do
you think each block was measured separately and then cut into the correct
shape? How about the shoes you are wearing — were they made from a template? A
template is a pattern and also is referred to as a cookie-cutter. Have you ever
used a cookie cutter to form consistent cookie shapes or other foods? How about
a muffin tin to make perfectly shaped muffins?
EX 423: Plan Ahead Box (Critical Thinking):
Design the Template and Plan the Formatting
LECTURE
NOTES
CLASSROOM
ACTIVITIES
1.
Quick Quiz:
1) Multiple Choice: What is dummy data? a) Data
that are known to be unreal. b) Data used to find out how dumb the formulas
really are. c) Data used to validate formulas. D) None of the above. (Answer: C)
2) True or False: All numeric cell entry
placeholders should be formatted properly for unit numbers and currency
amounts. (Answer: True)
EX 434:
Formatting the Template
LECTURE
NOTES
FIGURES
and TABLES: Figures — 6-17, 6-18, 6-19, 6-20, 6-21, 6-22, 6-23, 6-24, 6-25,
6-26, 6-27, 6-28, 6-29; Tables — 6-2, 6-3
BOXES:
1.
BTW: Summing a Row or Column. Describe
how to reference an entire column or an entire row in a function argument by
listing only the column or only the row.
2.
BTW: Copying. Describe how to copy
the contents of a cell to the cell directly below it.
3.
BTW: Creating Customized Formats. Explain
that format symbols have special meaning and are described in Table 6-2.
4.
BTW:
5.
Other Ways: Encourage another way to assign a currency style.
6.
Other Ways: Describe the key sequence to create a new style.
7.
Other Ways: Describe the key sequence to apply a new style.
TEACHER TIPS
When creating a new
style, the name students initially see in the Style name box in the Style
dialog box may be Normal, as it is in this project, or it may be something
else. If the active cell has a different style, the name in the Style name box
may not be
CLASSROOM
ACTIVITIES
1.
Quick Quiz:
1) What do the four sections of a format code
describe? (Answer: The format for positive numbers, the format for negative
numbers, the format for zero, and the format for text)
2.
Class Activity: Divide the class into small groups. Give each group a section
of the blackboard or a flip chart. At the top of the blackboard or flip chart,
write the following six numbers: 67123, -123, 0, .534, 98.7, -5.354. Ask each
group to turn to Table 6-2. Choose a person to begin. Have that person write on
the chalkboard or flip chart one of the examples of symbols from column 2 of
Table 6-2. Then, have every member of the group write down the six initial
numbers, showing how they would appear when formatted according to the example.
The group should compare their answers and come up with a consensus as to the
correct answer. The next person in the group should then go to the board or
flip chart and enter another example.
LECTURE
NOTES
TEACHER TIPS
When you select a
template from either the New Workbook or the New dialog box to create a new
workbook, Excel names the new workbook by appending a “1” to the template name
(for example, Template1). This is similar to what Excel does when you first
start Excel and it assigns the name Book1 to the workbook.
Excel provides
additional workbook templates, which you can access by clicking the links in
the Templates list shown in Figure 6–30. Additional workbook templates also are
available on the Web. To access the templates on the Web, click the links in
the Microsoft Office Online section of the Templates list.
FIGURES
and TABLES: Figure — 6-30
BOXES:
1.
BTW: Opening a Workbook at Startup. Explain
that at startup, Excel will open a workbook or template that you have defined.
CLASSROOM
ACTIVITIES
1.
Quick Quiz:
1) True or False: You can open a workbook (or
template) automatically when you turn on your computer by adding the workbook
(or template) to the Startup folder. (Answer: True)
2.
Project to Assign: Read more about templates in the Excel Help system.
LAB
ACTIVITIES
1.
In the lab, experiment with the different template styles available on the New
Workbook dialog box. Print out and bring three styles that you have chosen to
class to share with the other students.
EX 446:
Creating a Workbook from a Template
LECTURE
NOTES
·
Use
Figures 6-31 and 6-32 to explain how to open a template and save it as a
workbook
·
Explain
that a workbook contains three worksheets by default and that more can be added
·
Use
Figures 6-33 and 6-34 to describe how to add a worksheet to a workbook
·
Use
Figures 6-35 through 6-37 to explain how to copy the contents of a worksheet to
other worksheets in a workbook
·
Discuss
the concept of speed data entry (or drilling an entry), in which Excel allows
you to enter a number once and drill it through worksheets so it is entered in
the same cell on all the selected worksheets
·
Review
the entries in Table 6-4 that are used to drill an entry in the project
worksheets
·
Use
Figures 6-38 and 6-39 to describe drilling an entry through worksheets
·
Use
Tables 6-5 through 6-7 and Figures 6-40 through 6-42 to describe how to modify three
of the worksheets
·
Discuss
how to reference cells in other sheets in a workbook
·
Describe
how to modify the company sheet
·
Use
Figures 6-43 through 6-48 to enter and copy 3-D references using the Paste button
menu
·
Use
Table 6-8 to review the commands on the Paste button menu
FIGURES
and TABLES: Figures — 6-31, 6-32, 6-33, 6-34, 6-35, 6-36, 6-37, 6-38, 6-39,
6-40, 6-41, 6-42, 6-43, 6-44, 6-45, 6-46, 6-47, 6-48; Tables — 6-4, 6-5, 6-6,
6-7, 6-8
BOXES:
1.
BTW: Drilling an Entry. Describe the
components that can be drilled down through a workbook.
2.
BTW: Importing Data. Discuss linking
to a workbook or importing data from a file or database.
3.
BTW: Circular References. Explain
that this reference depends on its own value and discuss the example.
4.
BTW: 3-D References. Describe the
keys to use when summing numbers on noncontiguous sheets.
5.
BTW: The Move Chart Button. Describe
how to move a chart from a chart sheet to a worksheet.
6.
Other Ways: Mention the keyboard shortcut for adding a worksheet to a workbook.
7.
Other Ways: Describe other ways to copy a worksheet to another worksheet or
workbook.
TEACHER TIPS
At work, if all
employees use the same Excel template for their expense reports (with the same
format, layout, and formulas), the uniformity of the reports will make them
easier to read, analyze, and compare.
Emphasize that
drilling data through worksheets is an efficient way to enter data that is
common among worksheets.
CLASSROOM
ACTIVITIES
1.
Group Activity: Divide the class into small groups. Have each group carefully examine
the four worksheets in Figures 6-1a through 6-1d. Ask each group to list all of
the differences they can find in the worksheets. Challenge each group to find
the most differences.
2.
Critical Thinking: What are some of the situations in which you would use
multiple worksheets and want to reference them from other sheets? Do you think
anything in your life could be improved by creating worksheets and drilling the
data? What about your work or school life?
EX 447: Plan Ahead Box
(Critical Thinking): Identify Additional Worksheets Needed in the Workbook
LECTURE
NOTES
CLASSROOM
ACTIVITIES
1.
Group Activity: Divide the class into
groups. Using Table 6-8 have each group review among themselves the Paste
button menu commands shown there, and the action that Excel takes when each of
the buttons is chosen. Choose a spokesperson from the group for each command.
Call the class back together, and randomly ask the groups to define the
commands. The appropriate spokesperson from each group must give the answer.
2.
Quick Quiz:
1) What is the maximum number of worksheets you can
have in a workbook? (Answer: 255)
2) When a new worksheet is added to a workbook,
where does its tab appear? (Answer: To the left of the active tab)
EX 461: Drawing the Clustered Cone Chart
LECTURE
NOTES
·
Use
Figure 6-49 to explain that the Clustered Cone chart is similar to a 3-D Bar
chart in that it can be used to show trends or illustrate comparisons among
items
·
Use
Figures 6-50 through 6-54 to describe how to add a Clustered Cone chart to a
new sheet and then change the chart’s layout, remove the series label, and add
a title to the horizontal axis
·
Use
Figure 6-55 to illustrate the formatting of the Clustered Cone chart
·
Use
Figures 6-56 through 6-61 to describe how to use WordArt for adding a chart
title
·
Use
Figures 6-62 through 6-65 to discuss annotating elements on the worksheet or
chart
FIGURES
and TABLES: Figures — 6-49, 6-50, 6-51, 6-52, 6-53, 6-54, 6-55, 6-56, 6-57,
6-58, 6-59, 6-60, 6-61, 6-62, 6-63, 6-64, 6-65
BOXES:
1.
BTW: Moving Charts. Describe how to
move a chart to a new sheet and to a chart sheet.
2.
Other Ways: Encourage other ways to draw the Clustered Cone chart.
3.
Other Ways: Encourage other ways to format the Clustered Cone chart.
TEACHER TIPS
Charts of any kind
are useful in the financial market. Brokers and investors like to use charts to
quickly compare stock data, annual report data, and for other comparisons of
related data. Creating charts that represent data and compare values is a
beneficial skill to acquire.
CLASSROOM
ACTIVITIES
1.
Critical Thinking: Do you think the Clustered Cone chart is a good way to
represent the data? Would you prefer a 3-D Bar chart? Why or why not? Do you
think certain personality types prefer different chart styles or do you think
most people have the same reactions to certain styles of charts that you do?
2. Quick Quiz:
1) True or False: The Clustered Cone chart can
be used to show trends or illustrate comparisons among items. (Answer: True)
2) The Clustered Cone chart is similar to a
_____ Bar chart? (Answer: 3-D)
LAB
ACTIVITIES
1.
Using the data from the project, experiment with different types of charts.
Report your findings in class.
EX 461: Plan Ahead Box (Critical Thinking): Plan
the Layout and Location of the Required Chart
LECTURE
NOTES
·
Use
Figure 6-49 to review the plan for the chart’s layout and location
CLASSROOM
ACTIVITIES
1.
Critical Thinking: What are some of the things you think about when you plan
the layout of anything? Perhaps it is the layout of the garden or the
arrangement of furniture in a room. A sketch is one of the easiest ways to get
started. The layout of anything has a psychological affect on people. Think of
laying out the chart as the feng shui of worksheet and workbook design.
2.
Quick Quiz:
1) True or False: The Clustered Cone chart is
helpful in representing a comparison of values. (Answer: True)
2) What is the reason for placing the chart on a
separate worksheet? a) So the other worksheets can maintain a similar look. b)
The other worksheets do not have enough room. c) It has to be on another sheet
to be linked. d) It must not be on a separate worksheet. (Answer: A)
EX 471:
Adding a Header and Footer, Changing the Margins, and Printing the Workbook
LECTURE
NOTES
·
Describe
the meaning of header and footer, their location on the page, and the initial
setting for the header and footer
·
Discuss
changing the header, footer, and margins
·
Use
Figures 6-66 through 6-71 to add a header and footer, change margins, and
center the printout horizontally
·
Remind
students that page setup characteristics are not copied automatically from one
sheet to another
·
Describe
how to add a header to the Clustered Cone Chart sheet
·
Describe
how to print all worksheets and nonadjacent sheets in a workbook by selecting
them before using the Print command
·
Use
Table 6-9 and Figures 6-72a and 6-72b to explain how to select and deselect
sheets
·
Use
Figures 6-73 and 6-74 to explain how to insert and remove horizontal and
vertical page breaks, and find boundaries
·
Use
Figures 6-75 and 6-76 to describe how to hide the page breaks so they do not
cause a distraction
·
Describe
the two file formats, PDF and XPS, for saving a workbook
FIGURES
and TABLES: Figures — 6-66, 6-67, 6-68, 6-69, 6-70, 6-71, 6-72a, 6-72b, 6-73,
6-74, 6-75, 6-76; Table — 6-9
BOXES:
1.
BTW: Quick Reference. Point out the
location for the Quick Reference Summary and the Excel 2007 Quick Reference Web
page.
2.
Other Ways: Describe other ways to insert a page break.
3.
Other Ways: Describe other ways to hide a page break.
CLASSROOM
ACTIVITIES
1.
Group Activity: Divide the class into groups. Give each group a copy of Table
6-9 that has been cut apart on the row and column lines, so that each cell is a
different piece. Challenge the groups to put the table back together correctly.
Tell them they may review Table 6-9 at any time.
2.
Group Activity: Excel allows you to hide the page breaks in a worksheet.
Brainstorm with the class to learn about circumstances where hiding page breaks
might be preferable to showing them.
3.
Project to Assign: Excel’s default margins are set to one inch top and
bottom and .75 inch left and right. Have students research to find situations in which changing the margins
might be desirable.
LAB
ACTIVITIES
1.
In the lab, create a workbook that has three worksheets. Add headers and
footers to the worksheets. Add the date and time, your name, the page number of
the worksheet and number of pages in the workbook. Experiment with different
formats for the headers and footers – for example, different fonts, font sizes,
colors. Experiment with adding WordArt to the header.
EX 481: The Find and Replace
Commands
LECTURE
NOTES
·
Use
Figures 6-77 through 6-79 to describe the Find command for use in finding a
string
·
Use
Figures 6-80 and 6-81 to explain how to replace a string by using the Find and
Replace dialog box
·
Review
the steps for quitting Excel
FIGURES
and TABLES: Figures — 6-77, 6-78, 6-79, 6-80, 6-81
TEACHER TIPS
Caution
students that the Replace All command must be used with some care, or
unintended consequences could result.
BOXES:
1.
BTW: The Find Command. Discuss using
the Find command to search a range.
2.
Other Ways: Explain the keyboard shortcut to open the Find dialog box.
3.
Other Ways: Explain the keyboard shortcut to replace a string.
CLASSROOM
ACTIVITIES
1.
Group Activity: The Find and Replace commands are common to many applications.
Discuss when students might want to use the Find command to locate specific
text or numbers. Brainstorm situations when they might want to use the Replace
command.
2.
Critical Thinking: Imagine life without the Find command. Of the software applications
you have used, which of them had Find and Replace commands that were easy to
use and powerful? Which applications had inferior Find and Replace
functionality? If you could design a Find and Replace feature, what options or
enhancements would you like to see as part of the design? For example, would
you like to search for colors, patterns, shapes, conditions, etc? Which
applications have a Find command but no Replace command? What about Web
applications or Web pages? As a user, you can search for a word on a Web page,
but you cannot replace it.
LAB
ACTIVITIES
1.
In the lab, experiment with using the Find command to search for a string or
part of a string. For example if you search for “tab” does Excel find just that
literal string or does it find “Tab” and “table” also. Experiment with
narrowing your search to find specific strings. Then, experiment with using the
Replace command to replace one string of text with another string. Use the Find
and Replace commands at the same time. Keep a list of some of the problems you
encounter while setting up the search, performing the search, and performing
the replacement string.
EX 485:
Consolidating Data by Linking Workbooks
LECTURE
NOTES
·
Review
the concept of consolidating data from worksheets in external workbooks and
explain that this method of consolidating is called linking
·
Use
Table 6-10 to summarize the worksheets and workbooks that will be part of
consolidation
·
Use
Figures 6-82 through 6-86 to illustrate the steps for locating files (searching
for workbooks) and then opening them
·
Use
Figures 6-87 and 6-88 to explain the steps for creating a workspace file that
will bind the workbooks before the data can be consolidated
·
Use
Figure 6-89 to explain how to consolidate data by linking the workbooks and
then how to update the links
·
Summarize
how to close each workbook and then quit Excel
FIGURES
and TABLES: Figures — 6-82, 6-83, 6-84, 6-85, 6-86, 6-87, 6-88, 6-89; Table — 6-10
TEACHER TIPS
When a workspace file
is saved, opening the workspace can open all associated files.
BOXES:
1.
BTW: Consolidation. Discuss
consolidating data across different workbooks using the Consolidate button.
2.
Other Ways: Encourage another way to search for and open workbooks.
3.
Other Ways: Encourage your students to explore other ways to create a workspace
file.
4.
BTW: Workspace Files. Discuss
displaying information about open workbooks.
5.
BTW: Excel Help. Refer the students
to Appendix C for using Excel Help.
6.
BTW: Certification. For more
information on the MCAS program see Appendix G or visit the Excel 2007
Certification Web page.
CLASSROOM
ACTIVITIES
1.
Assign a Project: Have the students use the Web to research more about
workspace files. Have them summarize, in a 2-page paper, the types of
information available, which sites were most helpful or useful, and which
offered examples. Have the students provide links to those sites and then pass
the links around to the other students in the class so they too can see the
information.
2.
Quick Quiz:
1) Consolidating data from other workbooks also is
referred to as _____. (Answer: Linking)
2) True or False: A workbook file saves
information about all the workbooks that are open. (Answer: False. A worksheet
file saves information about all open workbooks.)
§
Learn
It Online is a series of online student exercises that test your knowledge of
chapter content and key terms.
§
Apply
Your Knowledge is a student assignment that helps you to reinforce the skills
and apply the concepts you learned in this chapter.
§
Extend
Your Knowledge is a student assignment that challenges you to extend the skills
you learned in this chapter and to experiment with new skills. You may need to
use Help to complete the assignment.
§
Make
It Right is a student assignment that requires you to analyze a presentation
and correct all errors and/or improve the design.
§
In
the Lab (Lab): In the Lab is a series of student assignments that ask you to
design and/or create a presentation using the guidelines, concepts, and skills
presented in this chapter. The assignments are listed in order of increasing
difficulty.
§
Cases
and Places is a series of student assignments where you apply your creative
thinking and problem solving skills to design and implement a solution.
·
3-D
range (EX 456)
·
3-D
reference (EX 456)
·
annotate
(EX 469)
·
arranged
(EX 486)
·
arrow
(EX 469)
·
auto
fill series (EX 427)
·
brace
(EX 469)
·
Clustered
Cone chart (EX 461)
·
consolidation
(EX 418)
·
date
series (EX 427)
·
dependent
workbook (EX 490)
·
drilling
an entry (EX 451)
·
dummy
data (EX 424)
·
footer
(EX 471)
·
format
code (EX 438)
·
header
(EX 471)
·
growth
series (EX 427)
·
linear
series (EX 427)
·
link
(EX 485)
·
margins
(EX 471)
·
match
case (EX 481)
·
match
entire cell contents (EX 481)
·
object
(EX 466)
·
page
setup (EX 471)
·
page
breaks (EX 478)
·
production
environment (EX 433)
·
ROUND
function (EX 428)
·
sheet
reference (EX 456)
·
source
workbook (EX 490)
·
standard
accounting format (EX 436)
·
string
(EX 481)
·
style
(EX 440)
·
template
(EX 418)
·
text
box (EX 469)
·
WordArt
tool (EX 466)
·
workspace
file (EX 488)