Pivot tables allow you to collapse data together to make sense of it. For example, you have students or teachers fill out a survey on Google Forms.  You then have a lot of entries of data that you then have to sort through.

Going to Form->show summary of responses in the spreadsheet is really helpful but does not always show the data like you want it.

Take students reading logs for example.  Typically these are on a piece of paper... which is fine, but the problem is that that log is in the hands of the student or the teacher but not usually both.  Having quick and easy access to data so you can analyze it and make decisions on how to motivate students is critical.

Have students fill out their reading log on a Google Form.

Click Here for a sample reading log form.

One thing that is very important for your forms is that you have a piece of data that is the SAME for what you want to analyze.  The problem with asking for student names is that they do not always enter it the same way or spell it correctly.  For this reason I always ask for student ID number.

If you want to give students feedback (I like to use a mail merge) you will want to ask for their email address.

You now have a spreadsheet with how many pages the students are reading... now how do you make sense of that....

Step 1: Highlight the columns (sorry no clicking on the corner)
Click Here for the sample data

Step 2: Go to the data menu and choose Pivot Table Report

 Step 3: A new sheet is automatically created, but it is blank.  Over on the right hand side is the report editor.

Step 4: You can compare data 2 ways.  Rows and Columns.  Many times I just do rows or columns. For this example I will just do rows.  Click on “Add field” next to rows.

It is important that you choose a field (column header) that has data that is repeated. You want to merge together the information for each student.

For this example I asked each student to have an animal they are assigned to, the purpose of this is that I want to publicly share the results but not give away identifying student information.

Notice the data is collapsed into just a single occurrence of each animal. Not each book reading submission (which is what is on the spreadsheet form submissions).

Step 5: Identify what you want to know... How many pages the student has read? How many times the student submitted a reading log?  How many types of books the student is reading?

For VALUES choose the column that contains the data you want to summarize.  Click on “Add Field”

For this example I chose the number of pages column.  

Step 6: How do you want your data summarized?  Add up the values or count the values?

By default the values will “SUM” or add up.

Step 7: The pivot table will now display the sum of how many pages each student has read. This table will automatically update as students submit their reading logs.

THE NEXT STEP

This process is great just for helping the teacher to have real time instant data to help them make decisions and put grades in the gradebook.  Unfortunately that means that only the teacher has access to this information and is constantly having to answer “How many pages am I at?”  

The teacher can not just share the spreadsheet since it has sensitive private student information.

Step 1: Create a NEW spreadsheet

Click Here for sample 2nd spreadsheet.

Step 2: From the 1st spreadsheet get the sheet ID.

https://docs.google.com/spreadsheet/ccc?key=
0AiqrAI5UxSJNdDB6X0o0UHRncEhfWm1zTmU1XzFRSXc
#gid=2

The key does NOT include the #gid and comes after key=

Step 3: Go to spreadsheet 2 and use the =importrange( function

After the parenthesis you will need a quotation mark, paste the 1st spreadsheet key, end the quotation.

put a comma

quotation

name of the tab

exclamation mark

range of pivot table on spreadsheet 1

quotation

end parenthesis

Example of my import range formula:

=importrange("0AiqrAI5UxSJNdDB6X0o0UHRncEhfWm1zTmU1XzFRSXc","how much!A1:C50")

Step 4: Click on the blue share button in the upper right and make it viewable by anyone with the link.  Share this with students.  The 2nd spreadsheet automagically updates as the 1st spreadsheet is updated.

Advantage to Students: As soon as a student submits their reading log, they can check the spreadsheet to see their totals.  There is some magical gratification about being able to instantly see your progress. Students can challenge themselves to read more by always knowing where they are at.

You can actually import multiple pivot tables onto spreadsheet 2.  For one project I am working on I have several spreadsheets.  My team members do not need access to all of the spreadsheet information, nor do they need multiple spreadsheets to check.  So I created one spreadsheet with tabs for the different pivot tables from each spreadsheet that the team members need.  This gives them ONE place to check summary data.