Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Spreadsheet Day – 5 Tips for Google Sheets

The answer is always a spreadsheet
Spreadsheet Day – 5 Tips for Google Sheets

Jonathan Rochelle the answer is always a spreadsheet

In honor of spreadsheet day here are a few things to up your spreadsheet skills. I am including a spreadsheet template to try them out.

Template

tinyurl.com/spreadsheetday 

1) Not For Numbers

While spreadsheets are amazing for calculating numbers, you do not need to use any numbers to use spreadsheets. Spreadsheets are perfect for organizing information! Unless you are writing an essay, your default document type should be a spreadsheet.

Try It

On the first tab type in some comments for students in the list.
Type some text

2) Comments

The only thing better than a spreadsheet is a collaborative spreadsheet. Google Sheets allows you to collaborate on a spreadsheet, which means you can also insert comments to facilitate collaboration.

Right click on a cell or use the keyboard shortcut Control Alt M to insert a comment.
Right click to comment

Try It

On the 2nd tab, insert comments to notify the collaborator that they have grammar errors.
Comment in Google Sheets

On the tab is a count of the number of comments on the sheet.
2nd Tab

Note: When you make a copy of a spreadsheet, the comments do not copy. Use insert note instead of insert comment for ideas that need to be contained in copied documents. 

3) Create a Hyperlink

A hyperlink is text that when you click on it, links to another website. If you type a website URL into a cell it will automatically create a hyperlink. However, a web URL tends to be long and does not fit nicely into a cell. Creating linked text can make for a more attractive spreadsheet that more clearly communicates the destination of the hyperlink.

Type text into a cell. Single click on the cell to select the cell. Use the chain link icon in the toolbar or use the keyboard shortcut Control K to create a hyperlink.
Insert link

Google Sheets will attempt to predict the URL you wish to use. The hyperlink dialogue box has 2 boxes. The top box is the text that will appear in the cell. The bottom box is the link address. Paste the destination URL or choose from the drop down of predicted options.
Auto predict in Google Sheets

Try It

On the 3rd tab named “Hyperlink” click on each text cell and hyperlink the text to the corresponding website. Google Sheets should predict the website without you having to look up the web addresses.

Note: While in edit mode of a spreadsheet, hyperlinks require 2 clicks. Clicking on the linked text will display the destination URL either above or below the text. Click on that hyperlink to launch the website. In published view, the hyperlinks work as a single click. 
Linked Text

4) Duplicate Data

One advantage to using a spreadsheet is you can avoid having to update the same information in multiple places. Using cell referencing, when you make one change, other occurrences of that information can be updated.

For teachers, one example of this is a class roster. Oftentimes I need to have a list of student names on more than one sheet. I have a list of student names I type once. Any other instances of students names I will reference the original list.

To reference a cell, click on a blank cell and type an equals sign. Either click on the referenced cell or type the cell address. For example =A5 will put the value of A5 in the new cell.

Try It

On the 4th tab, named “Names again,” use cell referencing to insert the list of names from the first tab. In cell A2 type an equals sign. Go to the “Put in text” tab and click on cell A2. Press enter. If cell A2 is covered, click on a different cell and edit the cell address. cell referencing

In cell A2 of the “Names again” tab, the cell reference should be
=‘Put in text’!A2

Note: You do not have to individually reference each cell. Spreadsheets pick up patterns. Click on cell A2 and notice the fill down square in the bottom right-hand corner. Click and hold down on the square. Pull DOWN to continue the pattern down the sheet. Pull to the RIGHT to continue the pattern to the right. 
Fill down square

5) Named Ranges

For those of you who are already spreadsheet ninjas this tip is for you. Jonathan Rochelle (@jrochelle) taught me this trick to name ranges. Naming ranges allows you to refer to a range of cells by a name in a spreadsheet rather than the range address.

For example instead of =vlookup(A2,$D$5:F:15,3)
Name the range D5:F15 as “Table.”
The new formula becomes =vlookup(A2,Table,3)

To name a range, highlight the range and right click. Choose “Define named range…” Choose a name for the range. When writing formulas type the named range in place of the range address. No quotation marks needed.
named range

Try It

On the 5th tab, named “Named Range,” highlight the table of data. Right click and choose “Define named range…” Choose a name. Double click on cell B2 to edit the formula. Replace the range with the named range.

 

1 thought on “Spreadsheet Day – 5 Tips for Google Sheets

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 21st or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session as we learn how to revolutionize your assessment strategies. We will dive into the essentials of crafting high-quality rubrics that go beyond traditional scoring methods to offer rich, meaningful feedback. We’ll learn about the components of an effective rubric and how to mathematically ensure the accuracy of your assessments.

Exit this pop up by pressing escape or clicking anywhere off the pop up.