Google Sheets: Pivot Tables | Complete Guide

Pivot Tables are a great way to process data. Whether you want to perform a data analysis using the statistics or just summarize your large datasets, pivot tables are what you’re looking for. 

In this guide, you will learn how to create a pivot table on your Google Sheet data.

What is a Pivot Table and Why do you need it?

A pivot table is a table based on your (large) spreadsheet data that basically summarizes your spreadsheet making it easier for you to read, analyze and draw any conclusions for your data.

Can you make a Pivot Table on Google Sheets data?

Yes, you can create pivot tables in Google Sheets as well. It’s not only easy to do so, but these pivot tables are quite powerful and flexible according to your needs. Google Sheets is smart enough to build a pivot table itself on your data, while also allowing you to alter it in any way that you want to.

How to make and use a Pivot Table on Google Sheets data?

1. Open your desired Google Sheets.
   For this article, I’ll be using a Google sheet showcasing which coupon and how many of each coupon has each member of a Sales team sold over a period of time.

2. Select all the cells containing data and click on Data -> Pivot Table. A blank table will open.
   
   

You can choose where to show the table. A new sheet makes the table cleaner and easier to read.

If your Google Sheets only contains a table like those from Google Sheets templates, then you do not need to select the cells first.

3. A panel for options and any suggested tables will be shown on the right-hand side. If Google Sheets is unable to suggest any table to you, go through the pointers below to check what you might have missed out.

4. Click on the suggested tables to generate the pivot table.

Average Sales by Date

More Suggested Options

5. The Suggested tables might be enough for you. However, if you’re not looking for just that data, you can alter the conditions in your own way by playing around with rows and columns and other features that pivot tables on Google Sheets allow.
   

• Select or Add more rows using the Rows options.
  
  Click on ADD to add more types of rows to the pivot table:
  
  
  
• To change the order of the rows in the pivot table, just drag the row box to your desired order.
  
  
  
• The sorting options give you even more flexibility in analyzing your values:
  
  
  
• Now let’s explore the columns on our pivot table. Click on ADD to add more types of columns to the table. This may sound confusing, but think of it this way: adding columns spreads the data from the selected rows over the added columns. Data from rows is spread out horizontally, whereas data from columns is spread and mapped out vertically.
  
  
  
  However, it is interesting to note that the horizontal Grand Total (here it is the total average) shows the grand total average of that particular column, whereas the vertical  Grand Total (also the total average) shows the grand total average of that particular row.

• Now, let’s finally change the calculated values. Currently, the table was showing the AVERAGE total values. This can be changed in the Values option.
  
  
  Click on the Summarize by dropdown list to select a value:
  
  
  This list includes many popular and useful Google Sheets formulas such as SUM, MAX, MIN, AVERAGE, MEDIAN, etc. 

• In the Values box, another very useful feature is the Show as dropdown list. This list lets you showcase your values as percentages of other values in the table.
  
  
  
• If you want to make your data even more specific, you can use the Filters box to filter out any unwanted data.
  
  Say, I want to check who and when sales of more than 5 coupons were made. The logic here is to apply a filter of: “number of coupons sold > 5“. 
  
  Click on ADD to add a type of filter:
  
  
  
  In the Status dropdown list, click on Clear to select the desired number of coupons, or click on the numbers not wanted to uncheck them. Then click on OK to apply the filter.
  
  
  
  If you want to select all the options, click on Select All.
  
• Just like other features, you can add more than one filter. Say, now I want to see how many of these sales made more than $100. I will repeat the above process to add my new condition:
  
  

There you go! You can now not only create pivot tables on your Google Sheets data easily but also manipulate them in your own ways.

Pointers to create the perfect Pivot Tables

While you are creating pivot tables on Google Sheets, you might encounter issues such as no suggested tables, distinction by column indexes instead of titles etc. Here are some tips to help you counter these issues and prepare a great data set in Google Sheets:

1. Add headers for every column to avoid distinction based on column indexes. Moreover, if you do not add column headers such as Date, Name, Sales etc. in your spreadsheet, the Rows, Columns, Values and Filters options will consider the first row of your data to be the headers.

Errors in Pivot Table due to Missing Column Headers

2. Make sure you leave no cell blank. If any value is missing, your pivot table will show a division by zero error:

3. Correctly format your columns, eg. number, date, currency etc. Failure to do so may result in no suggested tables for your data, or worse, it will hinder with you editing options, such as if I format my No. of Coupons Sold column to Plain Text instead of Number, the sorting will be done based on text based sorting instead of numerical:
   

The number of coupons are sorted in Ascending order, but in the highlighted block, the text based sorting considers the number 10 as the smallest, hence making my data inaccurate.

To format your columns, select a column, go to Format -> Number and select your desired format:

If you do not see your desired format, click on More Formats in Format -> Number to select a format according to your specifications.

If you found this guide helpful, do check out more of our tips and tricks on Google Sheets.