Plotting Two Data Sets on One Graph
Two (or more) data sets can be plotted on a single graph. In the following data set we will plot melting point and density against the atomic number (x-axis).
First, select the first column by left-clicking on the 1 at the top of the column. The entire column will be highlighted. Excel will use the 1st row (At. No.) as Legend information in the plot. Next, select column 7 by left-clicking on it while holding down the control key, ctrl-left-click. Now both columns 1 and 7 should be highlighted. Finally, select column 11 with another ctrl-left-click and now 3 columns should be highlighted as shown below.
Next choose Chart... from the Insert menu. In the 1st step of the Chart Wizard select XY(Scatter) type chart and click on the example which has the lines connected. (This is an unusual example of when it is actually useful to have dot-to-dot connections of a data set.)
Step 2 of the Chart Wizard shows roughly what the plot will look like.
Step 3 of the Chart Wizard allows you to enter Axis Titles, add Gridlines
Step 4 of the Chart Wizard allows you to either create a small graph within your spread sheet, or create a chart as an entire new page in the workbook. Select "As new sheet" so that the graph will be large enough to work with on the screen.
Notice under the chart below that there is a "Chart 1" tab with the newly created plot, and a "pt data" tab with the original data sheet. the problem with this plot, of course, is that the y-axis is taken only from the maximum range of both data sets. Thus, the density data set, which has a small range, does not show a trends well on this scale. To correct this scale problem, we will add an additional y-axis for the density data and put it on the right side of the plot.
Right-click on any of the density data and then left-click on the "Format Data Series..." option.
Select the Axis tab in the Format Data Series dialog box. Chose Secondary axis and the Density data will be rescaled as a new axis is added to the right side of the plot. You will need to put a y-axis label on the right side but that cannot be done in this dialog box, so just hit OK to return to the plot.
Right-click in the open area (gray here) in the plot, then left-click on "Chart Options..." in order to type in the secondary y-axis title.
Type in the secondary axis title (Density (g/cm^3) here) and select OK.
The final graph shows the similarities and differences in the respective Periodic Trends of Melting Point and Density.