Sharing Classroom Data with Sheets

There are some really interesting ways that classroom teachers can use Google Forms, Sheets, Slides, and Sites to share information.

The best way to explain what I mean is to show you an example.

I created a classroom reading log to track the number of books that my class read.

Students fill out a Google Form each time they finish a book and that information is automatically published on our class website.

Because forms, sheets, and sites are all connected, the class chart is immediately updated each time the form is submitted. Like magic!

There are a few things going on behind the scenes that makes this system possible.

Using a form ensures the consistency of the data and makes it very easy for students to fill out.

You can easily share your form in Google Classroom for easy access.

Here's an example of my reading log form.

Need some help? Take my free introduction to Google Forms.

Step 2: Collect & Calculate in Google Sheets

Each time the form is submitted, the data is logged automatically into a Google Sheet.

That's great, but we need to perform a few simple calculations in order to track our class goal of reading 100 books and 10,000 pages.

If you view the spreadsheet you will notice that for every form entry I add "1" to the "Book #" column. This allows me to add up all of the submissions to determine the total number of books that we have read.

In order to make sure that my calculation is added for each new form submission I am using the Google sheet add-on "copy down" to copy my formula to all new submissions.

If you look at the "class data" sheet you will see where I have entered target goal of 100 books and 10,000 pages as well as the current progress (added from the form responses) and our % of completion.

All of these calculations were created using simple spreadsheet functions.

Here is what the sheet looks like.

Need some help? Watch this video on adding simple calculations into Forms

[Do You Teach With a Paper-Based Mindset?]

Step 3: Create your Charts

Now that I have collected the data and calculated my total, I can create graph.

The graph was created from the "class data" sheet that summarizes all of the submissions. I only have two data points (books read and pages read), so this is a pretty easy chart to create.

The chart editor in Google Sheets will walk you through the process. Just highlight your data, click on insert > Chart and edit as needed.

For my book log I chose the "gauge" chart type. You could also chose a line graph, pie chart, etc.

Need some help? Watch this video on creating custom charts with Sheets!

Once you have created your chart you can share it in a variety of ways:

Because each of these charts is connected back to the spreadsheet, as new form submissions come in, the chart will automatically update!

Need some help? Watch this video on publishing charts.

Other Ideas:

Class reading logs aren't the only way that you could use this idea in your classroom or at your school. Here are a few other ways that displaying live data might be helpful:

Track student absences
Track plant growth (or any lab data)
Track overall student performance
Track physical fitness (health class)
Track fundraising goals
Track team stats (turnovers, rebounds, assists, etc)

Interested in setting this up for your school? I did all of the hard work for you! Click here to make a copy of everything you need!Note: due to limitations with the new Google Sites, you won't be able to make a copy of my site. Setting up your own will only take a few minutes.

Want to become a Forms Master?

I offer a 5-week online course called "Google Forms for Educators." This is one of the activities that I help you create during the course. If you are interested in more tips on using Forms and Sheets in the classroom head over to the course page and add your name to the wait list!

cross posted at electriceducator.blogspot.com

John Sowash creates useful resources for educators on his blog, The Electric Educator. John is the author of The Chromebook Classroom and founder of the Google Certification Academy. You can connect with John on Twitter (@jrsowash) and Instagram.