Ecology
Published March 2018
www.BioInteractive.org
Page 1 of 5
Measuring Biodiversity in Gorongosa
Mac Excel Tutorial
A. Overview
This tutorial provides instructions for analyzing data downloaded from the Gorongosa WildCam Lab for the
Measuring Biodiversity In Gorongosa activity. In this tutorial you will investigate the richness, evenness, and
Shannon Diversity Index of the different vegetation types in Gorongosa National Park.
B. Make a Pivot Table
For research questions in which you need to add up the number of animals in a particular category (e.g., number
of animals in each season), creating a pivot table is a quick and easy way to analyze your data.
1. Open the spreadsheet you downloaded from WildCam Lab and immediately save it as an Excel file (.xls or
.xlsx).
2. Click the Insert tab and select Pivot Table (Figure 1).
3. Click on the button next to Select a Table/Range. Navigate
to your data and click on the letter “A” to highlight the first
column, then drag the cursor to the last column to highlight
the entire data set. Under “Choose where to place the
PivotTable,” select New Worksheet. Click OK (Figure 2).
Figure 2: Selecting the range of data.
Figure 1: Inserting a pivot table.
Measuring Biodiversity in Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 2 of 5
C. Build the Correct Pivot Table
A pivot table can be used to group data by certain variables. In this example, the numerical variable,
“species_count," will be grouped by two categorical variables, “veg_type” and “species.” To build the pivot
table, you will follow the steps explained below to place the grouping variables (veg_type and species) in the
ROWS field and the variable being grouped (species_count) in the VALUES field.
4. In order to create a table that organizes the number of animals
in each species for each vegetation type, you will need to select
“veg_type”, “species” and “species_count” fields in the list of
PivotTable Fields (Figure 3).
5. By default, the fields you select will appear in the VALUES
field. "Count of veg_type" THEN the "Count of species"
variable to the"Row Labels" field, in that order (Figure 4).
This will produce a table with the veg types and species in
one column and the species_count in the other column.
6. In the VALUES column, make sure that the setting for your variable
(species_count) is appropriate for the dependent variable you have
chosen. In this case, we want to sum the variable “species_count.” The
default is to count the number of observations (photos), but we want
to total the number of animals observed in each photo. Click on Count
of species_count in the VALUES column and click on Values Field
Settings. Select “Summarize Values By” Sum (Figure 5).
Figure 3: Selecting the fields to use in
building the pivot table.
Figure 5: Choosing how to summarize the value field.
Figure 4: Moving the veg_type and species
variables to the ROWS field.
Measuring Biodiversity in Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 3 of 5
D. Calculate Richness
You now have a table with each species listed under the three vegetation types in the left column and the total
number of animals in the column to the right. To calculate species richness, you simply have to count the
number of species within each vegetation type and record it in the table in question 6 of your worksheet. There
is an Excel formula that will make this easy for you.
7. In any blank cell to the right of the pivot table, use the formula =COUNTIF(range, criteria) to count how
many species are in the
Grassland vegetation type.
This formula will count how
many cells that you select
contain any text. When you
type the formula, in the
"range" section, highlight the
cells that contain all the
species under the Grassland
category. In the "criteria"
section, type "*", which
means count the cell if it
contains any text. Write the
value in the table in question
6 of your worksheet.
8. Repeat the steps above for other three vegetation types and record your results in the table in question 6 of
your worksheet. When you are done, you can clear the values from the cells you used to do the calculation.
E. Calculate the Shannon Diversity Index
You will use the pivot table to calculate the Shannon Diversity Index for each vegetation type. Remember that
the Shannon Diversity Index is represented by the letter "H" and the formula is: H = -SUM (pi * ln(pi))
Where:
pi = abundance of a species / total abundance
ln = natural log
9. Create the following column headers
next to the pivot table in columns C
through F: pi, ln(pi), pi x ln(pi), H.
Figure 6: Calculating richness for the Grassland vegetation type.
Figure 7: Adding column labels to the spreadsheet
Measuring Biodiversity in Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 4 of 5
10. Select the cell in the "pi" column next to the
first species in the table. You will use the
formula =species abundance/total
abundance. For the species abundance, select
the cell for the “Sum of Species count” of the
first species in the grassland vegetation type.
For the total abundance, select “Sum of
Species count” of all animals in the grassland
vegetation type.Repeat this for all species in
all of the vegetation types.
11. Select the cell in the "ln(pi)" column next to the first species in the table. You will use the formula =LN(pi).
Type =LN() and, with your cursor between the parentheses, select the cell that corresponds to the pi value
for that species (the cell directly to the left).
Copy this cell and paste it into the remaining
cells within that vegetation type. This will
repeat the formula using the cell to the left of
it for the pi value. Repeat for each of the
vegetation types.
12. Select the cell in the "pi x ln(pi)" column next to the first species in the table. You will use the formula
=pi*ln(pi). Type the "=" sign, then select the cell that corresponds
with pi for that species (two cells to the left), type the "*" symbol
(which is the multiplication symbol) and then select the cell
corresponding to ln(pi) for that species (the cell directly to the left).
Copy this cell and paste it into the remaining cells within that
vegetation type. This will repeat the formula using the cell to the left
of it for the pi and ln(pi) values. Repeat for each of the vegetation types.
13. Select any cell in the "H" column that is within the first vegetation type. Use the formula =-SUM(pi*LN(pi)) to
calculate H for the Grassland vegetation type. To
do this, type =-SUM() into the cell. With your
cursor inside the parentheses, select all the cells
in the "pi x ln(pi)" column for that vegetation
type. Use the same formula for the other
vegetation types. These values are the Shannon
Diversity Indices for each vegetation type. Record
your results in the table in question 6 of your
worksheet.
Figure 8: Calculating pi.
Figure 9: Calculating ln(pi).
Figure 10: Calculating pi x ln(pi).
Figure 11: Calculating H.
Measuring Biodiversity in Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 5 of 5
F. Calculate Evenness
You will use the Shannon Diversity Index and richness values to calculate evenness for each vegetation type.
Remember that Evenness is represented by the letter "E" and the formula is: E = H / H
MAX
Where:
H = Shannon-Weiner Diversity Index
H
MAX
= the highest possible diversity for that sample (calculated by ln(richness))
14. Create a table in a new worksheet
with four rows for the four
vegetation types and four columns
for: “H”, “Richness”, “H
MAX
”, and
“Evenness”. Fill in the H and
Richness values you calculated in
parts D and E.
15. In the first cell in the H
MAX
column of the table,
use the formula H
MAX
= LN(Richness). Type =LN()
and, with your cursor between the parentheses,
select the cell that corresponds to the richness
value for the Grassland. Repeat this for all
vegetation types.
16. In the first cell in the Evenness
column of the table, use the formula Evenness = H / H
MAX.
Type "=" and select
the cell that corresponds to the H value for the Grassland, then type "/" and select the cell that corresponds
to the H
MAX
value for the Grassland. Repeat this for all vegetation types. Record your results in the table in
question 6 of your worksheet.
Note: The version of Excel used in this tutorial is Excel 2013 for Mac. Not all features and functions may work the
same way in other versions of Excel.
Figure 12: Table to calculate evenness.
Figure 13: Calculating H
MAX
.
Figure 14: Calculating Evenness.