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

!WOW! What a clever solution !!!
Thank you so much for sharing your knowledge.

I follow your solution. However, how do you solve this same problem with a Pivot chart without changing your source data (ie inserting blank columns in the data series)?

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

Thanks for the help. It took a few reads and watches but I got there.
Much obliged.

Awesome – exactly what I was after. Thanks!

Thanks for the amazing comment Owen. Much appreciated. Glad to help. Steve=True

It works!
Thank you for sharing your knowledge.

You are quite welcome Antonio. So glad to help. Thanks for the great comment. Steve=True

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

Hi Erin,

Try this setup with no Pad Columns.

3rd-Column-for-Secondary-Axis-Overlapp-Setup.png

Let me know if it helps.

Steve=True

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!

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 for the great comment. So glad to help! Steve=True

Great solution thank you, but a big fail to Excel for making what should be a very simple task so complicated.

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

Super clever solution. Thanks!

Thanks for the nice comment. Glad to help

Great post. Very helpful. Thank you!

You are welcome. Thanks for the kind words.

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.

I fight to do it for hours until I saw your video. Thanks a lot!

You are very welcome. Thanks for the great comment.

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.

Hi Susan, not sure how your data is set up, but you may want to try and switch your rows/columns.

Thank you for this solution. Saved me some time and my sanity!

Thanks for the great comment. So glad to help. Steve=True

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.

Thank you so much for this Excel hack! 🙂

You are very welcome.

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

Hi Alexander, are you looking to do something like this? https://www.exceldashboardtemplates.com/how-to-setup-your-excel-data-for-a-stacked-column-chart-with-a-secondary-axis/

Thank you very much for this info! I was really struggling to get a solution for this problem 🙂

So glad to help. Thanks for the great comment!

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,

Hi Whitney, thanks for the question.

Yes this can be done with other padding data series. Here is a video that explains more: https://youtu.be/wzfhUetUk2o

this was great !!!
thanks so much

Thanks!

Brilliant solution

Thanks

Thanks it really helped me today..

So glad to help. Thanks for the nice comment!

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?

I am not sure, can you post some basic data and what is on the primary and secondary axis?

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!

Doesn’t work. As soon as I move the non-pad column to the secondary axis it overlaps again.

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.

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.

This is a brilliant method, but will not work if you want to add a Data Table below the chart.

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.

Thanks for the comment and note. I will have to take a look 🙂 and correct the picture.