Watch a video about how to create a pivot tables report (not available for all languages)
For this sample pivot table report, put yourself in the shoes of a university physics professor. The data set in the template shows the names, class levels, and other details about each student in the class.
First, click the link to the pivot table report template, and press the Use this template button. This will create a personal copy of the spreadsheet for you, and it will appear in your Documents List.
Let's start building the sample pivot table report. In building this pivot table report, you want to get a better understanding of your students and their majors in order to build a curriculum for the semester. First, let's see how many students are Freshmen, Sophomores, Juniors, or Seniors.
- From the Data menu, select Pivot table report.
- A new tab named "Pivot table 1" will open.
- In the Report Editor to the right of your spreadsheet, check that the detected data range is correct. It should read 'Class Data'!A1:F31.
- In the Rows category of the Report Editor, click the Add field link and select Major.
- In the Columns category of the Report Editor, click the Add field link and select Class level.
- In the Values category of the Report Editor, click the Add field link and select Major.
- In this field, click the drop-down arrow next to Summarize by: and select COUNTA.
Note that you have more Freshmen students in your class than any class level.
Next, you want to understand how many of your students are taking your class as a required course for their Physics major. You want to see this information within class levels.
- In the Filter category of the Report Editor, click the Add field link and select Major.
- In the Major field, click the drop-down arrow next to Show: 0 items and select Science.
The pivot table report shows that you have 7 Physics majors. Of the 7 Physics majors, 3 students are Freshmen, but you also have two Sophomores, one Junior, and one Senior.
Click the X in the top right of the Filter: Major field to return to the full view of your pivot table report.
Now, you want to format your pivot table to share it with other professors in your department. This will make it easy for your colleagues to understand the data at a glance. Here's how to format your data:
- Change the background colors of the rows and columns using the text background color menu in the toolbar.
- Change the background of the column labels to grey and the text to white.
- Change the background of the row labels for Major to dark blue and the text to white.
- Change the background color for the Grand Total row and column to light blue.
Your pivot table is ready to share with your colleagues! Press the Share button in the upper right of your spreadsheet and enter their email addresses.