Instructor’s Manual Materials to Accompany

GO! ALL IN ONE - CHAPTER 9

EXCEL PIE CHARTS, LINE CHARTS, AND

WHAT-IF ANALYSIS TOOLS

OBJECTIVES
Objective 1: Chart Data with a Pie Chart

A pie chart shows the relationship of each part to a whole, where the size of each pie slice is equal to
its value compared to the total value of all the slices. A pie chart is generated from data that is
arranged in a single column or single row. A pie chart should be considered when there is only one
data series to plot.

A data point is a value that originates in a worksheet cell and is represented in a chart by a data
marker. In a pie chart, each pie slice is a data marker. Together, the data points form the data
series—related data points represented by data markers—and determine the size of each pie slice. A
legend identifies the pie slice categories.

Objective 2: Format a Pie Chart

In a pie chart, it is usually more effective to place the labels within, or close to, each pie slice. The
order in which the data series in pie charts are plotted in Excel is determined by the order of the data
on the worksheet.

Individual chart objects, as well as, the entire chart—the chart area—can be formatted.

Objective 3: Edit a Workbook and Update a Chart

When data in a worksheet is edited, the chart data markers—the pie slices—will adjust automatically
to accurately represent the new values.

WordArt is a gallery of text styles that can be used create and add decorative effects, such as
shadowed or mirrored text to a chart.

Objective 4: Use Goal Seek to Perform What-If Analysis

What-if analysis refers to the process of changing the values in cells to see how those changes affect
the outcome of formulas in your worksheet, and Goal Seek is one of Excel’s What-if tools.

Chart sheets are printed in a manner similar to printing worksheets, however they will use more
toner or ink than a smaller chart displayed as part of a worksheet.

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

Objective 5: Design a Worksheet for What-If Analysis

Recall that the process of changing the values in cells to see how those changes affect the outcome
of formulas in your worksheet is referred to as what-if analysis.

As is done in mathematics, when writing a formula in Excel, use parentheses to communicate the
order in which the operations should occur.

Objective 6: Answer What-If Questions by Changing Values in a Worksheet

If a formula depends on the value in a cell, then changes made to the value of that cell will be
reflected in the formula. When copying a cell containing a formula, Paste Special displays multiple
options—such as Formulas & Number Formatting, and Values & Number Formatting—for copying
the cell.

Objective 7: Chart Data with a Line Chart

A line chart displays trends over time. The time is displayed along the bottom axis and the data
point values connect with a line.

An axis is a line that serves as a frame of reference for measurement and it borders the chart plot
area. The plot area is the area bounded by the axes, including all the data series.

The area along the bottom of a chart that identifies the categories of data is referred to as the
category axis or the x-axis, and the area along the left side of a chart that shows the range of
numbers for the data points is referred to as the value axis or the y-axis.

Teaching notes for Chapter 9

Objective 1: Chart Data with a Pie Chart

A pie chart is based on two ranges. One range contains labels for each slice of the pie chart and the
other contains values that each part adds up to a total whole.

A chart sheet is a workbook sheet that contains only a chart and is useful for viewing a chart
separately from the worksheet data.

Teaching Tips

An example of a pie chart application is company expenditures by type, or the number

of each letter grade within a class.

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

 To create a pie chart, you should select the labels and one series of data. If they are not
adjacent, use Ctrl as you drag to select non-consecutive cell ranges.

Do not include any blank cells or totals in the chart range. Ask the students to explain
why this could become a problem.

Discuss the various reasons to place a chart on its own sheet versus within the same
worksheet as the data.

When you insert a chart, it may overlap the data in the worksheet. Reinforce the
methods for moving a chart within a worksheet or to a new worksheet.

Let students explore the contextual tabs. Make sure that students select the chart;
otherwise, these options are not visible.

A chart moves independently of the data. You can easily move it anywhere within the
worksheet or the workbook.

Objective 2: Format a Pie Chart

As with other worksheet objects, the font size and color of chart titles and labels can be changed and
rearranged.

To gain a different visual perspective of a chart, it can be rotated within the 360 degrees of the circle.

Additional effects, such as a 3-D bevel, can be applied to chart objects, and one or more slices of a
pie chart can be pulled out—exploded—to emphasize them.

A text box is a movable, resizable container that can be used for text or graphics.

Teaching Tips

Discuss changing the font and chart element sizes to make the chart easily readable.

You can turn off the legend if you add labels to the series.

When you show the legend at the bottom of the chart area, the legend takes up less
room; thus, the chart is larger.

When choosing a Shape Style, use Live Preview to try each option and choose the one
that fits your presentation slide the best. There are a lot of appealing looks to choose
from.

You can also rotate a pie chart by using the Rotate handle. This can be a little tricky,
however, because the rotation may not be exactly what you intend.
3

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

 Explode a pie slice when you want that data to stand out from the rest. You can
explode all slices to show each one independently of the whole.

Before you drag a pie slice to explode it, look at the sizing handles. Be sure the sizing
handles displayed are for the individual slice only. If the whole pie is selected, all
slices will explode when you drag.

Inserting a text box does not affect the data because the text box is superimposed on
the worksheet and not part of any cell or range.

Objective 3: Edit a Workbook and Update a Chart

In an Excel worksheet, WordArt can be effective if the worksheet will be displayed in a PowerPoint
presentation or viewed online.

Teaching Tips

Have students watch the chart as they change the data to see the changes apply
immediately after pressing Enter.

