Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cluster and Stacked Column (Excel 2000)

    Is it possible to combine clustered and stacked column in one chart ? For example, I want to show for the past five years how many chairs and desks were made each year: actualy vs. budget. Chairs and desks would be stacked, but actual and budget would be clustered. Is it possible ?

  2. #2
    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: Cluster and Stacked Column (Excel 2000)

    Yes.

    It is a little tough to explain without making a chart, but the way I typically do it is to make the whole chart stacked and keep extra series for the items you don't want stacked.

    It can also be done using a secondary x-axis, but this usually also requires "dummy" series to space them correctly which is why I prefer the first method.

    If you provide some sample data and some details I can demo it for you.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cluster and Stacked Column (Excel 2000)

    Steve,

    Thanks. An example is attached. Would appreciate your help.

    TQ

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cluster and Stacked Column (Excel 2000)

    How's this look?

    InsertChart. Choose Clustered Column
    Data Range: A1:A5. Click on Series In: Columns
    Click Finish

    Put Chairs onto Secondary Axis
    Right Click on any Chairs Actual Column on the chart and click on Format Data Series
    On Axis tab, choose Secondary Axis
    Repeat this on Chairs Budget Column

    Right Click on the Y axis on the right side of chart.
    Set scale so that it matches the Y axis on the left side of the chart.
    You might want to right click on the Y axis on the left side of the chart and uncheck Auto on Scale maximum to make sure your 2 Y axes stay in sync.

    In looking at your graph, what happens if the number of chairs is greater than the number of desks? Then the chairs will hide the desks in the chart. You might want to make the chairs columns narrower or farther apart so that even if they are taller than desks, everything will still be visible. To do this, right click on any chairs column, go to the Options tab, and adjust the Overlap and/or Gap Width to suit your needs.

  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: Cluster and Stacked Column (Excel 2000)

    I created a table on another sheet that reads your data:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td valign=bottom>Chairs</td><td valign=bottom>Desks</td><td align=center valign=bottom>2</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>3</td><td align=right valign=bottom>1998</td><td valign=bottom>Budget</td><td align=right valign=bottom>150</td><td align=right valign=bottom>250</td><td align=center valign=bottom>4</td><td align=right valign=bottom>1998</td><td valign=bottom>Actual</td><td align=right valign=bottom>100</td><td align=right valign=bottom>200</td><td align=center valign=bottom>5</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>#N/A</td><td align=center valign=bottom>#N/A</td><td align=center valign=bottom>6</td><td align=right valign=bottom>1999</td><td valign=bottom>Budget</td><td align=right valign=bottom>180</td><td align=right valign=bottom>300</td><td align=center valign=bottom>7</td><td align=right valign=bottom>1999</td><td valign=bottom>Actual</td><td align=right valign=bottom>120</td><td align=right valign=bottom>240</td><td align=center valign=bottom>8</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>#N/A</td><td align=center valign=bottom>#N/A</td><td align=center valign=bottom>9</td><td align=right valign=bottom>2000</td><td valign=bottom>Budget</td><td align=right valign=bottom>216</td><td align=right valign=bottom>360</td><td align=center valign=bottom>10</td><td align=right valign=bottom>2000</td><td valign=bottom>Actual</td><td align=right valign=bottom>144</td><td align=right valign=bottom>288</td><td align=center valign=bottom>11</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>#N/A</td><td align=center valign=bottom>#N/A</td></table>

    Columns A&B are just entered C1 and D1 are entered
    I left the blank rows (2,5,8,11) as column spacing "between clusters"
    Cell C3 has:
    =INDEX(Sheet1!$B$3:$C$5,MATCH($A3,Sheet1!$A$3:$A$5 ),MATCH($B3,Sheet1!$B$2:$C$2))
    Cell D3 has:
    =INDEX(Sheet1!$D$3:$E$5,MATCH($A3,Sheet1!$A$3:$A$5 ),MATCH($B3,Sheet1!$D$2:$E$2))

    These read your data, Copy C33 to C411

    Make your chart from column B1:C11. Format as desired

    I added the years underneath the "X-labels" using text boxes

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cluster and Stacked Column (Excel 2000)

    Want to thank both Chipshot and Steve for the wonderful solutions !!! Both work great !

Posting Permissions

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