Stacked Column Charts

We already understand the clustered column charts in excel as we have used this option in all the previous column chart tutorials. This is the first option under column charts and is called so as all the columns for a particular category value are clustered.

Another requirement could be to have all the columns for a category label to be clubbed vertically (making a single column) rather than standing beside each other. This very option is called a stacked column chart.

This difference is shown in the image below:

stacked column chart concept

Making a stacked column chart: Select the whole data (& headers) and click to Insert (from top ribbon) -> then under the Chart section, click on ‘Insert Column Chart’ -> Click on Stacked Chart option and the stacked chart will be created. The process steps are shown with an image below:

Inserting stacked column chart

Next, we go beyond the default selection and look under the hood to see the data arrangements that go into making a stacked chart.

Let’s say that there is an electronics company that deals with multiple product lines. Its marketing manager wants to take the board through the sales trend of last year and impress upon the change in product mix (certain categories are selling more while other are either de-growing or not growing very fast). Following is the data that compares two year sales (by value in ‘000s of $):

product line wise data

As discussed before, when we have headers, it will help to keep top left cell as blank (so that excel correctly identifies the headers)

Now select the whole data (except the last row that has total), then go to Insert->Chart->Stacked Chart as discussed above. The resultant chart will be like the one shown below:

Now this is not what we want. If you recollect from a previous tutorial, this chart has 2 data series (2012 & 2013) and all product lines are shown as category labels. To re-confirm the current data arrangement & to correct the chart we will go to the ‘Select Data’ wizard: (right click on chart -> Select Data)

The current data selection will be visible in the ‘Select Data’ pop-up as shown below:

Select data wizard & switching row-columns

To change the data selection as per our requirement we just need to switch the row/column data. The same is achieved through a single click as shown above.

The result is as per our requirement (year on year comparison by product lines), but it is too cluttered. For stacked chart to work (& not just look impressive), number of data series should be limited (not more than 5 as a thumb rule). Also, the color selection should make sure that the chart is easier to read.

Let’s assume that the manager wants to push for a re-design of outlets to give more prominence to emerging categories (Tablets & Mobile phones in particular), then the focus should be on showing the great growth these products are having. To do so & to un-clutter this chart, we will club all other products into a single line called ‘others’ in our chart.

To do this change the data accordingly & re-create the chart. Result is shown below:

re-aligned and uncluttered stacked column chart

This clearly drive home the point about the growth of certain categories among all the product lines, and these are precisely the scenarios where you may want to use the stacked column charts.

Now, the last thing that we cover in this tutorial are certain formatting options that are unique for the stacked charts (Formatting options for column chart already covered here)

Click on any data series (any column on chart) and then the following data series options will be visible:

Stacked column chart series formatting options

Gap width option is about controlling the space between the columns.

The first option ‘Series Overlap’ is very interesting here. Y default this value will be 100% for Stacked column charts. Reducing this will decrease this overlap and the data series columns will pull apart from each other. Negative values for this will mean that there will be space between the data columns for each category label (apart from the space that is there between the category labels already). This is shown in the image below:

Stacked zColumn Chart & its Series overlap options explained

This is a property that is used widely to create certain charts and other interesting effects (like creating a waterfall chart). We will cover such charts in some of our future tutorials.