Combo Stacked and Clustered Chart in Excel 2013

This was something I helped a user with that was much more difficult that it seemed like it should be.

The scenario:  Create a bar chart where some of the data is regular bars and 2 of the columns are stacked together.

After much research and testing…I learned that if you have quite a few bars – you can’t tweak your graph with spacing and bar width.  If you have only a few bars of data – you can use this method.  Small combo stacked/clustered chart

If not…prepare yourself.

  1. Here is the original data and chart we will be using as a sample.chart
  2. The goal is to leave most of the chart as is – in clustered bars but to stack the Debt with Expenses.
    Resulting chart should look like this.
  3. First get a new sheet you will put your data on.  Determine (counting) how many unique series you have – this one has 4.  We do not count the stacked one (debt). So 1. Budget, 2. Income, 3.Expenses/Debt, 4. Pledges.
  4. Arrange your data sheet to look like this. Notice – you would continue the pattern for each year. Notice under each year there are 4 lines – representing the 4 unique series we counted above.
  5. Now take your data from the original format and populate it as follows. The pattern is that if you want the data to be stacked – the numbers are adjacent – notice debt and expenses are on the same lines (where there are values).  I also have blank rows between which helps the clustering to have some space in between.
  6. Now select all the data – and choose a stacked chart.
  7. You’re resulting chart should  a combines stacked/clustered chart.

Leave a Reply

Your email address will not be published. Required fields are marked *