My students love mystery pictures and I love them because they are so easy to grade. Ever since I started digitizing my games and activities I’ve wanted to create a digital mystery picture activity, but couldn’t figure out the best way to go about it. This past week I learned how and I want to share it with you. The secret is conditional formatting. I’ll give you the step-by-step process here, but if you prefer a video version there’s one below.
1. Create your picture in Google Sheets (or Excel if you prefer). Get it exactly how you want it to look once the students have correctly completed the activity.
2. Go over several rows from your picture and start typing your questions. In this case I had sentences that I wanted students to complete, but it could be anything from a math problem to a factual questions. You just need to have a defined answer that everyone will type the same. Place one question per row.
3. In front of your questions color the box where you want students to type their answer. Students must type the correct answer, in the correct box, for the picture to “magically” appear.
4. Now you’re ready to start conditionally formatting your picture. Go over to the picture and randomly select however many cells you want to tie to the first answer. Be sure all of the cells are the same color!
5. In order to help myself remember which cells I’d already done I first changed the selected cells to white.
6. Click on Format, Conditional Formatting.
7. Click “Add Another Rule”
8. In the Format rules box click the down triangle and choose “Custom formula is.”
9. In the box enter the formula: =$Y$3=”Who’s” The Y is the letter of the column where the answer will be typed. The 3 is the number of the row where the answer will be typed. Inside the quotation marks is the text for the correct answer. If your answer is numerical it does not require quotation marks.
10. Choose the color you want the cells to turn.
11. Click done.
12. Repeat steps 4-11 for each of the questions you’ve created, being sure to conditionally format all of the cells in your picture.
You can test your work by going through and typing the answers in the boxes and watching the picture appear. When incorrect answers or typed, or correct answers are typed incorrectly, nothing will happen and the boxes will stay white (or whatever color you set them to be).
The first time I created a mystery picture I fell into the trap of recreating digitally what I had on paper. I made every cell correspond to an answer (80 questions for an 80 cell picture) and every answer correspond to a color (all of the “am” answers were blue). The great thing about digital is that you are freed from these restrictions. You can make every answer correspond to as many or as few cells as you choose (just remember they all have to be the same color). You can also make any answer be any color, even if it wasn’t that color previously (hence my activity has two possible answers but three different colors). The digital format opens up a lot of possibilities!
My students already enjoy mystery color pictures and I can’t wait to see their response to the digital version! Want my mystery picture activities? Use the picture links and buttons above!
The Gaming Grammarian 