Business Intelligence

How to create a pivot table in Google Spreadsheets

google spreadsheet pivot table pie chartIn yesterday’s post I mentioned about Google Spreadsheet Element which allows us to publish our Google Spreadsheet onto our web sites.

From the earlier post, I published some responses from my Wareprise survey and as stated, I was curious to know how many people who answered the survey :

  • Are female
  • Are 50 and above
  • Find the articles in Wareprise interesting
  • Would recommend Wareprise to friends and family members

Below is the flat spreadsheet data again.

Now, in order to analyze the flat data we would create a pivot table in Google Spreadsheets through Panorama’s Pivot Table for Google Docs. With this cool gadget, I could finally answer some of the questions I had.

Here, I’ll walk you through how I managed to create a pivot table in Google Spreadsheets.

1. Sign in to your Google Docs account.

2. Create a new spreadsheet. Either manually type in some data or import from an external source.

3. In my case, you can see the flat data above.

4. Once your flat data is ready, click on Insert > Gadget

insert gadget

5. From the left menu, click on Tables. You should see Analytics for Google spreadsheets. Click on the Add to spreadsheet button.

6. A new window should pop up allowing you to specify some settings for the Pivot Table. See below.

edit settings

7. To select the range, simply select the flat data that you wish to include into the Pivot Table.

8. If your spreadsheet has a row of header then select 1 instead of Auto.

9. Click on Apply to start generating the Pivot Table.

* Do note that while it’s trying to generate the Pivot Table, warnings may be displayed. In my case, it stopped proceeding because columns within my header row has special characters like ( ? [ ] ). After fixing it, it warned that I do not have any numeric measures available on my table. This is why, if you look at the flat table above, you can see a column called Count with values of 1 in it. No matter what warning messages you get, just keep fixing them until it stops complaining and it will eventually process successfully.

10. Once done, you should be able to see the pivot table like below. Go ahead, give it a try by selecting the value fields and column fields.

Now, I can easily get answers to my questions above by simply selecting the column against the value which I would like to analyze. I now see that there are 65 males respondents and 13 female respondents.

This analytic tool really helps to answer some key questions. Without it, it’s really difficult to find out just by looking at the raw and flat data. Thanks to Panorama for making this gadget available for Google Spreadsheets users.

I hope this quick walkthrough on how to create a pivot table in Google Spreadsheet helps. Do write in the comment box below if you have any questions or comments.

About the author

Bob Lee

Hi! My name is Bob Lee and I’m a web developer / technical writer who specializes in developing and reviewing web applications. As an entrepreneur, blogger, developer, and tech enthusiast, I have been in this field for more than 10 years, and have been loving every minute of it.

Leave a Comment

/* ]]> */