Graphing Excel Tutorial

Key Points to get ya started:

·  Type your data into excel with axes labels

·  Highlight all your data

·  Click on Insert

·  Click the type of graph your want to create( we will often use scatter plot)

·  Click the chart layout that includes axis labels and chart title

·  Click on your axis titles and type in the correct titles- Don’t forget to include units!

·  If you have more than one series (2 sets or more of data) click on select data.

·  Click on Series 1 then click edit

·  Type in your series name or highlight your series title (example data set 1)

·  Click on series x values and highlight the x-values

·  Do the same with the y-values

·  Click okay

·  Follow the same steps to set up your other series

·  Those are the basics J

1. Take the following data table and create a graph in excel.

The table shows data collected on the pH level of an Adirondack lake from 1980 to 1996.

Using the information in the data table, construct a line graph below(scatter plot). Label the axes. Don’t forget to include a title.

2. A biology student performed an experiment to determine which of two species of single-celled organisms would survive best when cultured together in a certain environment. The student placed 10 organisms of each species into a large test tube. Throughout the experiment, the test tube was maintained at 30°C. After the test tube was set up, the population of each species was determined each day for 5 days. The data collected are shown in the table below.

a) Using the information in the data table, construct a line graph on the grid following the directions below.

Mark an appropriate scale on each labeled axis. You should have two different lines/scatter plots on one graph.

3. Insecticides are used by farmers to destroy crop-eating insects. Recently, scientists tested several insecticides to see if they caused damage to chromosomes. Six groups of about 200 cells each were examined to determine the extent of chromosome damage after each group was exposed to a different concentration of one of two insecticides. The results are shown in the data table below.

a) Using the information in the data table, construct a line graph. Make sure to include the two different series just like the last example.

b) Which insecticide has a more damaging effect on chromosomes? Support your answer.

Bar graph with error bars-Standard Deviation

This is used when you have at least 3 trials. This helps to show the precision of data using statistical tools such as the average(mean) and the standard deviation. The smaller the standard deviation the more precise your data is.(this is a good thing, it means you keep doing trials and your measurements are very close each time. ) The larger the SD, the less reliable the data.

·  In Excel the mean is calculated using the formula

o  =AVERAGE(range)

·  the SD is calculated using

o  =STDEV(range)

This spreadsheet shows two sets of data with the same mean. In group A the confidence interval is small compared to the mean, so the data are reliable and you can be confident that the real mean is close to your calculated mean. But in group B the confidence interval is large compared to the mean, so the data are unreliable, as the real mean could be quite far away from your calculated mean.

4. Here are the results of an investigation into the rate of photosynthesis in the pond weed Elodea. The number of bubbles given off in one minute was counted under different light intensities, and each measurement was repeated 5 times. Use Excel to calculate the means and 95% confidence limits of these results, then plot a graph of the mean results with error bars and a line of best fit.

light intensity
(Lux) / repeat 1 / repeat 2 / repeat 3 / repeat 4 / repeat 5
0 / 5 / 2 / 0 / 2 / 1
500 / 12 / 4 / 5 / 8 / 7
1000 / 7 / 20 / 18 / 14 / 24
2000 / 42 / 25 / 31 / 14 / 38
3500 / 45 / 40 / 36 / 50 / 28
5000 / 65 / 54 / 72 / 58 / 36

5. In a test of two drugs 8 patients were given one drug and 8 patients another drug. The number of hours of relief from symptoms was measured with the following results:

Drug A / 3.2 / 1.6 / 5.7 / 2.8 / 5.5 / 1.2 / 6.1 / 2.9
Drug B / 3.8 / 1.0 / 8.4 / 3.6 / 5.0 / 3.5 / 7.3 / 4.8

Find out which drug is better by calculating the mean and 95% confidence limit for each drug, then use an appropriate statistical test to find if it is significantly better than the other drug.