How do I create a stock chart which has two Y-axes and one shared X-axis?

**Joe Miller** · 08-08-2009, 10:17 AM

In Excel 2007, how do I create a chart which has two Y-axes and one shared X-axis? [Or at least displays the Y-axis on the right rather than on the left].

I am able to do it for a clustered column chart but I cannot get the procedure to work for an “open-high-low-close” stock bar chart.

**masteff** · 08-11-2009, 11:02 PM

Did you figure it out yet?

I'm in Excel 2003 but I assume it's still the same in Excel 2007 based on the samples here: http://office.microsoft.com/en-us/ex...337501033.aspx

The trick is that the stock charts are simply line graphs will certain options selected.

So... to put two stocks on the same graph w/ separate y-axes...

1) First column is date, next four columns are open/high/low/close for stock A, and last four columns are open/high/low/close for stock B. It helps if the headers for stock B are different from those of stock A (ie Open-A & Open-B) (it makes step 3 easier, you can always change them later).
2) Start a LINE graph selecting all 9 columns for your data source.
3) Carefully click each line for stock B to select it, right click to choose "format data series" and assign each one to the secondary axis.
4) Again click each line (for both stocks), right-click to format and a) change line and marker to "none" and b) under "options" check both "high-low lines" and "up/down bars". Note that you only have to set the "options" once per stock because Excel figures out that the four lines are related.

P.S. - I was just playing w/ it some more... for step 3 and 4, after you do the format change for one line, you can click the next line and use ctrl-y to instantly repeat the format change, makes it go fast.

**Joe Miller** · 08-12-2009, 06:56 AM

Thanks,
I learned a lot from that - seriously. However I have only one stock and merely want to place the vertical axis on the right rather than on the default left side.

**Andy Pope** · 08-12-2009, 07:10 AM

Is the second Y axis just a repeat of the first or do you want other data plotted on it?

Can you post example of what you currently have.

**Joe Miller** · 08-12-2009, 07:28 AM

The second axis is simply a repeat of the first [i.e.] a simple stock bar chart with the axis on the left originally, but now I want to display the axis on the right. Just one axis on the right is ok, or two axes - one on the left and one on the right is ok. The objective is to somehow get an axis on the right where it is adjacent to the most recent bars because those are the bars of primary interest.