In the
previous part of this series, you learned how to create and manipulate simple
graphs in Excel. In this part, you’ll delve a little deeper into graph creation
and will also learn how to use the graphing component related to Excel’s
PivotTable feature: PivotCharts.

This data analysis series consists of these articles:

The sample

For
many of the examples in this article, I will be using the data from the table
shown in Figure A.

Figure A
A fairly typical data set provides a good place to demonstrate Excel’s
graphing abilities.

More data views

With a
graph, the type of diagram you select is critical when it comes to getting your
point across. For example, if you wanted to diagrammatically show how much of
your monthly payment went toward rent, you would probably not use a line graph.
Instead, a pie chart would be more appropriate since it shows you slices of a whole.

In the
last article, we looked at line and pie charts. In this section, I’m going to
walk you through one of Excel’s pretty nifty graphing features: the 3-D bar
chart. With 3-D charts, your chart manipulation options expand to allow you to
actually rotate the chart to get exactly the view that you want.

For
this example, I’d like to create a bar chart that you can manipulate beyond
just changing the colors, text, and line types.

Remember
the basic steps for creating a graph are to select the data that you want to
appear on the graph and then click the Chart Wizard button. For this example,
since I’m going to use all of the data on the graph, I can just click anywhere
on the data table and click the Chart Wizard button.

In
Step 1 of the wizard, I have selected to create a 3-D Column chart, as you can
see in Figure B.

Figure B
The 3-D bar is a sub-type of a normal bar chart.

The
second step of the wizard asks you to select your data range and to determine
whether data is separated by columns or rows. For this example, I didn’t need
to select a range. For the series data, I chose “Rows” since I want
the category to be selectable (Figure C).
If I had chosen the columns option, the graph would be a little more difficult
to manipulate. This will make more sense in a few minutes.

Figure C
The data range matches my selection, and I want the “Rows” data
series.

On the
next screen of the wizard, I have the opportunity to provide chart and data
details. For this example, all I’ve done is to provide a title for the chart,
as shown in Figure D.

Figure D
This step provides fields for better explanation of what’s on your graph.

I’m
not going to show a screenshot for Step 4. This is the step that asks where you
want to place the new graph—on its own tab, or on the same sheet as the data. I’ve
opted to show the graph on its own tab in Excel. The resulting graph is shown
below in Figure E.

Figure E
This isn’t the best view of the data, but there’s a good reason.

This
graph isn’t perfect. It’s the right kind of
graph, but the data isn’t really the best for this kind of comparison. This is
mainly because of the way the bars a lined up and because of the large range of
values. The scale has to run from $40.00 all the way to $1,000.00, meaning that
there isn’t room to show minor variations in amounts. This graph would probably
be more useful if the rent series was omitted, for example. After all, the rent
isn’t usually a variable payment (and neither is the normal car payment, for
that matter), so placing it on a graph like this won’t provide you with a whole
lot of information.

But,
since the data series is set up as row data, each color specifies a category. Even
better, with a click of the mouse and a tap of the keyboard, I can eliminate a
series. For example, if I want to get rid of the rent and car payment series,
all I need to do is select one of the rent bars and hit the Delete key on the
keyboard. The same is true for the car payment.

Take a
look at Figure F, which omits the
rent portion of the chart. Likewise, since the car payment amount never
changes, I omitted that from the chart as well.

Figure F
With the removal of the two series, the graph looks quite a bit different.

This
still isn’t the best graph, but you’ll quickly notice that the differences from
month-to-month are much more pronounced now. This is largely because the scale
now tops out at $250 rather than $1,000. I’m going to show you an interesting
feature and then show you some ways that you can manipulate this graph to make
it more readable.

Hold
your mouse over one of the bars in the graph. A tool tip appears providing you
with information about that data point. See Figure G.

Figure G
The tool tip provides with you all of the information related to the bar.

Advanced graph manipulation

Previously,
I showed you how to perform some simple manipulation of your graph to be able
to help you make it look attractive and better reinforce the point you’re
trying to make. Now, we will move on to graph manipulation. Manipulating a 3-D
graph in Excel can be a little tricky, but once you do it, you’ll probably find
that it’s pretty neat, and potentially very useful.

