It’s my birthday and everyone knows no one loves a spreadsheet more than I love a spreadsheet! Here are 5 things you can do with a spreadsheet that will save you time!
1) Automatic Numbering
Spreadsheets recognize a pattern. Highlight the pattern and grab the fill square to drag the pattern. The fill square is the small square in the bottom right of the highlighted section.
2) Split Text to Columns
How many times have you had a list of student names where the first and last name is together and you needed them split up? Highlight text and use the Data menu to “Split text to columns.” An option will come up next to the highlighted text to let you choose the separator. Usually this is the space between the first and last name. It might be a comma if you have last, first.
3) Hide the Timestamp
One of the best reason to use Google Forms… the responses can go to a spreadsheet. The timestamp when the Form was submitted is awesome, however, it is also takes up room. On the spreadsheet, hover over the column with the “Timestamp” and locate the tiny arrow in the upper right of the column indicator. Choose the option “Hide column.”
4) Freeze the Student Names
As you scroll to the right on a spreadsheet to review student responses to a Google Form you lose being able to see whose name is on the row. Freeze the column with student names so that when you scroll you always know whose work you’re looking at. I will hide the timestamp and the email address and then freeze the column to lock student name to the left of the spreadsheet. Use the View menu and choose “Freeze” to freeze rows and columns.
Alternatively, you can grab the freeze bar to the left of the Column A indicator. Your cursor changes into a grabby hand that you can drag the freeze bar over.
5) Use an Equals Sign
In any cell, type an equals sign and then click on another cell. This is called cell referencing. Whatever is in that cell… put it in this cell. If you are creating a spreadsheet template for students where they respond in different cells, try creating a sheet that has a list of all the student responses in one spot. No scrolling required!
You can use cell referencing across multiple sheets. On your “Answer sheet” sheet, type an equals sign and then click on the tab at the bottom where the first student response is. Click on the cell where the student would respond. Press Enter.
Want to Try a Spreadsheet?
Check out this spreadsheet I made for my students to practice using a spreadsheet. I have conditional formatting to allow them to create a Minecraft Creeper with pixel art. Then use the formula =COUNT(range) to determine how many cells there are in the graphic. =COUNTIF(range, criteria) indicates how many of each color are used in the graphic.
