Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining bar and stacked bars in a chart (Excel 2000)

    I need to create a chart that displays a series of stacked bars (each stacked bat represents the two dollar amounts that make up an employees weekly disability pay; i.e. state disability PLUS the amount paid out by the company) and I need to display this stacked bar next to a solid bar that represents the employees normal weekly salary.

    Is it possible to mix the two types of bars in a single chart?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining bar and stacked bars in a chart (Excel 2000)

    This is a bit tricky but it can be done.

    Say that your data are in columns. Select the source range for the chart plus one extra empty column.
    Create a stacked bar chart; make sure that series are columns.
    Select the third series (salary), select Format | Series and specify that it is to be displayed on the secondary axis.
    Same for the fourth (dummy) series (you can select it from the dropdown list on the Charts toolbar.)
    Next, activate the Options tab in the Format dialog and set the Overlap to -100 and the Gap Width to 0.
    Click OK.
    Select one of the two other series, and select Format | Series.
    Set the Overlap to 100 and the Gap Widths to 200.
    Click OK.

  3. #3
    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

    Re: Combining bar and stacked bars in a chart (Excel 2000)

    An alternative method to Hans' is to use 3 series:
    1) state disability
    2)the amount paid by company
    3)Normal salary

    Then for each X category actually create 2 categories. The "odd ones" use a value from 1 &2 and has 0 in 3. The "evens" have 0 and 0 for 1 and 2 and has the value for 3. This method alleviates the need to use the secondary axis. Even though all are stacks, The even columns will stack just the 2 and the odds will "not stack" since there is only one value.

    I prefer NOT having to use the secondary axis, since you must make sure they always have the same min/max so the items match since you want them really to be the same scale in this type.

    If it is not clear, if you provide a sample set of data, i could demo the technique.
    Steve

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining bar and stacked bars in a chart (Excel 2000)

    Thanks for the help. I didn't have time to totaly reformat my data table so I went with the 2nd axis display option. But thanks for the hint on the scales having to be the same. That fixed the problem.

    I have attached the file if you want to take a look at it.

  5. #5
    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

    Re: Combining bar and stacked bars in a chart (Excel 2000)

    Here is an example of my method. It is a plain stacked column. The key is the data setup.

    Some suggestions:
    Like you I explicitly used the "constants" 52, 0.55, 0.65 and 602. It would be better to put the constants in a table, name the cells and use the named cells in your formulas:
    1)If the constants ever change, changing one value in a table will update all the formulas without having to edit any of them!
    2) it makes the cell formulas easier to understand since they have names rather than cells references
    3) excel calculates quicker

    I am not sure why you progressed from 1 salary to the next in the manner you did. I would (from a chart standpoint) have prefered equal progressions throughout and more "round numbers".
    I would have started at 375 and progressed by 125 for the chart. (or start at 250 and step by 250 with less columns)

    To do this with my data change c2 to :
    <pre>=(250+125*A2)*52/0.65</pre>

    or even
    <pre>=(250*A2)*52/0.65</pre>


    Then copy this to C3:C34

    If you have any questions on the technique let me know.

    Steve

  6. #6
    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

    Re: Combining bar and stacked bars in a chart (Excel 2000)

    I thought of a way around the issue of 2 different Y-ranges and keeping them "in-sync"
    You need the 2nd axis to allow one series to be stacked and the other to be "non-stacked"
    The way you have it set up is 1 x-category axis, 2 Y's.
    What you need is 2 X and only 1 Y-axis!
    Right-click on chart
    Chart options - Axes tab
    Check "Category (x) Axis"
    and make sure that "Value (Y) Axis is NOT Checked
    Hide the new x-axis:
    dbl-click the "Upper" x- axis
    Select "None" for major and minor tick mark type and tick mark labels in Patterns tab

    Now 2 x-axes and only 1 Y, so there is NO issue of different scales.
    Steve

Posting Permissions

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