I have groups of data points in categories. I can easily calculate the
average in each category and show these as a bar chart, but is it possible to
show the individual data pioints in each category as well?

Thanks,

1. First, create your bar chart with the averages for each category.
2. Right-click on one of the bars in the chart and select "Add Data Labels" from the drop-down menu.
3. The averages for each category will now be displayed on the chart. To add the individual data points, right-click on one of the data labels and select "Format Data Labels" from the drop-down menu.
4. In the Format Data Labels pane that appears on the right-hand side of the screen, check the box next to "Value From Cells."
5. Click on the "Select Range" button and then select the range of cells that contains the individual data points for the category you want to add them to.
6. Click "OK" to close the Format Data Labels pane.
7. The individual data points for that category will now be displayed on the chart next to the average.
8. Repeat steps 3-7 for each category in your chart to add the individual data points to all of them.

Hi,

How are you plotting the data points - as columns or line? and also could
you show us dummy data layed out so we can understand what you data looks
like. If you plot a clustered column chart you could show the average for
each cluster as a line within the cluster or as a single point.

--
Thanks,
Shane Devenshire


"lenos" wrote:

I have groups of data points in categories. I can easily calculate the
average in each category and show these as a bar chart, but is it possible to
show the individual data pioints in each category as well?

Thanks,

On Wed, 30 May 2007, in microsoft.public.excel.charting,
lenos said:

I have groups of data points in categories. I can easily calculate the
average in each category and show these as a bar chart, but is it possible to
show the individual data pioints in each category as well?

Yes it is. I would recommend using a combination of the bar chart series
with a scatter chart series.

1) Give the data "Y" points an "X" value of 1 to n, depending on which
bar they are to be with.

2) Add the second set of data to the bar chart using "Copy.. Paste
Special", taking care to choose "Categories (X labels) in First
Column", but not "Replace Existing Categories". This creates a new
bar series.

3) Right click on the new bar series, and select "Chart Type.. XY
Scatter)". This changes the bars to scatter points.

4) Adjust the X and Y scales of the scatter series so that they fit
nicely over the bars, and properly match the values.

This method of presentation works best if you make the bars a nice pale
colour, or even no colour at all, so that they do not hide the data
points. I also like to use error bars to mark the data as thin
horizontal lines, then set the data markers to "none". Select your
colours as you wish, to adjust the boldness of the data and the
aggregate measure depending on what you want the reader to pay most
attention to: bright bars for the aggregate and pale grey markers for
the data, or bold coloured markers for the data, and light pastel for
the averages.

I am a strong believer in showing all the data if possible, now that we
don't have to pay a draftsman to draw all the lines and points.
Aggregate statistics like the average are no longer necessary for labour
saving in this day and age, only for comprehension.

Be careful with the Y scales: it's obvious if the X scales are
mismatched, but it's easy to accidentally publish a combination chart
with mismatched Y scales, so that the averages don't seem to match the
data, which can be embarrassing. You will probably have to set the
scales to fixed zero and maximum values instead of letting them be
automatic, although there are techniques involving invisible dummy bar
series if you really need to leave the two scales free to adjust
automatically.

You might consider abandoning bars altogether, and plotting all the data
points and their aggregate statistics as scatter series with appropriate
markers. This avoids the hassle of mixing chart types.

(admittedly, this leaves the question of how to place the labels, a
problem which is often solved by creating a dummy category series, which
takes you right back to mixing chart types again!)

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.