Skip to main content

BSCI 1510L Literature and Stats Guide: Excel Reference and Statistics Manual

Introduction to Biological Sciences lab, first semester

Dropdown subpages to read content

Note: to carry out the statistical tests described in this manual using Excel for Windows, the Data Analysis Tools must be enabled.  This has already been done on the lab computers but if you are using a computer elsewhere, you may need to enable it.  Go to the File tab then select Options.  Click on Add-Ins.  Select Analysis ToolPak, then click OK. 

The Data Analysis Tools pack is only available in Office for Mac 2016 - it can't be enabled in older versions.  However, all Vanderbilt students can download a free copy of Office 2016 for Mac from the software store.  If you have Mac Office 2016 Excel, you can enable it as follows:

1. Click the Tools menu, then click Add-Ins.

2. Select the Analysis ToolPak from the Add-Ins available box.  Click OK. 

3. Restart Excel.

The Analysis tools should appear on the Data ribbon at the far right.

Suggested reference listing for this guide

Baskauf, S. J. 2016. Introduction to Biological Sciences Lab (BSCI 1510L) Excel Reference and Statistics Manual. Vanderbilt University, Nashville, TN, USA. http://researchguides.library.vanderbilt.edu/bsci1510L

Contents

1 Formulas in Excel
1.1 Making a basic formula in Excel
1.2 Copying a formula to other cells
1.3 Using the fill handle
1.4 Preventing cell reference changes during a copy/paste or fill handle operation
1.5 Using Excel functions

2 Basic operations in Excel
2.1 Paste values
2.2 Sorting
2.3 Cutting and deleting cells
2.3.1 Cutting vs. copying
2.3.2 Delete key vs. Delete cells
2.3.3 Eliminating cells that bound a range argument in a formula

3 Measurement and variation
3.1 Describing quantities and their variation
3.1.1 The mean
3.1.2 The variance (s2)
3.1.3 The standard deviation (s)

3.2 Samples versus populations
3.2.1 Human height example
3.2.2 The standard error of the mean (S.E.) and sample size (n)
3.3 Calculating descriptive statistics in Excel

4 Variation and differences
4.1 Getting "the answer"
4.2 Sources of variability
4.2.1 Experimental error
4.2.2 Intrinsic variation
4.2.3 Experimental effect
4.3 Describing the causes of variability
4.4 Alternative explanations of results
5 Differences in experimental science
5.1 Aside: commuting to Nashville
5.2 P and detecting differences in variable quantities
5.3 Statistical significance
5.4 A test for differences of sample means: 95% confidence intervals
5.4.1 What is a 95% confidence interval?
5.4.2 Testing for significance using 95% confidence intervals
5.4.3 Statistical power
5.4.4 Calculating 95% confidence intervals using Excel
5.4.5 An example of the application of 95% confidence intervals
5.4.6 Weaknesses in using 95% confidence intervals as a significance test
5.5 Error bars in figures
5.5.1 Examples from journal articles
5.5.2 Creating a column chart with error bars using Excel
5.6 Discussing statistics in your scientific writing

6 Scatter plot, trendline, and linear regression
6.1 Creating a scatter plot in Excel
6.2 What do we need a scatter plot for?
6.3 Regression as a statistical test
6.4 Descriptive information provided in a regression analysis
6.5 Adding the equation and R2 of a trendline in Excel
6.6 Performing a linear regression analysis using Excel

7 The t-test of means
7.1 The t value
7.2 Relationship between t and P
7.3 Performing a t-test of means using Excel

8 Paired t-test
8.1 The problem of uncontrollable variation
8.2 Performing a paired t-test using Excel

9 Two-tailed and one-tailed tests

10 Variation on t-tests: ANOVA

11 Reporting the results of a statistical test

12 Summary of statistical tests