Transcript Slide 1

CHAPTER 16
Inserting and Formatting Charts
Learning Objectives
• Create a chart
• Work with chart elements
• Modify a chart
• Create an exploded pie chart
• Create a column chart
• Create a line chart
• Edit chart data
• Insert and format sparklines
• Insert and modify data bars
Communicating Effectively with Charts
• Studies show that people interpret information more easily
in a graphic form than in a tabular format.
• As a result, charts can help communicate the real story
under-lying the facts and figures you present to
colleagues and clients.
• A well designed chart can illuminate the bigger picture
that might be hidden by viewing only the numbers.
• However, poorly designed charts can mislead readers and
make it more difficult to interpret data.
Communicating Effectively with Charts
• Keep it simple. Do not clutter a chart with too many
graphic elements. Focus attention on the data rather than
on decorative elements that do not in-form.
• Focus on the message. Design the chart to high-light the
points you want to convey to readers.
• Limit the number of data series used in the chart. Line
charts and column charts should display no more than
three or four data series. Pie charts should have no more
than six slices.
• Use gridlines in moderation. Gridlines should be used to
provide only approximate values for the data markers.
Having too many gridlines can obscure the data being
graphed.
Communicating Effectively with Charts
• Choose colors carefully. Display different data series in
contrasting colors to make it easier to dis-tinguish one
series from another. Modify the default colors as needed
to make them distinct on the screen and in the printed
copy.
• Limit the chart to a few text styles. Use a maximum of two
or three differ-ent text styles in the same chart. Having too
many text styles in one chart can distract attention from
the data.
Creating a Chart
• A chart (graph) is a visual representation of a set of data
values
• Charts show trends or relationship that may not be readily
apparent from numbers alone.
• Topics Covered:
• Selecting a Data Source
• Selecting a Chart Type
Selecting a Data Source
• Data source – the range that contains the data to
display in a chart.
• Data series A range of values that is plotted as a
single unit on a chart.
• Series name The first row of the data range,
which identifies the data series.
• Category values The first column of the data
range, which contains the groups or categories to
which the series values belong.
• Series values The data displayed in the chart.
Selecting a Data Source
Seriessource
Data
name –The
thefirst
range
rowthat
of the
contains
data range,
the
data toidentifies
which
display inthe
a chart.
data series
Category
Series
Data
series
values
values
A range
The
The
data
offirst
values
displayed
column
thatof
in
is the
plotted
data
chart
as a single
range,
which
unit
contains
on a chart
the groups or
categories to which the series values belong
Selecting a Chart Type
• You can apply a wide variety of chart types to the
selected data source.
• Deciding which type of chart to use requires
evaluating the data and determining the ultimate
purpose or goal of the chart.
Chart Type
• Column - Compares values from different
categories. Values are indicated by the height of
the columns.
• Line - Compares values from different categories.
Values are indicated by the height of the line. Often
used to show trends and changes over time.
• Pie - Compares relative values of different
categories to the whole. Values are indicated by the
areas of the pie slices.
• Bar - Compares values from different categories.
Values are indicated by the length of the bars.
Chart Type
• Area - Compares values from different categories.
Similar to the line chart except that areas under the
lines contain a •fill color.
• Scatter - Shows the patterns or relationship
between two or more sets of values. Often used in
scientific studies and statistical analyses.
• Stock - Displays stock market data, including the
high, low, opening, and closing prices of a stock.
• Surface - Compares three sets of values in a threedimensional chart.
Selecting a Chart Type
• The different chart types are found on the insert tab in the
Chart area.
CMPTR Chapter 16: Inserting and Formatting Charts
Working with Chart Elements
• Topics Covered:
• Selecting Chart Elements
• Moving a Chart to a Different Sheet
• Repositioning and Resizing a Chart
13
Selecting Chart Elements
• Charts include individual elements that can be
formatted, including: the chart area, the chart title,
the plot area, data markers, and a legend.
Selecting Chart Elements
Chart area - The area
that contains the chart
and all of the other
chart elements (area in
black line).
Chart title - A descriptive
label or name for the chart.
Legend - A
rectangular
area that
indicates the
data markers
associated
with the data
series.
Plot area - The part of
the chart that contains
the graphical
representation of the
data series.
Data marker - An
object in a chart that
represents a value in a
data series, such as a
pie slice or column.
Moving a Chart to a Different Sheet
• Each chart you create is inserted as an embedded chart,
which is an object in a worksheet.
• You can move an embedded chart to a different
worksheet in the workbook or you can move it into a chart
sheet.
• You use the Move Chart button on Chart Tools Design
tab.
• When you click on the this tab the following dialog box
appears
Creating a Chart sheet
• Chart sheets are helpful for detailed charts that need
more space to be seen clearly or when you want to
show a chart without any worksheet text or data.
• Some reports require large expansive charts rather
than compact graphs to provide more detail and
make them easier to view and share. In those
situations,
• Chart sheets are used for graphic elements like
charts and images, and do not contain worksheet
cells for calculating numeric values.
Repositioning and Resizing a Chart
• You can reposition and resize an embedded chart to
better fit on the worksheet.
• To move a Chart to another location in the worksheet
move your pointer over the chart (you should see the
words Chart Area appear) drag the chart to the new
position.
• To resize a chart move to one of the corners until you
see the double headed arrow hold down the left
mouse button and resize the chart.
• Tip: To retain the chart’s proportions, hold down the
Shift key as you drag the sizing handle.
Modifying a Chart
• Topics Covered:
• Changing the Chart Style
• Changing a Chart Layout
• Positioning and Formatting a Chart Title
• Positioning the Chart Legend
• Working with Data Labels
• Changing the Color of a Data Series
Changing the Chart Style
• You can modify the appearance of a chart by applying a
chart style that formats the entire chart.
• The chart styles are found on the Cart Tools Design tab.
Changing a Chart Layout
• Chart layouts provide different options for displaying and
arranging chart elements.
• The chart layouts are found on the Cart Tools Design tab
Positioning and Formatting a Chart Title
• The chart title provides a description of a chart or an
overview of its purpose.
• The title is like any other text box, you click in it and
start typing.
Positioning the Chart Legend
• The chart legend identifies each of the data series
in the chart.
• You can move the legend by moving your mouse
over it and dragging it to a new position.
• To resize a chart legend move to one of the corners
until you see the double headed arrow hold down
the left mouse button and resize the chart.
• Tip: To retain the chart legend’s proportions, hold
down the Shift key as you drag the sizing handle
Working with Data Labels
• A data label is text for an individual data marker, such as
pie slices.
• The label options are available on the Data Labels button
in the Labels group on the Chart Tools Layout tab and in
the Format Data Labels dialog box. data label Text for an
individual data marker.
Changing the Color of a Data Series
• The data series is the range of values plotted on the
chart.
• Usually, you use one color for an entire data series.
However, in a pie chart, you want each slice to have a
different color so that the slices are easy to distinguish.
Creating an Exploded Pie Chart
• An exploded pie chart moves one slice away from
the others as if someone were taking the piece
away from the pie.
• Exploded pie charts are useful for emphasizing one
category above all of the others.
Creating a Column Chart
• A column chart displays values in different categories as
columns so that the height of each column is base don its
value.
• A bar chart is a column chart that is turned on its side so
that the length of each bar is based on its value.
• The process of creating and formatting a chart is the same
for each type of chart, although the specific formats and
options available reflect the current chart type
Chart Axis
Category axis is the horizontal axis that
shows the category values from each data
series.
Value axis is the
vertical axis that shows
the range of series
values from all of the
data series plotted on
the chart.
Scale is the range of values
along an axis.
Tick Marks
• Tick marks identify the unit on a chart axis at
regular intervals.
• The primary axis usually appears along the left
side of a chart.
• The secondary axis usually appears on the right
side of a chart.
Creating a Line Chart
• A line chart displays data values using a connected line
rather than columns or bars.
• Line charts are typically used
• When the data consists of values drawn from categories that follow
a sequential order at evenly spaced intervals,
• Line charts are also commonly used instead of column charts when
there are many data points across several data series.
• Topics Covered:
• Editing the Axis Scale and Labels in a Line Chart
• Adding an Axis Title
• Adding Gridlines
Editing the Axis Scale and Labels in a Line Chart
• Sometimes you will want a chart to show the labels
at specific intervals to save space or make the
chart more attractive.
• You can do this by setting the major and minor tick
marks.
• When a chart involves large numbers, the axis
labels can take up a lot of the available chart area
and be difficult to read.
• You can simplify the chart’s appearance by
displaying units of measure more appropriate to the
data values.
Adding an Axis Title
• An axis title is descriptive text that appears next to the
axis values.
• You can choose how the axis title appears on the chart by
selecting an option from the Axis Titles button in the
Labels group on the Chart Tools Layout tab.
Combination Chart
• A combination chart combines two or more chart types in
a single graph, such as a column chart and a line chart.
• To create a combination chart
• First select the data series in an existing chart that you want to
appear as another chart type.
• On the Chart Tools Design tab, in the Type group, click the Change
Chart Type button
• Click the chart type you want to apply to the selected series
• Click OK.
• The selected series changes to the new chart type on the
chart, leaving the other data se-ries in its original format.
Adding Gridlines
• Gridlines extend the values of the major or minor
tick marks across the plot area.
• Gridlines are similar to borders in that you can
change their color and design style as well as add
drop shadows or glowing color effects.
Editing Chart Data
• Topics Covered:
• Changing a Data Value or Label
• Adding a Data Series to an Existing Chart
• Modifying Lines and Data Markers
Changing a Data Value or Label
• Charts remain linked or connected to their data
sources, even if they appear in different
worksheets.
• If you change any value or label in the data source,
the chart is automatically updated to show the new
content.
• One advantage of creating charts in Excel is that
you can quickly see how changing one or more
values affects the chart.
Changing a Data Value or Label
• You can modify a chart by adding a new data
series.
• The new data series appears in the chart with a
different set of data markers.
• You can change the appearance of the lines and
data markers in a line chart with the Marker
Options in the Format Data Series dialog box.
Inserting and Formatting Sparklines
• Sparklines are graphs that are displayed within a
cell.
• The goal of a sparkline is to convey a large amount
of graphical information within a very small space.
• They don’t include chart elements such as legends,
titles, gridlines, or axes.
• You can create three types of sparklines:
• A line sparkline, used to highlight trends
• A column sparkline, used for column charts
• A win/loss sparkline, used to highlight positive and negative
values
Inserting and Modifying Data Bars
• A data bar is conditional formatting that adds a
horizontal bar to the background of a cell containing
a number.
• When applied to a range of cells, the data bars
have the same appearance as a bar chart with
each cell containing one bar.
• The lengths of data bars are based on the values in
the selected range.
• Cells with larger values have longer bars; cells with
smaller values have shorter bars.
• Data bars are always placed in the cells containing
the value they represent, and each cell represents
only a single bar.