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

By
Steve True
-

Don’t worry, Excel is not changing your chart to a Stacked Clustered Column Chart or Stacked Bar Chart when you move a data series to the secondary axis.  And here is how to fix it.

The Problem:

With this data set:

A B C
1 Tea Coffee
2 Jan 300 1000000
3 Feb 700 5000000
4 Mar 300 5000000

You created a 2-D Clustered Column Chartimage_thumb5

Then, because your data series is not the same scale, so, you decide to create 2 vertical axis’ so that the scales are distinct for the two data series.  Then you move the tall orange columns to the secondary axis.image_thumb6





But it looks like Excel made it a Stacked Colum Chart  How can I fix it?

This is the chart I really wanted:image_thumb11

 

The Breakdown

Excel is plotting your data on two different axis in the same space.  So they will overlap.  In order to not have them overlap, we need to add a pad space to push the tea column left and the coffee column right. (Thanks to Maruf for this graphic).image

1) Create Chart Data Series

2) Insert 2 Columns Between Tea and Coffee

3) Highlight Data Range and Create 2-D Clustered Column Chart

4) Switch the Rows/Columns in Your Chart

5) Move Pad 2 Data Series to the Secondary Axis





6) Move Coffee Data Series to the Secondary Axis

7) Delete Pad Tea and Pad Coffee Legend Entries

Step-by-Step

1) Create Chart Data Seriesimage





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





 

2) Insert 2 Columns Between Tea and Coffee

image

 

3) Highlight Data Range and Create 2-D Clustered Column Chart

Your chart will look like this:image





 

4) Switch the Rows/Columns in Your Chart

Click on your Chart and then go to the Design Ribbon and Press the Switch Row/Column button in the Data Group:

image

If you don’t know why you have to do this, check out this link:

Why Does Excel Switch Rows/Columns in My Chart?

Your chart will now look like this:image

5) Move Pad 2 Data Series to the Secondary Axis

Select the Pad Coffee data series in the chart.  If you can’t select it, check out this post:

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

or this Link:





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

Then Press Ctrl+1move that series to the Secondary Axisimage

Your chart won’t look any different since there is no data in the empty Pad Coffee Series.

 

6) Move Coffee Data Series to the Secondary Axis

Now repeat step 5 for the Coffee data series (column) and move it to the secondary axis.

Your chart will now look like this:image

 

7) Delete Pad Tea and Pad Coffee Legend Entries

We are almost done.  All we need to do now is remove the Pad Tea and Pad Coffee legend entries.

To delete the Legend entries, do the following:





a) Select the Chart

b) Select the Legend

c) Select the Legend Entry for Pad Tea

d) Press the delete key

e) Repeat A-D steps for Pad Coffee legend entry

 

Here is a cool post you may have missed about legend entries:

Tips and Tricks – Longer Legend Color Bars in Excel Charts

Here is what your final chart will look like:

 





Video Demonstration

Here is a detailed video tutorial showing you how to stop Excel from converting your converting your clustered column chart into a stacked column chart (even though we now know that it is just overlapping):

Sample Excel Download File

Here you can download free the sample chart:

How-to-move-a-data-series-to-the-second-axis-and-not-overlap-the-columns.xlsx

 

Congrats to Peter, Don and Maruf who were successfully able to make the final chart.  Also, don’t forget to comeback as we will be showing you the Excel Super Bowl Dashboard Entries

 

Steve=True





