You
know the saying: “A picture is worth a thousand words.” In most
cases, an appropriate pictorial representation—aka
graphs—of data can be even more valuable than the actual data since it can help
a manager spot trends that might otherwise go unnoticed. As such, graphs can go
a long way toward helping a business make better decisions about the future.

In
this article, I’ll use the budget spreadsheet example I’ve been using for this Excel data analysis series, but only for a specific
example. In order to provide a wide overview of the different kinds of graphs
available, I’ll also be using another data set that lends itself well to a
specific type of graph.

In
part two of the graphing discussion, I will show you how to create a
PivotChart, a graph-based PivotTable and how to create 3-D graphs.

This data analysis series consists of these articles:

The reason

As
you’ve seen in the previous parts in this series, Excel gives you a lot of ways
to organize and present your data. However, when it comes to simply presenting
data, nothing can send a more powerful message than a well-designed graph. For
example, when a manager looks at the figures $10,000 and $100,000, he knows
that they are very different, but when faced with a bar chart that has one line
ten times longer than the other, the message simply hits home a little harder.

Take
the information shown below in Figures A
and B. In Figure A, I’ve provided a budget breakdown by expenditure category.
Suppose, for this example that upper management wants to see this information
for the upcoming budget review cycle.

Figure A
A list of budget information in a raw form. Notice
the use of subtotals.

Along
with a little prose, this information would easily convey what you want it to
convey. But, look at Figure B.

Figure B
The same information, but in graph form.

The
first thing that this chart screams at you is that payroll costs are a huge
percentage of the budget. Beyond that, it gives you an at-a-glance look at
exactly where your money is going.

Graphs aren’t always useful

When
used correctly, a graph can be a very useful tool. When used incorrectly, a
graph could work very much against you. I can offer two tips to help you with
this:

  1. Always use the right kind of graph (bar, pie, line,
    etc)
  2. Take the time to format the graph so that it has the
    greatest impact on whatever you’re trying to present.

For
example, in Figure B, I
intentionally rotated the graph so that the Payroll information would be front
and center. If I was presenting a case to senior management for reducing the
salary line, this would have the best impact as opposed to having it less of
the focus.

Take a
look at Figure C. This is a great example
of how not to create a graph. This
graph is a pie chart breakdown of the Administration department’s total
expenditures for each of three months. This information would be best left in a
table!

Figure C
This graph tells you very little, and actually confuses the issue.

Graphing

Whenever
possible, I like to show you how to do things with reasonable examples rather
than with boring “now do this, now do this” kind of stuff. To that
end, for this first example, we’ll use a small personal expense spreadsheet to
create a couple of different kinds of graphs. A look at the data behind the
upcoming graphs is shown in Figure D.

Of
course, for such a small data set, a graph isn’t going to provide mind-blowing
insight. However, this small data set with provide fodder for examples that can
be easily applied to larger sets of information.

Figure D
This is a snapshot of a personal expense budget. What you learn using this
small sample can be easily applied later to a larger workbook.

A look at trends

With a
graph, trend analysis becomes a simple at-a-glance task. For example, suppose
you want to get a look at a specific trend for a single item on your list to
see what kinds of ups and downs you’ve had. As an example, let’s look at the
total gas expenditures for each month and plot them on a line graph. Why a line
graph? A line graph is the best kind to use when you are interested in getting
a look at trends. Figure E shows you
a sample graph based on the sample data shown in Figure D.

Figure E
This chart shows you very quickly that gas prices skyrocketed and are
coming back to earth.

You
might be asking yourself why you would care about a graph like this. Consider
this: Suppose you work for a shipping company. Gas prices factor very heavily
into how you do business. By analyzing a trend like this, you can attempt to
nail down the root cause of an increase and, if possible, take steps to correct
it. In this case, the higher gas prices would be totally out of your company’s
control. Based on this kind of information, you could reasonably make a sound business
decision to increase your delivery fees to cover your increasing costs.

Now,
I’m going to provide the very quick steps you take to create a graph:

  1. Select the data, including row and column headings,
    that you want to appear on the graph. Omit what you don’t want. You can
    select multiple areas by holding down the Control key on the keyboard while you make
    your selection. Alternatively, you can just start the chart wizard and
    provide this information as a part of that process, but I find that more
    cumbersome, personally.
  2. Click the chart button on the Standard Toolbar (Figure F).
  3. Select the type of graph you would like to create.
  4. Follow the instructions in the rest of the graphing
    wizard to complete your graph.
  5. Manually format your graph to provide the most impact.

Figure F
Use the chart button after you’ve selected the information you want to
graph.

Step-by-step

I walk
step-by-step through the procedure I used to create the graph you saw in Figure E.

First,
in order to get just the headings and the data I wanted, I selected cells A1
and A3, and the ranges C1:H1 and C3:H3, as shown below in Figure G. I selected the row and column headings as a part of this
process since I want them to show up in the graph. Notice that I skipped
selecting the category field. I don’t need it for this chart.

Figure G
I selected the cells highlighted in blue.

Once I
selected the cells I want to chart, I clicked the Chart Wizard button I showed
you in Figure F. This button opens
the Chart Wizard, which consists of four steps. Step 1 is shown below in Figure H. On this step, pick the kind
of chart you want to create. I opted for a line chart.

Figure H
The first step asks you to choose the kind of chart you want to create.

