Pivot Tables

There are numerous features built into Google Sheets that allow teachers to analyze the data they collect. Some of the most useful are the sort function, custom filter view, the motion chart, and the pivot table. Each has a specific use case and when used in conjunction with others provides a powerful tool set for the teacher and their students’ growth.

Sort Function

Sometimes you want to rank or sort rows in a spreadsheet. That’s where the ‘Sort’ function comes in. To sort all the values in column A enter =Sort(A:A). By default the action is ascending, but can be altered to be descending.

Filter Function

Filtering data can be very handy. We may have to filter by grade level, period, or a single student. If so, the Filter function is the way to go. For example, imagine you want to find all of the the 8th grade students. Column A has student names and column B has their grade. The formula for this is =Filter(A:A,B:B=”8th Grade”). This is complex but incredibly useful. Have a look at how you can quickly view data by a student in this example.

Pivot tables

Pivot Tables are another tool that provide a dynamic interface for working with large data sets, including the ability to create custom calculations. These tables draw results directly from the original data and therefore always reflect the current information. Whether by date, score, grade level, or year, users can quickly switch the inputs and axes of the data being analyzed.

Motion Chart

A motion chart is an effective way to visualize substantial amounts of data. It adds the variable of time to standard education data points. This allows teachers to visualize and track student results with animated charts that compare two or more data points over time. There is a play button and the data points move as time passes.

Create a Pivot table

A pivot table is a convenient tool to compare various aspects of a data set and are typically built to summarize data numerically. For example, a teacher might give a quiz and organize the results by grade level, class period, or any other criteria she chooses.

You can use pivot tables to narrow down a large data set or analyze relationships between data points.

Try it out: https://goo.gl/PBHpiM

(The Edu Level 2 Exam will give you the settings to produce your pivot table.)

From the GETC - Add and edit pivot tables

  1. On your computer, open Google Sheets.
  2. Open the spreadsheet with the data you want to put in a table.
  3. Select the cells with data you want to use.
    • Put the data you want to use into columns. Each column is one data set.
    • Each column needs a header.
  4. In the menu, click Data Pivot table. Click the pivot table sheet, if it’s not already open.
  5. Next to each category in the "Report editor" window, click Add field, then click a column to use.
    • Your table has one row or column for each number in the column you use, even if the number is in more than one cell.
    • Data from the columns you use for "Values" is sorted into the matching rows and columns.
    • If you want to add a field to the "Values" category that calculates values based on a formula, click Calculated field and enter the formula.
    • "Filters" hide data that you don't want to show in the table.
  6. You can change how your data is listed, sorted, summarized or filtered. Next to what you want to change, click the Down Arrow .
  7. To format the pivot tables, use the buttons in the toolbar.

Change or remove data

  • To move a data set from one category to another, drag it to the category you want.
  • To remove a data set from a category, click Remove .
  • To expand the range of data used for your pivot table, click Edit range.
  • The pivot table refreshes any time you change the cells it's drawn from.


https://edutrainingcenter.withgoogle.com/advanced_training/unit?unit=23&lesson=26