Share via

Graphing with logarithmic X axis and secondary Y axis

Anonymous
2011-04-14T18:35:09+00:00

I’ve come across a bug that occurs when using logarithmic x-axis scaling with primary and secondary y axes.

I am trying to graph two sets of data. The x-axis data is common, and needs to be displayed with a logarithmic scale. The y-axis data has different scales, and needs to be displayed on different axes. Here’s some example data that will demonstrate the problem:

X,Y1,Y2

1,1,100

3,7,110

10,25,173

30,14,137

100,12,88

I create a scatter plot of this data, put Y2 on the secondary axis, and choose logarithmic scale for the x axis. The Y2 data gets displayed on a linear scale, not a log scale. If I put Y2 back the primary axis, it displays just fine (but then the Y1 data is nearly invisible).

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-04-14T23:03:48+00:00

    You need to put the Y2 data on a secondary x-axis also (what a pain). Click the chart, open the ChartTool | Layout tab and locate the Axes group, click the Axis tool and specify Secondary Horizontal Axis - Show Axis with Log Scale.

    See image at http://cid-162119ff97286948.office.live.com/self.aspx/Public/XYchart.JPG

    I DO wish I could paste an image - was able to do it last week.

    If the second x-axisis bothersome in a report, just format it so the font colour is the same as the chart area and it will be invisible

    best wishes

    6 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-04-15T06:05:05+00:00

    ICMEngineer,

    The Help page (http://office.microsoft.com/en-us/excel-help/add-or-remove-a-secondary-axis-in-a-chart-HP010342149.aspx) seems to imply a secondary horizontal axis is optional rather than auto-generated - maybe the page could do with some re-wording.

    _________________

    Regards, Tom

    0 comments
  2. Anonymous
    2011-04-14T23:40:17+00:00

    Your graph looks just like mine, with a linear X axis for the Y2 data (if Y1 and Y2 were plotted on the same axis, their markers would line up vertically).

    However, your diagnosis is correct, and your instructions are perfect. After a little experimentation, I was able to produce the plot I wanted. Thanks!

    I have to say (note to Microsoft), this is a needlessly painful process. No secondary X axis ever appeared, and there are no secondary X axis options when I'm formatting the data series (I can format the Y axis from the "Format Data Series" dialog, but not the X axis). I had no idea that a secondary X axis even existed, and no idea how to get to it.

    This could definitely use some improvement - critical parameters shouldn't be this hard to find!

    0 comments
  3. Anonymous
    2011-04-14T20:22:52+00:00

    Only the X axis is log scaled; the Y1 and Y2 axes are linear. The X axis range is [1, 100], the Y1 range could be [0,30], and the Y2 range could be [0,200] (those are the defaults; they demonstrate the problem). To prove the Y2 data is being plotted on a linear X axis, you can set the X limits to [0,200]. The Y2 data will be plotted on half the X axis when you do.

    -- Mike --

    0 comments
  4. Anonymous
    2011-04-14T19:09:18+00:00

    ICMEngineer,

    Just to check, do you want both X and Y2 axes as log scales, or just X?

    Could you also give the min & max for each axis?

    _________________

    Regards, Tom

    0 comments