Combining Information in a Bar Chart

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.

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.

----
Regards,
John Mansfield
http://www.pdbook.com


"B. Scace" wrote:

> 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.

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.

"John Mansfield" wrote:

> 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.
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "B. Scace" wrote:
>
> > 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.

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/
_______

B. Scace wrote:

> 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.
>
> "John Mansfield" wrote:
>
>
>>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.
>>
>>----
>>Regards,
>>John Mansfield
>>http://www.pdbook.com
>>
>>
>>"B. Scace" wrote:
>>
>>
>>>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.

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.

"Jon Peltier" wrote:

> 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/
> _______
>
> B. Scace wrote:
>
> > 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.
> >
> > "John Mansfield" wrote:
> >
> >
> >>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.
> >>
> >>----
> >>Regards,
> >>John Mansfield
> >>http://www.pdbook.com
> >>
> >>
> >>"B. Scace" wrote:
> >>
> >>
> >>>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.
>
>