Previous Story
Stop Excel From Overlapping the Columns When Moving a Data Series to the Second Axis
Posted On 30 Jan 2014
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 Chart
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.
But it looks like Excel made it a Stacked Colum Chart How can I fix it?
This is the chart I really wanted:
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).
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 Series
2) Insert 2 Columns Between Tea and Coffee
3) Highlight Data Range and Create 2-D Clustered Column Chart
Your chart will look like this:
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:
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:
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 Axis
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:
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
!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.