Category: Self-Checking

Digital Escape Rooms, formative assessment, Geometry, Google Sheets, Self-Checking

Self-Checking Magic Squares Sheet

Mandi

This tutorial is combining two ideas together that I have previously shared. This first is a Magic Squares sheet by Jason Pullano, and the other was a recent post about self-checking sheet. This activity is great for a self-checking activity or as a clue in a digital escape room.

Many of the steps are the same, so I am just reposting them below so you don’t have to flip screens.

Create your Questions

I find it’s easier to have my questions and images (if needed) ready to go before I begin building the spreadsheet.

I’m using this for a Geometry review so I’ve created my images in advance in a Google Slide and I changed the page size to the standard 4:3. I can download each card as a PNG or JPG image to use in my self-checking activity.

Create the image

This activity loads an image into squares one at a time as a correct answer is entered. You will need to decide how many squares you want. I went with a 4×4 grid.

I used Google Drawing. In page set-up I set the size to 10 x 10.

Once your image is designed, place a table on top of it and drag it to fill the screen. I learned this trick from Jason Pullano. The table creates a perfect grid and you don’t have to worry about placement of the lines.

Now you crop your image. There is more than one way to do this but I chose to use my snipping tool and just snip each section. I found it helpful to zoom in to get a good snip.

Open Sheets

Now we will put this all together.

I start with my blocks of information. I merged cells to create the sections for instructions, question images, and also to create my grid. By selecting columns G-J and right clicking, I can resize my columns to 99. Do the same for rows 2-3 and resize to 33. This should give us squares.

I just typed in my header this time, but you could always create a colorful header like we did the last time. If you create the header image, insert instructions are below.

Place images and answer in a new tab.

Click the plus button in the bottom left corner of your spreadsheet. This will create a new tab in Sheets. This will be the location for out images and answers.

Place the images (insert image) for the questions and for the square reveal. They can be VERY TINY. It’s ok. It will scale to fit the size we allow it.

We also want to place the answers to our questions here. We are going to hide the tab later so students don’t see it.

Dropdown menu

Start with the dropdown menu where you have the word instructions. Place the same words, Choose a Question, Question 1, etc. in your list. You can view a tutorial here.

Now right below that, you will enter a vlookup code.

=vlookup(A4,Sheet2!A1:B13,2,0)

This is telling sheets to see what word is selected in A4 (Choose a question, Question 1, etc., then go to sheet 2 and select the image that matches my words that are in column A starting at A1 and my images are in column B ending at B13.

Answers

Now let’s set the conditional formatting for our answers. There is a tutorial for conditional formatting in the link above as well.

I’m going to have three rules for each cell. If the cell is empty, I want it to be transparent. If the cell has an entry it will be gold for correct or red for incorrect.

It’s a little tricky to get conditional formatting from another sheet.

Here is what you would type in:

We will do the same thing to get a wrong answer but select text does not contain and type in the same formula and change the color to red.

Repeat for the remaining answers.

*This seems like a lot of work, but this process allows you to use this template again simply by changing the images and answers in Sheet 2.

Load Image

Now we need to load an image square. When an answer is correct, we want a square to load in an image space.

Below the fun stuff in Sheet 1, I have the sheet respond with 1 if an answer is correct and 0 if it’s incorrect using the following formula:

Once you return, there will be a little blue box in bottom right corner. You can drag that box down 10 spaces and it will auto fill the formula for you.

Choose a square you want to load. For this example, if the answer is correct it will go to sheet 2 and load the image in D1. You just need to make sure you load them in random order and that you include each one.

I have 16 boxes and only 12 questions so a few of my questions load to squares.


Clean it up and Assign

I like to hide the parts of the sheet we don’t need.

I’m going to highlight columns K-Z and right click and hide those cells

You can also hide the rows that have your adding trick on them by doing the same thing.

I’m also going to hide the sheet with my answers on it. Now a spreadsheet savvy student will now how to unhide this so you can always password protect the sheet so they can’t access it.

Don’t forget to set it to make a copy for every student in your LMS.

Here is the final result with all of the answers filled in:

These are so much fun. You can even get more advanced and have one image load and it changes to another image as you get the answer correct.

You can always just use the created spreadsheet below as a template if you don’t want to make your own. Just switch out the images and answers on Sheet 2.

Spreadsheet

If you make one, please let me know. I love to make these and my students love to complete these.

Geometry, Google Sheets, Make Math Not Suck, Self-Checking

Self-Checking Pixel Art Tutorial

Mandi

*Updated 9/2021*

Self-Checking activities are very common in my classroom. I have used them for remediation days, for review days, and as a check your understanding right after a lesson. I shared the Mystery Picture idea from Jason Pullano that can also be used as a self-check activity. Now, I want to take the Pixel Art idea shared by Alice Keeler and turn it into a self-checking activity.

You will want to have your questions and answers prepared and have an idea of an image to use. You can go as in depth with your image as you wish. I prefer to keep it simple and build upon a background image. For this tutorial, I’m going to use a simple snowman on a light blue background. You can do a full image like these examples if you are ambitious.

Prepare your Google Sheet by making your cells into squares. We will merge cells together to write our questions so no worries. Make the squares as small as you need them for your image. The left side is where my questions will go. The right side is where the image will appear.

Now put your questions into the Sheet. You will merge cells to allow room for the question and NOT distort the grid for your image. I put my answers in also. You can delete them once the activity is complete.

merge button

Now we will start our design. I am NOT a Google Sheets expert. There may be an easier way to do this but this is how I do it. I use 0 and 1. 0 when the answer is blank or incorrect and 1 when it is correct. You can change the color of the numbers so you don’t see them. I will start with my first question and my first black block.

*Update*

You can also add a sheet 2 in the bottom left corner & type your answers into Sheet 2 to prevent students from seeing the answers. So let’s say this answer is in cell B2 in Sheet 2. I would type

=if(D3=Sheet2!B2, “1”, “0”)

*end update*

D3 is the cell with my answer and 125.5 is the correct answer. “1” will return a 1 if correct and “0” if incorrect.

Now copy and paste this formula in every other box that should be black. I will set the color in the next step.

Now let’s change the color of these 1s. Click on ALL the 1s you have on the screen (hold down the command key on a Mac -IDK for Windows – sorry). Go to Format – Conditional Formatting.

Change the Format cells if… dropdown to “text is exactly” and then type 1. Pick your color and also change your font to that color.

Repeat steps 4 and 5 until you have all colors on your screen.

Now it’s time to delete your answers in the boxes. When you do this, your 0s should appear. Once you see all of you zeros, change the text color to white (or blue in my case since my background its blue.)

Your activity is ready to be sent to your students! Don’t forget to set it to “make a copy for each student”.

Here is the link to my completed activity if you want to use it or investigate.

Enjoy! If you make one of these, let me know how it goes!!!