Movement of materials through a cell membrane
Constructing Graphs Using Excel
Data entry & calculations
1. Enter your data onto a spreadsheet.
A B C D E
1 Germination (%) Germination (%) Shoot length (mm) Shoot length (mm)
2 control experimental control experimental
3 100 55 10 5
4 98 65 9 6
5 100 43 7 4
6 100 49 9 5
7 99 53 9 4
8 average
9 standard deviation
2. Calculate the averages.
a. To enter a formula, you will use “=,” followed by the command “average” and then a front-parenthesis “(“. Highlight the cells containing the data that should be averaged here, then hit “enter.” In the above example, the formula in for control-before (cell B8) will read: =AVERAGE(B3:B7)
b. To calculate the average for the remaining columns, copy the first formula and paste it into the remaining cells in the “averages” row.
3. Calculate the standard deviations (a measure of variability in the samples).
a. Again, use “=” to indicate a formula, and this time the command is “stdev”. You must choose the same cells for the standard deviation that you chose for the average.
b. In the above example, the formula in B9 will read: =STDEV(B3:B7)
c. To calculate the standard deviation for the remaining columns, “copy” the first formula and “paste” it into the remaining cells in the “stdev” row.
Creating the graph
1. Create the basic graph.
a. For the first graph, highlight the two averages for “Germination.” For the second graph, highlight the two averages for “shoot length.” The average measured values will be graphed on the Y axis
i. You can graph several averages (more than 2) by highlighting them all
b. Select “Insert” from the menu at the top of the screen, then choose “Chart.
c. Select “Column” and the simple 2-D column graph option (upper left).
d. Look at your graph and make sure that the axes show the appropriate information.
For all of the following chart modifications, the “chart tools’ tab must be active. If it is not (you’ll know this because you won’t see any of the options listed below), click on the chart.
2. X/horizontal axis labels for bar graphs
a. Right-click on the graph and select “select data:
b. In the pop up window you will see “Category (X) axis lables: and a box to enter values next to it.
i. Either enter the cell numbers (e.g. B2:C2, in the first example above) or click the small square to its right, this will allow you to select the values from your table.
c. Click ok and the values along the x-axis should now match the values in your table
3. Chart and axis titles
a. Select the “Chart Layout” tab
b. Choose the appropriate pull-down menu (“Chart title” or “Axis Title”) from the “Labels” section on this toolbar.
c. Click on the appropriate box in your chart to enter the title. For the figure axes, don’t forget to include the units, when appropriate.
4. “Legend” (Key)
a. Since you have only one set of data, you don’t need a legend – select the “Layout” tab under “Chart Tools,” select “Legend” from the “Labels” section of this toolbar and remove the key/legend (“none”).
5. Standard deviation bars:
a. Right-click on the data in the graph and select“Format Data Series”
b. Select “Error Bars” from the options on the right side.
c. DO NOT choose “Error Bars with Standard Deviation!” This will not show the standard deviation. Really. Look at your calculated values compared to the graphed error bars if you don’t believe me.
d. Choose “Custom” and “Specify Value” at the bottom of the popup menu.
e. For “+,” highlight the cell range where the standard deviations are found (in the above example, B8:C8 for the “germination” standard deviations).
6. To transfer the graph to your lab report: Click on the graph and select “Copy.” Paste into the Word file containing your lab report.