How to Create Excel Chart With 2 Y-axis or X-axis?
If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..
Sometimes its necessary to have two charts overlapping each other which helps in better understanding of the problem or the scenario. In those cases, it maybe useful to have two Y-axis or X-axis in a chart to visualize it.
So, how to create an Excel Chart with 2 y-axis or x-axis?
My first advice to anyone who wants to have a good chart in Excel is to have Excel Chart Advisor Plugin.
I’ll explain how to do it without that plugin but it will help you in your future charts.
Lets say we have a data like below with Item, Net Value & Profit and we want a chart like the one mentioned below.
Choose the columns that you want to be in the chart, in our case we need to select Columns A, B and C. Go to Insert tab and choose the chart type you want. I have chosen Column type chart.
And this is how it will look like, we have Item on the X-axis and Net Value and Profit on the Y-axis but they are both on the same side.
This doesn’t explain the data clearly, so you have to try to display the Profit in a different form which will help us in understanding the chart better.
Click in the chart near the area which corresponds to profit, this will be highlighted with small bubbles at the end. Right click and Choose Change Series Chart Type.
You can choose a different chart type, say line chart. Again right click and choose Format Data Series.
Under Series Options, choose Secondary Axis and click close.
Now you’ll have two Y-axis in the chart.
You can change the titles and legend options from Chart Tools –> Layout –> Axis Titles and Legend
And here is the final chart with two Y-axis which is much better to understand the table.
Click here to read more Excel Tips like this.
Hi
Thanks
It really helped me in my presentation
Good stuff!!!
Very nicely explained. Detailed yet comprehensive.
Thank you.
Thanks, I’m very happy when read this instruction because it helped me so much.
Thnx
very nice solution.
This didn’t explain how to create a second x axis. Its quiet frustrating. Please help. It’s not the same process…
Great tip, very clear – many thanks.
Additionally when you are trying to ‘Click in the chart near the area which corresponds to profit’ it may not always be clickable if values are very small relative to the other chart areas. Therefore if you click anywhere in the chart and scroll through the different chart areas (just by pressing the arrow key) until the desired data set is higlighted with bubbles so you can then change series chart type.
I agree with ben,
There’s no reference as to how one adds a secondary x-axis. a process that was extremely easy with 2003a program which for some reason my employer has disposed of entirely.
a secondary x-axis is incredibly useful tool. It’d be a shame if Microsoft gave us yet another reason to stop using their software.
I have just found someone with the info for x-axis.
at the top of the window there will be a tab that says chart tools
below that select “layout”. below that select “axes”.. It’s right there….
I can’t get it on the bottom of the window yet.. but hey baby-steps eh?…
THANKS A LOT!!!