Slice and Dice Data Like a Ninja: Advanced Google Forms and Sheets

Permalink: sedck12.org/sheetsninja (updated February 28, 2024)

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. Then, we’ll tackle some tasks that you don’t do all the time, but can save you a TON of time when needed. 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!

Creating a Form

Let’s take a quick look at creating a Form. Pro Tip: just start typing forms.new in your Chrome address bar – works for docs.new, slides.new and sheets.new as well!

If you have not made a Form 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 tab, you have options
  • Make this a quiz – you can now create a self-graded quiz with Forms!
  • Responses
    • 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.
  • Presentation
  • Defaults
• 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.
  • Want to make sure you get a phone number in the same format from all who fill out the form? Use: Regular Expression matches ^[2-9]\d{2}-\d{3}-\d{4}$ to get a number in xxx-xxx-xxxx (don’t ask me why!), making sure to add that format in the question description as a hint. Some other options are here.
• The paint palette will give you access to color and theme options
  • Access a wide array of themes by clicking the ‘Choose image’ button under the Header section
• 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

Let’s Get Some Data to Experiment With

First off, let’s gather some data to work with the following:

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 (3 vertical dots) menu in the ‘Responses’ tab

Once we have gathered our sample data, make a copy for yourself to experiment with. How did I do that???

Make a copy Google Sheet of our data

Making & Working with Pivot Tables

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 Insert –> Pivot Table… We’ll be working with the ‘Pivot Table Editor’ on the right
  • Rows –> Add –> What grade level…
  • Values –> Add –> 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 –> 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 Insert –> Pivot Table… This will start a new pivot table.
  • Rows –> Add –> What t-shirt size…
  • Values –> Add –> What t-shirt size…, Summarize by –> COUNTA
  • Filter –> Add –> What t-shirt size…, Show –> uncheck ‘(Blanks)’ and click OK.
  • Now, to figure out male and female sizes, add Columns –> Add –> 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

Seldom Used but VERY Handy Tasks

Have you ever had First and Last names in one column and needed them separated, or vice-versa? If your sheet has a lot of rows, it can get very time consuming to separate them by hand.

Concatenate & Split Equations to the rescue!

Combining Names/Columns of Info

1. If needed, create a new column to contain your combined cells/columns. Add a data header in the top cell of the column if using.
2. In the top open cell, type =CONCATENATE( and use the helpful prompts to choose the two cells you’d like to combine, as well as any text or spaces to include between them.
   1. If I have first names in column B and last names in C, starting on row 2 I’ll add =CONCATENATE(B2,C2) as long as I don’t want/need a space between the values. If a space is needed, then the equation will look like this: =CONCATENATE(B2,” “,C2) with a space between two double quote marks – don’t forget to separate the new space with an additional comma in the equation! 
   2. As long as the output looks correct, you can then just fill the cell contents down as many rows need to be combined.

Separating First & Last Names to New Cells

1. If needed, create new columns to contain your separated cell contents. Add a data header in the top cell of each new column if using.
2. In the top left open cell, type =SPLIT( and use the helpful prompts to complete the equation, as well as any text or spaces to include between them.
   1. If I have combined first and last names in Column B, then I’d create an empty column C and D (or more if you have a middle name in there!).
   2. In the top cell in column C, type =SPLIT(B2,” “) – B2 is the source cell, and since the names are separated by a space, you’d have a blank space surrounded by quotes, as the 2nd value in the equation to tell Sheets that this is the separator in your combined information. 
   3. If, for example, your data was in the Last, First format, then your equation would look like this: =SPLIT(A2,“, “) and the last name would be filled in column C and the first name into D. 

Work Smarter, Not Harder – Sheets Templates

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

Here’s a practical example. This is a blank template of our new budget tracker (opens in a new tab) (the link will prompt you to make a copy for your own) that took me a solid month to perfect. It’s specific to our needs in the PD department at SEDC. Our Media Specialist and myself work from the same budget, and have had issues in the past tracking our combined spending. Here’s what it does:

• Monthly credit card statement – we call it our monthly purchasing logs, and there is one sheet per person per month. The main purpose here is to provide a statement that we can submit to our office manager, and also to code our credit card purchases to the budgets that they need to be recorded into.
• Summary Sheet – this is the “Super Sheet!” Each of us have a summary area that pulls our monthly purchases, adds the purchases with the same budget codes together, and shows a total for the month, as well as a running total for the year. It also has a combined spending area which also includes fields to add in our total budget for each area, subtracts what we’ve spent, and even provides totals of what is remaining in the budget, complete with some conditional formatting. As long as it’s green, we still have plenty of funds. As it gets more red, the closer to spending down that area we are.
• Budget codes sheet – mostly there for reference while we are submitting our monthly purchasing logs.

What else? Let’s explore some Sheets and Forms Add-Ons

• Open a Sheet, and in the menu bar click Extensions –> Add-ons –> Get Add-ons
• In a Form, add-ons accessed from snowman menu upper right
• Yet Another Mail Merge for doing mail merges that you used to need Word and Excel for! Form Mule works great too.
• Add Reminders will help you save time by automating a time-consuming process: sending reminders to your contacts and to yourself. Set up notifications to be sent months, weeks, days or hours before / after each deadline.  Choose who to notify for each task by adding email addresses in a designated column, separating addresses with a comma. Your contacts will get the notifications according to the settings you chose as well as a direct access to the spreadsheet.
• 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!

Credits

A BIG THANKS to Mark Hammons (@mhammons) – I learned most of this from his session at the AZ Google Apps for Education Summit in March 2017!