Average bar charts with individual data points

Discussion in 'Excel Charting' started by Nimbox, Apr 25, 2013.

  1. Nimbox

    Nimbox Guest

    Hi,

    I am trying to produce a bar chart displaying averages for 4 variables
    but also plotting the individual data points horizontally on each bar.

    This is the most similar example I could find
    http://jcb.rupress.org/content/177/1/7/F1.expansion

    I am awful with excel; obviously I have managed to make the simple ba
    chart with the average values, but I am having difficulty adding th
    individual data points to each bar. Apparently this involves combinin
    scatter points.

    Is anyone able to explain simply how I do this?

    Cheer


    --
    Nimbox
     
    Nimbox, Apr 25, 2013
    #1
    1. Advertisements

  2. Nimbox

    Guest

    On Friday, April 26, 2013 7:15:25 AM UTC+12, Nimbox wrote:
    > Hi,
    >
    >
    >
    > I am trying to produce a bar chart displaying averages for 4 variables,
    >
    > but also plotting the individual data points horizontally on each bar.
    >
    >
    >
    > This is the most similar example I could find:
    >
    > http://jcb.rupress.org/content/177/1/7/F1.expansion
    >
    >
    >
    > I am awful with excel; obviously I have managed to make the simple bar
    >
    > chart with the average values, but I am having difficulty adding the
    >
    > individual data points to each bar. Apparently this involves combining
    >
    > scatter points.
    >
    >
    >
    > Is anyone able to explain simply how I do this?


    (Excel 2007) Here is a way which might do what you want but it is a little involved. Make a table with categories and the data and averages, something like this -

    A B C D E
    15 50 26 31 36
    34 13 50 37 24
    37 39 35 38 21
    49 47 23 17 40
    33.75 37.25 33.5 30.75 30.25

    Highlight the whole lot and Insert - Line chart - pick the first one with dots. Delete the legend. You now have five lines of different colours and markers.

    Locate the line which is the averages, right click on it and Change Series Chart Type to Column. You now have one bar for the averages and four lines..

    Now right click on one of the line charts - Format the Data Series. Make the Line Colour = No line. Make the Marker Options = Built in - round size 3 or whatever you like the look of. Marker Fill = Solid Fill black. The co;oured line should now be little black dots.

    Repeat with the other three lines.

    Cheers

    dc
     
    , Apr 29, 2013
    #2
    1. Advertisements

  3. Nimbox

    Guest

    This is great
    thanks so much

    On Sunday, April 28, 2013 10:15:30 PM UTC-7, wrote:
    > On Friday, April 26, 2013 7:15:25 AM UTC+12, Nimbox wrote:
    >
    > > Hi,

    >
    > >

    >
    > >

    >
    > >

    >
    > > I am trying to produce a bar chart displaying averages for 4 variables,

    >
    > >

    >
    > > but also plotting the individual data points horizontally on each bar.

    >
    > >

    >
    > >

    >
    > >

    >
    > > This is the most similar example I could find:

    >
    > >

    >
    > > http://jcb.rupress.org/content/177/1/7/F1.expansion

    >
    > >

    >
    > >

    >
    > >

    >
    > > I am awful with excel; obviously I have managed to make the simple bar

    >
    > >

    >
    > > chart with the average values, but I am having difficulty adding the

    >
    > >

    >
    > > individual data points to each bar. Apparently this involves combining

    >
    > >

    >
    > > scatter points.

    >
    > >

    >
    > >

    >
    > >

    >
    > > Is anyone able to explain simply how I do this?

    >
    >
    >
    > (Excel 2007) Here is a way which might do what you want but it is a little involved. Make a table with categories and the data and averages, something like this -
    >
    >
    >
    > A B C D E
    >
    > 15 50 26 31 36
    >
    > 34 13 50 37 24
    >
    > 37 39 35 38 21
    >
    > 49 47 23 17 40
    >
    > 33.75 37.25 33.5 30.75 30.25
    >
    >
    >
    > Highlight the whole lot and Insert - Line chart - pick the first one withdots. Delete the legend. You now have five lines of different colours andmarkers.
    >
    >
    >
    > Locate the line which is the averages, right click on it and Change Series Chart Type to Column. You now have one bar for the averages and four lines.
    >
    >
    >
    > Now right click on one of the line charts - Format the Data Series. Makethe Line Colour = No line. Make the Marker Options = Built in - roundsize 3 or whatever you like the look of. Marker Fill = Solid Fill black.. The co;oured line should now be little black dots.
    >
    >
    >
    > Repeat with the other three lines.
    >
    >
    >
    > Cheers
    >
    >
    >
    > dc
     
    , Apr 19, 2014
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest
    Replies:
    2
    Views:
    249
    Andy Pope
    Dec 16, 2003
  2. del
    Replies:
    1
    Views:
    300
    Jon Peltier
    Mar 31, 2006
  3. del
    Replies:
    2
    Views:
    273
    MrShorty
    Mar 31, 2006
  4. Richard
    Replies:
    1
    Views:
    244
    Jon Peltier
    Oct 26, 2006
  5. lenos
    Replies:
    2
    Views:
    144
    Del Cotter
    May 31, 2007
  6. doug
    Replies:
    2
    Views:
    246
    Jon Peltier
    Oct 4, 2007
  7. Darril
    Replies:
    8
    Views:
    178
    Drag and Drop data points in excel chart
    May 20, 2009
Loading...