WordArt is a lot of fun for most students. There are many options and experimentation
should be encouraged. However, caution students not to overdo the use of WordArt.

The WordArt image may overlap existing data in the worksheet. Reinforce to watch
the visual cursor clues and position on a border edge to move an object.

WordArt and other images don’t actually reside within a cell. They overlay the
worksheet.

Objective 4: Use Goal Seek to Perform What-If Analysis

Goal Seek is an Excel what-if analysis tool, which finds the input needed in one cell to arrive at the
desired result in another cell.

Teaching Tips

Goal Seek is a special what-if analysis tool. If you need to know how one value adjusts
in connection with another value, use Goal Seek. If you know where you want to go but
don’t know how to get there, use Goal Seek.
4

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

 Goal Seek allows you to specify the desired value for a formula cell by selecting one
cell as the changing cell. Goal Seek limits you to one changing cell and does not permit
specification of constraints.

The Set cell should contain a formula and is the value that Goal Seek will adjust as
close to the desired amount as possible by manipulating the value of the By changing
cell value.

The hardest part about using the Goal Seek tool is setting up the data and choosing the
correct cells to use. Offer students tips on how to use the tool effectively and ask for
possible examples in which they might use the tool.

Headers and footers have been discussed previously, but in this activity, students are
introduced to custom headers and footers.

Objective 5: Design a Worksheet for What-If Analysis

After typing an equal sign (=) and the beginning letter or letters of a function name, Excel’s Formula
AutoComplete will display a list of function names that match the typed letter(s). Double-click to accept
an entry in the list.

When numbers are entered in a format that Excel recognizes, Excel automatically applies that format to
the cell. Format Painter can be used to copy formatting to other cells.

Teaching Tips

When using AutoFill for a series that is not default in Excel, you must include at least
two cells within the series to help establish a pattern. Excel must be able to detect a
pattern in order to generate the series.

Point out that the percentage rate of increase is a formula that students are using, not a
function.

Ask students what happens in the percentage rate of increase results if parentheses are
not included in the formula =(C6-B6)/B6. That is, C6-B6/B6. Excel will perform
calculations according to the order of operations. In this formula, it will divide B6 by
B6, and then subtract the results from C6. What are the results?

When using the Format Painter, there are basically two steps: select the cell that
contains the format you want to copy, and then click the cell where you want the
formatting to be applied. Discuss the rules of Format Painter: It is a toggle tool. Click
once and you can use it for one application. Double-click to use it for multiple
applications, but be sure to click again to toggle it off when finished.

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

 Point out the Paste Link button in the Paste Special dialog box (also available from
right-click Paste Special menu). Explain linking and how beneficial it can be.

Objective 6: Answer What-If Questions by Changing Values in a Worksheet

When copying a cell that contains a formula, Paste Special allows both the numeric format and
either the formula or its value to be copied to other cells.

Teaching Tips

When pasting a group of copied cells, you only need to click in the upper-left corner of
the destination range. If you decide to highlight the entire range, it must be the exact
size, or Excel will give an error message.

Go over the other Paste Special options that are available. Explain when each would be
beneficial to use.

Objective 7: Chart Data with a Line Chart

The line in a line chart emphasizes the flow from one data point value to the next, whereas the
columns in a column chart and the pie slices in a pie chart emphasize the distinct values of each data
point.

An Excel chart has two background elements—the plot area and the chart area—and both of these
can be formatted.

Teaching Tips

A line chart could be used to show growth in sales.

The chart style used is a matter of personal preference.

Take time to explain the chart elements in the table and selection techniques for
individual chart elements. Generally clicking once selects the whole while a second
click drills down to select a portion of the whole.

Axis titles explain the series and the units of the series. Axis titles are not the same
thing as category labels.

When you alter the Major units, you can make the chart more meaningful by making
the trend more obvious.

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

 A chart with a dark background is not appropriate for print, but it is effective in a
PowerPoint presentation or online.

Discussion Questions for Chapter 9

1. Discuss the uses of various types of charts. For example, it is easier to see a trend or changes in data
using a line chart. Be sure to include examples of uses in education and business.

2. Discuss the power of Excel’s calculations. What-if analysis is a key feature of Excel. You can make
changes to data and see the results immediately without needing to duplicate data.

Ethical Discussion for Chapter 9

1. We hear a lot today about companies downsizing by laying off large numbers of their employees.
How much weight should age and seniority carry when making lay-off decisions? Is it right to lay
off an older employee simply because they are closer to retirement than younger employees? Is it
ethical to lay off workers due to budget cuts, yet continue to pay executives large salaries and even
larger bonuses and perks? Discuss the implications of such decisions.

2. Discuss the ethics of outsourcing. Many companies opt to outsource some jobs to other countries
because the costs of doing business in the other country are better for business. What about the
unemployment rate in the United States? What about our economy? Does a U.S.-owned company
have the obligation to support the U.S. economy above its own business economy?

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

KEY TERMS

3-D
Absolute cell reference
Axis
Base
Bevel
Category axis
Chart area
Chart Elements box
Chart sheet
Data marker
Data point
Data series
Explode
Format as you type
Formula AutoComplete
Fund
General fund
Goal Seek
Legend
Line chart
Major unit
Order of operations
Percent for new value =
base percent +
percent of increase
Percentage rate of increase
8

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

Pie chart
Plot area
Rate = amount of increase/base
Relative cell reference
Text box
Tick marks
Value after increase = base × percent for new value
Value axis
What-if analysis
WordArt
x-axis
y-axis

Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall