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.
- Now select all the data – and choose a stacked chart.
- You’re resulting chart should a combines stacked/clustered chart.
