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.

Centered Stacked Bar Chart 1

Let’s insert a default stack bar chart first to see what can be improved. Here is the default chart:

Centered Stacked Bar Chart 2

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:

Centered Stacked Bar Chart 3 Centered Stacked Bar Chart 4

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:

Centered Stacked Bar Chart 5

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):

Centered Stacked Bar Chart 6

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:

Centered Stacked Bar Chart 7

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

Download FAQ

If you like this chart, you can also check incell chart which is virtually the same but has a completely different built.

You may also like...

3 Responses

  1. Sylina says:

    Your post captures the issue pelfcetry!

  2. Travis says:

    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.

  3. http://www./ says:

    That’s a sensible answer to a challenging question

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.