The effects of bison grazing on plant diversity in a tallgrass prairie (Konza Prairie LTER)

Objectives and audience

The objective of these activities is to give students experience calculating species richness and diversity indices as well as to have the students critically evaluate and interpret the calculations. In particular, they should think about what factors may affect the indices, especially sample size and the initial assumptions of each calculation. At the same time, students are given real data to use for their analysis and a real ecological question to address with their new skills. The student instructions are written for introductory to intermediate level students. The instructions assume that students have little or no experience with Excel. Students with more experience with Excel will likely take less time to complete the activities.

Suggestions for using this dataset

This dataset is divided into four parts: Overview, Part 1:Introduction, Part 2: Activities and Part 3: Assumptions and Conclusions.

The Overview contains reading material that contains background information on Konza Prairie, the study design and data collection.

Part 1: Introduction contains a short description of biodiversity, species diversity, and the ecological question. It also contains a short Activity that asks students to compare two communities given a list of species in each community, with or without information on abundance. The purpose of the Activity is to get students thinking about the two main components of species diversity (richness and evenness) using a student-active approach of writing before discussion. This activity could also be structured using the Think-Pair-Share method. More description of these methods can be found on the TIEE website: Time for the introduction activity (if it is all completed within class time, see suggestion below for another option), including some discussion time, is about 20 minutes.

Suggestion: The Overview and Introduction can be assigned as pre-class readings. In addition, we suggest that students begin the Introduction Activity before coming to class by writing answers to Questions 1 and 2. Question 3 asks the students to discuss their answers to Questions 1 and 2 in small groups (~ 5 minutes of discussion should be appropriate) and this discussion may be a great way to start off the class. Structuring the class this way will also allow for maximum computer time in class.

Part 2: Activities contains the computer work that students will complete and is divided into three activities that altogether will require approximately 4 hours, largely depending upon student familiarity with Excel. One to three questions are posed after each activity. Some simply ask the students to compare their results, while other questions require more thought and analysis. The questions should be considered during class (rather than having the students go back to the questions after completing all of the computer work) as they help to focus student attention throughout the exercise.

Suggestion: The activities should be done in order, but one option is to spread the activities over two classes, with students completing activities 1 and 2 in one class period and activity 3 in another. Another option is that if students have enough class time to become comfortable with Excel and the format of the dataset, they can finish the activities on their own outside of class.

Part 3: Assumptions and Conclusions does not require any computer-based data calculations. Rather, it challenges students to consider the assumptions of the methods they have learned and how these assumptions may affect the conclusions they have drawn. It should take approximately 20 minutes.

Suggestion: Part 3 can be assigned as an outside-of-class activity to reinforce what the students have learned in class.


In order to allow maximum class time for completing the exercise, we strongly suggest that instructors make sure that the diversity plug-in is installed in the copy of Microsoft Excel the students will be working with. The plug-in is available free from the University of Reading in the United Kingdom. Instructions for downloading and installing the plug-in are given in the Before beginning section of the student instructions.

We decided to use the diversity plug-in to speed up the calculation process for species richness and for the Shannon Index so that students hopefully would focus on interpreting the data rather than getting bogged down with calculating and using Excel. However, the Simpson Index algorithm used by the plug-in is not compatible with the frequency measurement of abundance we use in these activities; therefore, the activity walks students though calculating the Simpson Index by programming formulae in Excel. Having students use an already programmed formula for the Shannon Index and then programming their own formulae for the Simpson Index allows them to experience both methods and saves time as well. Be aware, however, that some students may notice that Simpson is an option with the diversity plug-in, and if they use it instead of following the instructions, they will not calculate the correct answer.

Another issue with the Simpson index is the counter-intuitive nature of its initial calculation for D, and why using the formula 1-D is preferable because it is more intuitive. Faculty may wish to illustrate this point with an example during class.

One important issue this dataset does not consider is replication and statistical comparison of the treatments. While students compare the effect of sample size (i.e. number of plots sampled for species identification) on species diversity measurement, they are still only comparing a single grazed watershed with a single control watershed. We chose to do this for simplicity. As it is, there is a substantial amount of data for students to deal with and many concepts to master without replication. For more advanced students, the instructor may wish to introduce the idea of treatment replication and discuss how it would affect the comparison of the grazed and control treatments. The exercise could be expanded over more class periods by obtaining data for more watersheds and/or more years from the Konza Prairie LTER website. Students would then be able to fully address the ecological question posed in this exercise and see if they obtain results similar to those already published in the cited literature. Deeper analysis of more data may also be an appropriate independent project for more advanced students.

Notes on the Excel spreadsheets

