Centered Stacked Bar Chart
If you need to compare 2 data series, stacked bar chart might be just what you need. You can of course insert a stack bar chart from INSERT ribbon without any problems but default centered stacked bar chart is not a handsome one. In this post, I will show you how to make yours eye-catching.
For this example, I will use a simple data set as our chart data.
Let’s insert a default stack bar chart first to see what can be improved. Here is the default chart:
As you see, it is hard to determine values of orange series, you constantly need to look down for labels and up for bars making it harder to see it as a whole, etc.
We can easily make it a centered stacked column chart just by modifying source data. Here is modified data and resulting chart:
As you can see, this time where each data series starts and ends is pretty obvious. You can easily see Yes/No distribution, thus determine how which series is doing just by a glance.
Now that we want to continue with this chart, we need to make it look better. Since one of our series has negative values, we cannot use vertical and horizontal axes labels or numeric data labels. Also vertical axis and gridlines is not useful with this setting. So delete it all.
Here is our new cleaner chart:
Now all that left is adding data labels. Insert a text box and set it up as shown in the picture below (format: no fill, no outline):
You can multiply your labels with copy paste once you created the first one. I know it is a bit of work to do. But trust me the result will worth it.
Now select orange data series and add label and change label contents from “Value” to “Category Name” (to open labels options, double-click on a label). Then category names are displayed, drag them to the middle of your data series and align them to their touching point.
Now some more formatting for finishing touches, and here is the finished chart:
Now you may wonder how are going to use or even move this charts with so many labels hovering around. All you need to do is select the cells that houses your chart, copy them and paste it as a linked picture. This way you can move your chart as a picture which reflects any changes on source chart in real-time.
To download this chart and play with it, use this link:
Download Centered Stacked Bart Chart
If you like this chart, you can also check incell chart which is virtually the same but has a completely different built.
Your post captures the issue pelfcetry!
Your labels on the final chart are ascribed to the wrong bars (i.e. 1930 should be beside Mobile Phone and not beside notebook PC)…Other than that this is a great technique. Appreciate the work that went into this.
That’s a sensible answer to a challenging question