0

Is there a way to introduce a secondary horizontal axis into an MS Excel scatter plot chart? I'm using Office 2013 on a Windows desktop.

More specifically, I have a series of outputs Y. Each "Y" corresponds to two different sorts of inputs X1 and X2. Let's say X1 is meters, X2 is yards, and Y is .... something else. So, I want to plot Y so that each Y point is sitting right over its corresponding value of meters (X1) on the lower horizontal axis, and at the same time, each Y point is sitting right below its corresponding value of yards (X2) on the upper horizontal axis. Can this be done?

But please note: I have seen similar questions to this on this site but they either 1) deal with formatting axes that are already present, 2) deal with vertical axes, 3) deal with plots other than scatter plots, or 4) just don't have an answer. So please don't mark this as a duplicate unless you are sure you have found a true duplicate that has an answer. Thanks.

2 Answers 2

1

The other answer was incomplete, so I've redone it. See my screenshot below.

You need to start with your primary data, I'm assuming in meters, and calculate the secondary values, yards. Select the Meters data and hold CTRL while selecting the Y Value data, and insert a scatter chart (blue markers first chart).

Select the Yards and Y Value data and copy it, then select the chart, and use Paste Special to add the data as a new series in columns, with series names in first row and X values in first column (added orange markers in second chart).

Select the orange data in the chart, press Ctrl+1 (numeral one) to open the Format Series task pane, and select Secondary axis. Excel adds a secondary Y axis (third chart, bottom left).

Click the '+' icon floating beside the chart, click on the right-pointing triangle next to Axes, check Secondary Horizontal Axis and uncheck Secondary Vertical Axis (fourth chart, middle of bottom row).

Finally, sync the axes. I've made a calculation in the sheet below my data to make this easier. I know I want the Meters axis to end at 30, and this converts to 32.8084 yards. Double click the Meters axis, and manually type in zero and 30 in the Format Axis task pane for minimum and maximum so that they become hard-coded (no longer automatic). Then double click the Yards axis, and enter zero and 32.8084 for min and max. The points coincide and the axes show the proper relationship between yards and meters (last chart).

Primary Axis Meters, Secondary Axis Yards

0

Something like this? Create 2 series enter image description here

edit series 1 enter image description here

edit series 2 making sure the Y values is the same as series 1 enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .