A Better Format For Excel Chart Secondary Axis Columns Overlap with 3 Series

Better Format for Excel Chart Secondary Axis Columns Overlap with 3 Series
Learn a better charting format when your Excel Chart Secondary Axis Columns Overlap with 3 data series.

The Problem

I didn’t love the last chart we created that solved the problem that Whitney was having, however, it didn’t seem like it was amazingly clear as to which column went with which axis.
Final Clustered Column Chart on Stop Excel Overlapping Columns
You can read more about what I am describing here: Stop Excel Overlapping Columns on Second Axis for 3 Series
When we are dealing with 2 chart data series it can be assumed that the left most column corresponds with the left (primary) axis.  Likewise, the right most column would correspond with the secondary axis (right).
However, this presents a problem when you have 3 data series.  With which axis does the middle data series in the chart correspond?  The reader will never know.  But I have a workaround that I like, but let me know in the comments below what you think.  The proposed chart would have a gap separating left and right axis columns in the chart area as well as a grouped legend like you see here:
Better Format for Excel Chart Secondary Axis Columns Overlap with 3 Series

The Breakdown

In this tutorial, I will be combining 2 concepts for a work around when your Excel Chart Secondary Axis Columns Overlap for 3 data series.
1) Add Padding Columns to Stop Excel Chart Secondary Axis Columns Overlap
this is the primary technique of adding columns of blank or zero value data so that we can position the columns in a way that Excel will not overlap the columns.
2) Group Legend Entries by Axis
This is a really cool technique that I created awhile ago, but we will use it to show which columns correspond to which axis.
You can read more about this technique here:
How-to Group and Categorize Excel Chart Legend Entries

Step-by-Step

1) Create Padding Columns

So the first steps of this process is to create padding columns for each axis.  We will need 5 data series for each axis.  One will be used for the Legend Grouping on each axis.  Then one data series will be used for the gap between the columns.  The rest are to make sure that the columns don’t overlap.

You should really check out this post to make sure you understand the technique:

Stop Excel Overlapping Columns on Second Axis for 3 Series

Your data setup should look like this:

A B C D E F G H I J K
1 Left Axis Tea Decaf Coffee Left Pad 1 Left Pad 2 Right Pad 1 Right Pad 2 Right Pad 3 Right Axis Regular Coffee
2 Jan 150 140 4500
3 Feb 100 90 4000
4 Mar 175 185 4300

 





2) Create Clustered 2-D Column Chart

You are now ready to create the chart.  Highlight the range A1:K4 and go to the Insert Ribbon and select Clustered Column Chart.

Insert Column Chart

Your chart should now look like this:

Excel Chart Secondary Axis Columns Overlap with 3 Series (1)

3) Switch the Chart Rows/Columns

Because of how we setup our data, we will need to switch rows/columns for the chart.  You can do this by selecting your chart, then go to the Design Ribbon and then click on the Switch Row/Column button in the Data grouping.


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Switch Row Columns Button

If you want to learn more about why Excel is doing this, check out this article:

Why Does Excel Switch Rows/Columns in My Chart?

The current chart will now look like this:

Excel Chart Secondary Axis Columns Overlap with 3 Series (2)





 

4) Move Data Series to the Secondary Axis

The next step is to move all of the data series that we want to on the secondary axis.  This will include the Regular Coffee series as well as all of the Right Pad series.  To do this, click in the chart and then select the Regular Coffee series and then press CTRL+1 to launch the Format Data Series dialog box.  Then select the Secondary Axis radio button in the Series Options tab.  Perform this for all of the Right Pad data series.

If you are having problems selecting the data series for each of the Right Padding columns, you will need to temporarily give them a large value so that you can see them or read this article on how to select hard to click on series here:

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

When you are done moving the series to the Excel Chart secondary axis, your graph will now look like this:


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Excel Chart Secondary Axis Columns Overlap with 3 Series (3)

5) Remove Legend Entries

Next we want to remove any unneeded legend entries from the Filler/Padding data series.

First, delete the text that you have entered into the spreadsheet in I1 for Right Pad 3.  We want to keep the legend entry as a spacer, so that means we don’t want to delete it, just remove the text so that it appears blank in the legend.

Second, we need to clean up our Legend entries and remove the ones that are not needed (the ones titled Pad), first select your chart.  Then select the Chart Legend.  Next select a Legend Entry and finally then press the delete key on your keyboard.

Your chart should now look like this:





Excel Chart Secondary Axis Columns Overlap with 3 Series (4)

 

6) Change Data Series Fill Color

Finally we just have one last clean up on the Legend and that is to set the Fill type to No Fill.

We need to change this setting for the following data series: Left Axis, Right Axis and Right Pad 3 (that now has a blank label).  You can do this by selecting the Excel chart and then selecting the correct data series.  Then press CTRL+1 and go to the Fill options in the Format Data Series dialog box.  Then Choose No Fill from the Fill tab as you see here:

Format Data Series No Fill
Format Data Series No Fill

Your Final Chart will look like this:


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Excel Chart Secondary Axis Columns Overlap with 3 Series (Final)

I think that, when you have a 3rd or more data series in your chart and you use a secondary axis, this is a much better format for Excel Chart Secondary Axis Columns Overlap issues.  What do you think?  Let me know in the comments below.

Video Demonstration

Watch me combine these 2 techniques in this quick Video Demonstration below.  Note that I have added an extra padding column in the write-up above so that the columns are all the same size.

File Download

You can download the sample file here to see the charts described above in action.

Better-Excel-Chart-on-Overlapping-Columns-with-3-Series-on-a-Second-Axis.xlsx





I think that these are really cool techniques.  Excel is so powerful but it is great to have work-arounds.  What is your favorite tip and trick in Excel?  Let me know in the comments below.

Steve=True





1 COMMENT

  1. I come up with this approch to the problem:

    The numbers on the left axe with the same colour of Tea column (for example red) and the Decaf Coffe with a shade of Red.

    The numbers on the right axe with the same color as the Regular Coffe column (for example a Orange).
    The numbers could be bold.

    It’s not so Excel defiant.

    Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here