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!
Last edited by lhf; 07-14-2010 at 01:42 PM.
Upload an example, I'll walk you through it.
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
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.
Thanks in advance!
(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
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Thank you so much! This was enormously helpful.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks