Categorized | excel chart

Bar Graph with Helper Stacked Bars

Always wanted to make a stacked bar graph but were afraid connoisseurs would smirk. Let me present – the stacked bar graph with helper bars – the latest addition to the charting fraternity. (Drum rolls)


In case you were wondering how this one got made, here are the steps:

Set Up the Data for the Bar Graph

bar-graph-dataIn this example, we pick up a data set that shows the cumulative sales for a set of products and also the split according to the constituent product categories. The various products are listed columnwise and the categories across successive rows.

Create a Stacked Bar Graph


The second step is to create a neat looking stacked bar graph. We select the entire source data range and using the ribbon (or the menu toolbar, depending on what’s your weapon of choice), create a stacked bar graph. Now comes the trick – Our initial chart (in this case) will have four series – the first one representing the total and the remaining three for the respective categories. We now edit the chart’s source data and add another series. The data range values for the new (and fifth) series is the same as the first one (the one showing the total).


Since we included the data labels in the graph’s sources data, the graph comes out with the category names showing up as series labels. We then turn on the legend so that we have all the information we need to have in order to clearly identify the constituents of the chart.

Change the First and Second series to Secondary Axis

This one’s easy. One by one, double click on the first and last chart series and change the axis to secondary. The moment one does that, the secondary scale shows up on the chart.


The first and the last series, since they refer to the same data points, will show up with the same height and are the tallest. When you change the axis to secondary, they can tend to cover up the remaining series in the background in the bar graph.


That brings up to the next step which is to the adjust the overlap and gap width.

Adjust the Series Overlap and Gap Width for the Graph

Before you jump straight in to this, you can make the second series (the second one that we moved gap-and-overlapto the secondary axis in the previous step) invisible by right clicking and taking out the fill and the border (choose ‘None’ for both). This makes it easy to adjust the gap and overlap for all the other remaining series. Play around with those till you just about get it right. If you get it right, you will achieve a condition where the first series covers most of the stacked series in the background.


Final Step – Format the Bar Graph

So now the only thing left for us to do is to format the chart. Adjust the gridlines, title and spacing of elements. Turn on the value labels if need be and remove the secondary axis. Also remember to remove the extra entry for the fifth (and duplicate) series in the legend (place two slow single clicks and press ‘delete’).


You can download a sample worksheet containing the bar graph with stacked helper bars here or click on the button below:


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Mjollnir wrote:

    Hello, why when I moved the 1st and the 5th series to secondary axis, they are stacked but not standing parallelly? Could you please provide me a little help? Thank you.

  2. jaijo wrote:

    why when I moved the 1st and the 5th series to secondary axis, they are stacked but not standing parallelly? Could you please provide me a little help?

  3. Robert W wrote:

    I have the same question…..when moved to a secondary axis the ‘ALL” series end up stacked not parallel to each other :(

  4. Cos wrote:

    Same problem as everyone else here…. Any sollutions??

  5. anonymous wrote:

  6. Petri wrote:

    It looks like that in this example you actually should start with “Stacked Column” design and change it “Clustered Column” right after changing the second “All” series to the Secondary Axis. That second “All” series should be still activated when you change chart type to “Clustered Column” if you want this effect to come true. After that you can adjust the overlap and gap width as it has been explained.


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel