Creating a stacked column chart with 2 axes.

[David Hall]

I am working within Excel 97. I need help creating a stacked column
chart with 2 axes. An example of the source data that I'm using
appears on a worksheet as follows:

GROUP SERV_TYPE YEAR_QTR #CLAIMS
CONTROL AMB 2001Q1 96
....
CONTROL HOSP 2001Q1 68
....
CONTROL DRUG 2001Q1 32
....
EXPERIMENTAL AMB 2001Q1 99
....
EXPERIMENTAL HOSP 2001Q1 27
....
EXPERIMENTAL DRUG 2001Q1 68
....

My goal is to use the values for "YEAR_QTR" as the category (x) axis
labels and show (3D) stacked bars for both the CONTROL and
EXPERIMENTAL groups side by side for comparison. The bars themselves
should be stacked in a manner that will illustrate the proportion of
claims for each quarterly time period that are AMB/HOSP/DRUG for both
the CONTROL and EXPERIMENTAL groups, respectively.

Any suggestions would be much appreciated.

Thank you,
David

 

[Jon Peltier]

David -

Set up your data like this (I repeated the same data to represent the
next quarter's values):

AMB DRUG HOSP AMB DRUG HOSP
a a
2001Q1 CTRL 96 32 68
EXPT 99 68 27
a a
2001Q2 CTRL 96 32 68
EXPT 99 68 27
a a

Note the rows that are blank except for "a" in the first two columns.
Also note that the first two cells in the header column (with AMB, DRUG,
etc.) are blank cells. In the real data range, use a space character
instead of "a", but don't leave the cells blank. Having a text character
there tells Excel how to do the axis labels.

Make a stacked column chart. You will have different stacks above CTRL
and EXPT, with a wider space between each quarter, because of the
blanks, which plot here as zeros. Double click one of the series, and on
the Options tab, set the Gap Width to zero. The X axis labels look like
this:

------------------------------------------
CTRL EXPT CTRL EXPT
2001Q1 2001Q2

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