Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.


  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Please attach the workbook.
    Regards
    Don

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    sorry, I made sure the attachment stuck this time.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    nothing is attached.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    London, UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Code:
    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.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by MNN View Post
    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.
    Attached Files Attached Files
    Regards
    Don

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Attached Files Attached Files

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by SteveA View Post
    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
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by SteveA View Post
    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
    Attached Images Attached Images
    Regards
    Don

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For this example, I'd probably do this.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Attached Files Attached Files
    Regards
    Don

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Attached Files Attached Files

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by SteveA View Post
    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
    Attached Files Attached Files
    Regards
    Don

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •