How-to Create a Stacked Clustered Column Chart with 2 Axes

In response to another post of mine, EC wrote:

“My issue: This site has been really helpful so far! I have a follow up question. Could I create a secondary axis for one of the horizontal subcategories? I have clustered two stacked columns for each month in the year – one column is dollar value of purchases and the other is the number of purchases. Stacking captures the purchase type (service vs product). The each purchase is very high dollar value while there aren’t that many total purchases in a given month so the dollar value bar is much bigger than the number bar. In fact the number bar barely shows up on the chart. Is there anyway to put dollar value on the primary axis and number of purchases on the secondary axis so the info can be displayed better?  -EC”

 

To see what EC meant visually, if we take a sample set of data like this:image

His Clustered Stacked Column Chart looked like this:image





Normally, it would be fine, however, since he has two sets of data that are not of the same scale, you cannot see the Deals stacked column.

So what EC would like to do is to create the same Stacked Clustered Column Chart in Excel but this time with 2 Axes so that you can now see the deals columns as you see below:image

If you want to learn more about creating this type of chart and why it works so easily, check out this post:

How-to Easily Create a Stacked Clustered Column Chart in Excel

And here are a few other posts related to additional formatting of Stacked Clustered Column Charts:



How-to Close the Gaps Between Chart Series in an Excel Stacked Clustered Column Chart

How-to Add Centered Labels Above an Excel Clustered Stacked Column Chart

 

Now lets get to how we change up this Excel chart type so that you can move the data to a 2nd Axis and have it work correctly.

 





The Breakdown

1) Create Chart Data Set

2) Create Stacked Column Chart

3) Move Deals to 2nd Axis

4) Change Vertical Axes Number Format



5) Change Series Fill Color and Gap Width and Delete Legend Values

 

Step-by-Step

1) Create Chart Data Set

This is real magic of how the chart will work for a 2nd axis.  Normally we would create our data like this to take advantage of the Excel Charting Multi-level Categories.  image





However, this will set the data in the same series for both Revenue and Deals, so we have to separate them like this in order to split the Deals onto the 2nd axis without the Revenue:image

Note that there is a SPACE entered into cell A4 so that you can get the vertical tick line after Services like this:  image

otherwise your horizontal axis categories will look like this:image

 

2) Create Stacked Column Chart



Now that we have our data setup the way we need, you will now want to highlight cells A1:F6 and then click on the Insert Ribbon and then click on the stacked clustered column chart:image

Your chart should now look like this:image

Now you can’t see the deals columns, but don’t fear, you will see them when we move them to the 2nd axis in the next step.

 

3) Move Deals to 2nd Axis





Now you need to select one of the Deals columns and then press CTRL+1 to bring up the Format Series Dialog box.

Then change the Series Options – Plot Series On to Secondary Axis:image

If you are having trouble selecting the Deal columns, then you should check out these posts:

How-to Select Data Series in an Excel Chart when they are Un-selectable?

The Quickest Way to Select an Data Series in an Excel Chart



 

Repeat step three for the other Deal column and your chart should now look like this:image

 

4) Change Vertical Axes Number Format

Now that you have 2 Axes (yes that is the plural of Axis, I had to look it up Smile), you may want to change the number format.  In our case, the revenue is in USD Currency and Deals are regular numbers.  To do this, simply select the chart, then select the left vertical axis and press CTRL+1.





Then click on the number format options and change it to Currency with zero decimals.image

Repeat the same selection of the right vertical axis and change it’s number format to Number with zero decimals:image

Your chart should now look like this:image

 

5) Change Series Fill Color and Gap Width and Delete Legend Values



The chart is almost done.  These steps are optional and a matter of personal preference.  Typically some people like to close the gap of the clusters.

To do this, select any Revenue series column in the Excel Chart and press CTRL+1.  Then change the Gap width of the series options to 0 (zero).image

 

Your chart will now look like this:image

Then repeat this step for any Deal column and your chart will now look like this:image





One other option you may wish to do is to change the colors to match the client tiers in both clusters.

To do this, select the Tier 2 Clients Deal Column and then press CTRL+1 and then change the color to Red to match the Tier 2 Clients Revenue Column:image

Repeat the step for the Tier 1 clients and your chart should now look like this:image

Now it may be confusing that you have duplicate legend entries, so you should delete on of the sets.

To do this, select the chart, then select the legend and then select the right blue Tier 1 Clients legend entry and press your delete key.  Repeat this step for the right red Tier 2 Clients legend entry.

Your final chart should now look like this:image

Now for the discussion – Some people may say that the chart will confuse readers or may fool the readers.  It can be hard to determine which axis the column is associated.  Also, looking at the example above, the right most deals looks almost equal to the right revenue, so they seem to the human eye and reader that they are comparable.  Alternately, it may be better to change the position of the data so that the deals are together and on the right and the revenue columns are on the left and together.  To do that with the same data set, you would want to change your chart data elements as such:

image

And your new chart would look like this:image

I think it looks better than the original because the deals are now more closely associated with the right vertical axis and they are more comparable because they are next to each other.





 

Video File

 

Free Download of Excel Stacked Clustered Column Chart with 2 Axes Template

Stacked-Clustered-Column-Chart-with-2-Axes.xlsx

 

Would you use this chart and technique in an Excel Dashboard?  Also, which layout do you like better?   Services vs Products or Revenue vs Deals?  Which is more readable and/or accurate?  Let me know in the comments below.

 

Steve=True









2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here