A Beginner's Guide to Interpreting Biological and Ecological Data
using MS Excel
(online version, revised 2014)

Written and posted by Dr. Rhine Singleton
Departments of Biology &
Environmental Science
Franklin Pierce University

July, 2005: I would like to thank several colleagues, friends and family members. From the beginning, Catherine Koning, Fred Rogers, and Jacques Veilleux encouraged the idea of writing a guide to statistics for undergraduates. The enthusiasm and creative suggestions of Michael Lehner were great sources of motivation while I was writing. April, Jordan, and Isaiah were supportive of my efforts even when this manual was a source of distraction. Most importantly, I want to thank Jordan for his thorough reading and insightful comments that helped improve and clarify the text.
June, 2014: Once again, Catherine Koning has been a source of encouragement for this project and making it available online. My friends Hadley and Guntra were kind enough to share their apartment in Latvia where many of these revisions were made. My fellow musicians (you know who you are!) have kept me relatively sane when data analysis has threatened to do otherwise. Most importantly, I thank April for her patience, support, sound advice, and tolerance of my obsessions with projects such as these.


How to Install the Analysis Toolpak
(necessary for doing statistics in MS Excel)



location and spread of data
pattern, dependent and independent variables
categorical vs. continuous variables
random chance

Chapter 1—Comparing the Means of Two Groups of Numbers:
Scatterplots and the t-Test
Entering & Describing Data
Interpreting a t-Test Scatterplot
Interpreting t-Test Output

Making a t-Test Scatterplot
Formatting Your Scatterplot
Doing a t-Test

Chapter 2—Comparing Means Among Three or More
Groups of Numbers: Analysis of Variance

Why Anova Rather than a t-Test?
Interpreting Anova Output

Adding Mean Values to a Scatterplot
Doing an Anova and Formatting Your Output

Chapter 3—Looking For Relationships Between
Dependent and Independent Variables:
Scatterplots and Regression Analysis

Independent and Dependent Variables
Interpreting a Regression Analysis Scatterplot
Interpreting Regression Analysis Output

Making a Regression Analysis Scatterplot
Doing a Regression Analysis

Chapter 4—Comparing Counts With Expected Values:
Chi-Square Test

Count; Value; Frequency; Relative Abundance
Contingency Tables
Expected Frequencies based on Reference Data or Theory

Calculating Expected Values for Cells in Contingency Tables
Making a Bar Graph
Using a Table and Calculator to Determine Chi-Square
Calculating Expected Values from Expected Frequencies
Setting Up Spreadsheets to Calculate Chi-Square Values

APPENDIX I: The Language of Statistics

APPENDIX II: Hypotheses

APPENDIX III: What test is right for these data

APPENDIX IV: Using Formulas in MS Excel

APPENDIX V: Histograms

APPENDIX VI: Putting Error Bars on Graphs

APPENDIX VII: Finding and Displaying
Hidden Data on Scatterplots

APPENDIX VIII (coming soon): Pairwise Comparisons in Anova

Data Sets and other Excel Files

Mountain Lion Data
Butterfly Data
Fish Diversity Data
Crayfish Data
Tree Data
Fish Diversity Data (hidden data points)

Summary Statistics Calculator
Chi-Square Calculator for Contingency Tables
Chi-Square Calculator for Reference Data or Theory


References Cited

Stats Page at EcologyAndEvolution.Org

©2008-2019 EcologyAndEvolution.Org, All rights reserved.