Using a Google Sheets spreadsheet for students to record their responses has many benefits! One is the ability to chunk up the work over multiple tabs. Another is the ability to “persevere in learning.” Students can keep working on the same spreadsheet workbook over time, building on previous answers. While it is awesome to have students do their work across multiple tabs, what is not awesome is trying to locate all of their responses in the workbook. To make this easy make a student answer sheet in Google Sheets.
A Google Sheets spreadsheet is a document. If you can do it in a Google Text Document you can probably also do it in a Google Sheets spreadsheet. I like to say “The Answer is Always a Spreadsheet.” Incorporating spreadsheets into your class provides students with a valuable life skill. Spreadsheets are not scary if you use them! Get students using spreadsheets.
Google Sheets Answer Sheet
I am going to show you how to make a student answer sheet in Google Sheets.
- Google Sheets Answer Sheet
- Make Use of Tabs
- Answer Boxes
- Sample Spreadsheet
- Response Sheet
- Equals Sign
- Hide the Answer Responses
- View Hidden Sheet
Make Use of Tabs
One of many things that is awesome about a spreadsheet is that you can add multiple sheets. This allows you to chunk up the assignment into parts. Students complete the questions or activity on the first sheet and then go onto the next sheet. This in and of itself is a great life skill. Simply feeling comfortable to look for multiple sheets in a spreadsheet will help everyone!
Create Answer Boxes
Create multiple answer boxes in your spreadsheet. I like to use the paint can to fill the cells in yellow to indicate to the students where to put the answer.
Download a Sample Spreadsheet
I got a little fancy with this spreadsheet, but notice how there are multiple sheets along the bottom. Students start with the first sheet and put their name in the yellow box. Then move onto the next sheet to do the next activity.
Create a Student Answer Sheet
Add a sheet to the spreadsheet and name it “Responses” (or whatever you want.) The idea is all of those yellow answer boxes all come to a single answer sheet. This helps you to avoid having to click on every tab in the student spreadsheet to see if they did the activity.
Use the Equals Sign
The formula you need to create a responses sheet is the EQUALS SIGN. That is it!! On the responses tab type an equals sign into a cell. Then click on the tab that has a yellow answer box and click on where the student is expected to insert a response. Press Enter. Repeat this for every answer box in the spreadsheet. When students type into the yellow boxes their answer will populate on the responses answer sheet.
Hide the Answer Responses
Tip: HIDE this answer sheet. You want students to put their answers on the individual tabs, not type them onto the answer sheet. Click on the tiny triangle on the tab and choose “Hide sheet.”
View Hidden Sheet
When reviewing a spreadsheet that a student has completed, go to the View menu and select “Hidden sheets.” You can then reveal the responses sheet to find all the responses in one place! No having to go tab to tab.