Data Manipulation and Analysis in Ecology Lab using Excel - Prof. Brent Blair | Lab Reports Ecology and Environment

Ecology Lab! Biol 251

- 1 -

Data Manipulation with Excel*

DATA ENTRY

Data may be entered directly or cut and pasted from other Excel

files or files from other programs. Before entering data, you

should always consider the objectives of using a particular data set;

columns should be designed according to the manner in which data

will be used &/or collected.

In this exercise, you will be copying and pasting data from a text

file into Excel. There should be a text file named “Competition

Data” on the desktop of your computer. Open that file and copy the

complete data set. Paste these data into Excel using the

EDIT…Paste Special…Text command (at right).

The data can then be formatted such that the sheet is easier to read

by highlighting column headers, re-sizing columns, centering text,

etc.

You will notice columns for Plot, Distance, Treatment, Shade,

Light, Plant mass, Percent Cover, Diversity, and Species Richness.

The two statements at the top of the sheet explain the origin of these

data. Under Treatment, S = shaded, U = unshaded.

FILTERS AND SORTING

Depending upon the information one wishes to derive from the data, it

may be necessary to re-sort the data or to extract portions of the data to

be pasted into another worksheet for manipulation. To sort or filter,

open the DATA menu on the upper toolbar, and select the appropriate

option. First, let’s re-sort the data to group together the shaded and

unshaded treatment data. In order to do this, you must highlight the

data set.

Now, select DATA…sort…and sort by Treatment, then by Plot, then

by Distance, all in ascending order. Also be sure to indicate whether

you have included your header row in the selected data to be sorted.

Now, click OK and the data will be sorted.

We now can calculate summary data for the two shade treatments from the raw data in each of the last six

data columns.

CREATING SUMM ARY INFORMATION

Two of the most useful pieces of summary data you can obtain from Excel are a mean and variance

estimates from each group of data to be used in the laboratory exercises. The typical variance estimate

many scientists use is the “Standard Error.” Standard error (SE) is computed as the standard deviation of a

dataset divided by the square root of the number of values in the set.

*This lab was adapted from Using Excel, Basics (2005) by Gary Ervin’s (Mississippi State U.)

Partial preview of the text

Download Data Manipulation and Analysis in Ecology Lab using Excel - Prof. Brent Blair and more Lab Reports Ecology and Environment in PDF only on Docsity!

Data Manipulation with Excel

DATA ENTRY

Data may be entered directly or cut and pasted from other Excel files or files from other programs. Before entering data, you should always consider the objectives of using a particular data set; columns should be designed according to the manner in which data will be used &/or collected. In this exercise, you will be copying and pasting data from a text file into Excel. There should be a text file named “Competition Data” on the desktop of your computer. Open that file and copy the complete data set. Paste these data into Excel using the EDIT …Paste Special…Text command (at right). The data can then be formatted such that the sheet is easier to read by highlighting column headers, re-sizing columns, centering text, etc. You will notice columns for Plot, Distance, Treatment, Shade, Light, Plant mass, Percent Cover, Diversity, and Species Richness. The two statements at the top of the sheet explain the origin of these data. Under Treatment, S = shaded, U = unshaded.

FILTERS AND SORTING

Depending upon the information one wishes to derive from the data, it may be necessary to re-sort the data or to extract portions of the data to be pasted into another worksheet for manipulation. To sort or filter, open the DATA menu on the upper toolbar, and select the appropriate option. First, let’s re-sort the data to group together the shaded and unshaded treatment data. In order to do this, you must highlight the data set. Now, select DATA …sort…and sort by Treatment, then by Plot, then by Distance, all in ascending order. Also be sure to indicate whether you have included your header row in the selected data to be sorted. Now, click OK and the data will be sorted. We now can calculate summary data for the two shade treatments from the raw data in each of the last six data columns.

CREATING SUMMARY INFORMATION

Two of the most useful pieces of summary data you can obtain from Excel are a mean and variance estimates from each group of data to be used in the laboratory exercises. The typical variance estimate many scientists use is the “Standard Error.” Standard error (SE) is computed as the standard deviation of a dataset divided by the square root of the number of values in the set. *This lab was adapted from Using Excel, Basics ( 2005 ) by Gary Ervin’s (Mississippi State U.)

To calculate the mean and SE of a group of data, such as shade, let’s first copy and paste the data and grouping variables onto a new worksheet. Now, add a header for mean and SE. Type the formula for average into the cell beneath the header [=average (data)] and for SE into the appropriate cell [=stdev(data)/sqrt(n)]. The data range should include the first 16 rows of shade data. The last 16 rows will be summarized in the two cells adjacent to the first data for unshaded plots, in the same manner as above. This process can be repeated for all seven response parameter columns, preferably with each in a new, appropriately named worksheet. Create a new worksheet by clicking INSERT …worksheet. Name worksheets by double-clicking the worksheet tab at the bottom of the screen and typing a name.

CREATING GRAPHS

Now that you have summary data that can be used to compare the two treatments, you are ready to create pictorial representations of those data to actually see how the treatments compare. In the shade data example, let’s see whether the experimental manipulation actually resulted in a real difference in the light environment among the plots. Bar Charts Click on the chart wizard icon in the middle upper of the top toolbar (it looks like a miniature bar graph). The top selection is highlighted (column chart); click NEXT. You now need to indicate which data to plot; select the Series tab, and click ADD. There are numerous ways to enter the data to plot. The easiest is to click on the small worksheet icon to the right of each empty data selection windows. You also can type the data directly into the window or type in the worksheet range where the data are located. For X-axis labels, you may simply type “Shaded,Unshaded” into the window. A preview of the chart in the upper portion of this dialogue box. NEXT , you have the option of modifying various of the chart features, including axis titles, gridlines, etc. To add the SE estimate, we will add error bars by double-clicking on one of the bars in the chart. This opens a dialogue box to Format Data Series. Click the “Y Error Bars” tab, and add SE the same way you added the means to be plotted. Bu sure to indicate that you want BOTH plus and minus error bars, and click OK.

Data Manipulation and Analysis in Ecology Lab using Excel - Prof. Brent Blair, Lab Reports of Ecology and Environment

Related documents

Partial preview of the text

Download Data Manipulation and Analysis in Ecology Lab using Excel - Prof. Brent Blair and more Lab Reports Ecology and Environment in PDF only on Docsity!

Data Manipulation with Excel

DATA ENTRY

FILTERS AND SORTING

CREATING SUMMARY INFORMATION

CREATING GRAPHS

Data Manipulation with Excel

HomeWork!

Use the techniques provided above to produce figures appropriately displaying THREE of the

following relationships. Graphs must have labeled axis and be easily interpreted by the reader.

Formatting is important! e.g., if you are printing in b/w make sure your graph doesn’t depend on

color for its interpretation.

For each figure you create, provide a sentence or two describing the pattern produced by the

data. In other words, what is each figure telling you?

1. Treatment (Shade/No Shade) vs. Dry Plant Mass

2. Dry Plant Mass vs. Percent Cover

3. Diversity vs. Species Richness

4. Treatment (Shade/No Shade) vs. Species Richness OR Diversity (you chose one)

5. Treatment (Shade/No Shade) AND Distance vs. Diversity

6. Treatment (Shade/No Shade) AND Distance vs. Species Richness

7. Distance vs. Light in plots 8 and 2

8. Distance vs. Species Richness in plots 8 and 2