What’s in my (Spread)sheets?
Introduction to Spreadsheets
Jason Appel
Barrington High School Math Teacher
bit.ly/2yQC9fQ
The Answer is Always a Spreadsheet
Cell referencing
Refer to cells by their column header and row number.
Use cell references in formulas. i.e. to add the numbers in cells A1 and A2, Type the formula =A1+A2
Cell referencing
These are “relative” references, meaning they adjust when copied and pasted. Notice below that when I copy the formula and then paste it in the cells to the right, the pasted formulas shift over so that cell C3 adds the values in column C, D3 adds the values in column D and so on.
Learn more about cell references by working through this tutorial. The video is particularly helpful for visual learners.
Basic Formulas
Note: to refer to a range of values, separate the upper-left and lower-right cells with a colon. i.e. to average the values from cells B2 through F2 use =average(B2:F2)
Typing the = symbol tells Sheets you want to use a formula or function. Here are a few self-explanatory, basic math function: sum, average, median, mode.
Notice that as you start typing, Sheets tries to predict your function and tell you more about it. This is a great way to discover new functions and learn how to use them.
Formatting
Format values as number, percent, currency, date, time and more.
Select a cell or cells, click the Format menu and choose Number to see options.
Note: Choose More Formats at the very bottom for formats not in the main menu.
Copy Formatting
Copy all formatting from one cell to another cell(s) using the Paint Format tool.
This tool is magical!
Rounding
Adjust the number of decimal places to display using the buttons
Row height and column width
Adjust the size of rows and columns by moving the cursor between columns or rows until it turns into a double headed arrow, then click and drag.
Adjust several columns/rows at the same time by selecting them first.
Row height and column width
Double-clicking automagically adjusts columns to fit the contents.
Hot tip: click the “magic box” to select the entire spreadsheet!
Conditional Formatting
This allows you to format cells automatically, based on any criteria. It is an absolute must for looking at data.
In this example, I need to identify all scores below 7, and any students with at least one such score. Which option would you choose?
Option 1...seriously?
Option 2...oh yeah!
Conditional Formatting - how did you do that?
To automagically highlight any score less than 7 I did this:
Conditional Formatting - how did you do that?
To highlight students who had at least one score below 7, I used conditional formatting based on a custom formula. The sky's the limit here. I decided that the easiest way to do this was to highlight the student if the minimum value of their scores was below 7. The custom formula I wrote was =min(B2:P2)<7
Learn everything there is to know about conditional formatting here.
Wrapping Options
There are 3 options for Text wrapping, choose wisely!
Overflow - Wrap - Clip
Merging cells
Merge multiple cells together for a cleaner looking spreadsheet.
Find and Replace
Quickly change any text by finding it and replacing it with something else. You can even use this within formulas by checking the “Also search within formulas” box.
Sorting Data
Sort all of your data by simply clicking on the down arrow in the header of any column and choose “Sort sheet A-Z or Z-A.”
Hot tip: If your data has column headers (and it probably does) freeze that row(s) before sorting.
Filter Data
Turn on filters in the Data menu. This allows you to quickly view only select data based on the contents of a column.
Publishing to the Web
Publish an entire spreadsheet document, or just specific sheets within that document to the web.
Data Validation
Use data validation to provide a dropdown menu of choices for data in certain cells, and prevent users from inputting incorrect data.
Hide Rows/Columns
Sometimes the easiest way to focus on the data you want to see is to simply hide columns and/or rows.
Show Columns/Rows
When rows/columns are hidden, you will see little arrows between columns or rows (you may also notice a gap in the alphabet!). Simply click the arrows to show the hidden content.
Explore