5 Steps to Create Self-Checking Activities with Google Sheets

Formative Assessment is an Important Part of Learning

I love that education has taken a turn toward standards-based learning! In my early years, everything was grade averages and completion. Nowadays, there’s a greater push toward mastery.  In a perfect world, emphasis is on the learning process, and grades are only assigned once learning has  been completed. Since we don’t need to take a grade on activities that happen early on the learning path, we can use lots of self-checking activities. Ongoing formative feedback is what pushes students along the learning path, and it isn’t until they reach their destination that we must assess them and assign a grade that represents the level of mastery.  Simply said, we don’t have to grade until mastery has occurred. 

Here's a How-To video that will walk you through creating your own self-checking Google Sheets.

When you think about grades this way, it makes a lot of sense.  If you follow me on social media, you know my daughter is passionate about musical theater.  When she has an audition coming up, she practices her singing, makes characterization decisions, and spends time blocking the perfect movements.  She records herself on her camera and watches it back to see if something just isn’t right. The final assessment of her skills comes at the very end when she performs for the directors.  Not a single person will see her first attempt.  They won’t even see her second-to-last attempt.  It’s the final attempt that matters.  She has put in the work, has made adjustments along the way, and none of her earlier failures makes one bit of difference.  Ultimately, only her final “grade” matters.

When we treat student learning in this way, we make students responsible for learning how to learn.  We teach them to see when something isn’t quite there, and we give them tools for improvement.  Maybe we assign peer mentors or create clarifying videos our students can watch. We make the rounds, checking up on students while they’re learning.  And we can even create and modify assignments so that the students have immediate feedback. This ongoing formative assessment is what inches students along the learning path and leads them to mastery.

The week’s blog offers one way you can create assignments that give students instant feedback.  It assures them that their work was correct or prompts them to try again or seek help.  In only a few simple steps, you will have a SELF-CHECKING assignment that you can share with your students.

 

Check out the tutorial video where I walk you through the steps and give you some helpful hints and tips!

Self-Checking Google Sheets Activity
Self-Checking Google Sheets Activity

Step 1: Open Google Sheets

Google is amazing because it’s FREE, easily integrates with Google Classroom and other platforms, and allows you to distribute a brand-new copy with a single link.  Just go to sheets.google.com and click the plus sign to create a new file.

Step 2. Write out your problems

Self-Checking Google Sheets Activity

Now just write out your questions.  You can change the fonts, or drag the columns and rows to different sizes, or you can go super basic.  Make sure you leave the first row blank so you can use it to label your columns .

Format your questions so that they have only 1 correct answer. Your students will have to get the exact answer, so sentences and long answer formats won’t work.  Short answer, fill-in-the-blank, multiple choice, numeric, or matching are the best types of answers.

Step 3: Decide how you want to give feedback

Self-Checking Google Sheets Activity

The easiest feedback would be right/wrong.  You can show if a student has answered correctly by creating a color code.  Once they answer the question, if they’re right, the answer can change colors. For this activity, if the student answers correctly, the cell will turn green. Alternatively, you could make it turn red if they’re wrong.  But I’d rather see that I’m right than that I’m wrong.

If you want to go a step further, you can give feedback to students when they are almost right.  I use this when very common mistakes are likely.  Be on the look out for my future post where I’ll show you how to do this!

Step 4: Do a little coding. Don’t worry, it’s easy!

Self-Checking Google Sheets Activity

This is the most difficult part, but once you do it once or twice, you’ll see that it is pretty easy.

Start by clicking Format, then Conditional Formatting.  You’ll see the coding area pop up on the right.  You really just need to think of it in three parts.

1.Tell the computer where to look.

  • In the “Apply to Range” box, make sure the cell you’re checking is entered.

2.Tell the computer what to look for.

  • In the “Format Rules” section, choose “Is equal to” in the first box. In the box directly underneath, type the correct answer for the question.

3.Tell the computer what to do with the info.

  • In the “Formatting Style” section, change the fill color to the color you want it to change to if the student is correct. I chose green.

Repeat this process until you have coded all of your answers. Test out each question to make sure it’s working before you share it with your students.

Step 5: Assign the activity

Self-Checking Google Sheets Activity

Assigning Google Sheets activities is easy!  If you use Google Classroom, you can do it straight from there. But you can also just create a link that forces your students to make their own copy of the assignment.  In the top right corner, look for the button that says “Share”.  Then click “Get Sharable Link.”   Copy the link and paste it into a new tab.  You’ll notice that the URL ends with a slash followed by the word edit, and some stuff.  Starting with the “e” in edit, delete everything to the end of the url.  Add the word “copy” after the slash.  Now you have your new URL that can be shared with your students.  If they click it or type it, it will force them to make a copy of the file so they don’t mess up your original.

Self-Checking Google Sheets Activity

It’s a really long URL, so use a URL shortener, such as bit.ly to create a short version that you can write on the board. Then students can just type it in their address bar.

 Need help? At the top of the post is a video where I walk you through all the steps!

Making Self-Checking Resources

If you’re like me, then you love creating student resources.  If you have time, be creative and think about ways you could use this technique to make some pretty cool self-checking activities for your students. 

Here’s one I made!  Instead of having the cell turn green when the answer is correct, I had a group of other cells turn other colors.  After all the questions are solved correctly, the entire picture will show. Cool, huh! If you like this type of activity, then I challenge you to make your own version.  It’s built on the same concept as the steps above, and it’s super fun to see it come together.  

If you don’t want to spend the time making it, you can pick it up in my TpT store for just a couple of bucks.  Just click the links to check them out!

Cool Self-Checking Google Sheets!

Watch this video! I'll walk you through creating your own self-checking activity!

  • Join Minds in Bloom UNLIMITED!
  • FREE PLANNER!

    FREE PLANNER!
  • Reading Practice for the Whole Year

    Close Reading Comprehension Practice
  • Make Learning Fun!

    Using Task Cards in the Classroom
  • You might also like these posts!

    Cookie Consent Banner by Real Cookie Banner