Total of chart series

When creating a Stacked Bar or Stacked Column chart, this is a convenient way to show the proportion of a product or its categories sold over a given period of time. However, even if we choose to show values ​​on the series, it is not clear what the total sum is. Here’s a simple trick to create a chart that shows the share of each series in the sum, as well as the total sum of values.

Let’s make one simple table with test data. The columns show the years, in the rows are the product/category values and names we have marked with A, B, C … The last row is the total of sales by year. If we select the header and the first three rows, without the row Total, we can create a Stacked Bar chart. This could be done in a number of ways, one of which is to select Recommended Charts on the Insert ribbon and then find the option to create a Stacked Bar Chart. When you have made the chart, select the desired display style, delete the title and add data labels to show values of series.

As you can see, it is easy to see the proportion of individual values ​​in the sum but not the sum. We can only guess how much it amounts to, based on the values ​​on the x axis. To see the value of the sum we will add it as a series. In the Design ribbon choose Select Data. Clicking on Add will bring up a dialog box where you need to add the series name (Total), followed by the range from which values ​​are drawn (B5: D5).

After clicking OK the sum will appear as a new series in the chart. We should further add data labels. Now all we have to do is format the series so that it is not visible, which means that we will set the color to transparent. We will do the same with frame color as well as all effects. Further we should format the x axis and make a few more minor formatting adjustments, and the final result is a chart in which we can see the proportion of individual values ​​as well as the total sum, represented by the data label on the right.