Page 1 of 7
Measuring Biodiversity: Google Sheets Tutorial
INTRODUCTION
This database contains a subset of data from Snapshot Wisconsin cameras deployed in two different
Wisconsin ecoregions: Southern Wisconsin Till Plains (SWTP) and Northern Lakes and Forests (NLF). The
columns of the spreadsheet refer to different pieces of metadata, metadata is defined as a set of data
describing and giving information about other data (in this case the trail camera photos). The metadata
describes the compass bearing of the trail camera, height from the ground, height from the trail, trigger
ID (the name that the photo is stored as), camera number, animal, number of animals, data the photo
was taken, time, and temperature.
These step-by-step instructions are designed to help students take a raw dataset and convert it into a
series of Pivot Tables. The Pivot Tables will allow students to view the abundance of species for specific
ecoregions, and calculate biodiversity indices.
PART I: ACCESSING THE DATABASE
1. Download “Measuring Biodiversity Database.xlsx” file.
2. Go to google.com/sheets. Under “Start a new spreadsheet” at the top of the page, select
“Blank.” Once in Google Sheets, select File Open Upload Select a file from your
computer navigate to “Measuring Biodiversity Database.xlsx”.
3. Now that Google Sheets will display the Measuring Biodiversity Database we can begin
manipulating the data to calculate biodiversity indices.
Step 3. View of Measuring Biodiversity Database in Google Sheets.
Page 2 of 7
Measuring Biodiversity: Google Sheets Tutorial
PART II: CONDENSING THE DATA
4. Highlight the “Camera Number” column by clicking on the “E” button above the heading. Under
the Data tab, click the Filter tool. A downward facing arrow will now appear on the “Camera
Number” heading. Click on the downward facing arrow, click “Clear” and then select the
cameras appropriate for the ecoregion of interest (Southern Wisconsin Till Plains or Northern
Lakes and Forests, whichever you start with – you will calculate the other later). Click “OK”.
Step 4. Filtering the data for only cameras from ecoregion of interest, in this case Southern Wisconsin Till Plains.
5. Google Sheets will now show a condensed version of the data only displaying the ecoregion of
interest. Highlight the filtered data by clicking in the upper left corner of the Google Sheet under
the f x icon. Use Ctrl + C to copy the data, paste the data in the Workspace sheet using Ctrl + V.
This will allow you to manipulate the data that you want, while leaving the original dataset
intact. In the original Database sheet, select the Data tab and click “Turn off filter” to restore the
original dataset.
Page 3 of 7
Measuring Biodiversity: Google Sheets Tutorial
6. For this exercise, we are only interested in the abundance of animals in each ecoregion. In the
Workspace sheet delete all columns, except for “Animal” and “Number of animal”, by right
clicking on the column heading letter and selecting “Delete column.” You can select multiple
columns to delete by using the Ctrl button while you select columns.
Step 6. View of condensed data of interest in Workspace sheet.
PART III: CREATING A PIVOT TABLE
7. The next step is to create a Pivot Table to summarize the data. Highlight the “Animal” and
“Number of animals” columns by clicking on the “A” column heading + shift + “B” column
heading. While these columns are highlighted, click on the Data tab and select “Pivot Table.”
Google Sheets will create a new sheet called Pivot Table 1, navigate to this sheet. The Pivot
Table will initially be blank -this is OK! Continue to the next step.
Step 7. Highlight columns A and B (or whichever columns your data are stored in) and create a Pivot Table.
Page 4 of 7
Measuring Biodiversity: Google Sheets Tutorial
8. On the right-hand side of the screen in the Pivot table editor toolbar, select the “ADD” button
next to “Rows”, select “Animal.” Ensure that the box that says “Show totals” is checked.
Step 8. Adding animals to the Pivot table.
9. Next, select the “ADD” button next to “Values” and select “Number of animals.” Make sure that
they are summarized by SUM (this accounts for cameras where multiple individuals of a species
appear in an image, for example three deer in one shot). The Grand Total will reflect the total
number of individuals in the entire ecoregion.
Step 9. Adding the number of photos that animals appeared in to generate species abundance
.
Page 5 of 7
Measuring Biodiversity: Google Sheets Tutorial
Step 9. Example of what your Pivot Table should look like.
PART IV: PERFORMING CALCULATIONS IN GOOGLE SHEETS
10. Now your Pivot Table should display each species and the total number of sighting on the trail
cameras. Rename the headings to “Species” and “Abundance”, and create new headings for P
i
,
ln(P
i
), and P
i
*ln(P
i
) in the adjacent columns.
11. To have Google Sheets calculate the P
i
value, enter in =(CELL_LOCATION / Total Abundance). For
this exercise, the Grand Total value reflects the total number of individuals, do not reference the
cell of the total abundance, or the following step will not work correctly. For example, enter
=(B2/2135). To autofill the P
i
value for each species, click on the calculated cell and drag down
blue square in the bottom right corner.
Step 11. Example of what your Pivot Table should look like with added columns for Google Sheets to help organize
calculations, the red circles show the total abundance and equation for calculating P
i
.
Page 6 of 7
Measuring Biodiversity: Google Sheets Tutorial
12. Use Google Sheets to calculate ln(P
i
) by typing in the equation =ln(CELL_LOCATION) into the
corresponding cell. For example, =ln(C2) [these cell locations may vary based on your dataset]
Step 12. Calculation for ln(P
i
).
13. Calculate P
i
*ln(P
i
) using the equation =(CELL_LOCATION * CELL_LOCATION). For example,
=(C2*D2) [these cell locations may vary based on your dataset]
Step 12. Calculation for P
i
* ln(P
i
).
Page 7 of 7
Measuring Biodiversity: Google Sheets Tutorial
14. Calculate the Shannon diversity index using the equation H = -SUM (P
i
* ln(P
i
)). You can perform
this calculation in Google Sheets using the equation
=-SUM(FIRST_CELL_LOCATION:LAST_CELL_LOCATION). For example, =-SUM(E2:E16) [these cell
locations may vary based on your dataset].
Step 14. Calculation for finding the Shannon diversity index.
15. You can use a calculator to calculate Evenness (E), or take advantage of Google Sheets to do this
calculation for you! Use some of the techniques that you have learned in this tutorial.
16. Repeat these steps for the next ecoregion. Record your answers in your Student Worksheet.
