2

I have two data sets each containing 10 coloumns of data.
X values in data set 1 go from 380 - 750 in steps of 5
X values in data set 2 go from 250 - 2500 in steps of 1

Data set 1 is permanent and will never change, however, data set 2 will be replaced every month with more up to data values.

I would like to know if there is:
1) An easy way to plot both data sets on the same scatter chart
2) An easy way to update the chart each month with the "new" data set 2.

What I have tried -

I currently plot each series individual by "selecting data" and editing the appropriate X and Y values.

I have tried giving each data set a defined name and plotting by entering the names into the "chart data range" box, but this plots both data sets against the X values from either set 1 or 2 and since they are different lengths this doesn't work. I have seen this link before, but instead of appending the new month's data to the old as in that example, I want to completely replace the old data set 2 with the new data set 2 while keeping data set 1 plotted as it was.

Improve this question
4
  • What have you done already? Where are you stuck? Have you tried plotting them? Did you look at part 2 of this?
    – Raystafarian
    Commented Oct 3, 2014 at 11:30
  • I currently plot each series individuall by "selecting data" and editting the appropriate X and Y values. I have tried giving each data set a defined name and plotting by entering the names into the "chart data range" box, but this plots both data sets against the X values from either set 1 or 2. I have seen that link before, but instead of appending the new months data to the old as in that example, I want to completely replace the old data set 2 with the new data set 2 while keeping data set 1 plotted as it was. Hopefully I haven't confused you :/
    – KineticSteve
    Commented Oct 3, 2014 at 13:00
  • I added that information into your question so it's easier to determine what might be going wrong.
    – Raystafarian
    Commented Oct 3, 2014 at 13:42
  • "but this plots both data sets against the X values from either set 1 or 2" - This is line chart behavior, not scatter chart behavior. Did you choose the chart type you intended?
    – Jon Peltier
    Commented Sep 10, 2015 at 12:02

1 Answer 1

Reset to default
3

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.

scatter plot data

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.

paste special dialog

The result: two series with independent X values.

scatter plot

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.

Improve this answer
4
  • Thanks for the reply Jon but the problem is a little more complicated than that. As the top image shows, for each data set I have one set of x values (columns A and M) and 10 sets of y values (columns B - K and N - W). The first set are those that remain constant and the second set need to be replaced every month tinypic.com/r/dbjfh1/8 tinypic.com/r/5e7qwx/8
    – KineticSteve
    Commented Oct 6, 2014 at 12:07
  • More data, but not more complicated. Where I show Data Set 1, pretend it's Data Set 1A through Data Set 1J with X in column A and Y in columns B:K. Create the chart with this data. Where I have Data Set 2, pretend it's Data Set 2A through 2J, with X in column M and Y in columns N:W. Copy this range and use Paste Special to add these series to the chart.
    – Jon Peltier
    Commented Oct 7, 2014 at 13:43
  • Thanks Jon, just tried it and it has made things 10 times easier! Is there also a way to get all of the curves to display as lines on the scatter graph rather than points, or is this something I will have to change manually for each curve?
    – KineticSteve
    Commented Oct 8, 2014 at 14:04
  • 1
    Select the whole chart (not just one series) and change the chart type (it's on the right-click popup menu) to scatter with lines and no markers.
    – Jon Peltier
    Commented Oct 9, 2014 at 17:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .