I've got a spreadsheet that has several columns that hold the same type of data (a list of average measurements). They're separated into different columns as each on represents a slightly different measurement.
I have a need to plot a scatter graph of the data in several of these columns as if they were concatenated into one single column and were one range. Simply CTRL+clicking multiple columns seems to create three ranges separated by commas and doesn't give the expected result on the graph (it seems to treat them as three distinct sets of data rather than combining them into one).
Here's an example of what I mean. The graph on the top right was created simply by setting the series name, leaving the series X-values blank and then selecting a single column for the series y-values. This creates that scatter graph I expect.
I then try to do the same thing for the graph in the bottom right, except I CTRL+click three columns for the series y-values. Unfortunately, it looks completely different (I don't really understand how it's representing the data and where it's getting those x-values from).
Is it possible to have multiple ranges treated as a single continuous range in formulae?
Best Answer
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.