I need help making a scatterplot with multiple data series using named ranges. I made up some data in the attached pic, but my data looks similar to what is shown in the attached pic, except it goes on for a thousand rows. I would like to create a scatterplot where the first series x-values (column B) and y-values (column C) are those corresponding to Type 1 (column A) only, where the second series x and y values are those corresponding to Type 2 only, and so on and so on. Basically, I need a formula for a named range that scans column A, identifies all the Type 1s, then sets the range of x-values in column B to be those corresponding to Type 1 only. Same for the range of y-values in column C. Then repeat the process for Type 2, and so on. Essentially, I need a formula for conditional named ranges. My goal is to make a scatterplot similar to that shown in the attached pic (which I did manually the cumbersome way, not using named ranges). Any help you can give me would be greatly appreciated.
1 Answers
Excel does not automatically identify series based on row data. However, assuming that know the unique values that may come for type (column A) beforehand, you may use the below workaround. First, enter the formula =B2 in cell D2. This is simply to make the range selection easier later on and can be omitted but then you will have to select the disjointed range when making the chart by using Ctrl key.
Next, in row 1 from column E onward, enter the distinct values for Type that you expect will come in the data (see cells highlighted in Yellow). This is to simulate different series of data.
Now, simply enter this formula in cell E2:
=IF($A2=E$1,$C2,NA())
and drag it down and to the right till the last row and last column. If you look at the data, this has simply added the value of Y under the appropriate type and all other types are #N/A (this is required because any other text or blank will be considered ax 0 by the graph and it will mess up the view.
Once this is done, it is easy, simply select the range from D2 till last row and last column of your data and add the scatter chart. It will give you the view you are looking for. You may then modify the series name, chart title etc. by using the chart options.
XandYseries for the Scatter Chart regardless if you are using the actual range or a named range. There are multiple ways of defining the named range but at the end of the day you still need to manually replace the actual range for each type with the named range. So the workload is pretty much determined by the number of different types you have from your actual data set. There are solutions for setting up dynamic range for charts in this community already. - Terry W#powerquerywhich can extract the X and Y values per type easily but you still need to manually set up the series for the chart once that is done. - Terry W