A Beginner’s Guide to Google Spreadsheet Formulas in Education

Google Spreadsheet Formulas exist in the cloud!

Guest Post on Google Spreadsheet Formulas from Zapier!

Today I am proud to share a post on Google Spreadsheet Forms from my friends over at Zapier.

They have an incredible product which I use to help me be more efficient in a number of different ways.

One way I use it is to manage my email service and to help deliver great FREE content to you.

You know on the podcast how I say "Text the word 'Phrases' to 33444" to get your free 15 phrases of effective school leaders? You've heard that on the podcast right?​

Without getting to technical, my old email service and my text service used to talk ... they were friends.

Then I switched email providers, but it doesn't talk to my text provider.

The solution?


So that is a techy way I use the service, but they offer so many different solutions like these ...

Do you know Zapier automates Google Spreadsheet Formulas?

The beauty of Zapier is that it connects apps that normally don't "talk" to each other and helps automate workflows. I highly suggest you check them out.​

How to Create Powerful Quizzes for Free in Google Forms

Quizzes are one of the quickest ways to see if your students are remembering the things you teach. Grading those quizzes, though, can take up enough time that you’ll only want to use quizzes when absolutely necessary.

What if you could quickly make a fun—yes, fun—quiz for free, one that automatically grades your students’ answers and saves them to a spreadsheet for you? That’s what makes Google’s new Google Quizzes feature so useful.

Here’s how to use Google Quizzes to quickly create a self-grading quiz—and how to put your quiz answers to work for you in a spreadsheet.

Google Quizzes: The New Tool Hidden Inside Google Forms

Google Docs, the free office suite from Google, is one of the best tools for classrooms since it’s free with a Google account. It includes all the basic features you need for documents, spreadsheets, and presentations, and is simpler to use than Microsoft Office thanks to its simplified menus and toolbars. Add a few add-ons, and it can be just as powerful as Microsoft Office, too.

It even can do things that Microsoft Office can’t, since it’s always connected to the internet. You can use add-ons and Google Sheets integrations to pull in data from the web, send emails from spreadsheets, create template documents automatically, and more.

Google’s even built in an extra tool to make use of its online nature: Google Forms. A free form tool that’s connected to Google Sheets, Google Forms is the perfect tool for building quick forms to gather data that’s saved directly to a spreadsheet.

Google Spreadsheet Formulas in Education Img 1

Google Forms includes all the standard form tools you’d expect, with multiple choice and checkbox questions, text fields to gather text and numbers, and more. You can learn more about each of the form field options—including their hidden features—in Zapier’s Google Forms Guide.

But it’s newest feature is also one of its handiest: Google Quizzes. Just create a new form—either a blank or a template form—then click the gear icon in the top right to open the form’s settings. There, select theQuizzes tab and select Make this form a quiz, and you can start adding answers to your questions.

Google Spreadsheet Formulas in Education img 2

Now, add your questions, and under each multiple choice, checkbox, or dropdown question you’ll see an Answer Key link in the bottom right corner. Click it, then select the correct answer, and optionally assign a point value to the quiz question in the top right corner. If you want, you can even add an explanation to correct or incorrect answers, with a link where students can learn more.

Google Spreadsheet Formulas in Education img 3

With that done, it’s time to test out your quiz. First, double-check the settings, and make sure you’ve enabled the Release Grade Immediately after each submission option, and have checked all the types of questions you included. Then, click the eye icon to preview your quiz, and try answering the questions.

Once you’re finished, view your results, and you can see your total score along with feedback on any missed questions.

Google Spreadsheet Formulas in Education #4

That’s all—you could share this quiz with your students, and they’ll get instant feedback on your quiz.

But what if you want to do more with your quiz answers? Let’s see what you could do with your answers if they’re saved to a spreadsheet.

Review Quiz Results in a Spreadsheet

Google Forms includes a nice form and quiz review page in its Responses tab, where you can see average results along with individual answers. To do more with your answers, though, you’ll want to save them to a spreadsheet.

For that, the built-in Google Sheets integration is perfect. Click the green spreadsheet icon in the right corner of the Responses tab, and either choose a spreadsheet for your results or create a new one.

Google Spreadsheet Formulas in Education #5

Then, you’ll see every answer to your quiz right in the spreadsheet. You could sort them by name or grade, create your own charts for reports, and more. Or, you could use Google Sheets functions to calculate totals or reformat text automatically.

If you’d like to add functions to your new form entries automatically, install the free copyDown add-on in Google Sheets, and add the formats and formulas you want to the top row. copyDown will find that info, and copy it to each new quiz result in your spreadsheet automatically.

Google Spreadsheet Formulas in Education Zapier

Or, perhaps you’d like to get notified about new form entries. Google Forms includes a built-in email notification in its settings, but it only emails you to say that the form or quiz was filled out.

For more detailed notifications, app integration tool Zapier can help. It can connect your Google Form or Sheets spreadsheet to over 600 apps—including Evernote, Gmail, Slack, and much more.

Here are some integrations to get notified about new form entries via email, Slack, or SMS message:

Google Spreadsheet Formulas and Zapier are a great resource

Or, if you’d like to add your quiz results to another app, there are integrations for that, too, to add the results to Trello, MailChimp, Google Calendar, and more:

Google Spreadsheet Formulas Zapier II

Excel may still be the best-known spreadsheet app, but Google Sheets can be a powerful competitor. It’s free, and with add-ons and integrations can be a handy way to gather data from quizzes, create detailed budgets and schedules, and much more.

Spreadsheets can be a bit overwhelming, though … not to mention Google spreadsheet formulas!

To help you get started, be sure to grab a copy of Zapier’s new free eBook, The Ultimate Guide to Google Sheets. It’s everything you need to know about Google Sheets. Starting with beginner steps to sort and format your data, you’ll learn how to both crunch numbers and text in spreadsheets, and turn them into your own customized apps for anything you need.

Download a copy—and see how you can start getting more done in less time with spreadsheets.

Did you enjoy this post on Google Spreadsheet Formulas? If you did, then I would be honored if you left a comment and shared on social media!

Daniel Bauer

I help school leaders create a winning culture, focus on the essential, and lead with courage and integrity.

Follow me on Twitter.

Follow Me on FB​

Connect with me on LinkedIn

Get behind the scenes access on Snapchat