Biologists frequently use bar graphs to summarize and present the results of their research. This tutorial will show you how to generate these kinds of graphs (with error bars) using a biologically relevant student-generated data set.
As part of her senior thesis research on territoriality
in the Mountain Dusky Salamander (Desmognathus ochrophaeus), Samantha
Witchell (Class of 1999) investigated the role of prior occupancy on the
outcome of territorial interactions and aggressive behavior. Samantha staged
encounters between a male that had been allowed to establish a territory
in a laboratory arena ("resident") and a comparably sized male without
any prior residency ("intruder"). In order to control for the effects of
body size, males were categorized into three size classes; small one-year-old
males (30-33 mm snout-vent length), medium-sized two-year-old males (36-39
mm), and large adult males (42-45 mm). The resident and intruder used in
a particular trial were matched by size, and Samantha staged a total of
eight encounters for each of the three size classes. In each trial, Samantha
allowed the two salamanders to interact with each other for 20 minutes,
during which time she collected data on several different behaviors. One
of the behaviors she recorded was the "all trunk raised" (ATR) posture,
an aggressive posture used by males in territorial encounters. Here are
data she collected on the number of ATR postures performed by residents
and intruders.
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
How can these data be summarized in an effective graph?
I. Use Excel to Calculate Summary Statistics
1. Open a new workbook in Excel and enter the data on
Sheet1 in the format shown below: When the data are entered, save the worksheet
and name it "BarGraphExample".
2. Once you have entered the data, you can use Excel to calculate the summary statistics to be plotted on your graph. In cell B12 type:
=AVERAGE(B3:B10)
When you hit the return key, Excel will automatically calculate the mean (average) number of ATR postures by small residents, using the 8 values entered in cells B3 to B10.
3. You can also use Excel to calculate the standard error, a measure of the reliability of the calculated mean. The standard error is calculated by dividing the standard deviation (explained below) by the square root of sample size. Persuading Excel to do these calculations requires a few additional steps:
A. In cell B13, type:4. You could repeat the above steps to write formulas for the data in the other columns. However, it is much easier to let Excel copy the formulas to the other columns. To do this, use the mouse to select the area from cell B12 to cell G15, so that the worksheet now looks like the one shown below:=STDEV(B3:B10)
When you hit the return key, Excel will automatically calculate the standard deviation of the number of ATR postures by small residents, again using the 8 values entered in cells B3 to B10. The standard deviation is a measure of the amount of variation in the data set; if all the observations are close to the mean value, the standard deviation is small. If most of the observations differ greatly from the mean, then the standard deviation is large.
B. In cell B14, type
=SQRT(COUNT(B3:B10))
When you hit the return key, Excel will automatically count the number of observations (8 in this particular example) and determine the square root of this number (2.828).
C. In cell B15, type
=B13/B14
When you hit the return key, Excel will automatically divide the value in cell B13 (the standard deviation) by the value in cell B14 (the square root of sample size), thereby giving you the standard error.
When these cells are selected and highlighted as shown, go to the "Edit" menu and select "Fill" and "Right". Excel will automatically copy the formulas to the other columns, and do the appropriate calculations.
II. Using Excel to Create Graphs
Now that Excel has crunched the numbers for you, it’s
time to plot them in a bar graph.
1. First, click on cell B1 (the cell containing the label for the smallest size class). Then, while holding down the "control" key, click on the other two cells containing the labels for the size classes (D1 and F1) and the three cells containing the mean values for the Resident (B12, D12, and F12). Your Excel window, with six cells selected, should look like the one at below:
2. Under the "Insert" menu, choose "Chart". This will launch Excel’s Chart Wizard and bring up a series of four dialog boxes:
A. "Select Chart Type" – The default choice (clustered column) is fine; click "Next >".3. Now we can add the data for intruders to the graph. Go to the "Chart" menu and select "Add Data…". A small "Add Data" dialog box will appear, asking you to select the data to add to the graph. Use the tabs at the bottom of the Excel Window to get back to "Sheet1", and click on cell C12. Then, while holding down the "control" key, click on the two other cells containing the mean values for the Intruder (E12 and G12). When you have selected the three cells, click "OK". A "Paste Special" dialog box will appear, but the default choices are the correct ones, so click "OK". If things went well, the intruder data should now be added to your chart.B. "Chart Source Data" – If the spreadsheet was set up correctly and you selected the correct range in step 1, Excel should have guessed correctly about the data range we wanted to plot; click "Next >".
C. "Chart Options" – Here you have a multitude of options:
D. "Chart Location" – You can either choose to have the graph created as a new sheet in your workbook, or inserted into one of the existing worksheets. For now, the "As new sheet" option is best. Then click "Finish". If things went well, you should see a spiffy new bar chart that neatly summarizes the data on the ATR posture for dominants."Titles" – A chart title usually isn’t necessary. Clearly labeled axes, however, are essential. Type "Size Class" for the x-axis label and "Number of ATR per Trial" for the y-axis label. "Axes" – The defaults are fine. "Gridlines" – It is usually best to turn all gridlines off. "Legend" – The defaults are fine. "Data Labels" – The defaults are fine. "Data Table" – The defaults are fine.
4. The final step at this stage is correcting the legend. Click once on one of the three bars corresponding to the data for Resident salamander ("Series1") to select this particular data series. Go to the "Chart" menu again and select "Source Data…". This will bring up the tabbed dialog box "Source Data". On the "Series" tab, change the name of "Series1" to "Resident". Then click on "Series2" and give it the name "Intruder". When you click "OK", your graph should now have a more informative legend.
III. Adding Error Bars
1.Click once on one of the three bars corresponding to
the "Resident" salamander to select the "Resident" data series.
2. Under the "Format" menu, choose "Selected Data Series…". This will bring up another tabbed dialog box that lets you set more chart properties.
3. Click on the tab labeled "Y Error Bars" and click the button labeled "Custom:". Then click on the icon to the right of the "+" field adjacent to the "Custom:" button. This will take you back to your Excel workbook to select a custom range of cells that contain the data for the error bars. Use the tabs at the bottom of the Excel Window to get back to "Sheet1". First click on cell B15, the cell containing the standard error for the smallest residents. Then, while holding down the "control" key, click on the other two cells containing the standard errors for the Resident (D15,and F15). Then hit a return. This will return you to the "Format Data Series" dialog box, and it should now look like the one below:
4. Click "OK", and error bars showing the standard errors should magically appear on your graph.
5. Repeat steps 1-4 for the bars corresponding to the "Intruder", taking care in step 3 to select the cells containing the standard error for the intruder (C15, E15, and G15).
IV. Fine-Tuning Your Graph
With Excel, you have control over virtually all aspects
of chart appearance. Experiment by double-clicking on the various chart
components (legend, x-axis, y-axis, data series, y-error bars, etc.), changing
fonts and font sizes, fill and background colors, etc. Be sure to explore
various other options by selecting the "Chart" menu item and selecting
"Chart Options…" You can also experiment with re-sizing and re-proportioning
your graph by clicking once on the plot frame and dragging the square "handles"
around the edges. Experiment and make changes until you are happy with
your graph. But don’t go too wild! The best graphs are clean, simple, easy
to interpret, and effective.
When you are happy with your final graph, save your workbook! You can then select, copy, and paste the chart into Word or other word processing applications.
Here is one possibility for a final, finished graph, with an appropriate and concise figure legend:
Figure 1: The mean number of ATR (all trunk raised) postures per 20-minute trial for residents and intruders in the three separate size classes. Error bars represent the standard error.