How-to Make an Excel Stacked Column Pivot Chart with a Secondary Axis

In this previous post:

Stopping Excel Pivot Chart Columns from Overlapping When Moving Data Series to the Second Axis

I had a fan question – How can you do this same type of Pivot Chart but instead for a Stacked Column Chart instead of a Clustered Column Chart?   Can it be done in Excel?  Yes it can, as Excel can do almost anything (almost).

How-to make a stacked column chart on 2 axes
How-to make a stacked column chart on 2 axes

Check out these posts if you don’t want to use a Pivot Table:

How-to Setup Your Excel Data for a Stacked Column Chart with a Secondary Axis

Stop Excel From Overlapping the Columns When Moving a Data Series to the Second Axis

 

The Breakdown

1) Create Data Table

2) Create Pivot Table

3) Add Calculated Items to the Pivot Table

4) Create Pivot Chart

5) Move Series to Secondary Axis

6) Change Series Fill Color

 

Step-by-Step

1) Create Data Table

First, you want to get your data into a layout that will work for a Pivot Table.  A Pivot Table data layout will have column headers for all the metadata and values.

We want to change our data from this:

North South
Jan Coffee  $        9,174,213  $        3,764,424
Tea  $                6,949  $                9,026
Feb Coffee  $        5,328,078  $        1,957,886
Tea  $                3,912  $                8,051

To this format:

Month Product Region Amount
Jan Coffee North  $     9,174,213
Jan Coffee South  $     3,764,424
Jan Tea North  $             6,949
Jan Tea South  $             9,026
Feb Coffee North  $     5,328,078
Feb Coffee South  $     1,957,886
Feb Tea North  $             3,912
Feb Tea South  $             8,051

Now for our Pivot Chart, we will need to add one additional column of data.  We need to create a Concatenated column of text that combines the Region and the Product.  In my case, I don’t use the Concatenate function, but instead I just use the & (Ampersand) between the cell references and other text.

So create a new column of data next to Amount.  If Month is in A1 and Region-Product is in E1, then my formula in E2 =C2&”-“&B2 (or if you need to use Concatenate, then it would =CONCATENATE(C2,”-“,B2).

If you want to learn more about this, check out this post:

How NOT to use Concatenate Function to build Dynamic Text in your Excel Dashboard Templates

Your final data for the Pivot Table will look like this:

Month Product Region Amount Region-Product
Jan Coffee North  $  9,174,213 North-Coffee
Jan Coffee South  $  3,764,424 South-Coffee
Jan Tea North  $          6,949 North-Tea
Jan Tea South  $          9,026 South-Tea
Feb Coffee North  $  5,328,078 North-Coffee
Feb Coffee South  $  1,957,886 South-Coffee
Feb Tea North  $          3,912 North-Tea
Feb Tea South  $          8,051 South-Tea

 

2) Create Pivot Table

Now that our data is ready, lets create the Pivot Table.  So highlight your range of data.  In our case, A1:E9 and go to the Insert Ribbon and press the Pivot Table button.

Insert Pivot Table
Insert Pivot Table

Then we want to setup the Pivot Fields with Month and Product in the Rows and Region-Product in the Columns and Amount in the Values as you see here:

Stacked Column Chart with 2 Axes Pivot Table Fields
Stacked Column Chart with 2 Axes Pivot Table Fields

Also, I changed the Number Format to currency of the Pivot Table Sum of Amount with the Value Field Settings options.

Your resulting Pivot Table will now look like this:

Stacked Column Chart with 2 Axes Initial Pivot Table
Stacked Column Chart with 2 Axes Initial Pivot Table

 

3) Add Calculated Items to the Pivot Table

We will use this step to group our Legend Entries.  If you are not familiar with this process or want to learn how to do it on non-Pivot Charts, then check out this post and video.

How-to Group and Categorize Excel Chart Legend Entries

To group the legend, we will need to add 3 additional Calculated Items to our Pivot Table.  To do this, first we need to select where the new items will appear.  We want these under the Region-Product, so select one of those labels in the columns of the pivot table.  In this case, I will select “North-Coffee” in my pivot table.  Then goto the Pivot Table> Options Ribbon > Fields, Items, & Sets > Calculated Item…

Stacked Chart Pivot Table Add Calculated Item
Stacked Chart Pivot Table Add Calculated Item

Then type in item of Left Axis with a value of Zero (0).

Pivot Table Add Calculated Item Details
Pivot Table Add Calculated Item Details

Repeat this step for a Right Axis item = (0) and a finally a ” ” (space) item = value of (0).

Your inserted items will look like this:

Pivot Table Add Calculated Item Details
Pivot Table Add Calculated Item Details

Your Pivot Table will now look like this:

Stacked Column Chart with 2 Axes Final Pivot Table
Stacked Column Chart with 2 Axes Final Pivot Table

 

4) Create Pivot Chart

So you should now have everything you need to create you Stacked Column Pivot Chart.  First, select your Pivot Table and then go to the Insert Ribbon.  Then click on the Column Chart Button and choose the Stacked Column Chart Type.

Insert Clustered Stacked Column Chart Ribbon Selection
Insert Clustered Stacked Column Chart Ribbon Selection

Your chart should now look like this:

Stacked Column Chart with 2 Axes Initial Pivot Chart
Stacked Column Chart with 2 Axes Initial Pivot Chart

I typically hide the field buttons.

Hide All Field Buttons on Chart
Hide All Field Buttons on Chart

You can do this by right clicking on any of the field buttons on the chart and select “Hide All Field Buttons on Chart”.

Your chart will now look like this:

Stacked Column Chart with 2 Axes and Hidden Field Buttons Pivot Chart
Stacked Column Chart with 2 Axes and Hidden Field Buttons Pivot Chart

 

5) Move Series to Secondary Axis

Now you can move the series that you want on the Secondary Axis including our legend grouping series.  For this chart, select the “South Tea” series and then press CTRL+1 and select Secondary Axis on the Format Data Series dialog box.

Move Series to the Secondary Axis
Move Series to the Secondary Axis

If you are having problems selecting the correct series, check out this post to help you out:

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

Repeat this step for the “North Tea” series as well as the “Space” series (the one that has a space for the name) and finally the “Right Axis”.  Your chart should now look like this:

Stacked Column Chart with series moved to the 2nd Axes Pivot Chart
Stacked Column Chart with series moved to the 2nd Axes Pivot Chart

 

6) Change Series Fill Color

We are almost there.  The only thing that is confusing on this Pivot Chart is that we see colors next to Left Axis and Right Axis.  To fix this, select one of these series in the chart and press CTRL+1 and then change the fill to No Fill.  If you want to see this technique on a regular chart, check out this post:

How-to Group and Categorize Excel Chart Legend Entries

Format Data Series No Fill
Format Data Series No Fill

Your final chart should look like this:

Stacked Column Chart with 2 Axes Final Pivot Chart
Stacked Column Chart with 2 Axes Final Pivot Chart

 

Video Demonstration

 

Free Sample File Download

How-to-create-a-Stacked-Column-Pivot-Chart-with-a-secondary-axis.xlsx

Do you use Pivot Tables more or just a stream of data for your charts?  Let me know in the comments below.

 

Steve=True