Creating a Pivot Table in Google Docs Database

of 05

Introducing Pivot Tables in Google Docs

Ezra Bailey/Getty Images

Pivot tables provide a powerful data analysis tool embedded within your current spreadsheet software. They offer the ability to summarize data without the use of a relational database or aggregate functions. Instead, they provide a graphical interface that allows users to create customized reports within a spreadsheet by simply dragging and dropping data elements to the desired columns or rows. For more details on uses of pivot tables, read Introduction to Pivot Tables. In this tutorial, we examine the process of creating a pivot table in Google Docs. You may also be interested in our related tutorial on building Pivot Tables in ​Microsoft Office Excel 2010.

of 05

Open Google Docs and Your Source Document

Begin by opening Microsoft Excel 2010 and navigating to the source file you wish to use for your pivot table. This data source should contain fields related to your analysis and enough data to provide a robust example. In this tutorial, we use a sample student course registration database. If you would like to follow along, you may ​​access the file and use it as we walk through creating a pivot table step by step.

of 05

Create Your Pivot Table

Once you've opened the file, select Pivot Table Report from the Data menu. You will then see the blank Pivot Table window, as shown above. The window also includes the Report Editor pane along the right side that allows you to control the contents of the pivot table.

of 05

Select the Columns and Rows for Your Pivot Table

You will now have a new worksheet containing an empty pivot table. At this point, you should select the columns and rows you would like to include in the table, depending upon the business problem you are trying to solve. In this example, we will create a report that shows the enrollment in each course offered by the school over the past few years.

To do this, we use the Report Editor that appears on the right side of the window, as illustrated above. Click the Add Field link next to the column and row sections of this window and select the fields you would like to include in your pivot table.

As you change the location of fields, you will see the pivot table change in the worksheet. This is very useful, as it allows you to preview the formatting of the table as you design it. If it's not exactly what you're attempting to build, simply move fields around and the preview will change.

of 05

Select the Target Value for the Pivot Table

Next, select the data element that you wish to use as your target. In this example, we will choose the course field. Choosing this field in the Values section results in the pivot table shown above -- our desired report!

You may also choose to refine your pivot table in a number of ways. First, you can modify the way the cells of your table are calculated by clicking the arrow next to Summarize By portion of the Values section. You may then select any of the following aggregate functions to summarize your data:

  • Sum
  • Count
  • Count Unique
  • Average
  • Max
  • Min
  • Median
  • Product

In addition, you may use the Report Filter field section to add filters to your report. Filters allow you to restrict the data elements that are included in your calculations. For example, you might choose to filter out all of the courses taught by a specific instructor who has left the institution. You would do this by creating a filter on the Instructor field, and then deselecting that instructor from the list.