The figure what I want will contains multiple dot points that line up nicely per each group ...

Please look at the sample figure at http://www.medscape.com/content/2004/00/46/70/467012/art-jags467012.fig3.gif

I'm not sure if this can be done in Excel. In R, however, you could make a pirate plot, which is kind of similar to this. (A pirate plot also has extra stuff on it, which you might not want, but it is customizable, so you could hide all the extra stuff and just keep the dots.)

Oh, this can be done in Excel, but it can be... intricate.

The method below uses excel's random number function in conjunction with Excel's scatter plot to 'jitter' the observations for each treatment group while still making them appear in a column style format, followed by x,y line graphing to display measures of central tendency . What I list below is not the only way to do this, but it works.

Put your individual observations for each treatment group in separate columns (order doesn't matter). For instance, in the example figure you linked, there would be four columns of observation data- one column for the individual measures in each treatment. Then, to the left of each of those columns, insert a new 'x' column- so you would have a total of 8 columns, one pair of columns for each data set.

You have real data in your 'observation' columns. Now you can make up numbers for your 'x' columns. These made-up x column values serve two purposes, spreading (jittering) the observations within a treatment so they don't overlap one another, and providing gapping between treatments.

So for instance, if you called your first x column 'control completers x jitter', just enter the formula

=rand()

and copy down to produce a random value between 0 and 1 for each of the y values in your first treatment group.

In the next 'x' column (in your example, these are 'experimental study completers x jitters', enter the formula

=1.5 + rand()

in the third x column

=3 + rand()

etc (note that the arithmetic value you add to the 'rand' function sets the gap width between columns- in my example, it is stepping by 1.5- so you can make columns appear skinny by increasing, or make them appear wide by decreasing, that stepwise addition..

Then, scatter plot the first pair of columns X vs Y, it will look like a mess

Then right click on the chart, select data, add new series, and add the second x,y pair, it will start to look like it makes more sense. Repeat for the third and fourth series.

You can hit F9 a few times to see if you like one random jitter more than another, you can play with the gaps between the 'columns', but when you find a jitter and gap that you like, copy and paste your formulas as values to lock that presentation down.

You'll have to delete the x axis labels (those numbers are only there to drive the jitter and gap features- they are meaningless to your Y axis data except that they help it to display in a certain format) and add your own X axis labels through inserted text boxes (or in PowerPoint).

Now, for the 'mean lines' for each treatment group- you need four numbers, for each line: the x and y coordinates for the the start of the line, and the x and y coordinates for the end of the line. BOTH y's will be the same value (probably the treatment group mean) repeated (or median, or whatever measure of central tendency you or your reviewer think is best), and the x's are the beginning and end of the possible x-range for that treatment group.

For instance, your first X,Y pair, the 'control completers'- let's say their mean value is 0, and we know from the 'random' function that our x values could range between is 0 and 1. So the coordinates for our first line are

X: 0,1

Y: 0,0

enter that little four-square of data somewhere (hint- the bottom of the column of observed data is a good place), rt click on your chart, 'select data', new series, give the series a name like 'mean control completers'- select the series, format data series, set symbols to 'none' and set line to 'solid'- for added great lookingness, make sure you line color matches the symbol color for that treatment group.

Repeat for the next treatment- but remember to account for your gap

For instance, 'experimental study completers' has a mean of we'll say 1000. But because of our x axis gap, its values start at 1.5 and range to 2.5, so it's X and Y would be

X: 1.5, 2.5

Y:1000, 1000

Repeat to get them all.

HTH,

-Eric