+ Reply to Thread
Results 1 to 4 of 4

Scatter plot with multiple series from 1 table

  1. #1
    Registered User
    Join Date
    12-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2016
    Posts
    18

    Scatter plot with multiple series from 1 table

    I enter the data into excel as I am performing the experiment and my final goal is to show the results like the chart shown below, which is created in GraphPad:

    Bar & Scatter.png

    I know it's difficult to make this in Excel but I am wondering if it is possible to make something similar? e.g., make separate charts like like like showing below, so that I can see the variability of each group as my experiment progress (I can make additional bar graphs to see the means):

    Scatter plots.png


    Below is the example of my data table

    Subject Group Measurement
    1 control 768
    2 Treatment-1 334
    3 control 307
    4 treatment-2 4840
    5 treatment-1 5150
    6 Treatment-2 8710
    7 control 10500
    8 treatment-1 6970


    Thanks a lot in advance!
    Last edited by bluesky6688; 02-15-2017 at 02:35 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Scatter plot with multiple series from 1 table

    I am not sure that the column + XY scatter combination chart that you show would be that difficult to create in Excel. Are you familiar with the basics of creating charts in Excel? with creating combination charts?

    Starting help file for creating charts in Excel: https://support.office.com/en-us/art...3-b3fd5d5680c2

    Assuming the data and the statistics for creating the chart is in the spreadsheet:

    1) Select the column chart data and create the column chart.
    2) Add the error bars for the column chart series.
    3) Select the data for the XY scatter chart series. Copy -> select chart -> paste special -> as new series with x values in first column/row. Note that the series will be added as a column series.
    4) Select the XY scatter series -> Change series chart type -> XY scatter (you may need to separately add the secondary x and Y axes, if needed.) http://best-excel-tutorial.com/56-ch...s-in-one-chart
    5) Format chart as desired.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    12-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Scatter plot with multiple series from 1 table

    Thanks for your replying, MrShorty. I use Excel everyday and I know the basics of creating charts in Excel, including creating combination charts.

    In order to create a combination chart, I need to reorganize my data so that each column is one group. This is okay if all my experiments are finished, otherwise I need to do this everyday. It's better to keep the original data format because I have multiple measurements for each subject and it is easier to do statistics using formulas.

    I have simplified the figures and table hopefully they are easier to understand.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Scatter plot with multiple series from 1 table

    In order to create a combination chart, I need to reorganize my data so that each column is one group.
    I find that a lot of charting questions are really about how to rearrange the data in the spreadsheet. What process do you use at the end of the experiments to rearrange the data from the list input to a good charting table format? Pivot table? filters? copy/paste? helper columns? You are going to need to do something with the data in order to get three separate scatter charts. If we understood how you like to rearrange the data, we should be able to come up with some way to rearrange the data quickly and easily so that you can analyze the data mid-experiment.

    I have included an example of one way to approach this. I used several "helper columns" to separate each measurement type into separate columns and get the data needed for the XY scatter chart. I used a pivot table to get the summary statistics (I used average and standard deviation, since I could not tell from you OP what you are using -- the points/columns in your picture don't match any of the data in the list). The trickiest part of creating this chart is that you cannot let the chart become a pivot chart, which would not be an issue if you are not using a pivot table to get your summary statistics. If you can figure out something like that, then you should be able to create your chart in such a way that you can monitor your data as you collect it.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-26-2016, 06:47 AM
  2. Scatter Plot Chart with multiple series
    By hyattj in forum Excel General
    Replies: 0
    Last Post: 08-14-2014, 03:36 PM
  3. Scatter plot multiple series
    By mitchurugie in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-17-2013, 11:59 AM
  4. multiple series scatter plot graph
    By airone12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-09-2012, 07:46 AM
  5. Scatter plot, multiple series (data) help!
    By LuckiCharmzXP in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-01-2011, 06:33 AM
  6. multiple series on a scatter plot
    By RobynB in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-17-2011, 07:51 PM
  7. Multiple series on a X-Y scatter plot quickly
    By gregoir in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-23-2010, 04:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1