There are seven spreadsheets in the Faculty Excel Workbook for this exercise. The spreadsheets are labeled Activity 1, Activity 2a, Activity 2b, Activity 3, Comparison, Table 3 and Table 5. Descriptions of the data found in each spreadsheet follow below.

Activity 1: This worksheet contains a total of 12 data subsets, including six data subsets for the Bison-Absent watershed (highlighted yellow in the worksheet) and six data subsets for the Bison-Present watershed (highlighted green in the worksheet). Each data subset is labeled 1 through 6 and has a sample size that corresponds to it. At the bottom of each data subset, just below the highlighted cells, we've outlined a cell in black to indicate where students should do their calculations. The answers for faculty are summarized in the Table 3 worksheet.

Activity 2a: This worksheet contains the data required to create a species accumulation curve for the Bison-Absent watershed. It contains 20 subsets of data, and each is labeled with its sample size (number of plots that were sampled). At the bottom of each data subset, just below the highlighted cells, we’ve outlined a cell in black to indicate where students should do their calculations. To the far right of the 20 data subsets, we’ve created two columns where students will copy and paste their results The student instructions walk them through the copy and paste techniques. In the faculty workbook, the answers are already available and a sample graph has been created just below the two columns of data (columns BL and BJ).

Activity 2b: This worksheet is set up just like the Activity 2a worksheet and contains the data required to create a species accumulation curve for the Bison-Present watershed.

Activity 3: This worksheet contains four data subsets all from the Bison-Present watershed. Students use these data to compare the effects of species richness on Shannon and Simpson diversity indices. Calculations are present in the faculty workbook and the answers are also summarized for faculty in the Table 5 worksheet.

Comparison: This worksheet contains the entire species lists and frequency values for the Bison-Absent (highlighted yellow) and Bison-Present (highlighted green) watersheds. The activity asks students to use these data to calculate Simpson diversity indices for each watershed and draw conclusions. The correct values are in bold at the bottom of the data lists.

Table 3: The student version of the Excel workbook does not contain this spreadsheet. This spreadsheet contains the correct values for Table 3, which students fill out as part of Activity 1.

Table 5: The student version of the Excel workbook does not contain this spreadsheet. This spreadsheet contains the correct values for Table 5, which students fill out as part of Activity 3.

Assessment and Evaluation

Various options can be used for evaluation, and they range from assessing basic levels of understanding and comprehension to assessing higher levels of synthesis and application. How you choose to evaluate students depends on your learning goals for your class.

Completion of the Excel activities and calculations, and submission of the two tables and two graphs from Part 2 would document basic understanding of the material and calculations of indices, as well as an ability to use Excel. More complete comprehension of the concepts and their complexity could be assessed by evaluating answers to all or a subset of the 12 questions posed in Parts 2 and 3. Whether you decide to have students answer these questions individually, as a small or large group, or a combination of both will depend on the degree you choose to emphasize independent or collaborative work. Since doing science is dependent on working within a community of scientists and exchanging ideas, we highly recommend incorporating group work or discussion to some extent. If you evaluate written work, be clear about writing expectations (e.g., do you expect complete sentences, or are short lists acceptable?).

Further, a synthesis activity can be assigned. One option is to have students write a short essay whereby they choose the best measure of biodiversity and defend their positions. This assignment could be modified by assigning groups of students to a measure of biodiversity and asking that they formulate a defense of why it is best. The groups could then have an oral debate in class. The instructor may wish to define a specific situation, either using the one described in the exercise or one or several new situations. One advantage to defining several new situations is the opportunity to highlight that the best measure is context-dependent.

Another synthesis activity would be to ask students to write an explanation for a non-scientist friend or relative about how plant ecologists might measure plant species diversity of a given area, and how they would compare that with another area. This is based on the wisdom we all know that if you want to learn something well, try teaching it.

A sample of a basic rubric is included below, which can be modified and adjusted to your own needs. The number of points you wish to assign each category depends on what you choose to emphasize.

Sample Assessment Rubric
Basic understanding
  • Able to use Excel data sets
  • Able to calculate indices using Excel
  • Turns in completed graphs and tables
  • Graphs and tables labeled appropriately
  • Answers all or a subset of the 12 questions
  • Answers in complete sentences (or whatever standards you have indicated), or through active group discussion
  • Answers show understanding and comprehension of the concepts and complexities
Group work
  • Asks questions when understanding is not clear
  • Listens to and is respectful of others
  • Shows evidence of applying learning to different situations
  • Is responsible for own tasks within the group in a timely manner
  • Is able to synthesize and apply concepts to new situation (according to assignment given)
  • Can articulate in own words the concepts covered

Additional resources

ESA's biodiversity fact sheet

Konza Prairie Biological Station

Tallgrass plant species links

Other tallgrass prairie sites in the Great Plains

Tallgrass prairie photos

Excel tutorials