STACKING TWO ITEMS ON A BAR GRAPH

[MNN]

Good Morning,

I am attaching a worksheet which has data that I am trying to graph. There are four (4) depts. I am trying to stack Departments M1 & M2 since it is really one large dept. Does anyone know how to do this while keeping the individual total production for each of these two depts . Of course , I still need the other two (2) departments on the graph, Dept OI and Dept SP.

Thank you.

[wdwells]

Please attach the workbook.

[MNN]

sorry, I made sure the attachment stuck this time.

[tfspry]

nothing is attached.

[scign]

You'll need 4 columns for your data:

First column contains the X-axis labels i.e. the 3 department names (counting M1 and M2 as one department)

Put the values for SP and OI in column 2, M1 in column 3 and M2 in column 4

Dept   Data1   Data2   Data3
M              15       5
SP     25
OI     20

Select those cells and create a stacked bar graph with that data. When I did this Excel decided to put the department names as the different series. Clicking 'select data' and 'switch row/column' easily fixed that.

[wdwells]

Good Morning,

I am attaching a worksheet which has data that I am trying to graph. There are four (4) depts. I am trying to stack Departments M1 & M2 since it is really one large dept. Does anyone know how to do this while keeping the individual total production for each of these two depts . Of course , I still need the other two (2) departments on the graph, Dept OI and Dept SP.

Thank you.

I suggest that the best approach ( a work around ), is provided in the attached workbook.

H.T.H.

[sdckapr]

Here is a modification of Don's example with the M1&M2 stacked (a "double column Chart") which I think is what you would like (you still haven't attached your example file)

See http://people.stfx.ca/bliengme/Excel...ubleColumn.xls for another example and a step-by-step explanation of creating it

Steve

[wdwells]

Here is a modification of Don's example with the M1&M2 stacked (a "double column Chart") which I think is what you would like

Wow

[wdwells]

See http://people.stfx.ca/bliengme/Excel...ubleColumn.xls for another example and a step-by-step explanation of creating it

Does anyone know of a method for controlling the spacing on the X Axis when using this chart technique. The problem I see is shown in the attachment.

TIA

[rory]

For this example, I'd probably do this.

[wdwells]

Aha
The spacing of the vertical grid lines is determined by the number of rows in the data table. There are two solutions:

1. Remove the empty rows from the data table which will remove all spacing from the chart; or
2. Have one empty row at the beginning and end of the table with two rows separating each series in the table.

The attached demonstrates.

[sdckapr]

The spacing of the vertical grid lines is determined by the number of rows in the data table.

Exacxtly. You are overlaying the primary axis (labels, but plotting zeroes so no values) over the 2nd axis which has no labels but data. To align they need to be multiples (with the small number in my original post it was not as noticeble....)

Steve

[sdckapr]

Here is an alternative presentation of the chart without using a primary and secondary axis. This has the entire dataset presented in a more straight forward manner.

The X-Axis is 2 columns, one with the labels and blanks to spread them thorugh the range and the other with a space. Rotatating the alignment of the space helps to decrease the width of this region in the chart.

Steve

[wdwells]

Here is an alternative presentation of the chart without using a primary and secondary axis. This has the entire dataset presented in a more straight forward manner.

The X-Axis is 2 columns, one with the labels and blanks to spread them thorugh the range and the other with a space. Rotatating the alignment of the space helps to decrease the width of this region in the chart.

Steve

The attached file represents my attempt to create a chart similar to what Steve has provided in his most recent post. The chart on the Problem sheet has the X axis formatted improperly. The chart on Steve's sheet is ideal.

I deviated from the instructions on the How To sheet as follows:

• Step 1 -- Selected A2:B17
• Step 2 -- Selected C2:G17
• Steps 3 through 5 -- omitted
• Steps 6 & 7 -- Performed as read
• Pulling down Chart > Source Data, in the Series tab; set the Name cell for Series 3 through 7, then
• Set the X axis labels to $A$2:$B$17
• Step 8 -- omitted
• Step 9 -- Deleted Series 1 and 2 from the Legend box.
• Steps 10 & 11 -- Not applicable

I would appreciate any insight into where I have gone wrong.

TIA

[sdckapr]

Note that the directions you posted were from the ORIGINAL solution I posted where the labels were created as the primary axis and the Y-Axis used the secondary axis. I did not create a step-by-step instruction for the alternative approach as it seemed more straightforward. I did mention (and perhaps it was a little cryptic: "The X-Axis is 2 columns, one with the labels and blanks to spread them thorugh the range and the other with a space. Rotatating the alignment of the space helps to decrease the width of this region in the chart."

You did not create the blanks nor rotate them. While going throught he steps to fix it, I also noticed that the offset also helped. Here are the steps to fix the X-Axis. While you have created the 2 level X-Axis (A2:B17), your 2nd column is composed of blanks which does not spread the 1st level lables (Col A) around as desired. The blanks cells in A essentially "negate" the 2 range effect we need.

The key steps are to make the 2nd col spaces so they have a label and then format to remove the ticks, and shorten the space from the X-Axis to the 2nd set of labels (Col A) so they appear as the only labels.

Make B2:B17 each one space:
1)Select B2:B17
2) Enter a space, then ctrl-enter to fill all will a space (or fill in B2 with a space and copy it down)

The X-Axis will now look more like you desire. To fix it more you can remove the line and make Labels closer to the X-Axis

Remove the individual lines in the top section
3) Dbl-click the X-Axis
4) Patterns (tab)
5) In "Major Tick Mark Type" choose "None"
Now fix the top labels by rotatiting them and removing the offset: the spaces will then take up less room
6) Alignment (tab)
7) In "Orientation" choose or enter 90 degrees (or -90 degrees, it does not matter which)
8) Change "Offset" to 0
9) OK

Is that more what you are after?

Steve
PS: If desired click OK after step 4 and step 7 (and afterwards dbl-click the X-Axis) to see the effect of each of the steps to help understand what they are doing.