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.

If you look under the 'Data' ribbon/tab on the top-line, there should now be "Data Analysis" on the far right.

IF you see "Analysis tool" or get "Excel data miner"....those are NOT the correct package.  

The above is a basic set of steps, your system may be different. Search on-line for more specific set-up instructions.

BECAUSE THERE are a variety of systems (Dell, Mac, Acer, etc.) and also OS types/models (Windows 10, Windows 11, macOS 14 Sonoma, etc. ) and also internet browser types (Edge, Chrome, Safari, etc.), please be aware that ALL instructions given are based primarily on what is available on the BSCI computers in the BSCI Lab.  If your system differs, you may need to adjust instructions as the software (look) may differ or 'steps' be different.

Apologies, we cannot make a version for every system/OS possible. 

 

Use of Excel

---

We will use Microsoft Excel extensively throughout this course.  Students enter this class with a wide range of experience with Excel, ranging from very little to extensive.  If you are an Excel expert, you will already have most of the basic skills outlined on this page.  If you are a novice, YOU NEED to learn all of the techniques explained in the links below (and in later labs).  It will be assumed that all students have the skills outlined on this page after this first lab week.

WE WILL BE USING EXCEL in multiple labs experiments for data analysis.  It is NOT a good idea to have one partner perform Excel calculations while the other sits and watches. Aside from almost all of you having Excel available on your home computers/laptops, Excel has many wonderful uses outside of this course and is a basic skill needed in the world at large.

This is your chance to learn it!  We can show you how to!

If you are an Excel “PRO”, we are not trying to overload you.  You may find something useful as well that you were not aware of.  If you are an Excel novice, the reading (with examples) WILL be of great help to you.

There are also three keyboard command codes that are essential for not only Excel, but also any typing that you perform (Word, Power Point, etc.):

For Dell, Acer, and other 'PC' types 'crtl' key plus a letter	For Mac, MacBook, any 'Mac' system
control-z	command-z	Pressing 'control' and 'z' at the same time will undo the action/mistake you just did.
control-c	command-c	Control-c (copy) discussed later on this page in Section 2.
control-v	command-v	Control-v (paste) discussed later on this page in Section 2

Click here to go to the statistics manual for the second semester.

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

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 (s²)
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 R² 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