Exploring Microsoft Excel 2003 Revised Edition

1
Exploring Microsoft Excel 2003Revised Edition
Chapter 3 Graphs and Charts Delivering a
Message Robert Grauer and Maryann Barber
Committed to Shaping the Next Generation of
IT Experts.
2
Objectives

• Describe how a chart can be used to deliver a
  message.
• List several types of charts and describe the
  purpose of each
• Distinguish between an embedded chart versus a
  chart in its own sheet
• Use the Chart Wizard to create and modify a chart

3
Objectives (continued)

• Use the Drawing toolbar to enhance a chart by
  adding lines and objects
• Distinguish between data series in rows versus
  columns
• Differentiate between a stacked-column chart
  versus a side-by-side column chart
• Create a Word document that is linked to a
  worksheet and an associated chart

4
Case Study Delivering a Message

• The opening case study demonstrates using Excel
  to analyze and chart data, then linking that
  worksheet into a Word document. This allows you
  to combine Excels charting capability with
  Words capability to produce an attractive memo.

5
What is a Chart?

• A graphic representation of data in a worksheet
• Chart elements
• Category labels descriptive text entries
• Data points numeric values
• Data series a grouping of data points

6
Chart Types

• Keep it simple
• Use the appropriate chart type
• Pie and Exploded pie charts display proportional
  relationships
• Column charts display numbers rather than
  percentages
• Bar charts display numbers horizontally

7
Pie Charts
Chart title
Each slice of the pie represents the percentage
of the dinner bill each person pays
Slices are exploded
8
Column Charts
Embedded chart shows both the chart and the data
Row 4 contains column headings and forms labels
for X axis
9
Creating A Chart

• Two ways to create
• Embed chart in worksheet
• Sizing handles allow you to size, move, copy, or
  delete an embedded chart
• Create in separate chart sheet
• Charts are linked to underlying data
• A change in the data instantly updates the
  chart(s) created on that data

10
Using the Chart Wizard

• Select the cells that contain the data
• Click the Chart Wizard button on the standard
  toolbar
• Select the chart type
• Check the data series
• Complete the chart options
• Choose the location

11
Choose the Chart Type
Sub-types change as a different chart type is
selected
Select any of the standard chart types or click
the Custom Types tab to create your own
Definitely use this button. If all you see is a
blank screen, cancel and reselect data.
12
Check the Data Series
Preview the chart before going further
Collapse button hides the dialog and allows you
to select a different range
The first row is used as a default for the X axis
labels
13
Complete the Chart Options
Use the other tabs to add descriptive text to the
chart and enhance its formatting
Enter a title for the chart. If you want labels
for the axes, enter them here
14
Choose the Location
Give the chart sheet a name
Use this option to create the chart in a chart
sheet. Remember F11 provides this result instantly
Use this option to embed the chart
15
Modifying a Chart

• Add labels, change the data type, or format the
  chart with the Chart toolbar
• Add text boxes, arrows and other objects for
  emphasis with the Drawing toolbar

16
Enhancing a Chart
Arrow with embedded text box highlights fourth
quarter increase
17
Moving and Sizing the Chart
Sizing handles indicate a chart is selected and
can be moved, sized, copied, or deleted
Drag a corner handle to change height and width
simultaneously and keep in proportion
18
Hands-on Exercise 1

• Title of Exercise The Chart Wizard
• Objective To create and modify a chart by using
  the Chart Wizard to embed a chart within a
  worksheet to enhance a chart to include arrows
  and text.
• Input file Software Sales
• Output file Software Sales Solution

19
Multiple Data Series

• Select multiple data series when you want to see
  individual data points rather than totals
• Determine whether data series are in rows or
  columns
• Data points plotted are the same either way, but
  grouping will be different.

20
Rows Versus Columns

• If data series are in rows
• First row is used for category labels
• Remaining rows are used for data series
• First column is used for the legend text
• If data series are in columns
• First column is used for category labels
• Remaining columns are used for data series
• First row is used for legend text

21
Data Series in Rows
Data series in rows, so first row is used as
category labels and remaining rows are data series
First column used as legend text
22
Data Series in Columns
Data series in columns, so first column is used
as category labels and the first row as legend
text
Data points are the same different grouping
allows you to make different comparisons
23
Stacked Column Charts

• Depicts totals by category instead of each
  individual data point
• Each data point is plotted as part of a whole
• Useful when you want to compare totals by category

24
Example of Stacked Column Chart
Each category is graphed as a total. Denver is
plotted beginning where Miami left off
Data labels show the value associated with each
piece of the column
25
Hands-on Exercise 2

• Title of Exercise Multiple Data Series
• Objective To plot multiple data series in the
  same chart to differentiate between data series
  in rows and columns
• Input file Software Sales Solution (from first
  exercise)
• Output file Software Sales Solution (additional
  modifications)

26
Object Linking and Embedding

• Create a document in one application that
  contains objects from another application
• Embedded object is stored in the document
• an Excel chart becomes part of the Word document
• Linked object is stored in its own file
• any change in this file is automatically
  reflected in the main document

27
Linking A Worksheet
Worksheet and embedded chart are linked into this
document
28
Multitasking

• Multitasking the ability to have more than one
  application open at the same time
• Use the taskbar to switch between open
  applications

29
Updating Links
Change the source if the link becomes broken
Use this if changes to the linked document are
not updated in the main document
30
Hands-on Exercise 3

• Title of Exercise Object Linking and Embedding
• Objective To create a compound document
  consisting of a memo, worksheet, and chart
• Input file Software Sales Solution (from second
  exercise)
• Output files Software Sales Solution (additional
  modifications),Software Memo Solution (Word
  document)

31
Summary

• Charts graphically represent data in a worksheet
• Select chart type based on the message you are
  trying to convey
• Create with the Chart Wizard
• Can be embedded onto a worksheet or created on a
  separate chart sheet

32
Summary (continued)

• Multiple data series may be specified in either
  rows or columns
• Same data points, but different grouping gives
  different comparison
• Object Linking and Embedding (OLE) creates a
  compound document
• Contains objects from multiple applications
• Embedding the object is stored within the
  compound document
• Linking the object is stored as a separate file

33
End-of-Chapter Exercises

• Multiple Choice
• Practice Exercises
• Exercise 1 Theme Park Admissions
• Exercise 2 Hotel Bookings
• Exercise 3 Flexibility in Charting
• Exercise 4 Page Break Preview
• Exercise 5 Your Next Car
• Exercise 6 Irrational Exuberance
• Exercise 7 Worksheet References

34
End-of-Chapter Exercises (continued)

• Practice Exercises (continued)
• Exercise 8 Additional Practice
• Exercise 9 A Combination Chart
• Mini Cases
• The Convention Planner
• Designer Clothing
• PowerPoint Presentations
• Exploded Pie Charts
• The Petting Zoo
• Chapter Recap Tax Cuts and Dinner

35
Questions?