Excel – How to plot multiple data sets with different x and y values on one graph

Assuming your data looks like this. Each set separate from the other, X values in first column, Y values in second column, blank cell above X values, series name above Y values. This is the standard Excel chart data layout that will cause the least frustration.

Select the first data set and insert an XY Scatter chart. (A line chart forces subsequent series to use the same X values, and the X values are not plotted according to value, but just according to sequence.)

Select and copy the second data set. Select the chart, choose Paste Special from the Paste dropdown on the Home tab, and choose these options: New Series, Values in Columns, Series Names in First Row, X Values in First Column. Click OK.

The result: two series with independent X values.

To update the chart each month, either paste new values on top of the old series 2 data, or delete the series in the chart and use the copy/paste special technique to add the new data.

I've recently written Multiple Series in One Excel Chart, a tutorial describing this technique.

Another name for scatter chart is XY chart. This type of graph plots Y values against X values, so both are required. The X axis is a continuous numerical value and something needs to define where to place values along it. On something like a line chart, the X values are just categories, so what goes on the X axis doesn't need to be explicitly defined (successive entries are just stacked next to each other). With a scatter chart, when you select just Y values, Excel uses some kind of built-in rule to define something for X values.

To plot what you are looking for, enter each dataset as a separate data series. You can enter X and Y columns to create the first series, then add each remaining series. Or, highlight the three Y columns to get the process started and then edit each data series to add the X values.

By default, Excel will use a different marker symbol for each series and will create a legend. You can delete the legend and edit the marker symbols so they all match.

If you want to have Excel handle this as a single series using its default settings, you would need to copy the data to a new structure where you actually arranged all of the data into one X and one Y column.