3 different data ranges to chart on one graph

I have a graph I am trying to create that in essence has
three different types of data in it, but they are all
associated.

I am charting 2000-2005 information. I would have 7
columns of data

Year / Spending / %change from prior year / Segments / %
change / Project counts / %change from prior year

For example this would be what 2001 would look like...
2001/1,500,000/56%/22,000,000/-34%/16,000/84%

So my numbers are all over the scale. Can I do this?
Any charting advice would be greatly appreciated.

Hi Donna -

One of the principles of effective data presentation, is to keep it
simple. That is an awful lot of information, of vastly different value
and "type" (i.e., values vs. percentage change). I think that I would
avoid putting it all onto one chart, and break it up in one of two ways.
(I know that sometimes it's hard to convince the boss, but you have to
try.) Otherwise it will take viewers a long time and a lot of mental
energy to figure out the complex chart.

Here's my preferred way. Make three charts, each one showing one of the
values, paired with the corresponding percentage change. Start each by
making a clustered column chart with both the value and the percentage.
The percentage is tiny compared to the value, but we'll adjust that.
First select the percentage series, and from the Chart menu, select
Chart Type, and pick a Line chart type. Look at that, a combination
chart, and you did it yourself. Select the new line chart, choose
Selected Data Series from the Format menu, and on the Axis tab, choose
Secondary. Adjust the two Y axes to improve the appearance of the chart.

For example, if you have some negative percentages, the X axis should
cross the secondary Y axis somewhere between its endpoints. Pick min and
max values for the secondary Y axis that look okay, then choose min and
max values for the primary Y axis which are proportional to these. Say
the percentage axis goes from -5% to 20%. That means the min is -1/4 of
the max. So if the max of the primary Y axis is 40 Million, the minimum
is -1/4 of that, or -10 Million. Now the X axis lines up with both
zeros, and if you're clever with your tick spacing, the gridlines will
line up with both sides. Now, the X axis and the gridlines are both
black lines, too confusing. Double click the plot area, give it a black
outline, and a white fill, not that ugly gray Microsoft likes. Double
click the gridlines, and change their color to the lightest gray. What
an improvement. Double click the X axis, and on the Patterns tab, select
Low for the tick label position, so the labels aren't hovering within
the plot area.

My second favorite way to plot this data would be a clustered column
chart of the values. Now I realize the numbers are way off scale from
each other. The smart thing would be to normalize them (fancy word for
divide them) by a certain reference point. I would use the first year's
values. This way, in the first year, the plotted value for each would be
100%, and the values would show the relative change since the first
year. To display the values, use data labels. But it's hard to get data
labels to show something other than the category or the value, and our
values have been normalized. But there are third party utilities to add
the data labels you want, based on what's in some other cells. Here are
two, both free, easy to install, and easy to use, and they work just as
if Microsoft remembered to add this feature in the first place:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

So use one of these to put the real values on the columns. Make a second
chart, maybe using a line chart again, for the percentage change.

As I said before, I think it's ill-advised to try to get all that
disparate data onto a single chart. Just because you CAN combine lots of
series and use lots of colors and wonderful formatting effects, doesn't
mean you SHOULD combine lots of series and use lots of colors and
wonderful formatting effects. If my boss still insisted I put them all
into one chart (and back when I had a boss, he just might have), I'd
probably just do the normalized clustered column chart I described just
above, with data labels that conveyed both the values and the percentage
change.

Another idea is to do the two chart method, one large chart with
clustered columns and values in the data labels, then make the line
chart with percentages much smaller, and drag it over a corner of the
larger chart. Look at that, a nice inset chart, just like in Time magazine.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Donna wrote:

I have a graph I am trying to create that in essence has
three different types of data in it, but they are all
associated.

I am charting 2000-2005 information. I would have 7
columns of data

Year / Spending / %change from prior year / Segments / %
change / Project counts / %change from prior year

For example this would be what 2001 would look like...
2001/1,500,000/56%/22,000,000/-34%/16,000/84%

So my numbers are all over the scale. Can I do this?
Any charting advice would be greatly appreciated.