# Secondary Axis for 2 Currencies

1. ## Secondary Axis for 2 Currencies

I would like to create a column chart that has 2 axes--one for \$US and one for \$CAN (so that each column corresponds to \$US on the left and \$CAN on the right). The only way I can figure out how to do this would be to enter the \$US and \$CAN separately, so the columns would be separate for each currency. There have been threads about this before (http://www.excelforum.com/excel-char...ry-y-axis.html), but it seemed to me that those methods would only work for line graphs. Any help you could give me would be appreciated!

2. ## Re: Secondary Axis for 2 Currencies

Upload an example, I'll walk you through it.

3. ## Re: Secondary Axis for 2 Currencies

Here's an example. If possible, I would like the columns to be stacked (with Y1 and Y2 being the stacked series), and the Canadian values on the right of the spreadsheet to be a secondary axis of the chart.

4. ## Re: Secondary Axis for 2 Currencies

(We're basically doing the thing explained in the second link of your reference)

In order to make the numbers line up correctly, we need to set the maximum of the secondary y axis manually - this means the values in the second series are not really relevant - in fact, it's easier if we just make them 0 so we don't have to worry about them (someone should explain this to Tushar...), so:
D2=Dummy (not strictly necessary either)
Click chart - expand source data range over to include D3:D7
I have source data =Sheet1!\$B\$2:\$D\$7 (in order to retain series labels)
Click a blue bar, this should select the Y1 series, press up to select the red series, then up again to select our empty series
Format->selected data series
Axis = secondary (this creates the secondary axis, currently with a meaningless scale)
OK
Click the legend, then click the 'dummy' entry in the legend (selection should be specifically aorund that legend entry)
Delete
The axis maximum in CAD is the axis maximum in USD / conversion rate - i.e. 160/0.973 = 164.9
Right-click a number on the secondary axis
Format axis
Scale
Minimum = 0
Maximum = 164.9

JD

5. ## Re: Secondary Axis for 2 Currencies

Thank you so much! This was enormously helpful.

There are currently 1 users browsing this thread. (0 members and 1 guests)