3

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).

Screenshot

Is it possible to have multiple ranges treated as a single continuous range in formulae?

3
  • 1
    If it is a scatter chart with only point markers (no lines), the only thing that should distinguish the different "variables" is the marker symbol (and the legend would show three entries). You can change the marker symbol so they all match and remove the legend. If this is not what you're looking for, can you clarify the desired result or post a screen shot and describe what's wrong with that result?
    – fixer1234
    Dec 27, 2014 at 22:44
  • 1
    The problem is probably that you need to specify the X values (another name for scatter chart is XY chart). Enter each set as a separate data series, specifying X and Y values so the chart displays all three series. If you select all of the Y columns at once, edit the series definitions to specify the X values. Then you can play with the marker symbols if you want them to match.
    – fixer1234
    Dec 27, 2014 at 23:17
  • Thanks that sounds like a reasonable workaround. Perhaps you could write it up as an answer? If I don't get any other suggestions, I'll mark yours as the answer.
    – Amr Bekhit
    Dec 27, 2014 at 23:19

2 Answers 2

2

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.

1

I've discovered what the problem was. The reason why the graph wasn't turning out correctly was because I was selecting the entire column, including the heading and assuming that Excel would have discarded the heading.

Once I selected just the data from those multiple columns, I got exactly the graph I was expecting, where Excel was treating the multiple columns as one single continuous data series.

1
  • Excel needs something for X values and makes assumptions if you don't explicitly specify it. It can handle the inclusion of column headings but I suspect these are numerical values, so it used them as X values. Without the headings, it picks something else, perhaps another column or uses relative position in the list.
    – fixer1234
    Dec 28, 2014 at 17:32

You must log in to answer this question.

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