How To Split A Stacked Chart In Excel

Mon 3rd January 2011

Learn how to split the stacks in an Excel stacked chart to give you even more ways of working with this very useful chart type.

Have you ever wanted to split the stacks in an Excel stacked chart? This is surprisingly easy to do and lets you compare parts of the stack in separate columns. For example you might want to compare the stacked two lower parts against the stacked two upper parts of a four part stack. In this article we'll recap what a stacked chart is and how to build one. Then we'll cover how to split a single stack into two pieces. Once you've got the hang of the technique you'll be able to break down multiple stacks within a single chart.

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.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1345-how-split-stacked-chart-in-excel.html