Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
Impedding/Overlaying Charts | Charts and Charting in Excel | |||
Combining Data on a pie chart | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel | |||
Why do my text boxes disappear from my chart when I click out? | Charts and Charting in Excel |