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
In 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 to 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: