Analyzing Tree Size Structure to Understand Fire Effects on Population Dynamics

An Introduction to Excel

Synopsis:

Tree diameter measurements reveal the demographic history written into forest stands. They can help us determine whether populations exhibit continuous recruitment or regeneration pulses. In this workshop, you'll learn the ecological theory behind size structure analysis and master Excel techniques to transform DBH data into frequency distributions that expose recruitment bottlenecks and fire effects. Using Caribbean pine data from Belize and your own field measurements, you'll calculate population statistics, create publication-quality graphs, and interpret what different distribution shapes tell us about past disturbance regimes. Students interested in learning these same analyses in R programming can find the instructions here.

In this workshop, you will learn to:

  1. Explore the data and perform quality control

  2. Create size class distributions for each treatment

  3. Calculate summary statistics, including mean and standard deviation

  4. Create graphs to compare DBH distributions between treatments

  5. Interpret results in the context of fire ecology theory

Caribbean Pine (Pinus caribbea)

In collaboration with fire managers in Paynes Creek National Park, Belize, researchers examined the size distribution of 237 Caribbean pine trees. Methods involved establishing 11 permanent 20×50m plots in 2016-2017, with 6 plots in open savanna environments (sparse pine canopy, abundant herbaceous cover, little litter) and 5 plots in shrubby savanna environments (variable pine overstory, primarily shrub groundcover with some bunchgrasses). Column Definitions:

EntryID: Sequential number (1-237)
Plot: Plot number (identifies different locations/treatments)
Veg: Vegetation/treatment type (fire frequency category)
Tag: Individual tree identification number
Stage2017: Life stage category (seedling, sapling, adult, etc.)
DBH2017: Diameter at breast height in 2017 (cm)

Reference: Fill, J.M., M. Muschamp, F. Tricone, R.M. Crandall, and R. Anderson. 2025. Large trees are most influential for long-term persistence of Caribbean pine (Pinus caribaea var. hondurensis) populations in lowland Belize savannas. Biotropica, DOI: 10.1111/btp.70019.

Download Data

Introduction & Data Import

Materials Needed

Files: #008a99

  • CPineData_forPH.csv (provided)

  • Excel_Analysis_Template.xlsx (you'll create this)

Software:

  • Microsoft Excel 2016+ with Analysis ToolPak enabled

Preparation:

  1. Download CPineData_forPH.csv to your computer

  2. Create a new folder: Caribbean_Pine_Analysis

  3. Save the CSV file in this folder

Open Excel and Import Data

Detailed Instructions:

  1. Open Excel → Click "Blank Workbook"

  2. Import the CSV file:

    • Click Data tab

    • Click Get Data (or From Text/CSV in older versions)

    • Navigate to CPineData_forPH.csv

    • Click Import

  3. In the preview window:

    • Verify that columns are separated correctly

    • Check that "Data Type Detection" is set to "Based on entire dataset"

    • Click Load

  4. Save your workbook:

    • File → Save As

    • Name it: Caribbean_Pine_Analysis.xlsx

    • Save in your Caribbean_Pine_Analysis folder

  5. Rename the sheet:

    • Right-click on "Sheet1" tab at bottom

    • Select "Rename"

    • Type: Raw_Data

    • Press Enter

MODULE 1: Data Exploration and Quality Control

Part A: Initial Data Exploration

Step 1: Examine the Data

  1. Click on cell A1 (should say "EntryID")

  2. Press Ctrl + End to jump to last cell with data

  3. Checkpoint: How many rows are in the dataset?

Step 2: Explore Each Column

Plot Column (Column B):

  1. Click on any cell in column B (e.g., B2)

  2. Insert a PivotTable:

    • Click Insert tab

    • Click PivotTable

    • Select "New Worksheet"

    • Click OK

  3. In PivotTable Fields pane (right side):

    • Drag "Plot" to "Rows" box

    • Drag "Plot" to "Values" box (will show "Count of Plot")

  4. Result - See figure to right

  5. Checkpoint: How many unique plots?

  6. Rename this sheet: Right-click sheet tab → Rename → Plot_Summary

Go back to Raw_Data sheet (click tab at bottom)

Veg Column (Column C):

  1. Click on cell C1

  2. Click Data → Filter (or Ctrl + Shift + L)

    • Small dropdown arrows appear in the header row

  3. Click the dropdown arrow in "Veg" column

  4. Checkpoint: How many unique values do you see? These are your treatment categories!

Stage2017 Column (Column E):

  1. Click dropdown arrow in "Stage2017" column

  2. Checkpoint: What are the life stages?

DBH2017 Column (Column F):

  1. Click on cell F1

  2. Scroll down to look at the DBH values

  3. Checkpoint: What are the minimum and maximum values? Do you see any unusual values (very small, very large, blank)?

Part B: Quality Control Checks

Create a new sheet for organized data:

  1. Right-click on any sheet tab

  2. Select "Insert"

  3. Choose "Worksheet"

  4. Rename it: Organized_Data

Copy data to new sheet:

  1. Go back to Raw_Data sheet

  2. Click cell A1

  3. Press Ctrl + Shift + End to select all data

  4. Press Ctrl + C (copy)

  5. Go to Organized_Data sheet

  6. Click cell A1

  7. Press Ctrl + V (paste)

QC Check #1: Missing DBH Values

  1. Click on cell G1 in Organized_Data sheet

  2. Type: QC_Flag

  3. Press Enter

  4. Click on cell G2

  5. Type this formula EXACTLY: