Excel 2007 Tutorial 4.1. Pie, Column & Line Charts
Uploaded by djdates on 04.07.2010
Transcript:
In this video, we will be talking about charts in Microsoft Excel 2007.
When creating a chart, it is important to select the right chart for the right job.
We will be talking about pie charts, column charts, and line charts, but Excel offers
many different kinds of charts beyond these three types.
A pie chart shows a single series of data, showing us the percentage each piece of the
data is of the whole. We will start by entering some data. If you
would like, you can download this file with the data already created from the link on
this video. We can select the data, click on the insert
ribbon, and click on pie, and select one of the pie types.
Let’s take a look at some of the ways we can manipulate this pie chart.
We can click on any pie slice and drag it away from the rest of the pie.
We can right click on a pie slice and add a data label.
We can right click on our new data label and format the data label, adding a category name
or value. As we are working with raw numbers, we can
show the percentage or the actual data value. Notice the leader line option?
If we close this dialog box and drag a data label away from the pie, we will see a leader
line connecting the label and the related slice.
When dragging these labels, we want to make sure our cursor has changed to the four directional
arrows of the move cursor before we click and drag.
We can go to the Chart Tools Layout ribbon and add a chart title.
We can click on the Chart Tools Design ribbon and change the style of our chart
…or select a different type of chart, such as a three dimensional pie.
Like every aspect of our charts, we can manipulate individual elements, such as right clicking
on the pie and changing the 3D rotation. We might decide that we do not want the chart
on the same sheet as the data, so we can click on the Move Chart icon in the Chart Tools
Design ribbon and move the chart to its own worksheet.
Next, we will take a look at column charts. A column chart shows multiple series of data
so that we can compare different categories of data.
Let’s take a look at the different options provided when it comes to column charts.
When talking about column charts, and in a moment line charts, it is very important that
we understand the difference between clustered charts and stacked charts. A clustered chart
“compares values across categories” while a stacked chart “compares the contribution
of each value to a total across categories”. Generally, we want to avoid stacked charts
unless we are sure that the stacked chart makes sense with the data we are using. In
many cases, using a stacked chart will simply not make sense because the data in the same
category does not add up to a meaningful total. A bar chart is similar to a column chart,
except the shapes are horizontal instead of vertical.
Let’s talk about line charts. Line charts show trends over time, so line charts are
both much more useful and much more complicated. Let’s say that we want to start a home business
making pies. The first thing we need to do is buy an oven.
Let’s say our oven costs $600. This is our fixed cost.
Then we figure out that for each pie we make, we will be spending $4 on ingredients.
This is our variable cost. Finally, we will sell our pies for $10 each.
This is our sales price. We want to see the trend in sales cost and
where we might start making a profit. We will set up some data for Volume, Sales
Value, Variable Cost, and Total Cost. Our Volume will start at zero units.
Our Gross Sales will be our Sales Price multiplied by our Volume.
Because we will want to use the fill handle to duplicate this formula, we turn the cell
reference for Sales Price into an Absolute Reference, by pressing the F4 key to insert
dollar signs in front of the column and row. Our Variable Cost will be Variable Cost multiplied
by our Volume, and again, we will make our Variable Cost cell reference an absolute cell
reference by pressing F4. Our Total Cost will be our Variable Cost added
to our Fixed Cost, and again, we will make our Fixed Cost an absolute cell reference.
Let’s increment the Volume row by 100 and use the fill handle to fill in the rest of
the data. Once we have extended our data up to a volume
of 600, we will chart the data. We want to avoid selecting the Volume row
for now, but we will come back to this in a moment.
Our chart looks good. Our vertical axis is labeled with the amount
of money involved, but our horizontal access is not clearly labeled. Let’s fix that now.
We can select the horizontal axis and right click, and then click on Select Data.
On the left, is our series data. If we edit our series, it shows an Edit Series dialog
box where we can change the series label and the series values. As we click into these
text boxes, we can see which cells are selected to be the series label or values. Let’s
cancel out of this Edit Series dialog box. Let’s look at the Horizontal Axis Labels
and click on Edit. Instead of having the labels say 1, 2, 3,
etc., we want the axis to be labeled with our volume amounts, so we will select those
cells and click on OK. We can see the change immediately in our Select
Data Source dialog box and on our chart. Let’s click on OK and keep these changes.
We might want to change the formatting of our horizontal axis.
For example, let’s say we don’t like how the 0 on the horizontal axis is not flush
with the bottom left corner. We can fix this by right clicking on the horizontal
axis and selecting Format Axis. We can then position that axis on the tick
marks and click on Close. Let’s take a look at our chart.
The important thing about line charts is to show trends.
For example, from this line chart we can tell when our business looks like it might become
profitable, because we can see the line for Gross Sales cross the line for Total Cost.
Let’s label this point on the chart with a shape.
We can click on the Insert Ribbon, click on Shapes, and select a Down Arrow Callout box.
We can position it over top of the point on our chart where we break even and call this
the breakeven point. When we have the shape selected, as soon as
we start typing, we will be typing text into that shape.
We can format our shape by selecting the shape and clicking on the Drawing Tools Format ribbon
and selecting a shape style. We can format our chart by selecting the chart
and clicking on the Chart Tools Design ribbon and selecting a chart style.
Let’s take one last look at stacked charts. The important feature of our current chart
is that we can see the trend between gross sales and total cost.
If we were using a stacked chart, we would not see this trend and our lines our never
cross. This is why we want to make sure we only use
stacked charts when we are sure this style of chart is adding to our comprehension of
the information, and not detracting from it.
When creating a chart, it is important to select the right chart for the right job.
We will be talking about pie charts, column charts, and line charts, but Excel offers
many different kinds of charts beyond these three types.
A pie chart shows a single series of data, showing us the percentage each piece of the
data is of the whole. We will start by entering some data. If you
would like, you can download this file with the data already created from the link on
this video. We can select the data, click on the insert
ribbon, and click on pie, and select one of the pie types.
Let’s take a look at some of the ways we can manipulate this pie chart.
We can click on any pie slice and drag it away from the rest of the pie.
We can right click on a pie slice and add a data label.
We can right click on our new data label and format the data label, adding a category name
or value. As we are working with raw numbers, we can
show the percentage or the actual data value. Notice the leader line option?
If we close this dialog box and drag a data label away from the pie, we will see a leader
line connecting the label and the related slice.
When dragging these labels, we want to make sure our cursor has changed to the four directional
arrows of the move cursor before we click and drag.
We can go to the Chart Tools Layout ribbon and add a chart title.
We can click on the Chart Tools Design ribbon and change the style of our chart
…or select a different type of chart, such as a three dimensional pie.
Like every aspect of our charts, we can manipulate individual elements, such as right clicking
on the pie and changing the 3D rotation. We might decide that we do not want the chart
on the same sheet as the data, so we can click on the Move Chart icon in the Chart Tools
Design ribbon and move the chart to its own worksheet.
Next, we will take a look at column charts. A column chart shows multiple series of data
so that we can compare different categories of data.
Let’s take a look at the different options provided when it comes to column charts.
When talking about column charts, and in a moment line charts, it is very important that
we understand the difference between clustered charts and stacked charts. A clustered chart
“compares values across categories” while a stacked chart “compares the contribution
of each value to a total across categories”. Generally, we want to avoid stacked charts
unless we are sure that the stacked chart makes sense with the data we are using. In
many cases, using a stacked chart will simply not make sense because the data in the same
category does not add up to a meaningful total. A bar chart is similar to a column chart,
except the shapes are horizontal instead of vertical.
Let’s talk about line charts. Line charts show trends over time, so line charts are
both much more useful and much more complicated. Let’s say that we want to start a home business
making pies. The first thing we need to do is buy an oven.
Let’s say our oven costs $600. This is our fixed cost.
Then we figure out that for each pie we make, we will be spending $4 on ingredients.
This is our variable cost. Finally, we will sell our pies for $10 each.
This is our sales price. We want to see the trend in sales cost and
where we might start making a profit. We will set up some data for Volume, Sales
Value, Variable Cost, and Total Cost. Our Volume will start at zero units.
Our Gross Sales will be our Sales Price multiplied by our Volume.
Because we will want to use the fill handle to duplicate this formula, we turn the cell
reference for Sales Price into an Absolute Reference, by pressing the F4 key to insert
dollar signs in front of the column and row. Our Variable Cost will be Variable Cost multiplied
by our Volume, and again, we will make our Variable Cost cell reference an absolute cell
reference by pressing F4. Our Total Cost will be our Variable Cost added
to our Fixed Cost, and again, we will make our Fixed Cost an absolute cell reference.
Let’s increment the Volume row by 100 and use the fill handle to fill in the rest of
the data. Once we have extended our data up to a volume
of 600, we will chart the data. We want to avoid selecting the Volume row
for now, but we will come back to this in a moment.
Our chart looks good. Our vertical axis is labeled with the amount
of money involved, but our horizontal access is not clearly labeled. Let’s fix that now.
We can select the horizontal axis and right click, and then click on Select Data.
On the left, is our series data. If we edit our series, it shows an Edit Series dialog
box where we can change the series label and the series values. As we click into these
text boxes, we can see which cells are selected to be the series label or values. Let’s
cancel out of this Edit Series dialog box. Let’s look at the Horizontal Axis Labels
and click on Edit. Instead of having the labels say 1, 2, 3,
etc., we want the axis to be labeled with our volume amounts, so we will select those
cells and click on OK. We can see the change immediately in our Select
Data Source dialog box and on our chart. Let’s click on OK and keep these changes.
We might want to change the formatting of our horizontal axis.
For example, let’s say we don’t like how the 0 on the horizontal axis is not flush
with the bottom left corner. We can fix this by right clicking on the horizontal
axis and selecting Format Axis. We can then position that axis on the tick
marks and click on Close. Let’s take a look at our chart.
The important thing about line charts is to show trends.
For example, from this line chart we can tell when our business looks like it might become
profitable, because we can see the line for Gross Sales cross the line for Total Cost.
Let’s label this point on the chart with a shape.
We can click on the Insert Ribbon, click on Shapes, and select a Down Arrow Callout box.
We can position it over top of the point on our chart where we break even and call this
the breakeven point. When we have the shape selected, as soon as
we start typing, we will be typing text into that shape.
We can format our shape by selecting the shape and clicking on the Drawing Tools Format ribbon
and selecting a shape style. We can format our chart by selecting the chart
and clicking on the Chart Tools Design ribbon and selecting a chart style.
Let’s take one last look at stacked charts. The important feature of our current chart
is that we can see the trend between gross sales and total cost.
If we were using a stacked chart, we would not see this trend and our lines our never
cross. This is why we want to make sure we only use
stacked charts when we are sure this style of chart is adding to our comprehension of
the information, and not detracting from it.