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?
- Moved by Calvin_Gao Thursday, August 25, 2011 5:39 AM (From:Excel for Developers)
Thanks for your post.
For end-user question, however, I would recommend you to ask it at Excel Answer forum:
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.
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
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.
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
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?
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