How To Split A Stacked Chart In Excel
Mon 3rd January 2011
Using any Excel version, we'll go over how to create a single stack chart with four parts. To do this type in four labels in successive cells in a downward direction, and then add numbers to the right of each label, so you end up with a two column table, with the first column showing a list of labels and the second column a list of numbers.
Creating a four part stacked chart, from a single column of data
To create a stacked chart, first select both table columns (the actual cells rather than the entire columns), then click on the Chart wizard or the Insert tab, depending on your Excel version and choose a column chart. Then within the column chart options, choose the stacked column type - this is usually the top option in the second column from the left of chart types. Then click OK or Finish to complete building the chart. Excel assumes the data series is a column (because the numbers are in a column) and builds the chart but showing one column for each number in the table.
To change this to a four part stacked chart we need to change the source data from column to row, so the data in each row is combined in a stack. To do this first ensure the chart is selected. Then in Excel 2003 choose Chart, Source Data, choose Rows and click OK. In Excel 2007/ 2010, with the chart still selected, choose the Design tab and click the Switch Row/Column button. Now you will see your four part stacked chart in a single column with four parts.
Creating a split stacked chart from a single column of data
Suppose we want to create a stacked chart with separate mini stacks of the two upper parts and the two lower parts. To do this we need to change the position of the two lower data cells in the original table and then build the stacked chart again. So first delete the chart you just created. To re-position the data cells highlight the lower two numbers of the original table - only the numbers, not the labels. Then carefully drag the two cells one column to the right, and click off the cells to remove the highlight. Now the table shows two of the four data cells offset by one column. This offset is the key to splitting a stack.
To build the revised stacked chart, select all the labels and data, so you are now selecting three columns of data (again the actual cells not the entire columns). Then as before start the chart builder and choose the stacked column chart option and finish. Then as before change the sense of the chart to Row rather than Column data, and click OK or Finish to complete, and voila you have a split stacked chart.
Creating a split stacked chart from multi columns of data
If you have a multi column table the way the split is achieved is similar but involves more cell moving. Firstly you need to create an empty column between each column of data. This is to create offset space for each column. Then you offset the data within each column to create the data for each mini stack. Once you've arranged the data you then select the table cells, build the stacked column and change the data source setting to Row. Then you'll have a split multi stacked chart!
So splitting a stacked chart is perhaps easier to achieve than you think. The key is to separate each column of data with empty columns and then offset the data from each column in the chosen groups to create mini stacks.
If you've found this article useful you mike like to consider learning more about Excel features by attending one of the many training courses available. The best ones let you ask questions about your chosen topics, so don't forget to ask about splitting the stack.
Original article appears here: