06-19 Tips and Tricks for Google Forms for Summer Reading Tracking

Tips and Tricks for Google Forms for
Summer Reading Tracking
May 2019


Libraries track summer reading in so many ways, that we can't give instructions to each and every format here. If you need help setting up something that isn't addressed here, email marcia.literati@tenafly.bccls.org. We will follow up with a Tech Talk in early August to help you tally the data you've collected.

Data Collection

You can use google forms to simply track the number of people signing up for summer reading. The form responses will generate a spreadsheet which you can use to track data. Here are two examples: 

Once the names have been added to the spreadsheet you can simply add columns to the spreadsheet to “hand enter” data.  Alternately you can use a google form to enter data and then automatically total data categories.

In either case,

  1. Think about what data you will need at the end of the program. Your end-of-summer data analysis needs should dictate how you set up your google form.

  2. Collect the fewest number of data categories as possible. For example, if you only really need to know the total number of children, teens and adults that participate, create one multiple-choice question with 3 answers only and don't ask other questions such as school or grade.

    Do you need someone's phone number and email address or can you simply enter a library barcode number?  Do you really need any of this information?

    Example:  Use multiple choice question - Age group
    ◯ Children (ages 0 - 12)
    ◯ Teen (ages 13-18)
    ◯ Adult (over 18)

  1. Try to limit the number of fields in which you enter free form text. Instead use multiple choice questions whenever possible.

    1. For example, if you require a name entry, google will recognize MARCIA, marcia, Marcia as different values.

    2. From the standpoint of data collection, assigning a unique number to each participant limits possible data entry errors (of course you then need a way to keep track of the numbers you've assigned.)

    3. A library card number (if all readers have one) might be a good option. This also gives you the advantage of being able to scan the barcode which creates a smaller number of data entry errors than typing.

    4. If you require the entry to be a number and use response validation, you can limit some errors (see question 9 in sample google form).

      1. For example, if you track minutes but count everything under 15 minutes as 15 minutes, use “number”, “greater than or equal to” 15 to validate your data.


Data Analysis (use the spreadsheet generated by your google form)


You can manually enter additional data into the spreadsheet you originally generated when participants signed up.

For example, you can manually add a column for “# books read” and you simply enter the total at the end of the program. Or add a column for each week of the program and enter books read as reported by the participants, in each column.

Or you can use functions to automate the data analysis.

simple sum or total:  


Sum of values with a specific criteria (example: the total of all books read by grade)

=sumif(A1:A10, “Kindergarten”, B1:B10)

Sum of values with 2 specific criteria (example: the total of all books read by grade for each school)

=sumifs(E2:E23, C2:C23,"Kindergarten", D2:D23, "Mackay")

When you copy/paste formulas into new cells, google figures that you want the range of values as shown by A1:A10 to change. For example, if A1:A10 total is in cell B11 and I then copy the formula to cell B12, google will think it should total up the sum from cells A2:A11.There are times when you don't want the values to change.

If you want the values to be the same no matter where you paste the information use

A$1:A$10 to keep the rows from changing when you copy to another cell

Count all values:

=CountA (A1:A10)

List of unique values:

=Unique (A1:A10)

Use Query to find totals by unique values.

=query(A1:B22 , "select A, sum (B) group by A")


Article ID: 1978
Created On: Mon, May 13, 2019 at 8:51 AM
Last Updated On: Mon, May 13, 2019 at 12:08 PM

Online URL: http://bccls.knowledgebase.co/article.php?id=1978