Chapter 14 Spreadsheets 101
Spreadsheets are tables that organize data using rows and columns. Spreadsheets are used in nearly every field of study and in personal life. The two most popular spreadsheet programs are Google Sheets and Microsoft Excel. While they can do approximately the same things, the exact steps to perform analyses are slightly different. To keep things standardized between students we ask all students to use Google Sheets, which is also free for users with a Gmail account.
This section will cover some of the basic functions of Google Sheets.
A traditional mouse has a left button, a right button, and sometimes a center button. Typically, you click with the left button. Right clicking refers to clicking with the right button.
Modern laptops and tablets have translated this feature in different ways. If you’re not using a mouse or find that clicking with the right or right-bottom corner of the trackpad doesn’t work, try the following:
- Right click by holding Ctrl and left-clicking
- Tapping with two fingers as opposed to one
14.0.1 Getting a copy of the spreadsheet
Spreadsheets are featured in some of our C-MOOR curricula. We keep an original copy of the spreadsheet that can’t be edited by students and ask users to create a new copy to do their calculations in.
You will need to make a copy of the original spreadsheet in order to interact with it. To do so, in the top left corner of the window, under the name of the spreadsheet and to the right of the spreadsheet icon, click File > Make a copy. This new copy is yours to keep track of in your Google Drive.
14.0.2 Basic spreadsheet terms
Columns: Refers to the vertical groupings of data types and called by a letter (ex. Column A, B, C, and D.
Rows: Refers to the horizontal grouping of data types and called by a number (ex. Row 1, 2, 3…). In this example data, each row refers to a pet.
Cells: Refer to the intersection between a given row and column and can be called by a combination of their column and row. Example: B2 refers to the intersection of column B, row 2 (which contains “Dog”).
Header: Used to refer to the first row, which contains metadata or describes the data in each column.
14.0.3 Summary statistics by row or column
Clicking on a column or row gives you the count of how many filled in cells there are in the bottom right area of the window. Notice how there are 11 cells filled and reported but only 10 of them contain real data. Cell A1 contains “Name”, which is a label for what kind of data is stored, but not data itself. Keep this in mind when answering questions about count.
If numerical data is detected, you can click on the field in the bottom right corner and toggle it to display the sum, average, minimum, maximum, count value, and count value only including numbers.
14.0.4 Adding data to cells
To add data to a cell, click on the cell and enter your data. Confirm the entry with the “Enter” or “Return” key on your keyboard or by clicking or moving to another cell with the arrow keys.
Notice: Above the columns there is a field where you can see what is in a selected cell.
To add calculations to a cell, use the = sign. Spreadsheet software will treat any cell with an = sign as a calculation, and it will treat any cell without an = sign as text. To do this, compare what happens when you have cells with the following.
- 1+1
- = 1+1
1+1 will remain at 1+1, where as = 1+1 will cause the cell to calculate 2. We can see the original calculation we put into the cell above the columns. Calculations only work for numerical values. Cells with invalid calculations will cause a pop-up to warn you the cell is invalid.
To add calculations based on cell values, the formula must contain the specific cell. We can manually type out the combination of column and row that refers to a cell (ex. D2, or:
- Type = into a cell
- Click on the cell you want to use in a calculation
- Complete the calculation formula.
Notice: The cell used has colored borders matching the formula. You can interact with these borders to select different cells and find what is currently selected.
14.0.5 Applying a calculation to many cells
To use the calculation multiple times, we can do the following, all of which will copy the formula and carry down the selected cells so you don’t have to reselect the cells used for new rows and columns. Try the following below to see what we mean.
Method 1) Copy-paste the cell’s contents into new cells one by one. You can copy the cell with Ctrl+C or right clicking the cell and selecting copy. You can paste the calculation formula into new cells with Ctrl+V or right clicking the new cell and selecting paste.
Method 2) Paste into multiple cells at one time. If you have a calculation that needs to be applied many times, it can be inconvenient to paste a copied formula over and over. In this case, select multiple cells by holding down the shift key and paste into all of them at once.
Method 3) Drag the contents of the cell to copy the contents to new cells. When a cell is selected, a blue dot appears in the bottom right corner of the cell. Click and drag this dot and the formula will be applied to other cells
14.0.6 Manipulating the spreadsheet
Right clicking a column or row brings up a menu with options for adding or deleting columns/rows, filtering data, or sorting the spreadsheet.
Add or delete columns/rows: Insert or delete a column or row.
Filtering data: Filter data so that only rows that have a specific value are shown. In the above example, you could filter by species and retain only rows (pets) that are dogs.
Sort the spreadsheet: Sort the spreadsheet based on the values in the selected column (does not work for rows): alphabetically (A to Z), reverse alphabetically Z to A), lowest to highest (A to Z), highest to lowest (Z to A).
14.0.7 Useful formulas in Google Sheets
| Calculation | Formula |
|---|---|
| Sum | =sum(CELLS) |
| Average (Mean) | =average(CELLS) |
| Median | =median(CELLS) |
| Maximum | =max(CELLS) |
| Minimum | =min(CELLS) |
| Count cells that meet a certain condition | = COUNTIF(CELLS, “condition, where if true, the cell will be counted”) |
| Variance within a sample | =var.s(CELLS) |
Variance is a measure of how spread out or consistent a dataset is, and is similar in concept to the standard deviation of a dataset (although there are differences in how they are calculated). For both variance and standard deviation there are alternate formulas for datasets that comprise a sample - var.s (which is used above), and for datasets that comprise entire populations (var.p). We use the population version when our dataset encompasses the entire population that we are interested in. Make sure you select the right formula for the right question! The majority of times, var.s will be more appropriate. If you’re not sure what to use, ask your instructor.