• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine Column and Stacked Column Chart Both on Primary Axis

S

SteveT

New Member
Hi All,


All i have read is that you cannot combine a Column and a Stacked Column on the same Excel Chart. However, I have found an example of a chart that allows this and can't figure out how they do this.


At this link, if you select the "Ist" series and look at the chart type it is a regular column and if you select most other series like "Rot" (red in german) you will see that they are of a stacked column type. Finally, when you look at the "Format Data Series" dialog box, the "Plot Series On" selections are greyed out with Primary being chosen. look.


Link to File: http://www.hichert.com/downloads/software/540.xls


Is this a feature in the German version of Excel? Or is it a special add-in? Is it a previous version of Excel? Any thoughts are appreciated.


SteveT
 
This is not a combined column and stacked column... it is just a column chart. They add data labels (value, inside base) to represent the 'actual?' values. Then, any value from the 'actual' rows that is < or > than the 'plan'has a green/red arrow to show that it is up/down. the presentation of the dark versus light grey columns, the green/red arrows, the blue axes vs dashed grey axis are all manifestations of how the values are combined in the hidden rows (28-46).


If I'm missing something let me know.
 
I politely disagree. I can select the "Platzhalter" series and add a fill color of orange to it and it shows up. Also, if you change "AF6" to "Nein" you will get rid of the arrows. Also, when you select the "Platzhalter" series, and then check the "Change Chart Type", you will see that it shows up as a Stacked Column.
 
I understand your concern, however these are all different bar charts (ie platzhalter is separate from Ist.. the dark grey one), not one stacked chart. The arrows are a condition based upon rows 41-45 and just add a picture (tip of the arrow).


It's a pretty sophisticated use of overlapping bar charts.


I'll hang back and see if anyone else interprets this differently than me.
 
Oh... you are correct. I looked a little closer and they (platzhalter and Rot/Grun) are not separate charts, rather stacked as you indicated. wierd... not sure how it was done.
 
Thanks for your help. Glad you see what i see now. Now try this:


With this data:

Jan Feb Mar

Poor 10 12 11

Fair 9 8 8

Good 13 7 14

Actual 12 11 13

Target 25 25 25


1) In my efforts to recreate, I have gotten close. It involves creating a Stacked Bar Chart for Poor, Fair and Good from Jan to Mar.


2) Then copy Actual row from Actual to Mar, then select chart and then select Paste Special and then click okay. Change Series to Secondary Axis. Then Change Series to XY Scatter.


3) Then copy Target row from Target to Mar, then select chart and then select Paste Special and then click okay.


4) Then Select the Actual Series and change the Chart Type to Column (NOT Stacked). Then select the series and then change the Gap Width to Large Gap.


You will now notice that you cannot select the "Plot Series On" [Primary is Selected](greyed out). If you select the Stacked Bar Chart you will also notice that you cannot select the "Plot Series On" [Primary is Selected](greyed out). Futhur, you will notice that Actual is a Column chart type and that the Poor, Fair and Good series are still a Stacked Column type.


Has anyone seen this before?
 
Column A = Poor, Fair, Good, Actual, Target

Row 1 = Jan, Feb,Mar


Didn't translate well in the post


Column A,Jan,Feb,Mar

Poor,10,12,11

Fair,9,8,8

Good,13,7,14

Actual,12,11,13

Target,25,25,25
 
Got step one okay, but i am confused by what you mean "Then copy Actual row from Actual to Mar". Do you mean right click in chart and "select data", then replace the value for "Mar" in the horizontal axis with the range for the "Actual,12,11,13"?
 
If you use select data, then you won't be able to see the choices from the Paste Special dialog box.


So you must copy the new data series, then select the chart, then from the ribbon, select paste special and you will see a new dialog box i never saw before.


This is the only way to get this without the new data series also becoming a stacked chart. I think :)


I can't find any documentation in Microsoft or Excel about that Paste Special - Chart Dialog Box
 
The only place i have found this Paste Special option mentioned so far is on the Peltier site:


Search for Paste Special:


http://peltiertech.com/Excel/Charts/BoxWhiskerV.html?app=ZXL&ver=12
 
Finally found a description with pictures on the web of what i was talking about doing:


http://theclosetentrepreneur.com/how-to-add-a-vertical-line-to-an-excel-xy-chart
 
You must log in or register to reply here.
Back
Top