How can I neatly overlay a line graph series over a scatter plot?

4
votes

I have two series of data that should be able to easily share an X and Y axis.

Both series only have 12 unique values that should go on the X axis (one for each month in the 2015 year).

The difference is that Series 1 has only 1 value which corresponds to each month (hence the line graph), while Series 2 has several (for the scatter plot).

I want the scatter plot in the background with the line graph overlayed on top. It took me an hour just to figure out how to combine the line and scatter plot without major formatting issues (when I first tried simply changing the chart type of Series 1 to a line graph, almost all of the scatter plot points just dissappeared out of sight). Now I have it almost perfect, but part of the line graph is hidden behind the scatter points, and I would like to bring the line graph to the front. These are the steps I've taken to get it to this point:

  1. Insert a scatter plot of Series 1.
  2. Click "Select Data", and Add Series 2
  3. Right-click a data point for Series 1, Click "Format Data Series", and in "Series Options", choose to plot the series on the "Secondary Axis". A new Y axis appears on the right side of the graph in addition to the Y axis that was already on the left
  4. Right click a data point from Series 1, click "Change Series Chart Type", and make it a line graph
  5. Delete the right axis on the right of the chart
  6. How can I bring the line graph to the front?
1
excel
are the cells for series1 blank where series2 has a value, but series1 does not? – Scott Holtzman
It would help to share a sample of the data. There are a lot of ways to do this kind of thing, maybe with everything as scatter plots (which can be formatted to connect points with a line). – Jon Peltier

1 Answers

1
votes

I just replicated your issue and the following worked for me. I had my data set up like this:

enter image description here

  1. Right-click on the Chart and Choose Select Data
  2. Click Hidden and Empty Cells

enter image description here

  1. Choose Connect data points with line and check Show data in hidden rows and columns (EDIT: actually, only need to check Connect data points with line.)

enter image description here