+ Reply to Thread
Results 1 to 5 of 5

Secondary Axis for 2 Currencies

  1. #1
    Registered User
    Join Date
    07-14-2010
    Location
    MA
    MS-Off Ver
    Excel 2003
    Posts
    3

    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!
    Last edited by lhf; 07-14-2010 at 01:42 PM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Secondary Axis for 2 Currencies

    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.

  3. #3
    Registered User
    Join Date
    07-14-2010
    Location
    MA
    MS-Off Ver
    Excel 2003
    Posts
    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.

    Thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    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. #5
    Registered User
    Join Date
    07-14-2010
    Location
    MA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Secondary Axis for 2 Currencies

    Thank you so much! This was enormously helpful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1