Excel Charts

1
Excel Charts Basic Skills

• Creating Charts in Excel

2
Basic Chart Elements
Y-Axis Vertical axis on a chart.
X-Axis Horizontal axis on a chart.
Data Series Columns or rows of data from the worksheet.
Gridlines Lines in the plot area of the chart. Allows data markers to read more easily.
Legend Text that identifies the chart series.
Data Points Values from the worksheet.
3
Chart Types
Excel has many different chart types to choose
from. Generally, the end result of the data in a
worksheet will determine the type of chart to
use. Here are a few examples
When you want to Use a
Show the significance of values in a specified time period show the amount of change rather than time or rate of change. Area Chart
Show values for comparison. Categories are usually shown on the x-axis and values on the y-axis. Bar Chart Column Chart
Show trends over a specified time period. Line Chart
Show one data series (one column of information). Pie Chart
Show changes or frequencies of data in regard to a center point and each other. Radar Chart
4
2 Ways to Create Charts

• After creating a worksheet in Excel, you can use
  charts to graphically represent the data.
• You can embed a chart on a worksheet or place a
  chart on a separate sheet within a workbook.
• There are two ways to create charts
• The F11 function key
• The Chart Wizard

5
Using the F11 Key

1. Select the data to be charted.
2. Press the F11 key on your keyboard.
3. A Column Chart will appear.

6
Using the F11 Key (cont.)

1. To add additional chart elements, use the Chart
   Options command on the Chart menu.
2. To change the chart type, use the Chart Type
   command on the Chart menu.

7
Using the Chart Wizard
1. Select the data to be charted.
2. On the Standard toolbar, click the Chart Wizard button. Or, select the Chart command from the Insert menu.
8
Using the Chart Wizard (cont.)
There are 4 steps associated with the Chart Wizard. When you have completed a step, click the Next button at the bottom of the dialogue box.
Step 1
Select the chart category.
9
Using the Chart Wizard (cont.)
Step 2
Plot data by columns or rows
10
Using the Chart Wizard (cont.)
Step 3
Chart Formatting Options
11
Using the Chart Wizard (cont.)
Step 4
Chart Placement
12
Review
To create a chart using the F11 or Chart Wizard
method 1. Select the data to be
charted. 2. Press either the F11 key or select
the Chart Wizard icon. 3. When the chart is
created, use the Chart menu or Chart toolbar to
add or remove chart elements or change the chart
type.
13
Creating a Run Chart

• Tracking Trends

14
What is a Run Chart

• A Run Chart will allow you to measure the
  performance of a process over a period of time.
• A Run Chart allows you to detect trends, shifts,
  or cycles in a process.

