Do you have digital activities for your students on Google Sheets™? Have you had trouble with students cheating and finding the answers? Digital activities are great because of the immediate feedback, but there is a drawback sometimes when answers are formatted.
Let me help you! I have done A LOT of research on how to format my Google Sheets™ activities in order to make them cheat proof! In this post I am going to show you tips for Google sheets to help make your digital activities cheat proof.
*Disclaimer: There will never be a way to make your activities fully cheat proof. Students are smart and tech savvy so there will always be a few who might figure it out.
Setting Up Your Activity
Prior to securing your Google Sheets activity to prevent cheating there are a few steps you need to take.
First, open a new spreadsheet in Google Sheets. Create your activity where there will be a question in one cell and in another cell (ie: to the right of it) is where students will type in their answers. Make sure that the answer is one that can be typed in (numbers or letters).
Second, right click on the first answer cell and select “Conditional formatting”. Then, you will create two rules that will keep the cell white if it is blank or if it has “Type answer here” as the text.
To create the first rule, under “Format cells if…” select “Cell is empty”. Then, using the paint bucket symbol below “Formatting style” select the color of the cell to be white.
Next, add another rule. Under “Format cells if…” select “Text is exactly”. Then, type the text that is in the answer cell, such as “Type answer here” and select the color of the cell to be white.
Lastly, repeat this process for all of your answer cells.
How Students Can Cheat
Using regular conditional formatting to set up immediate feedback can be easy for students, especially older ones, to cheat. The basic rules I have used in the past are as follows:
-
If the text in the cell is equal to the answer, then it will change to green.
-
If the text in the cell is NOT equal to the answer, then it will change to red.
.
It was easy to create the conditional formatting rules, which means it is just as easy for your students to locate them. Once they do, it will show them the answer through the green conditional formatting rule.
Conditional Formatting Tips to Prevent Cheating
Step 1: Set Up a Second Sheet
At the bottom of your Google Sheet click the “+”. This will add a new sheet and it will automatically be titled “Sheet 2”.
You can keep the name, but I usually call it something random, such as “purple”, instead of titling it “Answers”. This is so students can’t search for the word “answers” in the document and find it.
Next, scroll all of the way to the right on the sheet and scroll down to around row 400. You do not need to do this, but remember we are trying to prevent cheating. Doing this adds an extra level of deception where if students know how to unhide a sheet, they will not be able to find the answers easily.
Start in cell Y400. In this cell you will put the answer to the first question from sheet 1. *Pro-tip: Type your answers into your activity on sheet 1 to make it faster for you to format. Then, delete the answers after all formatting is done.
In the cells right below Y400, type the remaining answers for your activity.
Step 2: Link Answers
Now we are about to get TRICKY!
Next, go to sheet 1 and scroll down to about row 50. Then, in cell A50 type “=”, go to sheet 2 and click on the cell with the answer to the first problem. Back in sheet 1 click in another cell, either to the right or below A50.
Repeat the process of entering an equal sign and linking it to the answer cell from sheet 2 until all of the answers are linked to your second sheet.
Step 3: Create Custom Conditional Formatting
Now it is time to set up our conditional formatting. Right click on the first answer cell on your activity and select “Conditional formatting”.
In the side toolbar under “Format cells if…” click the dropdown menu and select “Custom formula is”.
First, we will set up a rule to show what happens if a student’s answer is correct.
Under “Format cells if…” select “Custom formula is” from the dropdown menu. Type the following “=$A$50”. This means that the answer is equal to the text in cell A50. If your answer is in a different cell, then input the column number and row number in place of A50. Finally, select the color of the cell to be green.
*Pro-tip: We use the “$” because if we edit the activity and add in rows or columns, it will not mess up the conditional formatting of where your original answer came from.
Second, we will set up a rule to show what happens if a student’s answer is wrong.
Add another rule where you select “Custom formula is” and type in “<>$A$50”. This means that the answer is NOT equal to the text in cell A50. If your answer is in a different cell then input the column number and row number in place of A50. Finally, select the color of the cell to be red.
Repeat this process for all of the answers in your activity.
Step 4: Take It One Step Further
First, scroll down to A50 on sheet 1. This is where you have your answers listed. Then, highlight the text and set the color to white.
We do this because if they click on that cell they will not see the answer. Instead they see where it is linked to. For example, it would say “purple!Y400” because we linked the answers to the second sheet and your student would likely not know what this means.
Second, go to the second sheet where you have the answers listed. Then, highlight the text and change the color to white.
Next, you will set up a protection on sheet 2. Click “data” and then select “Protect sheets and ranges”. A side toolbar will open up.
On the side toolbar click “Sheet” and in the dropdown menu select the title you have for the second sheet (ie: purple). Then, click “Set permissions”.
When you share this with your students by making a copy for each one, it will not keep the sheet protected. Although, it will give them a warning message that should hopefully deter them from editing the sheet.
Lastly, right click on the tab of this sheet’s name and select “Hide sheet”.
Now your activity is cheat proof!
I hope these tips were helpful! Students can be like detectives and sometimes they put more effort into finding the answers than just solving the actual questions on your activity. Using these tips for Google Sheets to prevent cheating will help all of your students to get the practice you intended for them to get! Do you love making your activities cheat proof?! Comment below or tag me on Instagram and let me know!
Do you want to create more engaging resources?! Head to LindsayBowden.com/Training to sign up for my free training on creating engaging teacher resources!
You may also like:
Take a look at The Resource Starter if you are interested in my pre-coded templates.
