You’ve used Google Forms to quickly collect data. The automatic summaries and charts are great, but what’s next? How can you see beyond simple averages and totals to make better decisions and gain true insights? In this session we will learn more about some powerful features in Forms and Sheets like data validation, data filters, and pivot tables. We’ll also take a look at some useful add-ons that will allow you to do some amazing things for any classroom. Read more if you’d like to learn more, or follow along if you are in my session today!
Let’s take a quick look at creating a Form. If you have not done it in a while, there are some new features:
- Forms are getting smarter! As you enter in questions, the form will try to guess what responses you’d like to have. Try some examples from our sample data form below
- Under the ‘Settings’ gear icon, you have options
- Want anyone to respond? UNCHECK Restrict to (your domain) users
- Want to ensure who responded? CHECK Collect email address. This will limit your form responses to your district. You may also want to always add First and Last name questions – these will not be auto-collected.
- Quizzes – you can now create a self-graded quiz with Forms!
- Data Validation – very handy with quizzes or anytime you want to make sure that you are collecting the type of information that you need
- To validate collected data, click the ‘Snowman’ menu on the question (lower right) and choose ‘Response validation’ and explore the options there. I’ll show validating an email address.
- The paint palette will give you access to color and theme options
- Access a wide array of themes by clicking the ‘Mountain image’ icon on the lower right
- To send off your form, or to get a URL for the form, click the ‘SEND’ button. LOTS of ways to share your form there
First off, let’s gather some data to work with: Link to my Google Form
Want to re-use a Form but not add to the current data set? Unlink the form responses and create a new response destination – look for the ‘Snowman’ menu in the ‘Responses’ tab
Have you ever tried to create and use a Pivot Table before? Let’s practice.
- Select the data/cells you want to analyze. Want them all? Click the ’empty’ box above row 1, left of column A.
- Let’s figure out how many elementary, middle, high and college teachers we have
- Select all data, and click Data –> Pivot Table… We’ll be working with the ‘Report Editor’ on the right
- Rows –> Add field –> What grade level…
- Values –> Add field –> What grade level…, Summarize by –> COUNTA (this will count, not sum, any cell with an entry)
- Want to get rid of the zeros? Filter –> Add field –> What grade level… Show –> uncheck ‘(Blanks)’ and click OK.
- Take a minute to experiment – can you separate out how many male and female teachers we have at each level? Need a hint? Try adding the gender question as a column.
- Now, let’s figure out how many t-shirts we need, in male and female sizes
- Go back to the original sheet. Select all data, and click Data –> Pivot Table… This will start a new pivot table.
- Rows –> Add field –> What t-shirt size…
- Values –> Add field –> What t-shirt size…, Summarize by –> COUNTA
- Filter –> Add field –> What t-shirt size…, Show –> uncheck ‘(Blanks)’ and click OK.
- Now, to figure out male and female sizes, add Columns –> Add field –> Gender
- Keep adding fields in rows or columns to further slice and dice your information to gain new insights
- Not looking right? You can drag and drop items from rows to columns and vice versa
- What other questions could we answer with this data? Can you tell me:
- How many teachers were born in July?
- How many male middle school teachers we have?
- How many elementary teachers are having a freaking awesome time at URSA?
- What else?
- Now, let’s make a chart of one of your best pivot table findings
- Select the data you want to graph
- Insert –> Chart. You can experiment with the types of charts with the Chart editor on the right.
- Now, change some of the data in your spreadsheet. Your pivot tables and charts remain ‘live’ and will update as your data changes!
- Once you have a chart made, click the ‘Snowman’ icon on the upper right. What choices are there?
- If you copy the chart and paste it into a Google Doc or Slide show, that chart will remain live and will show any new/changed data!!!
- You can delete the chart you made in Sheets and it will remain active in your Doc or Slide show
What if you don’t want to create a Sheet or Form from scratch? Investigate the template options available.
- From Drive, click New –> Google Sheets (or Forms), then hover over the ‘>’ icon and choose ‘From a template’
- Take a minute to explore the options there
What else? Let’s explore some Sheets and Forms Add-Ons
- Open a Sheet, and in the menu bar click Add-ons –> Get Add-ons
- In a Form, add-ons accessed from snowman menu upper right
- Choice Eliminator lets you limit the number of responses for each question, e.g. party planning
- Simple Gmail Merge for doing mail merges that you used to need Word and Excel for! Yet Another Mail Merge is also a good option
- g(Math) for Forms allows hand-drawn form responses. There are also g(Math) add-ons for Docs and Sheets (links on bottom of the page)
- QR Code Generator add on for Forms – MAKE ID CARDS!!!
- Random Generator – quickly create as many student passwords or other random numbers as you need
- Some other recommended add-ons to investigate are on the right
Time to explore, experiment, and ask questions!