none
Secondary horizontal axis

    Question

  • I have a request to create an Excel chart with a secondary horizontal axis.  To get the smooth curve lines that I need, I'm using an X-Y Scatter chart.  So far, I haven't had any luck in getting what the user wants.  Here are some of the details:

    Sample data from spreadsheet:

    SecX PriX Data1 Data2 Data3
    6 .1319 91.52    
    7 .1102 68.54 100 100
    8 .0929 31.57 92.05 97.89
    10 .0787 2.54 32.95 79.54
    12 .0669 .23 6.14 53.4
    14 .0551   .25 25.05

    The chart has a primary (0-100) and secondary (100-) Y axis.

    The primary X axis is derived from column PriX.

    The user wants the SecX column to be the source of the secondary X axis (preferably on the top edge of the chart).  The "scale" of the SecX column isn't the same as the scale of the PriX column.  I'm at a loss as to how to do this or if it can even be done.  I'm leaning towards it can't be done.

    Does this make sense?  Anyone have any ideas?

    Thanks, Dan

    • Moved by Calvin_Gao Thursday, August 25, 2011 5:39 AM (From:Excel for Developers)
    Wednesday, August 03, 2011 8:32 PM

All replies

  • Which version of Excel are you using?
    Regards, Hans Vogelaar
    Wednesday, August 03, 2011 8:44 PM
  • Hi Dan,

    Thanks for your post.

    For end-user question, however, I would  recommend you to ask it at Excel Answer forum:

    http://answers.microsoft.com/en-us/office/forum/excel

    This forum is for some discussions related to Excel development.

    Thank for your understanding and support.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 04, 2011 6:52 AM
  • Hi

    Something is possible but you haven't mentioned which data series is associated with the second X axis.

    I'll assume it's Data3 so you'll have to adapt the following to suit your needs.

    • Select B1:D7
    • Insert a chart X-Y scatter
    • In Chart Tools select Design -> Select Data
    • Click Add
    • Set Series name as E3
    • Set Series X values A2:A7
    • Set Y value E2:E6
    • OK out of this dialog
    • Select Layout -> Axes -> Secondary Horizontal -> Show Default

    That will be enough to get the rough design of the chart with the axes as you wanted I hope.

     

     


    G North MCT
    Thursday, August 04, 2011 7:59 AM
  • Thanks for the responses.  Sorry I forgot to mention the version of Excel.  It's Excel 2010.

    G -- That's pretty much what I have done, but because I'm not dealing with a linear scale for the secondary X axis, the values don't match up correctly with the primary X axis.  It seems like it's "almost" logarithmic, but I can't even get a logarithmic scale to match up correctly.

    I'll try the regular Excel forum too.

    Thanks again.

    Thursday, August 04, 2011 1:52 PM
  • Hi

    Odd.  Using the data provided and Data3 asigned to the secondary axes I got 0, 5, 10, 15 across the top and 0, 0.02, 0.04, 0.06  ... 0.14 across the bottom

    -20 - 120 on the left  ans 0 - 120 on the right in intervals of 20.

    I checked the steps again, missed one before the last.

    Select the Data3 series and then Format Data series and assign to the secondry axis.


    G North MCT
    Thursday, August 04, 2011 2:57 PM
  • Yes, I get the same thing.  The problem is that this is how the primary and secondary X axis should line up (this is the full set of data I'm working with for the pri & sec X axis):

    Primary Secondary
    .1575 5
    .1319 6
    .1102 7
    .0929 8
    .0787 10
    .0669 12
    .0551 14
    .0465 16
    .0394 18
    .0335 20
    .0280 25
    .0236 30
    .0197 35
    .0167 40
    .0118 50
    .0098 60
    .0083 70
    .0059 100
    .0042 140
    .0030 200

    The primary X axis is from 0 to .16.  The secondary is from 0 to 200.  As you can see, it's not linear.  Any other thoughts?

    Thanks

    Thursday, August 04, 2011 3:15 PM
  • Hi

    Not sure this is what your aiming for, but have you tried this as a line chart (The lines are necessarily straight if you don't want them to be).

     


    G North MCT
    Thursday, August 04, 2011 3:52 PM
  • G,

    The lines for the actual data series need to be smooth curved lines.  I could only get that result by using the XY scatter chart.

    Thanks for the time you took to think about this.

    Thursday, August 04, 2011 5:35 PM
  • Hi

    In the format options for the data series of a line chart there is an option for Smoothed line.  Look for Line Style in the Format data series dialog, it's at the bottom.

    Other than that did you get the axis displayed the way you want them?


    G North MCT
    Thursday, August 04, 2011 6:24 PM