Error bars in Excel?

Gilles Bronchti gilles.bronchti at ia.unil.ch
Wed Dec 15 17:06:01 EST 1993

In article <CI2xr8.Itu at cen.ex.ac.uk> PBarnwel at cen.ex.ac.uk writes:
>From: PBarnwel at cen.ex.ac.uk
>Subject: Re: Error bars in Excel?
>Date: Wed, 15 Dec 1993 14:04:18 GMT

Drawing error bars in Excel is certainly not obvious, but it is quite easy to 
do when you understand how the program works. I'll try to explain how I am 
drawing real error bars (not hi-lo-close) in my graphics, using the capacities 
of the soft:
First you have to introduce in different columns your data: x-values, 
y-values. Then calculate the sd values, or SEM or whatever you want the error 
bar to represent. Add to your data 2 columns, one with mean+sd (or SEM) that 
will be call Uppermean hereafter, the second with mean-sd (or SEM) or 
Lowermean hereafter. For the example I am dealing with one y-value columns, 
but it works well with any number of y's you want.Be sure to enter the data so 
 the x-values column will be next to all y-values columns and then followed by 
Uppermean and Lowermean. Something like:
x values  y1   y2   y3   y1upper,  y1 lower, y2 upper, y2 lower etc...
then select all the columns for creating the graph. Create the graph as say a 
bar graph (if you prefer a curve, it works as well). You will get all your 
data points in bars a result which certainly does not correspond to your 
wishes, but be patient! Then select (I don't remember now in which menu) the 
graph overlay, something like create overlay... Then go to the menu for graph 
formating, then you will have 2 graphs to choose, the main graph and the 
overlay graph. Choose the main graph, choose then how you want it to look like 
(bar-graph, curve, scatter etc...), and in the same window you will have the 
possibility to choose which series belongs to the overlay graph (in the bottom 
of the configuring graph window), now, count that in our example above (with 3 
y's) you have 3 series pertaining to the main graph and that you want to be in 
bar graphs or curve, the rest, 6 series (y1uppermean, y1lowermean, 
y2uppermean, y2 lowermean etc...) will be moved to the overlay graph so the 
answer to the question in that windows, which series starts the overlay graph 
will be, in this case, series 4). Then go to the formating of the overlay 
graph. Choose it to be line. You have also there an option to click on to ask 
the program to connect the lower and upper values.  Then you will get a bar 
graph (to follow my first example) with lines passing through them, still not 
error bars, but close to them. Then click on the say upperline. Choose it to 
appear without lines (just points) and that the points will be of the form of 
the large "-" proposed by excel (there is a small one and a large one, choose 
the large!). Do the same with the lower line. Now you have a bar graph, or a 
curve with error-bars fitted on it.
My explanations might be complicate, but once you learn how to do that, you 
will see that it works easily, and faster than me writing all these. I am not 
good in macro writing, but I am sure that all this procedure may be done by 
macro, providing you give the proper number of the series you want to start 
the overlay graph. If something is not clear, feel free to contact me by 
e-mail, I will go to excel and look at the exact commands, name of menu etc...
Gilles Bronchti 

More information about the Bio-soft mailing list

Send comments to us at biosci-help [At] net.bio.net