Earlier we have learned how to create a Line Graph or Line Chart using Google Doc Spreadsheet. In this Google Doc tutorial we are explaining how to create a Pivot Table Report in Google Doc Spreadsheet.
Without knowing the use of Pivot Table reports you can’t be a Spreadsheet pro. Whether you use Microsoft Excel Spreadsheet or Google Doc Spreadsheet Pivot Table is there to mesmerize you.
The main purpose of Pivot Table is to analyze a large set of data. You can summarize your data dynamically and perform various calculations that without using any formulas manually. Also all these with in few clicks.
To understand the use of Pivot Table Reports we need to experiment with some kind of sample data. I know you don’t have enough time to spend for creating a sample data. So we can use the sample data provided by Google for experiment purpose. Follow the below link for the sample data to create Pivot Table Report in Google Spreadsheet. Make sure that you have already signed into your Google Drive.
Click the above link and click the Tab labelled “Use this Template”. It will open the sample Pivot Table report data in a new Spreadsheet in Google Doc.
Just try to understand the data first. It is a list of students in a college. The data is arranged in columns. There are 6 columns labeled as Student Name, Gender, Class Level, Home State, Major, and Extracurricular Activity. You can use some of these column labels to summarize your data.
For example here we took two column labels for our Pivot Table Report – “Class Level” and “Major”. Under the filed “Major” different subjects are given like Physics, Math etc. Under “Class Level” you can see age distribution like Senior, Junior, Freshman, etc. Now we can summarize the data to find the number of Senior, Junior etc. students for each subject.
Steps to Create a Pivot Table Report – Google Doc
Select the entire data. To select entire data in Google Doc Spreadsheet you can follow different methods. The ideal method is go to the first cell in your data, then press and hold the Shift key and use right/bottom arrow key to move to the end. Once you’ve reached the end cell release the keys.
Now go to Data->Pivot Table Report. A new blank sheet will be opened with a “Report Editor” where you can add the above two column labels to summarize data.
Now add row and column field from the Report Editor. Remember we are summarizing the data to find the number of Senior, Junior etc. students for different subjects. So we should show the Subjects in rows. The relevant filed containing the data in the sample Spreadsheet is “Major”. Click “Add field” against “Rows” and choose “Major”. Similarly click “Add field” against “Columns” and choose “Class Level”. Class Level contains the data of Junior, Freshman, Senior etc. The unfinished Pivot Table Report will now look like this.
Now use some logic. What we want. The age distribution in each subject. Means we should get how many Freshman, Sophomore, Junior and Senior are in the college class for each subjects. Column label or field for this purpose is the above same “Class Level” which we have added against “Columns”. Add this field again under “Report Editor” but this time in “Values – Add field”. We need the count of freshman, sophomore etc. So use the function COUNTA under “Summarize by”. Use COUNTA not COUNT as there is no numeric value. Now see the finished Pivot Table Sample Report in Google Doc Spreadsheet below.
Don’t mistake that the whole procedure is complicated. It only take 2-3 minutes to complete all these steps. You only need to understand what output you expect from your data. It’s important.
Leave your comments. Our mission with this section is to create a complete Google Spreadsheet tutorial here. Try it and share your views. Also if you find any difficulty in any of the steps mentioned reach us using the comments section below. We are ready to solve your problem.