Basically,
Excel gives you the ability to rotate a 3-D graph any way you like. To do so,
you need to click somewhere on the graph “wall” after which you can
manipulate the graph.

Figure H
The corner control is key to graph rotation
capability.

Now,
click one of the corner controls. You’ll notice that your mouse pointer changes
to a plus sign.

Figure I
It’s a little hard to see in this shot, but that is the plus icon control.

Now,
while holding down the left mouse button, drag one of the corner controls
around the screen. Notice that your graph rotates as you move the controls. While
you are dragging it around the screen, your graph appears to be a wire frame
instead.

Figure J
The wire frame helps to conserve computing resources. It takes a lot of
processing power to redraw the graph while you move it.

When
you’re done dragging, your graph looks completely different, and you can get
different views on the data.

PivotCharts

Up to
this point, the graphing options I’ve shown you have been handcrafted. That is,
you’ve had a data set, and decided what kind of graph to create, and then gone
ahead and put together a graph that met your needs. If you need to change something about that graph, you
need to look through a few screens of settings and find what you want to change.
And, if you want to add or remove a series, or other data, it’s not always as
straightforward as it should be.

Previously,
I went over the topic of PivotTables with you, and showed you how you can
harness their flexibility to quickly and easily analyze your data or create
useful reports. PivotCharts are the companion
graphing tool for PivotTables.

The
example screenshots I show you in this section will go back to using the budget
spreadsheet I used in earlier parts of this series.

Figure K
The budget spreadsheet is a fairly typical layout and provides a good
example.

Just like with a PivotTable, a PivotChart is started by
placing your cursor somewhere inside your data table and choosing Data |
PivotTable and PivotChart Report from the menu. This is where things change a bit. Under
“What kind of report do you want to create”, instead of selecting
PivotTable, select the “PivotChart report” option. (See Figure L)

Figure L
This will give you a blank chart to start with.

The
last two steps of the wizard are the same regardless of whether your just creating a PivotTable, or adding a chart. When you’re
done with the wizard, you’ll see a screen similar to the one shown below in Figure M.

Figure M
Here’s a look at your drawing board.

You
might notice that this looks a lot like a blank PivotTable sheet and, like that
sheet, this one has various places to which you drag fields to design your
report any way you like. (See Figure N)

As an
example, I’ll do the following:

  • Place the Month field into the “Category”
    area.
  • Place the Department field into the “Series”
    area.
  • Place the Amount field into the “Data Items”
    area.

Figure N
With three clicks and drags, I was able to whip up this chart.

Just
like with a normal chart, you can make all kinds of modifications to your
PivotChart. Just about everything available with a normal chart is available
here. For example, if you right-click one of the axes, you can format the text.
You can even add the source data table by right-clicking the chart background
and choosing Chart Options | Data Table | Show Data Table. Both of these
modifications are shown in Figure O.

Figure O
Change any option you like!

One of
the first things you might want to do is use a different type of chart. After
all, a bar graph isn’t always the best option. To change your graph type, from
either the PivotTable toolbar or the standard toolbar, click the Chart Wizard
button. Or, even easier, right-click the chart background and choose the Chart
Type option (Figure P) from the
resulting shortcut menu.

Figure P
You can change the PivotChart to any kind of chart you like.

For this
example, I’ll change the chart type to a 3-D pie chart. See the sample below in
Figure Q. I’ve also moved the
Department field to the Category slot, and removed the Month field completely. Why
did I remove the month field? A pie chart can’t break down both a category and
a series at the same time. By leaving the month field, the pie chart was being
based on just the first month’s worth of data. By removing the field, I was
able to get a pie chart that accurately reflected the total of each category
for all months.

Figure Q
Here’s a down-and-dirty pie chart.

Analysis tools

When
it comes to graphs, Excel can easily provide you with some outstanding options,
provided you don’t mind a little trial and error. Between PivotTables, PivotCharts, sorting, filtering and subtotals, you should
be able to analyze data in whatever way you like using Excel’s built-in tools.

This
also completes my Excel training series. However, if there is a topic that has
not been addressed, please let me know and I will write it. My next training
series takes on Word, and includes some Word/Excel integration topics.