To analyse data, it will be helpful to be a confident user of Excel. In fact, no matter what career path you choose, you will probably need to use a spreadsheet application from time to time. Therefore, having good Excel skills will not only help you in your studies, but will also be a great asset for future internship or job applications.
Please note that you will not need to remember the formulas you are going to use in this lab. However, you should be able to find them in this document and adapt them to a new analysis at a later point in time.
Please treat this chapter and the next as a continuous exercise. That is, if we describe something that is new to you, please try it out right away. To try things out, create very simple Excel files similar to the ones shown in the screenshots below.
If you have very little previous experience using Excel, you might also want to complete an introductory Excel course online. Many of these courses are available for free.
At the most basic level, you need to understand the layout of worksheets in columns, rows, and cells. In Excel, columns have letters, rows have numbers:
Every cell is addressed by the combination of column letter and row number. For example, the first cell is referenced like this:
The usual shortcuts also work in Excel (
Cmd for macOS,
Ctrl for Windows):
Cmd + Xor
Ctrl + X
Cmd + Cor
Ctrl + C
Cmd + Vor
Ctrl + V
Note that there is a very useful variant of paste: “Paste special”. You can read more about “Paste special” here.
To select one cell, click on it.
To select adjacent cells, click on the first cell, hold the mouse button down, and drag the mouse to the target cell.
To select a column, click on the cell containing the letter (note how the mouse pointer changes into a downward-pointing arrow).
Selecting rows works in the same way.
To select all cells in a worksheet, click on the
Select All button:
Select the cells, rows or columns, then click on
Alternatively, you can use the
Delete dropdown menu:
Delete will not only delete the content of the cells/rows/columns, but the cells/rows/columns themselves.
The default column width in Excel is 8.43 characters using the default font. This is somewhat annoying, as it will frequently only display part of your headers:
To quickly autofit the width of all columns on the worksheet, click the
Select All button, and then double-click any boundary between two column headings (note how the mouse pointer turns into a double arrow):
You can read more about changing column widths and row heights here on this topic.
Click on this icon to sort:
You will almost always need
If your worksheet has headers, make sure
My list has headers is ticked:
Custom sort allows you to add multiple levels of sorting criteria. This example would first sort by
response.corr (first listing all correct trials), and then by
response.rt (first listing the fastest trials):
The result might look like this:
Note: You need to make sure to select all columns before sorting (e.g., using the
Select All button), otherwise your columns might end up being out of sync (e.g., RTs might no longer be associated with the matching accuracy information). Excel might present you with a popup warning if you fail to select all columns.
Data in Excel cells can have one of a number of different formats. The most relevant for us are text and numbers (more information about number formats is available here). There is a simple way to identify those without explicitly checking the format: Text in a cell is left-aligned, numbers are right-aligned. Note that some functions (e.g.,
COUNT) only work on cells formatted as numbers.
There are two types of cell references in Excel, relative and absolute. Relative references are the default. Absolute references are indicated by the
A1is a relative reference to the cell
$A$1is an absolute reference to the cell
Please note that you can mix relative and absolute cell references:
A$2is a relative reference to column
A, and an absolute reference to row
$A2is an absolute reference to column
A, and a relative reference to row
How absolute cell references work is explained in Section 39.3.