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.
