Posting a workaround to re-introduce a feature for Excel 2013 that I think was removed for some reason and for which information on Excel 2013 (OSX-specific) is impossible to find through google searches. It is my hope that newer versions of Excel don't have this thoroughly annoying problem (and if there's an obvious way I don't know about to make this happen in a single shot, feel free to drop a line).
If you found this page via google, I'm going to assume you were searching for something like the following questions (which I'm including below so that search engines find similar questions):
How do I add:
– data with two different X-axes to a single plot in Excel?
– multiple plots to the same graph in Excel with different X-values but the same X-axis units?
– a second dataset to a plot in Excel with new X-axis values?
– a new dataset with different abscissa values in Excel?
– a secondary X-axis to plot new data on the same graph in Excel?
My Scenario – Overlaying Two Spectra On The Same Graph
This issue came up for me when trying to generate some simple spectral overlays in Excel. The problem proceeds as follows:
1. You've two datasets with the same X-axis values.
2. You've a third dataset for which the X-axis points have the same units, but different values.
3. You plot this third dataset with Chart… -> Add Data by selecting your X-axis and Y-axis values (just selecting the columns).
4. You don't get the expected results.
Ideally, Excel would see that the headers for the X-axis columns have the same exact labels in all the datasets and treat the new points (in Step 3) as values to be accounted for within the same range of numbers as the previous plot. If you plot dataset 1 and add dataset 2, there's no problem (because the X-axis column is identical). Step 3 will set you back an hour or so (assuming this isn't in the Help Pages, which take too long to load anyway).
The steps below describe a way to recover an old functionality in Excel 2007 that just simply worked without issue.
1. My Working Excel Sheet
To set the stage, my sheet is set up as shown below.
Rows 7-3115 and 3122-3498 are hidden for clarity
Col A: cm-1 – my X-axis values, with label
Col B: Exp.: Time=0 – My first dataset
Col C: Exp.: Time=20 – My second dataset; uses the same X-axis values as Col B (cm-1)
Col F: cm-1 – my X-axis values for a new dataset (in G,H), scalable using Cell J2
Col G: Theory – my calculated values
Col H: Theory (Scaled) – my calculated values in Col G, scalable using Cell J5
Col J Rows 1,2: X Scaling – used to scale the values of the calculated data along the X-axis (actual values in Col E)
Col J Rows 4,5: Y Scaling – used to scale the values of the calculated data along the Y-axis (Col H)
To explain briefly (for the non-spectroscopists), your calculated X-axis (energy, here in wavenumbers) and Y-axis (intensity) values are/can be adjusted for better agreement with experiment. In this case, I'm applying a global scalar to the X-axis (Cell J2 – make the plot wider or narrower) and Y-axis (Cel J5 – make the plot taller or shorter) values. These values will be adjusted to taste in the final images. I'm going into a little more detail than one would otherwise need because how you make your data fit properly will depend on how you change the source data (and not doing it properly will lead to a fitting problem. You'll see shortly).
2. First + Second Plottings
After plotting the first dataset (Col A + Col B) as a Scatter Plot and doing some cleaning up, you get the image below.
To add the data in Col C (which we see already uses the same X-axis values as Col B), you simply go to Chart -> Add Data, select Col C, and you should get the image below (20 min data in green):
3. Where The Trouble Starts
To this new plot we want to add the Theory results. You'll note that the experimental X-axis values differ in their increments from the theoretical data. But X-axis values are X-axis values, right?
Go to Chart -> Add Data, then select the new X/Y data you want to include in the plot. When adding the data, you should see the following.
Not cool. Instead of reading Col F as new X-axis values with the same units (with Excel 2007 did just fine), Excel 2013 sees this as a new dataset using the original X-axis values in Col A. The Theory plot in Col H (red) looks like it worked, but you'll see shortly that the defaulting to the X-axis values in Col A has resulted in very poor agreement with experiment (and it's the wrong X-axis values, so this is no surprise).
First off, the cm-1 (black) values are bogus, so delete that dataset to beautify the plot. That image is below.
4. Partial Fix (Nearly The Same As The Full Fix)
We clean up the spectrum by right-clicking on the plot and choosing Select Data… (shown below)
This will take you to a Select Data Source window where one of the Series will be selected. Click on the dataset you want to change the X-axis column for (here, Theory (Scaled)). You will see next to the obvious red arrow that the X values: column is reading $A$2:$A$3501. You'll note that Col A has no data in cell A3501 (that's bad enough).
Now, for the Partial Fix, double-click on the content of X values: to select it all, and then click on the column you want the X-axis values to be (for me, Col F). X values: will become the following:
If you do this and hit OK, you'll see the plot below, which is just what you expected from a well-behaved Excel program.
Your X-axis is as it should be, even if the peak intensities for the theory are too high. That you can remedy by changing Cell J5 (results below).
That is much better, but you can see that the most prominent peaks (around 1600 cm-1) are calculated too high. This is why there's a scaling factor in Cell J2. If you change the value of Cell J2 to, say, 0.973, you produce the following plot:
Which, as you can see, is exactly the same as the previous plot. Our X-axis scaling factor had no affect.
5. And Now, The Full Fix
We selected the new X-axis column (Col F) correctly, but Excel won't give us our proper scaling unless we specifically define the range of cells used for the X-axis values. So, we go back to Select Data… (right click) and put the actual cell numbers in. At the obvious red arrow below…
Change the $F:$F to our actual range, $F$2:$F$3501
Hit OK. If you didn't change your J2 value back to 1, you should see that your plot slid right into place (granted, the theory doesn't line up all that well anyway, but that's a problem for a different post).
And that's it. May this post spare you the time wasted searching for a solution to a problem that didn't previously exist.