15
How to Create a Run Chart
1. Gather Data. Collect at least 20-25 data
points to detect meaningful patterns.
16
How to Create a Run Chart
2. Select the data points to be charted.
17
How to Create a Run Chart
3. Use the Chart Wizard to create the chart
(select the Insert Menu and choose the Chart
Command, or click the Chart Wizard icon on the
Standard Toolbar.
On Step 1 of the wizard, select the Line Chart
type category, and then choose a line chart with
markers from the sub-types on the right (row
2). Click the Next button to advance to the
Step 2.
18
How to Create a Run Chart
On Step 2 of the wizard, ensure the your data is
plotted by rows. Click the Next button to
advance to the Step 3.
19
How to Create a Run Chart
On Step 3 of the wizard, add a chart title and
labels to the X and Y axis. Click the Next
button to advance to the Step 4.
20
How to Create a Run Chart
On Step 4 of the wizard, choose a location for
your chart. To place the chart on the same
worksheet as the data, select the As object in
option. Click the dropdown arrow to choose the
appropriate worksheet. Click the Finish button.
21
How to Create a Run Chart
Your Run Chart has been created.
22
(No Transcript)
23
Creating a Column Chart

• Comparing Values

24
What is a Column Chart?
A Column chart illustrates comparisons among
individual items. This type of chart compares
values across categories. It is also available
with a 3-D visual effect. In the following
chart, categories are organized vertically, and
values horizontally, to place focus on comparing
the values.
25
How to Create a Column Chart
1. Select the data points to be charted.
26
How to Create a Column Chart
2. Use the Chart Wizard to create the chart
(select the Insert Menu and choose the Chart
Command, or click the Chart Wizard icon on the
Standard ToolColumn.
On Step 1 of the wizard, select the Column Chart
type category, and then choose a Column chart
from the sub-types on the right. Click the
Next button to advance to the Step 2.
27
How to Create a Column Chart
On Step 2 of the wizard, ensure the your data is
plotted by columns or rows. Click the Next
button to advance to the Step 3.
28
How to Create a Column Chart
On Step 3 of the wizard, add a chart
title. Click the Data Labels tab to add values
to your columns.
29
How to Create a Column Chart
On the Data Labels tab, choose the Value
option. Click the Legend tab to remove the
legend.
30
How to Create a Column Chart
On the Legend tab, de-select the Show Legend
option. Click the Next button to advance to
the Step 4.
31
How to Create a Column Chart
On Step 4 of the wizard, choose a location for
your chart. To place the chart on the same
worksheet as the data, select the As object in
option. Click the dropdown arrow to choose the
appropriate worksheet. Click the Finish button.
32
How to Create a Column Chart
Your Column Chart has been created. The Chart
menu gives you many options for working with your
column chart.
33
How to Create a Column Chart
Formatting the Column Chart series At a slow
rate, continuously click on a series in the chart
until resizing squares surround it.
34
How to Create a Column Chart
Formatting the Column Chart series Right-click
on the selected series. Choose the Format Data
Point option from the menu.
35
How to Create a Column Chart
Formatting the Column Chart series When the
Format Data Point dialogue box appears, choose
the appropriate color from the palette on the
right. Click OK.
36
How to Create a Column Chart
Formatting the Column Chart series The series
is now formatted. Continue this process until
all series in the chart have been formatted to
the appropriate color.
37
Creating a Pie Chart
38
What is a Pie Chart?
Pie charts have only one series of data, so you
should use only one column or row of data. You
can also use one column or row of labels.
39
How to Create a Pie Chart
1. Select the data points to be charted.
40
How to Create a Pie Chart
If the cells you want to select for your chart
are not in a continuous range, do the following
Select the first group of cells that contain
the data you want to include. While holding
down CTRL, select any additional cell groups you
want to include. The nonadjacent selections must
form a rectangle.
41
How to Create a Pie Chart Using the Chart Wizard
2. Use the Chart Wizard to create the chart
(select the Insert Menu and choose the Chart
Command, or click the Chart Wizard icon on the
Standard Toolbar.
On Step 1 of the wizard, select the Pie Chart
type category, and then choose a Pie chart from
the sub-types on the right. Click the Next
button to advance to the Step 2.
42
The Chart Wizard
On Step 2 of the wizard, ensure the your data is
plotted by columns. Click the Next button to
advance to the Step 3.
43
The Chart Wizard
On Step 3 of the wizard, add a chart
title. Click the Titles tab to add a title to
your Pie chart.
44
The Chart Wizard
On Step 3 of the wizard, add labels to your
chart. Click the Data Labels tab to add
percentages or actual values from your
worksheet. Click the Next button to advance to
the Step 4.
45
The Chart Wizard
On Step 4 of the wizard, choose a location for
your chart. To place the chart on the same
worksheet as the data, select the As object in
option. Click the dropdown arrow to choose the
appropriate worksheet. Click the Finish button.
46
Pie Chart
Your Pie Chart has been created.
47
Pie Chart
Pull out all of the slices in a pie chart
Click the pie, and then drag away from the center
of the chart.
48
Pie Chart
Pull only one slice in a pie chart
Click the pie, click the slice you want to move,
and then drag the slice away from the center of
the chart.
49
Creating a Histogram

• Analyzing the Frequency of Data

50
What is a Histogram?
A histogram is a chart that takes a collection of
measurements and plots the number of measurements
(frequency) that fall with a set of intervals
(bins). A Pareto Chart is a variation of a
histogram. Data in this type of chart is
presented in an output table in descending order
of frequency. Both charts can be created in
Excel by using the Histogram tool in the Analysis
Toolpak, which is located on the Tools menu.
51
Analysis Toolpak
The Analysis Toolpak is an Excel add-in (A
supplemental program) program that is available
when you install Microsoft Office or Excel. To
use it in Excel, however, you need to load it
first. On the Tools menu, click Add-Ins. In
the Add-Ins available box, select the check box
next to Analysis Toolpak, and then click OK. (If
Analysis Toolpak is not listed, click Browse to
locate it.) If you see a message that tells you
the Analysis Toolpak is not currently installed
on your computer, click Yes to install it. Click
Tools on the menu bar. When you load the Analysis
Toolpak, the Data Analysis command is added to
the Tools menu.
52
How to Create a Histogram
Step 1
Test Turn-Around Time Test Turn-Around Time Test Turn-Around Time Test Turn-Around Time Test Turn-Around Time
Minutes Minutes  
31 124 35 115  
32 35 55 42  
41 46 21 23  
43 47 34 49  
54 29 88 102  
75 96 37 55  
24 43 73 23  
117 13 45 84  
36 61 27 37  
48 57 35 107  
68 38 77 61  
91 25 44 67  
56 48 69 47  
37 58 22 31  
35 9 33 48  
Enter the data you want to use as input for the
Histogram tool in columns. Please note that you
must enter quantitative data (item amounts or
test scores) in each cell of the input columns.
53
How to Create a Histogram
Step 2
In another column, enter the bin numbers you want
to use for the analysis. The bin numbers need
to be entered in ascending order. Please note
that if you do not enter bin numbers, the
Histogram tool automatically creates evenly
distributed intervals using the minimum and
maximum values in the input range. Bin numbers
represent the intervals you want the tool to use
for measuring the input data in the data analysis.
Minute Bin Sizes Minute Bin Sizes
0
10
20
30
40
50
60
70
80
90
110
120
130
54
How to Create a Histogram
Step 3
On the Tools menu, click Data Analysis.
55
How to Create a Histogram
Step 4
In the Analysis Tools box, click Histogram, and
then click OK.
56
How to Create a Histogram
Step 5 Entering the Input Range, Bin Range, and
Output Range in the Histogram Dialogue Box
This is the location of the input data in your
worksheet.
This is the location of the bins in your
worksheet. This box can be left blank and Excel
will create evenly distributed intervals.
This is the upper left cell of the range where
you want the analysis to appear.
57
How to Create a Histogram
Step 5 Displaying the Output
You can display the output of the analysis in a
chart, cumulative percentages, or sorted (pareto).
58
Output of Analysis in Chart Form
When you select the chart output option, the
Histogram tool simultaneously generates a chart
along with the frequency distribution table.
Analysis
59
Things to Keep in Mind When Reviewing the Output
of the Analysis

• The histogram reports the number of input values
  that are equal to or greater than the bin value
  but less than the next bin value and displays it
  in the Frequency column.
• The last value in the table reports the number of
  input values equal to or greater than the last
  bin value.
• If you specified the Chart Output option in the
  Histogram dialog box, the Histogram tool
  simultaneously generates a chart along with the
  frequency distribution table.

60
(No Transcript)
61
Creating a Scatter Chart

• Identifying the Relationship Between Variables

62
What is a Scatter Chart?
A Scatter chart identifies the possible
relationship between the changes observed in two
different sets of variables. This type of chart
supplies the data to confirm a hypothesis that
two variables are related.
63
How to Create a Scatter Chart
1. Select the data points to be charted.
64
How to Create a Scatter Chart
2. Use the Chart Wizard to create the chart
(select the Insert Menu and choose the Chart
Command, or click the Chart Wizard icon on the
Standard Toolbar.
On Step 1 of the wizard, select the Scatter Chart
type category, and then choose a Scatter chart
from the sub-types on the right. Click the
Next button to advance to the Step 2.
65
How to Create a Scatter Chart
On Step 2 of the wizard, ensure the your data is
plotted by columns. Click the Next button to
advance to the Step 3.
66
How to Create a Scatter Chart
On Step 3 of the wizard, add a chart
title. Click the Titles tab to add titles to
the X axis and Y axis.
67
How to Create a Scatter Chart
On Step 4 of the wizard, choose a location for
your chart. To place the chart on the same
worksheet as the data, select the As object in
option. Click the dropdown arrow to choose the
appropriate worksheet. Click the Finish button.
68
How to Create a Scatter Chart
Your Scatter Chart has been created.