Double stacked column

[Adrae]

Is it possible in Excel to create a graph that puts two stacked columns side by side for comparison. For example, the first column would include a segment for product budget and a segment for services budget. The second column that would be right next to it would be stacked with product actual & product budget. There would then be 5-6 of these pairs to represent different sales channels say, or months. Does this sound possible?

Any help would be greatly appreciated.

Thanks Adrae

[jrnyman]

I think what you want is along the lines of a 3D stacked and clustered column chart. Only, I have no idea how to do it.

I don't know if it's possible, but it ought to be given that both clusters and stacks exist independently.

[Adrae]

Any ideas at all would be greatly appreciated.

Thanks

[PDuPre']

To make a double stacked bar. This is what I do. A bit difficult to explain without graphics.

A B
1 2
2 2
3 2
4 2
5 2
6 2
7 2
8 2
9 3
10 1
11 3
12 1
13 3
14 1
15 3
16 1

Column A here is the first column of data, such as the budget. Column B is the actual for that category. Numbers 1 - 16 are the different categories. 1 - 8 simply repeat for 9 - 16. You will only use the labels for 1 - 8. Labels for 9 - 16 will not be shown.

Highlight the data. Click on the Chart Wizard. Select stacked bar. and then hit Next. Select series in rows. You will now see two stacked bars. Make any other changes you wish and then hit finish.

You now have a stacked bar with 16 labels and the colors do not match. To delete labels 9 -16 click once on the Legend. Then click once on the label for 16. In this case "16". Now hit the delete key. It will delete the label, but not the graphed data. If you had clicked on the color and not the label then you would have deleted the link to the input data. Repeat for labels 9 - 16.

Now simply click on the second stacked bar and change the colors to match the first stacked bar exactly.

Now you can go back and put your data into the cell range that the graph is pulling from.

Graphs always seem to take a bit to set up, but once you have it created and finessed for the presentation save it in a manner that it can be easily reused.

Philip

[PDuPre']

Please note that the graph input data did not show correctly. The first column contains row labels of 1 - 16. The actual value in the cells are number 1 - 16. The second column is labelled A and the values in the first 8 rows (1-8) are 2. There are no values in the second 8 rows (9 - 16). The third column is labelled B and contains no values in the first 8 rows. The second 8 rows (9 - 16) contain the data which was 3 and 1 alternating. In this example two stacked bars will appear next to each other and be the same height (16) but have different sized bars stacked on top of each other.

Philip

[Adrae]

I need a bit more help. Here is an example of my data. What I want is two stacked columns side by side, one to represent product budget & services budget & the other to represent product actual & services actual. I tried to put the column letter and row numbers applicable below, but the cut and paste from Excel is pretty sloppy. There are 4 categories, 2 for each stacked column, and 12 months which should result in 12 pairs. I can't get it to work with this data scheme. I end up with sigle stacked columns with 4 segments. Any ideas? Thanks again

A B C D E F G H I J K L M
1 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 Dec-02
2 Product Budget 100 100 100 100 100 100 100 100 100 100 100 100
3 Product Actual 100 100 100 100 100 100 100 100 100 100 100 100
4 Services Budget 100 100 100 100 100 100 100 100 100 100 100 100
5 Services Actual 100 100 100 100 100 100 100 100 100 100 100 100




[ This Message was edited by: Adrae on 2002-04-24 13:00 ]

[PDuPre']

Using your data in rows across. Jan-2002 is in cell B1 and Dec-2002 is in M1. Then the labels repeat again from N1 to Y1. I put the Budget data in cells B2-M2 and B4-M4 respectively. I then put the Actual information in cells N3-Y3 and N5-Y5 respectively. The labels in N1-Y1 do not matter as they will not be shown on the graph, but it makes more sense to keep them the same as B1-M1. Now when you highlight A1-Y5 and click on the Chart wizard it will attempt to create the chart you want. Just select the Stacked Column and then Next. Now since your data is set up across instead of down choose Columns and then make any other changes you want. When you click Finish the graph should be made.
Now with 24 entries the legend may not show all the entries. You can increase the size of the legend to see all the entries if they do not show up in the default graph. Once you can see them all it will be easier to determine which ones you want to delete from the legend. Procedure same as my first message from here on out.

This graph is really powerful. I hope it works well for you.

Philip

[PDuPre']

OK. Now that I've re-read your question I may have misinterpreted what you are trying to accomplish.
Go ahead and follow my directions above to create the graph. Once created you will notice that there is a divider between each label. What you would want is two stacked bar columns per label.

OK. Difficult to explain.

Row 1 will be your main label. Starting in B1 will be January. Columns B&C will be January data. I centered across columns so the word January will be centered across the columns. Column B is Budget and Column C is Actual. These labels are shown in B2 and C2 respectively.
Row 3 is Product Budget and row 4 is Product Actual. Row 5 is Services Budget and row 6 is Services Actual.

Now continue the month format across so December data is column X and Y.

Input your sample numbers so that only the intersection between a Budget row and a Budget column have values in them. The same for the actual. In otherwords, B3, B5, C4, and C6 will be populated but B4, B6, C3, and C5 will be empty. Repeat this format across all the months.
Now select A1-Y6 and click on Chart wizard. In Excel 2000 it will create a graph like you want. Not sure if it works in earlier versions.

[PDuPre']

Tested it on Excel '97 and it doesn't work the same. The graph looks OK, but the labels for January to not span Actual and Budget. A work-around for that would be to add the labels manually outside of the graph so that it prints on top of the graph in the correct spot. Or, upgrade Excel.

Philip

[Adrae]

Thanks Phillip. This works great. One last question. Is there any way to eliminate the gap between the bars in the same month. So that it appears more like a clustered chart with stcked columns?

Thanks again for your help.

Adrae