At the end of this workbook students will be able to:
Enter values into cells and format them
Create a table appropriate for graphing with rows of cases and columns of variables
1) SAVE A COPY – Download this file onto a flashdrive or the desktop (which you will then send to yourse
–> Top left corner “office button”
–> Save As
—> Name file “Excel Template” and save
Keep this file for reference now and for other analyses/classes.
2) Reopen this file and work through each tab at the bottom in sequence. Be sure to complete the acti
Save it and send it to yourself – you’ll be entering data, doing analysis and creating a graph as part of th
Be sure to scroll down and up the page (see scroll bar far right) to make sure that you start at the TOP of each pa
Workbook – an Excel document like this
Worksheet – each Workbook can have multiple worksheets – see the tabs at the bottom? Each one is a different wo
s and creating a graph as part of the exercise, then doing the same for your own lab.
Review this sheet to learn the basics of Excel.
Start by viewing one or both of these videos by GCF (Goodwill Community Foundation)
An 11:26 minute video that assumes that you have no knowledge
A 4:20 video that focuses on how to work with cells, and assumes
Below is an image of Excel with important components from Excel 2010, which will be similar to 2007.
Review carefully to familiarize yourself with important features.
(Creative Commons License)
Excel 2013 is very similar. Click here for a tour of the 2013 spreadsheet. If you are new to Excel you will want to v
mes that you have no knowledge of Excel; it uses the 2013 version. It is a good refresher, even if you are familiar with Excel.
to work with cells, and assumes you know some basics. It uses Excel 2010, however many screenshots shown apply to Excel 2013.
are familiar with Excel.
ts shown apply to Excel 2013.
Excel is an important tool for managing and analyzing data
Excel is regularly used for scientific studies to:
Create tables and spreadsheets
Explore and organize data
Do descriptive and comparative statistics
Represent data and summaries or statistics graphically
The simple study we are using as an example is a study of the heights of female and male students in an Intro Eco
On average men are taller than women
There is no difference in height between men and women
The average height of male students will be (statistically) greater than the average h
You will use this data set throughout your tutorial, however the goal is for you to apply this knowledge to your own
create a table of data, organize it, calculate statistics, and present your data and results graphically.
You can use this workbook for your own data, but use the tutorial to make sure you understand what you’re doin
Parts of a spreadsheet (table)
Read through the yellow boxes so that you understand how data can be organized
ROW – uses
COLUMN vertical, uses
The data shown here is of the heights of female and male students. In th
Each “case” is a student whose height has been measured. The actual he
NOTE: Always provide a title and include units
Height in cm
NOTICE – we will be comparing the heights of females to
males, the order of the data within each column does
NOTE: while there are advantages to having the same number in
each category of independent variable (here, female and male), it is
emale and male students. In this case the variables are “female” and “male”.
been measured. The actual heights are the data.
e heights of females to
n each column does
to having the same number in
iable (here, female and male), it is
Altering the appearance, position, color etc. of a cell can help make information easier to read and understand.
1. Watch this video for simple instructions on basic formatting of cell contents
Refer back to this video to learn to cut and paste
2. Answer these questions:
Where is the undo button?
In the table below, do Male and Female represent columns or rows?
In the table below, do the heights represent columns or rows?
What is the hypothesis being tested?
3. Copy, paste, then format the table.
Look at the table to the left and compare it to that on the right.
The table on the right is clearer and the numbers make more sense.
Learning how to format can help you more easily manipulate cells and present data.
Table Without formatting
Height in cm
NOW YOU DO IT!
Use the directions on this sheet
Create a formatted table here
First copy the cells Without formatting and paste them starting in cell G21 (column G, row 31)
Now format the cells so that they look like those in the table With formatting.
You will need to:
Change the number of decimals shown to one place (e.g. 187.0 vs. 187.0000)
Center cells with data
Bold the headings
Put a line under headings
How do you do all this? The videos will show you how!
You can also go YouTube to ask specific questions about Excel
1 (column G, row 31)
Sometimes you would like to know the highest and lowest values of the data, and the total range. There’s a formu
Depending on the version of Excel you have, this can be accomplished in different ways.
The most important thing to consider is whether or not you need to have all the data in a row stay in that row – in th
if that is the case you need to make sure that everything is sorted together. The easiest way to do this is to select all
In our example male/female heights don’t need to be sorted together, so we will sort each variable separately.
Height in cm
Put Your Table Here
Find the shortest, tallest, and range of heights for both females an
1. Copy and paste the table starting in cell 10D (Row 10, Column D)
2. Select the Female column (Female and all values)
3. Click on the Data tab on the toolbar
4. Find Sort, and click on the AZ arrow option. If you get a dialogue box li
just click “continue…”
5. Check your results. Is the smallest number at top and the largest on th
6. Now do the same for Male data
7. Answer the questions below and check them for accuracy
8. Check your answers
Second Answer the following questions:
What is the height of the shortest woman?
What is the height of the tallest woman?
What is the height of the shortest man?
What is the height of the tallest man?
The range is the simplest expression of the spread of data – it is the diffe
between the minimum value (smallest) and the maximum value (largest)
What is the range of heights for women?
What is the range of heights for men?
in a row stay in that row – in this case that would mean male and female heights staying together.
Purchase answer to see full