with Charts and Graphs Objectives Create an embedded chart Create and format a pie chart Work with chart titles and legends Create and format a column chart New Perspectives on Microsoft Excel 2010 ID: 693388
Download Presentation The PPT/PDF document "Excel Tutorial 4: Enhancing a Workbook" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Excel Tutorial
4:
Enhancing a Workbook
with Charts and GraphsSlide2
Objectives
Create an embedded chart
Create and format a pie chartWork with chart titles and legendsCreate and format a column chart
New Perspectives on Microsoft Excel 2010
2Slide3
Objectives
Create and format a line chart
Modify a chart data sourceCreate and format a combined chart
Create a 3-D chartCreate and format sparklines and data barsCreate a chart sheet
New Perspectives on Microsoft Excel 2010
3Slide4
Visual Overview
New Perspectives on Microsoft Excel 2010
4Slide5
Chart Elements
New Perspectives on Microsoft Excel 2010
5Slide6
Creating an Excel Chart
Charts show trends or relationships in data that are difficult to show with numbers
Select a range to use as chart’s data source
New Perspectives on Microsoft Excel 2010
6Slide7
Creating an Excel Chart
Select chart type that best represents the data
Use one of 73 built-in charts organized into 11 categories, or…
Create custom chart types based on built-ins
New Perspectives on Microsoft Excel 2010
7Slide8
Creating an Excel Chart
Three Chart Tools tabs appear on the Ribbon: Design, Layout, and Format
New Perspectives on Microsoft Excel 2010
8Slide9
Moving a Chart to a Different Worksheet
Move Chart dialog box provides options for moving charts
New Perspectives on Microsoft Excel 2010
9Slide10
Moving and Resizing Charts
Selecting the chart displays a
selection box (used to move or resize the object)
To move the chart, drag selection box to new location in worksheetTo resize the chart, drag a sizing handle
New Perspectives on Microsoft Excel 2010
10Slide11
Designing a Pie Chart
Charts include individual elements that can be formatted (chart area, chart title, plot area, data markers, legend)
Make changes using a built-in style that formats the entire chart or by selecting and formatting an individual element
Use a chart style to apply several formats to the chart at one time
New Perspectives on Microsoft Excel 2010
11Slide12
Designing a Pie Chart
Choose location of the legend, and format it using tools on Chart Tools Layout tab
New Perspectives on Microsoft Excel 2010
12Slide13
Formatting Data Labels on a Pie Chart
New Perspectives on Microsoft Excel 2010
13Slide14
Changing Pie Slice Colors
Use distinct colors to avoid confusion, especially for adjacent slides
Format each slice rather than entire data series (each slice represents a different value in the series)
New Perspectives on Microsoft Excel 2010
14Slide15
Designing a Pie Chart
Exploded pie charts
Move one slice away from the othersUseful for emphasizing one category above all of the others
New Perspectives on Microsoft Excel 2010
15Slide16
Creating a Column Chart
Column chart
Displays values in different categories as columnsHeight of each column is based on its value
Bar chartColumn chart turned on its sideLength of each bar is based on its value
New Perspectives on Microsoft Excel 2010
16Slide17
Column/Bar Charts vs Pie Charts
Column/bar charts are superior to pie charts
For large number of categories or categories close in value
Easier to compare height or length than areaCan be applied to wider range of dataCan include several data series (pie charts usually show only one data series)
New Perspectives on Microsoft Excel 2010
17Slide18
Inserting a Column Chart
Select data source
Select type of chart to createMove and resize the chart
Change chart’s design, layout, and format by:Selecting one of the chart styles, orFormatting individual chart elements
New Perspectives on Microsoft Excel 2010
18Slide19
Inserting a Column Chart
New Perspectives on Microsoft Excel 2010
19Slide20
Editing the Axis Scale and Text
Range of values
(scale) of an axis is based on values in data sourceVertical (value) axis: range of series values
Horizontal (category) axis: category valuesPrimary and secondary axes
can use different scales and labels
Add descriptive axis titles if axis labels are not self-explanatory (default is no titles)
New Perspectives on Microsoft Excel 2010
20Slide21
Editing the Axis Scale and Text
New Perspectives on Microsoft Excel 2010
21Slide22
Editing the Axis Scale and Text
New Perspectives on Microsoft Excel 2010
22Slide23
Formatting the Chart Columns
Columns usually have a common format – distinguished by height, not color
New Perspectives on Microsoft Excel 2010
23Slide24
Communicating Effectively with Charts
Keep it simple
Focus on the messageLimit the number of data seriesUse gridlines in moderation
Choose colors carefullyLimit chart to a few text styles
New Perspectives on Microsoft Excel 2010
24Slide25
Visual Overview
New Perspectives on Microsoft Excel 2010
25Slide26
Charts, Sparklines, and Data Bars
New Perspectives on Microsoft Excel 2010
26Slide27
Creating a Line Chart
Use when data consists of values drawn from categories that follow a sequential order at evenly spaced intervals
Displays data values using a connected line rather than columns or bars
New Perspectives on Microsoft Excel 2010
27Slide28
Formatting Date Labels
Set minimum and maximum dates to use in the scale’s range
Set major and minor units as days, months, or years to use for the scale’s interval
New Perspectives on Microsoft Excel 2010
28Slide29
Formatting Date Labels
Custom date formats use combinations of “m”, “d”, and “y” for months, days, and years
Number of letters controls how Excel displays the date
New Perspectives on Microsoft Excel 2010
29Slide30
Setting Label Units
Simplify a chart’s appearance by displaying units of measure appropriate to data values
Useful when space is at a premium
Example: Display the value 20 to represent 20,000 or 20,000,000Apply custom formats to numbers, including adding text
New Perspectives on Microsoft Excel 2010
30Slide31
Overlaying a Chart Legend
Makes more space for the plot area
An overlaid chart element floats in the chart area and is not fixed to a particular position; can be dragged to a new location
New Perspectives on Microsoft Excel 2010
31Slide32
Creating a Line Chart
Adding gridlines
Adding an axis title
New Perspectives on Microsoft Excel 2010
32Slide33
Editing and Revising Chart Data
Modify the data range that the chart is based on (do not directly modify data in the chart)
If values/labels in data source are changed, chart automatically updates to show new content
New Perspectives on Microsoft Excel 2010
33Slide34
Adding a Data Series to an Existing Chart
New data series appears in the chart with a different set of data markers
New Perspectives on Microsoft Excel 2010
34Slide35
Adding a Data Series to an Existing Chart
New Perspectives on Microsoft Excel 2010
35Slide36
Adding a Data Series to an Existing Chart
Understanding the SERIES
function
Can be edited within the formula bar to make
quick changes to the chart, but the function is tied to an existing chart; it cannot be used
within a worksheet cell or referenced from another Excel formula
New Perspectives on Microsoft Excel 2010
36Slide37
Creating a Combination Chart
Select a data series in an existing chart
Apply a new chart type to that series, leaving the other data series in its original format
New Perspectives on Microsoft Excel 2010
37Slide38
Choosing the Right Chart
New Perspectives on Microsoft Excel 2010
38
Chart
When to Use
Pie charts
Small number of categories; easy to distinguish relative sizes of slices
Column or bar chart
Several categories
Line charts
Categories follow a sequential order
XY scatter charts
To plot two numeric values against one another
Custom chart
Available charts don’t meet your needsSlide39
Creating a 3-D Chart
Provide illusion of depth and distance
Adds three spatial dimensions:The
x-axis (length)The y-axis (height)
The
z-axis
(depth)
Chart’s
perspective
controls how fast the chart appears to recede
New Perspectives on Microsoft Excel 2010
39Slide40
Creating a 3-D Chart
New Perspectives on Microsoft Excel 2010
40Slide41
Adding Sparklines and Data Bars
Both convey graphical information about worksheet data without occupying a lot of space
New Perspectives on Microsoft Excel 2010
41Slide42
Creating Sparklines
A mini chart displayed within a worksheet cell
Compact in size; doesn’t include chart elementsGoal is to convey maximum amount of graphical information in a very small space
Can be grouped or ungroupedGrouped sparklines share a common formatUngrouped sparklines can be formatted individually
New Perspectives on Microsoft Excel 2010
42Slide43
Types of Sparklines
Line sparkline
Highlights trendsColumn sparklineFor column charts
Win/Loss sparklineHighlights positive and negative values
New Perspectives on Microsoft Excel 2010
43Slide44
Creating Sparklines
Select a data range containing data to graph
Select a location range where you want sparklines to appear
New Perspectives on Microsoft Excel 2010
44Slide45
Creating Sparklines
New Perspectives on Microsoft Excel 2010
45Slide46
Adding and Formatting Sparkline Markers
Can specify only line color and marker color
Can create line markers for highest value, lowest value, all negative values, first value, and last value
Can create markers for all data points regardless of value or position in data sourceCan add an axis to a sparkline – horizontal line that separates positive and negative values
New Perspectives on Microsoft Excel 2010
46Slide47
Adding and Formatting Sparkline Markers
New Perspectives on Microsoft Excel 2010
47Slide48
Creating Data Bars
Conditional format that adds a horizontal bar to background of a cell containing a numeric value
Length based on value of each cell in selected range
Dynamic
Lengths of data bars automatically update if cell’s value changes
New Perspectives on Microsoft Excel 2010
48Slide49
Modifying a Data Bar Rule
Alter rules of the conditional format
New Perspectives on Microsoft Excel 2010
49Slide50
Modifying a Data Bar Rule
New Perspectives on Microsoft Excel 2010
50Slide51
Creating a Chart Sheet
For detailed charts that need more space to be seen clearly or to show a chart without any worksheet text or data
Do not contain worksheet cells for calculating numeric values
New Perspectives on Microsoft Excel 2010
51Slide52
Creating a Chart Sheet
New Perspectives on Microsoft Excel 2010
52