Combining Information in a Bar Chart

[Guest]

I'm using Office XP - Excel 2003.

I'm creating a bar chart by year. Each year has four separate values. The
values are LOI Received; LOI Invited; Proposals Received; Grants Awarded.

The LOI Invited value is a part of the total amount for LOI Received. For
example in 2004, we received 200 LOI's but we invited 150. I want my bar to
show the total of 200, but show the 150 carved out of the same bar that
indicates of the 200, we only invited 150. The same goes for Proposals
Received & Grants Awarded--of the total 265 proposals received, we awarded
215 from that total amount. Rather than having four separate bars per year,
I want only two bars--one for LOI information and one for Proposal/Grant
information.

I try using a stacked bar, but all four values become stacked on one bar.
How do I create a stacked bar, but select two value to stack in one and two
values to stack in the other, but still maintain all four values within the
same year. Your assistance is appreciated.

 

[Guest]

Assuming the title “LOI†is in cell B1 and “PR & GA†in cell C1, try setting
your data up like this:

LOI PR & GA
Invited 200 265
Received 150 215

Using the Chart Wizard, create a Clustered Column chart. Click once on the
second series of columns, right-click on your mouse -> Selected Object ->
Axis Tab -> Plot series on secondary axis. Then go to the Options Tab and
increase the gap width for the second series. When complete, you should have
two sets of columns with the Received column appearing inside of the Invited
column.

 

[Guest]

Thank you for this information. It worked perfectly. I have one further
question. Your response works for one year. How would I do this if I have
to display this same information for multi-years on one reports. Right now,
the LOI and PR&GA are my x-axis labels, but I need the x-axis to show Years
2002, 2003, and 2004 with this clustered information. Again, your assistance
is appreciated. Thanks.

 

[Jon Peltier]

Do your data like this:

LOI Inv LOI Rec Proposals Awards
2000 200 150 265 215
2001 220 165 292 237
2002 242 182 321 260
2003 266 200 353 286

Make a clustered column chart as John suggested. Put the LOI Rec and Awards series
onto the secondary axis (as John suggested), then delete the secondary axis. It
looks like a stacked clustered column chart, but it's easier this way.

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

 

[Guest]

Thank you for this information. Combined with the previous information, this
is exactly what I was looking for. My charts look great!! Thanks for your
assistance.