59 COMMENTS

    • Hi Cooper,

      I believe that the only way would be to add 2 additional columns to your pivot chart data for the gaps. Just the column headers and data is not needed. Thanks.

      Steve=True




  3. If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:







  7. If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  8. Thank you this was helpful!
    However, what if I have three sets of data, such as this. The first two (%G & %S) I want on the primary y axis and the third (Average stems) I want on the secondary y axis. I can’t figure out where to put the pad columns?
    Site % G % S Average stems
    A 32.9 87.3 3.0
    B 59.1 66.5 4.0

  9. Is there a way to keep the Site data all together? So %G, %S, and Average Stems are all together above Site A, for instance. This would be preferable. Thank you!

  10. Brilliant! Have had this issue for years, never bothered to look it up until now. And your article gave me a solution immediately. Thanks a lot!

    • Thanks Matt for the great comment. Much appreciated. Yeah, it would be nice if there was a check box that would do this. Thanks again. Steve=True




  13. If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  15. If you want no space between column, after following above solution and making a chart, just delete the empty columns from excel. Chart will not change.

  17. I tried this and it didn’t work for me. I have two years of monthly data, so 24 months. Now instead of plotting it by for example, tea and coffee it’s plotting it by month, which just created a mess.

  19. Hi Steve, why does this happen in the first place? I’m having same problem but this has never occurred before. Thank you.

    • Hi Shiv, it should be an option, but I think that it is the desired effect from Microsoft if your chart is a column and a line.

  21. Steve,
    grateful for this workaround. I now have a question/challenge for you. Out from the final screenshot when you have tea values on the left axis and coffee value on the right, I now want to have more granularity, meaning I want an internal (so to speak)stacked column for tea and coffee where for example in January out of 300 for tea, 200 is green tea and 100 black tea, same thing for coffee on the right hand (for every month) coffee will be for example in February out of the 5MM, 3 MM is arabica coffee and 2MM is colombian coffee, you follow me?. I just want to show granularity for both products at the same time they are using two different axis.

    Thanks in advance Alex

  23. This is great Steve, thank you!
    But, I am wondering if you have three sets of data – two on one y-axis and one on the other. I am trying to add in “Pad” columns but am failing to get them to not overlap.
    Say it’s decaf coffee and tea with low values and caffeinated coffee with high values – no stacking.
    Any help would be greatly appreciated,




  24. If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  28. Thanks for the tip! The issue i had is i have some negative values in my data, in this case my x axis does not seem correct, anyone has this before?

  29. thanks so much for the tip i tried for hours to solve this problem until i found your instructions!

    however i have another problem, following your instructions in the end after moving tea for example to the secondary axis, excel switched the values of tea so they are opposite to the real value for months. for example in my chart march got the tea values of january and january got the tea values of march. in other words the X axis (lines) does not fit to the secondary vertical axis.
    did you had this problem before?

    • Hi Shahaf, you should show all 4 axes in your chart (Primary and Secondary / Horizontal and Vertical) to see what is not lining up. You can force your axis min and max to specific values. Also, the issue may be that you have Reversed the order on the axis for one and not the other. Hope that helps!

    • Hi P. What version of Excel are you using? Also, you may want to make sure your pad columns are in between the non-pad columns as the placement does affect the position in the chart.

  31. Legendary hack solution. Thank you!

    Can’t believe Microsoft still has this issue four years later – why would stacking columns be their default solution?

    • Thanks Tim. Not sure that it was considered as an option needed from Microsoft. For instance, if you moved your column to the secondary axis and made it a line chart type, then you would want them centered to show the line on the center of the columns not next to it. However, i agree that it should be an option.

  33. Good article. Ridiculous that there still isn’t a box to check in Excel to handle this automatically (which itself should be automatically checked if the chart types are the same). Minor typo – your final picture is the same as the one before it, i.e. – it still shows the legend entries for Pad Tea and Pad Coffee.

  34. Thanks for this helpful hack, arrived here after searching for half an hour on internet to get desired result. However adding error bars is offsetting the columns.

  35. This is amazing! I struggled with this a bit, knowing there must be a way to stop Excel from overlapping the columns, but I couldn’t figure it out. I’ve been using Excel for 20 year, but would’ve never discovered this on my own. Thanks for taking the time to post this!

  36. I have struggled with this for almost 24hrs..thank you so much for posting this .I actually just stumbled here looking for answers and I’m.grateful i did.
    Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here