ANCILLARY NOTEBOOK.docx

EXCEL TUTORIAL for use in
NEUR/PSYC 2002 B V
Introduction to Statistics in Psychology
Written by: Brian Tansley
Introduction
This tutorial notebook has been written to introduce you to the basic Excel skills used so as to be able to
practically use the concepts taught in this course. The notebook is also intended as a resource to aid you in
completing the daily worksheet tasks assigned with each of the course’s lectures.
A data set obtained from a Survey Questionnaire administered to Section B of this course will be used to
help teach course concepts. Working with this data set is much easier with the knowledge of Excel topics
covered in this notebook. These and other course resources can be downloaded from CuLearn.
Note: Over the years I’ve found that many students don’t bother with this tutorial because they think they
‘learned’ Excel in high school. Maybe so. However, I recommend you read this notebook from cover to
cover RIGHT AWAY – and certainly before starting to work on the daily worksheets ­­ so you can better
gauge whether you know Excel well enough to work with it in this course. If it turns out you do know Excel
well enough then – great! However, if it turns out you don’t, then you’ve got some preparatory homework to
do before you can get into the course topics themselves. If you find you need an ‘Excel refresher’, then
spend the first few days of the course learning what’s in this notebook.
Learning to use Excel in addition to learning course concepts can be challenging.
Devote more time to your studying than you think you’ll need. If you find you are
having difficulty, email me and I’ll do what I can to help you, including arranging
an office meeting to get you over the speed bumps!
Why learn Excel?
Although you are enrolled in a sectiIntroduction to Statistics in Psychology ”, statistically
speaking, you are not likely to end up working in the discipline. Nevertheless, the quantitative methods and
power tools you learn in this course are general enough that you’ll find them useful in many areas of
endeavor. This makes learning the course material worthwhile.
1 Many of today’s work environments include data processing tasks – keeping track of
expenses and/or inventory, preparing a progress report, working with sales figures or employee hours and
schedules, analyzing program evaluation data or keeping track of clients in a database. Even if you know
what you want to do it can be difficult, cumbersome and time consuming to do it. You need power tools.
Excel is a general purpose power tool that can be put to use in automating the data processing
found in all of these tasks.
You can use Excel to do numerical summaries of data and a wide variety of other
numerical information processing tasks, including making tables and graphs to
include in written reports or visual presentations.
I’ve chosen to use Excel in favor of a ‘full­featured’ statistics program such as SPSS
for a number of reasons: its generality and ease of use amongst others. But perhaps the most important
reason is that you can easily convert statistical formulas into Excel spreadsheet calculations and, in doing
so, see how the ‘mechanics’ of statistical formulas actually work. I think this helps you understand statistical
principles more effectively.
Using Excel takes skill and skill takes practice. In order to become familiar with the
mechanics of entering various formulas you’ll get this practice by working on the Survey Questionnaire
data.
If you plan to do an honour’s degree and aspire to graduate training in the life or
social sciences, then Excel will be very useful because you can import data from Excel
worksheets into SPSS for additional analyses – getting the ‘best of both worlds’ in the process.
In a half­credit course there’s only enough time to learn a bit of Excel’s impressive feature list.
There are lots of books and lots of websites devoted to teaching Excel concepts, so you should have no
trouble learning Excel well enough to use it for this course. However, before you go out and buy an Excel
book, start with this tutorial notebook. It contains most of what you need to get on your way.
Tansley’s First Law of Computing: Sh*t happens and it happens to me.
Say this over ten times and believe it! Then, take steps to make sure that when it happens (AND IT WILL)
you have a way to recover. For example, make plenty of backup copies of your work and save them on
different media (and don’t keep them all in the same place – like your backpack, for example). Keep your
daily worksheet files stored separately, rather than all in a single large file. That way, if you lose one you’ll
still have all the others to study from… Back up your work frequently – every half hour – especially when
you are in the middle of critical work.
2 Basic Excel: Introduction and Skills Brian Tansley
What is Excel?
Excel is an electronic spreadsheet program: That is, a program designed to ‘look and feel’
like an old­fashioned paper spreadsheet – much like the ones accountants used before the advent of
desktop computers to ‘keep the books’ for a business...
While many of the early features of electronic spreadsheets were designed to assist in accounting tasks,
spreadsheets have become much more than this and are now used almost everywhere.
Excel automates the numeric processing work required to analyze data.
Excel is used business, education, scientific research, engineering, marketing, manufacturing, sales,
journalism, sports, and a host of other places you’d never expect (which is a great reason to want to
become proficient in Excel, since you’ll probably be using it in every job in your future).
You can also use Excel to create summary Tables and Graphs. This is very handy for producing research
reports. If you do a research project for your honour’s thesis, for example, your tables and graphs can be
created in Excel and pasted into the MS Word thesis draft document.
Some of the more powerful features of Excel include:
The ability to apply a wide variety of functions to entered data – either custom made ones or ‘canned’ ones
from the Excel function library.
The ability to copy and paste formulas into new cells so as to carry out the same numerical analysis on a
different portion of the spreadsheet (something we’ll be doing a lot of in this course).
The ability to reference data in cells on any worksheet in the workbook when writing a formula.
The ability to record your interactions with Excel that can be stored and played back to analyze a new data
set (with similar characteristics).
The ability to link to other programs, such as MS Word so that new data can result in new analyses, tables
and graphs, without requiring all of the original steps taken to create them.
While we won’t be using all of Excel’s features, it’s worth knowing that they are there and COULD be used
by you to perform significant feats of data analysis. Keep in mind that most people don’t know much about
how to use Excel, so this knowledge could provide you with a competitive advantage when looking for a job!
Excel operates on a FILE called “Workbook”.
An Excel ‘Workbook and consists of individual pages called ‘Worksheets’.
Each worksheet has its own name (found on the tab at the bottom of the worksheet). The default name of
the top worksheet (ie., the one that is visible upon opening) is “Sheet1”
This name is used as part of the address for data on that worksheet when referencing from another sheet.
You can change the name of a worksheet by double clicking on the tab and then writing over the existing
name.
3 Basic Excel: Introduction and Skills Brian Tansley
Each worksheet has an addressable name, located on the tab at the bottom of the
spreadsheet page.
A workbook can contain many spreadsheet pages.
Each worksheet consists of an ARRAY of Columns (consecutively lettered from A onwards
to the right) and Rows (consecutively numbered from 1 downwards).
The basic unit of a worksheet is the CELL, whose address is the intersection of the column
and row at which it is locatefor example, the address of the cell located at the intersection
of column E and row 5 is E5 .)
Opening Excel for the First Time:
Open Excel by double clicking on its icon on the desktop.
The first time you open Excel you are in “default” mode.
This means that the properties and characteristics of the program are the ones set by the manufacturer to
get you started.
They may or may not be what you prefer.
You can change things to suit your preference and/or the program will save these preferences along with
your work when you exit and save the program and will reappear the next time you open the file.
Setting up Excel for this course:
When you create your Workbook file and save it, Excel remembers the various options (formatting, etc.) you
have selected and keeps them in the file. So, when you open it again, all that work will be preserved.
This is another reason to save your file periodically (e.g., every 20 minutes or so).
Note the top row and leftmost column of the spreadsheet. The top row contain the column addresses and
the leftmost column contains the row addresses.
There are two ways of addressing cells, referred to as the A1 style and R1C1 style. We’ll use the A1
style in this course, which means the columns are Designated by LETTERS and the rows are
Designated by NUMBERS.
When you first open a new Excel file, check the top row of the spreadsheet. This contains the column
addresses. If the spreadsheet is in A1 addressing style, then you’ll see LETTERS in each cell across the
top. If this is the case you don’t need to change the addressing mode.
However, if you find the column addresses are designated by numbers this means your are in R1C1
addressing style by default and you’ll have to change to the A1 style.
For the MAC To return to the A1 style of cell addressing, go to Excel Preferences Authoring General Tab
and uncheck the box for R1C1 Reference Style.
For the PC: To return to the normal A1 style of cell addressing, go to Tools – Options. On the General
tab, uncheck the box for R1C1 Reference Style.
4 Basic Excel: Introduction and Skills Brian Tansley
Then Save the file. Every time you open this file from now on the A1 addressing style will be invoked.
While you are at it, you should set up your Preferences to Show Formula Bar and Show Status
Bar.
Resizing rows and columns:
There are several ways you can resize the width of columns and/or the height of rows.
To resize all columns select Format Column Width on the menu
To resize one column, click on the column header margin and drag the column to the width you desire.
To resize all rows select Format Row Height on the menu
To resize one row, click on the row header margin and drag the row to the height you desire.
Formatting text and numbers in cells:
Formatting text and number is similar to MS Word. Select the cells whose contents you want to format and
go to the Format Cells on the menu. You can change fonts, sizes, colours, alignment, number of
decimals shown (2 is typically enough).
You can also change the background colour of one or more cells. Highlight the cells whose background
colour you want changed and then find the ‘paint can’ icon in the toolbar. Select your new colour. It is best
to use pastel coloured backgrounds so you can see the text and/or numbers better.
What can you put into a cell?
You can enter DATA (numbers or text) into a cell OR you can enter a FORMULA into a cell (but not both).
DATA can be in a variety of numerical formats or text formats.
Numbers are entered directly from the keyboard or ‘pasted’ from other sources.
Text can be entered directly from the keyboard or ‘pasted’ from other sources as well.
Excel knows the difference between a number and a text version of the same number (which is, in fact,
treated by Excel as a text ‘string’ rather than as a quantity). A number embedded in a text string is treated
as a text string in Excel. For example the 4 in the text string “Brian4” is not treated in Excel as a number,
but as a text string. You can search for this string, but not perform arithmetic with it.
Formulas can be entered into a cell by ‘hand’ or through the use of a ‘formula builder’ which automates part
of the process for you.
5 Basic Excel: Introduction and Skills Brian Tansley
Formulas are preceded by the equals sign (=).
A formula has an ‘argument’ that defines the range of cells that the formula is to work on.
For example, if you wanted to calculate the MEDIAN of the data in cells F10 through F100, you’d write
=MEDIAN(F10:F100) and then press Return.
The formula will disappear from the cell and the numerical value will appear in its place.
We’ll learn about many of these features in subsequent Tabs in this notebook
Entering data:
Data are entered into Excel one datum per cell. Click on the cell and enter the data exactly as recorded.
Once done, click on the next cell and repeat the process until all data are entered. Check your work.
For the Survey we are using in this course, this means that the answer to one of the 50 questions from one
participant is entered into its own spreadsheet cell.
The answer might be in text format, as in Survey Questions 1, 2, 4, 10, 11, 13, 16, 18,2 0, 22, 24, 28, 29,
30, 32, 33, 34, 37, 48 and 50.
Forced­choice answers like the ones on this questionnaire typically involve categorical data.
Note that categorical data have a fixed number of categories (from 2 (as in “yes/no”) to 4
(as in “Spring/Summer/Fall/Winter”).
The answer might be in discrete numerical format, such as in Survey Questions 3, 7, 8, 9, 14, 15,
17, 19, 21, 23, 31, 36, 39, 44, 47, etc.
The answer might be in continuous numerical format, such as in Survey Questions 5, 6, 12, 25,
26, 27, 35, 38, 40, 41, 42, etc.
Since there are 50 questions in the survey, a completed survey from one participant will take up 50 cells in
the spreadsheet (in adjacent cells on the same row).
All Ss’ responses to a given question appear in the same column.
While there are many different ways to organize these data, this one is simple and suits the objectives of
this course.
The array for the survey data from a single individual are all stored in a single row starting with the
Subject’s ID code in Column A and ending with the Subject’s answer to question 50 in
Column AY.
Entering Functions:
One of Excel’s more powerful features is the ability to enter functions that operate on the data in cells
throughout your workbook.
6 Basic Excel: Introduction and Skills Brian Tansley
A function is entered in any cell by starting with an = sign.
Most functions operate on the data found in other cells.
For this reason you’ll write such functions using the following form: =Function(argument). The
argument contains various types of information the function uses to complete the calculation you want done
– for example, it will contain the start and end address of the data array you want to operate on. Let’s say
you wanted to calculate the arithmetic mean of the data in column B, starting at row 2 and ending at row
101. One way you can do this is to write your own function in the cell at the bottom of the column (say, row
103).
As an example, here’s one way to write the formula for the arithmetic mean
=sum(b2:b101)/count(b2:b202).
Note the formula has two parts: a numerator and a denominator.
The numerator tells Excel to add up the contents of the cells in column B from row 2 to row 101.
The denominator tells Excel to simply count how many cells there are between b2 and b101 (this is n).
Finally, you put a slash between the numerator and denominator to tell Excel you want to divide the former
by the latter.
You can write your own functions in Excel but there are also a wide variety of
‘canned’ functions available in Excel that will help you with much of your work.
For example. Here’s a ‘canned’ formula that will create the same result: =AVERAGE(b2:b101)
Note that this canned formula has the same argument because it has to operate on the same data.
You can enter the canned formula yourself OR you can select it from a list of canned formulas available in a
separate window (see below).
I recommend you try BOTH WAYS when first learning the equations we will be
using in this course.
Please be careful when using canned functions. It is helpful to compare the results with ones you have
written yourself. They aren’t always the same for reasons that will become apparent later in the course.
To find all of the canned functions in Excel, select a cell and then go to the Insert menu item
and select Function. The ‘formula builder’ will appear in its own window. Scroll down the list of functions
(the most recently used ones will appear first) until you find what you are looking for. Most of what we will
use in this course are found under the ‘Statistical’ heading.
Once you have found the function you are looking for, double click on it and it will appear in the cell you
previously selected. You have to enter the argument between the brackets and then press RETURN.
Copying and Pasting Functions in Excel:
Another powerful feature of Excel is the ability to write a function and then copy it and paste it into other
cells where it will perform similar numerical calculations there.
7 Basic Excel: Introduction and Skills Brian Tansley
Copying and pasting functions have some similarities to the copy and paste functions you are familiar with
in MS Word. However, there are some important differences which are noted here.
First, when you paste a function into a different location, Excel automatically re­assigns the argument
relative to the new cell address where you pasted the function. This means that all cell references in the
function’s argument change by the DISTANCE between the copied cell and the pasted cell.
Sometimes you don’t want to change the cell addresses in the pasted argument (for example, when you are
using a constant in the equation which is located in a given cell). An example is the z­score transformation.
To solve this problem, you can “LOCK” the cell addresses in the argument of a function by putting a $ in
front of the column address and/or a $ in front of the row address.
Here’s an example of the proper way to write the formula for the z­score
transformation (as originally described on the Z­score page) when you plan to copy and
paste the function into a whole range of cells (as you will do when you want to
perform a z­transform of every raw score in a sample of scores).
Let’s say we have the raw data from a sample in the cells of Column A, from A1 to A100. We’ve previously
calculated the mean of the sample in A101 and the st. deviation of the sample in A102. We want to
calculate the z­score for each datum and put it in the cell to the right (i.e., in the same row but in Column B).
We could write out the formula 100 times, but that would be a major pain. Instead, let’s write the formula
ONCE, then copy the formula and paste it into the other 99 cells!
Remember that Excel will re­address items in the equation relative to the new location. For items that you
DON’T want re­addressed (i.e., you want them to remain the same at every pasted location) you must
LOCK their addresses before copying the equation.
First, write the function in cell B1 using locked addresses for the sample mean and sample standard
deviation (these two addresses will always remain the same no matter where the function is pasted)
=(A1­$A$101)/$A$102 (note by adding the $ in front of the cell row and column addresses you’ve now
locked the addresses for the sample mean and sample standard deviation)
If you now highlight this equation in the cell and copy it, you can paste it into each cell from B1 to B100.
Another way to do this is to click on the cell containing the pasted formula and you’ll see a little box in the
lower right hand corner of the cell. Click on that box and Drag it down the column all the way to B100 and
then let it go.
This action will paste the new equation in each of the remaining 99 cells.
Note that the address for the raw score in the original equation was A1. Because you did not lock this
address, when it is pasted into a new cell, that address changes (the locked ones won’t). Thus, the
equation you pasted into cell B88 looks like this: =(A88­$A$101)/$A$102 That is, the equation
still takes the sample mean (located at A101) away from the raw score located at A88 and then divides this
by the sample standard deviation (located at A102).
Practice copying and pasting functions. After a while you’ll get fast at it!
8 Basic Excel: Introduction and Skills Brian Tansley
Sorting:
One of the most valuable features of Excel is its sorting capability.
The term ‘sorting’ refers to the procedure whereby the data for a given measure are rearranged in either
ascending or descending order, based on a criterion you give Excel.
For example, let’s say you want to look at the responses to the questions in the
sample broken down by gender.
In this example you would sort the data by the gender question (#2). This would rearrange all the data such
that all data rows for female Ss were placed before all data rows for male Ss (because “F” comes before
“M”).
You can sort your database using Excel commands. The commands you would use to sort
the database into female and male categories is as follows:
STEP 1: Highlight the area you want to sort (including the column headers).
For the survey data this means all columns from A to AY and all rows from top to bottom.
IMPORTANT! When you sort data you want to keep the data from a given Ss all together on the same
row. So you must be careful not to sort some columns but not others because if you do this, the data from a
given Ss will now be located on different rows (and it will be impossible for you to know ‘what’s what’.
If you mistakenly do this, simply undo it or close the program without saving it
and reopen it again.
STEP 2: Click on SORT.
A window will open that gives you some sorting options.
First, click on the + button to add a sort criterion. A blue band will appear.
Next, click on the blue band immediately under the Column header and the list of all survey questions will
appear.
Select Gender from the list.
Press the OK button at the bottom of the window.
The data will resort themselves with all the females above all the males.
You can do more complicated sorts, using more than one criterion. Practice doing this because we’ll be
doing it a lot in this course.
REMEMBER THAT YOU CAN ALWAYS UNDO WHAT YOU JUST DID BY GOING TO
THE EDIT MENU ITEM AND SELECTING UNDO.
ALSO, REMEMBER TANSLEY’S FIRST LAW OF COMPUTING…
9 Creating A Workbook Brian Tansley
Excel files are stored as workbooks – collections of worksheets.
Step 1: Open Excel and Select New Workbook. First, look at the column address
headers. They should be LETTERS, not numbers. If they are numbers, change them to letters by going
to the EXCEL menu and selecting Preferences, then click on Authoring General and DESELECT the R1C1
option. The column headers should now be l