Double Stacked Bar with Line Chart

G

Guest

Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart. I've
managed to create a double stacked bar chart, but I can't get a line chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis), you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| <-- this would be the line chart

Hope that helps. I'd appreciate any suggestions on how this could work.

Thanks,
Jay
 
J

Jon Peltier

It's really pretty easy, if you forget what you think you know about making
charts.

In my sample, I have this data in A3:H6, where denotes a blank cell and
indicates a cell with a single space character:

Jan-07 Feb-07
Budget Actual Budget Actual
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu > Paste Special to
add the data as a new series, series in rows, first row has categories and
first column has series names.

This adds another column to the stack, not at all where you want it. Select
the added column series, go to Chart menu > Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab, choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I had
placed in cells K5 and L5.

- Jon
 
G

Guest

I must be doing something incorrectly.

My line chart appears, but in category 1 and 2. It won't cross multiple
categories.

Does your line chart cross multiple categories?

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


Jon Peltier said:
It's really pretty easy, if you forget what you think you know about making
charts.

In my sample, I have this data in A3:H6, where denotes a blank cell and
indicates a cell with a single space character:

Jan-07 Feb-07
Budget Actual Budget Actual
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu > Paste Special to
add the data as a new series, series in rows, first row has categories and
first column has series names.

This adds another column to the stack, not at all where you want it. Select
the added column series, go to Chart menu > Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab, choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I had
placed in cells K5 and L5.

- Jon
Click to expand...
 
J

Jon Peltier

Don't make a line chart of the last series, make an XY chart.

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


Jay said:
I must be doing something incorrectly.

My line chart appears, but in category 1 and 2. It won't cross multiple
categories.

Does your line chart cross multiple categories?

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


Jon Peltier said:
It's really pretty easy, if you forget what you think you know about
making
charts.

In my sample, I have this data in A3:H6, where denotes a blank cell
and
indicates a cell with a single space character:

Jan-07 Feb-07
Budget Actual Budget Actual
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu > Paste Special to
add the data as a new series, series in rows, first row has categories
and
first column has series names.

This adds another column to the stack, not at all where you want it.
Select
the added column series, go to Chart menu > Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab,
choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the
budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I
had
placed in cells K5 and L5.

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


Jay said:
Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping
someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart.
I've
managed to create a double stacked bar chart, but I can't get a line
chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis),
you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead
of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going
to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| <-- this would be the line
chart

Hope that helps. I'd appreciate any suggestions on how this could
work.

Thanks,
Jay
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That was exactly my issue. Read what I wanted and not what you wrote.

That awesome, thanks! I really appreciate it. There's no way I would have
figured that out myself.

Cheers,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


Jon Peltier said:
Don't make a line chart of the last series, make an XY chart.

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


Jay said:
I must be doing something incorrectly.

My line chart appears, but in category 1 and 2. It won't cross multiple
categories.

Does your line chart cross multiple categories?

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


Jon Peltier said:
It's really pretty easy, if you forget what you think you know about
making
charts.

In my sample, I have this data in A3:H6, where denotes a blank cell
and
indicates a cell with a single space character:

Jan-07 Feb-07
Budget Actual Budget Actual
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu > Paste Special to
add the data as a new series, series in rows, first row has categories
and
first column has series names.

This adds another column to the stack, not at all where you want it.
Select
the added column series, go to Chart menu > Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab,
choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the
budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I
had
placed in cells K5 and L5.

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


Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping
someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart.
I've
managed to create a double stacked bar chart, but I can't get a line
chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis),
you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead
of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going
to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| <-- this would be the line
chart

Hope that helps. I'd appreciate any suggestions on how this could
work.

Thanks,
Jay
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

3D stacked bar and line chart on same graph 2
Add line series to stacked bar chart 1
Stacked chart with line 1
How to create a stacked bar, line combination chart 2
Double-stacked Bar Chart WITH a Secondary Y Axis Line chart? 1
2 axis chart stacked bar next to bar with line 1
how can create stacked column chart join with line 1
Combination Stacked Bar with Single Bar Chart 2

Top