Replicating the Clustered Stacked Column Charts by Peltier.

[e4excel]

Replicating the Clustered Stacked Column Charts by Peltier.

Dear Experts,

I am referring to 2 specific charts on which I need assistance on as I find them extremely useful in my work however I am unable to completely impersonate( replicate) them..

The Most Famous Clustered stacked column charts by Jon Peltier and an additional approach by Imp in this thread mentioned below..

http://peltiertech.com/WordPress/clu...mn-bar-charts/

http://www.excelforum.com/excel-char...ndary-bar.html

I just need some guidance as I have hit a roadblock in creating these charts..

1. Peltier's - Clustered Stacked Column Chart..

I have made the changes in the Tabular Arrangement to get Clustered Stacked Column Chart and that has already taken shape however the part of Adding the labels is not happening as I am not getting the same options as mentioned in the website for modifying the lables..

Adding the Labels

Almost done. We need to add the category (cluster) labels. We’ll do this by adding a “dummy” series to the secondary axis, and the secondary axis will have the category labels we want. Add a column to the original data range for the dummy axis series (column F in our example).

Image

CS_Data_06.png


Select this added data (F1:F4), and hold Ctrl while selecting the column with our labels (A1:A4), so that both areas are highlighted. Make sure you include the blank top cell in the first column. Copy the range, and use paste special to add this data to the chart as a new series, in columns, with series name in the first row and category labels in the first column. In other words, use these settings:

CS_PasteSpecialChart.png

Hope this helps..

Nevertheless, I am uploading my file which shows the Chart with the Changed Data but with no labels like Coffee Tea and Milk in the Final Chart..

So how do i get that..
I dont get the Paste Special Window as shown in the Website..


2. For imps Chart too, i am unable to add the X-Axis Lables below...and also need to know how to get rid of those Series1,2,3,4, legends in the Right Side..

Warm Regards
e4excel

[e4excel]

Please excuse, in case the Images did not come up as I have tried Inserting a Image for the very first time..

Thanks...

[e4excel]

Can someone please guide me on this..just need to know how to get those labels..

[Andy Pope]

in both your charts you have not set a reference for the category labels so the chart defaults to using 1,2,3 etc.

Use Select Data dialog to set range for category labels.

[e4excel]

in both your charts you have not set a reference for the category labels so the chart defaults to using 1,2,3 etc.

Use Select Data dialog to set range for category labels.

Dear Andy,

I was following the instructions from the website and unable to follow the Paste Special..
Per your advice , I go in Select Data Dialog and then what Do I do I tried that and I get the Coffee Tea Milk option for a single pair of Stack and not for different Stacks as the way shown in the website

[Andy Pope]

I don't know why you can not follow the instructions

See area in yellow upon which the chart is based. Select area and create stacked column.
Set series with to 100.

Replace c,t and m labels with space.

[e4excel]

Thanks ANdy, that was helpful..I could manage the Chart in the 2nd Sheet Imp Chart however for the Peltiers Char I am able to select the data with your help however there's a small problem the Text Label on the X-axis is Tilted and when i tried aligning that the middle Series "Tea" goes off..

So though it is there it becomes invisble..

It is not happening the way its shown in the website..

And whats the THUMBRULE for placing these Lables in the Column A..

Should the first label be in the same ROW as the DATA or one ROW above ?

Thanks in advance

[Andy Pope]

multl level labels use the empty spaces to determine start/finish of a group within a level.

So if you copy Coffee down over the 4 rows you get 4 Coffee labels. It does not detect repeating text.

Just play with the cell content to see impact on chart.

[e4excel]

multl level labels use the empty spaces to determine start/finish of a group within a level.

So if you copy Coffee down over the 4 rows you get 4 Coffee labels. It does not detect repeating text.

Just play with the cell content to see impact on chart.

I have tried that in Vain I could get all the respective labels however they appear TILTED in the basic-raw state and when I tired to format the ALignment the middle TEA label vanished.. so i am a loss for ideas now..really

[Andy Pope]

post your latest workbook as I'm not seeing any tilted labelling.

[e4excel]

post your latest workbook as I'm not seeing any tilted labelling.

See now with your help I got those labels however they are tilted and I need them to be HORIZONTAL and when I try doing that one label goes missing..

I think im facing problem with both Charts now, even in the Imp's Chart now I have got the Names but no Lines in between as shown in the original...thread..

I could tweak in the file you had made and in my own file for even the Imp Chart Im afacing issues now


Please guide me for the same..

Thanks in advance

e4excel

[Andy Pope]

This is my last post on the matter as you do not seem to be listening.

I have again changed your data to the requried data layout.
I have enter text for the inner labels that you need to replace with a space in order to complete the chart. The text is there so you can see ALL the information used in the chart.

If you do not want the inner down lines then remove tickmarks.

No text was tilted in the file you posted.

[e4excel]

This is my last post on the matter as you do not seem to be listening.

No Andy, It was not that, I was not listening, I was not understanding that and I did not know anything about TICK MARKS..

I am glad that now I understood I had to replace that extra column with just a space character and then remove the Tick Marks

No text was tilted in the file you posted.

In the first Chart-Clustered-Stacked Column Chart though, I managed to reach a stage of getting the labels, but the labels are tilted in a 45 degree angle i.e. Coffee Tea Milk and when i try to format them some labels just disappear..

So that is still different from whats shown in the Website...


But now i got it I have to follow the same procedure for both the Charts...

So dumb of me... So sorry for the trouble but now I understood what u meant..

Thanks a lot...

[e4excel]

Though ANdy i managed to get the labels but why is it still different than shown in the Peltiers website..I mean this statement which mentions the instructions about getting the labels are completely different..

http://peltiertech.com/WordPress/clu...mn-bar-charts/

Select this added data (F1:F4), and hold Ctrl while selecting the column with our labels (A1:A4), so that both areas are highlighted. Make sure you include the blank top cell in the first column. Copy the range, and use paste special to add this data to the chart as a new series, in columns, with series name in the first row and category labels in the first column. In other words, use these settings:

Then that window which is shown I never got that small window for paste special...

Though the results do come similar there are not Lines like Coffee | Tea | Milk |

So thats still startling..