Stacking correlated data sets on a single axis.

[TheWalrus]

Hello all. I am having a very difficult time in Excel 2010 with what I had hoped was a common problem. A search of your archives turned up nothing fruitful however.

I want to show a relationship between two sets of data by overlaying them on a single chart. The x-axis is a non-number set consisting of the names of several molecules. The two sets of y-axis data are vastly different numerically, but are crucially inversely related (one data set is ionization energy and the other is proton affinity if anyone actually cares)

Is there any way to set up two separate y-axes on a single line or scatter chart so that two sets of numerically distant data can be plotted together without horrible scaling issues?

Thank you in advance oh wise Excel Gurus! ray:

 

[TheWalrus]

Here are the two data sets I'm trying to stack. The two additional data points on the Proton Affinity chart will not be included.

 

[Jon Peltier]

How do you want to "stack" the data?

You could plot all data in one chart, leave Proton Affinity on the primary axis, and one by one format the Ionization series so they are plotted on the secondary axis. Keep in mind that secondary axes may confuse people, making a secondary-axis chart less effective than two primary-axis-only charts.

Wouldn't the relationship be best portrayed by plotting Proton Affinity as X and Ionization Energy as Y? You can use Rob Bovey's free Chart Labeler add-in to put the molecule names onto the associated data points.