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.

Here is the original data and chart we will be using as a sample.

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.

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.

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.

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.