Transcript – Basic Skills Excel Charts Creating Charts in Excel

Excel Charts – Basic Skills
Creating Charts in Excel
Basic Chart Elements
Data Points
Legend
Y-Axis
Gridlines
Data Series
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.
X-Axis
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
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
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.
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.
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.
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.
Using the Chart Wizard (cont.)
Step 2
Plot data by columns or
rows
Using the Chart Wizard (cont.)
Step 3
Chart Formatting Options
Using the Chart Wizard (cont.)
Step 4
Chart Placement
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.
Creating a Run Chart
Tracking Trends
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.
Sam ple 100
Number of Colored Beads
30
25
20
15
10
5
0
1
2
3
4
5
6
7
8
9
10 11 12 13 14 15 16 17 18 19 20
Tr ial
How to Create a Run Chart
1. Gather Data. Collect at least 20-25 data points to detect meaningful patterns.
How to Create a Run Chart
2. Select the data points to be charted.
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.
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.
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.
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.
How to Create a Run Chart
Your Run Chart has been
created.
Creating a Column Chart
Comparing Values
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.
How to Create a
Column Chart
1. Select the data points to be charted.
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.
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.
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.
How to Create a Column Chart
On the “Data Labels”
tab, choose the “Value”
option.
Click the “Legend” tab to
remove the legend.
How to Create a Column Chart
On the “Legend” tab, deselect the “Show
Legend” option.
Click the “Next” button
to advance to the Step 4.
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.
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.
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.
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.
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.
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.
Creating a Pie Chart
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.
How to Create a Pie Chart
1. Select the data points to be charted.
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.
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.
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.
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.
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.
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.
Pie Chart
Your Pie Chart has been created.
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.
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.
Creating a Histogram:
Analyzing the Frequency of Data
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.
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.
How to Create a Histogram
Step 1
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.
Test Turn-Around Time
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
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.
Minute Bin Sizes
0
10
20
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.
30
40
50
60
70
80
90
110
120
130
How to Create a Histogram
Step 3
On the Tools menu,
click Data Analysis.
How to Create a Histogram
Step 4
In the Analysis
Tools box, click
Histogram, and
then click OK.
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.
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).
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
Chart
Things to Keep in Mind When
Reviewing the Output of the
Analysis
1. 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.
2. The last value in the table reports the number of input values equal to or
greater than the last bin value.
3. 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.
Creating a Scatter Chart
Identifying the Relationship Between
Variables
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.
Nbr of Holes Played vs Nbr of Pars
Nbr of Pars
25
20
15
10
5
0
0
5
10
15
20
Nbr of Holes
25
30
35
40
How to Create a Scatter Chart
1. Select the data points to be charted.
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.
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.
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.
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.
How to Create a Scatter Chart
Your Scatter Chart has been created.