Ecology
Published March 2018
www.BioInteractive.org
Page 1 of 4
Scientific Inquiry Using WildCam Gorongosa
PC Excel Tutorial
A. Overview
In this tutorial, we will use the example research question from your handout:
During which season is the highest abundance of animals present in the limestone gorge vegetation type?
First, we filtered the data in WildCam Lab to include only photos from the limestone gorge vegetation type. To
analyze the data to address the research question, we will create a bar graph showing the total number of
animals captured in trail camera photos in the limestone gorge in each season. You may have to filter your data
differently and make a different type of graph to answer your own question later.
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. If you think your
data needs to be analyzed in a different way, consult your instructor for guidance.
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 Table/Range. Navigate to your data
and click on the letter “A” to highlight the first column. With the
left mouse button held down, drag the cursor to the last column
to highlight the entire data set. Under “Choose where you want
the PivotTable report to be placed,” select New Worksheet.
Click OK (Figure 2).
Figure 1: Inserting a pivot table.
Figure 2: Selecting the range of data.
Scientific Inquiry Using WildCam Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 2 of 4
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 the categorical variable, “season.” To build the pivot table, you will follow
the steps explained below to place the grouping variable (season) in the ROWS field and the variable(s) being
grouped (species_count) in the VALUES field. You can group data by more than one variable, and you can also
group more than one variable.
4. In order to create a bar graph that shows the number of
animals in each season, you will need to select the
“season” and “species_count” fields in the list of
PivotTable Fields (Figure 3).
5. By default, the fields you select will appear
in the ROWS field. Drag the “species_count”
variable to the VALUES field (Figure 4). This
will produce a table with the seasons 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 4: Moving the species_count variable to the VALUES field.
Figure 3: Selecting the fields to use in
building the pivot table.
Figure 5: Choosing how to summarize the value field.
Scientific Inquiry Using WildCam Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 3 of 4
D. Create a Bar Graph
7. To create a bar graph, select the data inside the pivot table (in this case, cells A4:B7), click the Insert tab, and
select the Column chart dropdown arrow. Choose the 2-D clustered column chart type (Figure 6).
Figure 6: Inserting a bar graph to graph the data from the pivot table.
8. Customize your graph to meet your instructor’s guidelines for any report that is due from this activity (Figure
7).
Figure 7: Bar graph of the number of animals photographed in the
limestone gorge vegetation type in each season.
0
1000
2000
3000
4000
5000
6000
7000
8000
Dry Jul-Sep DryWet Oct-
Dec
Wet Jan-Mar WetDry Apr-
Jun
Total number of animals observed
Season
Scientific Inquiry Using WildCam Gorongosa
Ecology
Published March 2018
www.BioInteractive.org
Page 4 of 4
E. Answering Your Question
In this activity, you came up with your own testable research question. Now you will use the techniques from
this tutorial to create a graph to explore your research question.
First, ask yourself which type of graph you need to create to help answer your question. Which variable(s) are
independent variables (Rows), and which ones are dependent variables (Values)?
Step 1: Create a Pivot Table
Once you have downloaded the data from the WildCam lab, follow the instructions in Section B to create a pivot
table as you did for the example in the tutorial.
Step 2: Build the Correct Pivot Table
Use the instructions from Section C to build the correct pivot table. You will only select the variables that you
need to create your graph. Drag the appropriate grouping variable(s) (your independent variable(s)) to the ROW
field. Drag your dependent variable(s) to the VALUES field, and be sure to select the correct calculation (e.g.,
count, sum, average, etc.). If you are unsure which variables belong in which field, test it out and see if the pivot
table makes sense. You can always drag variables back if you made a mistake.
Step 3: Create a Bar Graph
Use the instructions from Section D of this tutorial to guide you. Consult with your instructor to determine
whether a different type of graph is necessary to present your data.
Note: The version of Excel used in this tutorial is Excel 2013 for PC. Not all features and functions may work the
same way in other versions of Excel.