The
second step of the chart wizard asks you to select your source data. Since I
did that before I started the chart wizard, all of the information is already
filled in. I find it much easier to
select my data before I start the chart wizard than I do to use this screen,
shown to you in Figure I.

Figure I
We’ll take a closer look at some options on this screen later on.

Step 3
of the wizard asks you for a whole lot of information, including axis titles,
gridline display information, whether or not you want to see the legend, what
you want to display on each data point, and whether or not you want to display
the source data with the chart. In the example I showed you in Figure E, the only change I made at
this step was on the Data Labels tab, where I enabled the display of the dollar
value of each data point. See Figure J
for a look.

Figure J
In the Label Contains box, by enabling the check box next to Values, the
actual source value for each data point is shown on the chart. You can see the
result in the mini-sample chart in this window.

Your
final decision to make is an easy one. Do you want the chart to get its own
sheet in your workbook, or do you want to add the chart to an existing sheet in
your workbook. For this example, I’ve opted to create a new sheet for the chart
as you can see in Figure K.

Figure K
A new chart can be added to an existing worksheet, or you can segregate it
off onto its own sheet.

The
result of these choices is not exactly
what you see in Figure E, though. In
fact, take a look at Figure L to see
the direct results of the chart wizard.

Figure L
The end of the chart wizard is just the beginning of your charting
adventure.

I
don’t particularly like the base result from the wizard, actually. The data
line is not pronounced enough, and the Y-axis scale places the results very
high on the chart. It doesn’t provide as much of an impact as it could with a
more refined scale. So, I’ll change it!

First,
I’ll change the Y-axis scale so that the hills and valleys in the graph are
more pronounced. To do this, right-click any value on the Y axis (up and down
axis), and choose Format Axis from the shortcut menu. This opens
up a dialog box that provides you with the ability to completely customize the
look and feel of the Y axis (See Figure
M). You can change the scale, the font, how the tick marks are displayed
and much more. For this example, I will just change the scale. I will make the
minimum value 100 and the maximum value 200.

Figure M
By changing these values, the graph will stretch to incorporate more of the
window.

The
result is shown in Figure N.

Figure N
Note that the graph is now stretched out more, but the line is still a
little ugly.

In Figure E, you’ll notice that the data
line is rounded and a little thicker. To change the look of the data line
itself, right-click the line and choose Format Data Series from the shortcut menu. If you get the option “Format
Data Labels” instead, move your mouse away from a data point and then
right-click the line.

For
the look you saw in Figure E, I
changed the weight of the line, and turned on the “Smoothed line”
checkbox on the Patterns tab in the Format Data Series dialog
box, as you can see in Figure O.

Figure O
Change the values on this screen to modify the look of the data line
itself.

The
end result: The chart that you saw in Figure
E.

If the
point of a line chart is to provide trending information, why not add that
information right to the chart. In Figure
P below, I’ve added what’s called a “moving average” trend line
based on an average of the previous two months of data. This kind of trending
is much more useful with more data, but the example in Figure P at least shows you how it works.

To add
your own trend line to chart, right-click the data line and choose Add Trendline. You will also notice that I made
some other cosmetic changes to the graph, including changing the background
color. To change a chart’s background color, right-click somewhere on your
chart away from your data point and data lines, and choose the Format Plot Area option from the resulting shortcut menu.

Figure P
A view with a trendline and
some other changes.

Definitions of charting terms

You
might think this section is out of place, but it’s not. I wanted to wait until
you saw an example before I sprung a bunch of terms on you. Rather than just
define terms, I will provide you with a definition and use the previous example
to help you get a better feel for the term.

  • Category Name: A label on the X axis, also known a
    tick-mark label. In the previous example, the months are the category names.
  • Data Label: Refers to a label identifying each
    individual data point on a chart. In this example, the dollar values for each
    point on the data line are Data Labels.
  • Data Points: A Data Point is a value from a
    worksheet cell that is displayed on the chart. There are six data points on the
    chart in this example.
  • Data Series: A related group of Data Points,
    such as the yellow line shown in Figure
    P    .
  • Explode: You can see exploding in action in Figure B. In this pie chart, the pieces
    of the pie do not join together to create a circle, but appear to be exploded
    away from the center of the pie chart. Exploding a pie chart can help to make
    it more readable.
  • Gridline: The lines across the charting plot
    area which make it easy for the eyes to follow back to the Y axis.
  • Legend: A box on the chart, such as the one
    to the right of the chart in Figure E,
    which explains what each line on the chart refers to.
  • Nonadjacent Selections (also called discontiguous selections): A nonadjacent selection happens
    when you select data from a worksheet that is not immediately next to other
    data you select. For this example, I used a nonadjacent selection, as you saw
    in Figure G.
  • Plot Area: The big green space in Figure P! The area in which your chart
    with be created.
  • Series Lines: The lines between stacked columns
    that connect the top of each series within the stack. Can be used on stacked
    bar charts. To set, click the appropriate chart type group on the Format menu, and then click to select the check box on the Options tab.
  • Tick-mark: Indicators on each axis that mark
    off the scale or category, or separate series groups on the X axis. In Figure P, notice the small black
    horizontal lines between each number on the Y axis. These are tick marks.
  • Trend line: An optional addition to a chart
    that may help you to determine trends based on your data. Figure P shows you a trend line in action.

What’s next

In the
next, and final, part of this series, you will learn how to create and
manipulate 3-D graphs and PivotCharts.