Google Sheets: Pivot Tables | Complete Guide

Pivot Tables on Google Sheets

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?

statistics table

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.
pivot table on google sheets sales data
  1. Select all the cells containing data and click on Data -> Pivot Table. A blank table will open.

    cGjSpxj5tMLwaW6OUh5H3CjRW20zSl48Szf8Blz07QSwghGMjJKyMcaYTvk tnlXes6D4FHcpWFctx HdOV0pm6Afh y35NNASLMhcP7yWG SPs2YyAcekX4Ggt59Th79wz9ifFk


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.

  1. 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.
pivot table editor on google sheets
  1. Click on the suggested tables to generate the pivot table.

9xm6oYDQ1kKh0d 7wZ3V yB8I el87kQaD5jqu Ohqw48nFDdR1a245M5BkJtSkbQjoLtFsBjQB1Kjb3mAUDJfJ4PQeyes iOAy9L5IsWXgnWlyHMEJ6IEoy5EQMTWVDNGBoZiy5
Average Sales by Date

4PBACMCOerTJOzIisVPxap2cMa2aUa9Ej c1vaEwURXIZ3JQVYmJiHmFv7J1gaKEcYZvzHz7 B UIhlGgmERpeCF8CGB bO3G1 p8XbRM0O4G6DjP9qeT 3AqOwVaAo2NoyU87ic
More Suggested Options

  1. 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.
    NAirAaaGeG7 H8NYSP RoEKJkcDfikVJfMGnhKlorNvyGxGwUThHj1OD2gSizyQUrHqNisve6tfFiyPyaVFubrNF1u1OyrSV84fjwOwOWzrKw8JGSTQVt 9yz MPj3N5aqU8IbG8
  • Select or Add more rows using the Rows options.
    7NgT8lL7MbXLw3TVSIMYdM WP0czqd
    Click on ADD to add more types of rows to the pivot table:

    qolHfOK8YIchzVb5L9mkeEAdb0WmsU0QsDnNhb CQn2tLm1gKa7wdFVMDh EwS0dt7SD7litCiviQHJWo6uekSVM90qosLmIA O9ofdypd9mgZ1j9PDN7OTyXzLUuWvLVNbgMK1r
  • To change the order of the rows in the pivot table, just drag the row box to your desired order.

    JOk3h8nzubnbAiOdtiB5OElwRFK2bPDvX qfk8xjz5Bomt3vwXxDL0ZUQg077sJwMF6fkk9 XIkZGi6fZfyHavLjmB Xi 6mHNGPGcbyiQJQUd3NQFBQSwirfIU wJcK1ZuwtAj2
  • The sorting options give you even more flexibility in analyzing your values:

    2n8d49NW iBC4TV3JMaiI57PHOJ1CO j8pHN4kW7IP5iZM0jG xNcPzJfLA4HncJgNsALSuJcIIAOX89DOMqr7rvER94nqnSHcMvTX2B1TfxB2YnZbdT0T8Nr5G3 gDrqr535kH4
  • 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.

    VUIDG6f8vGBwsNjkBWnzpiW20FsvRXA5Vd6JyCbgSZcBasfqJe0H3j7kzkGS9QEEsVVWDyfiAEXxQf4dPtjYvKfPOcWB5 W5pwHcCM1zKptLS6GWFKnRLYL7T31iWRklmtQ3D7ct

    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.
rows and columns in google sheets
  • 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.

    oOkdEeFAHFFFWUcg H0xMYtGx05FdzP0VlkQv0OmO 019bt220C XjVHC0W80NzR92wAuVsK2FpD93sS OnsRVWEv40ZZ0y jAWDRW94uuhN0EuHh4BBFVkGP8oddDs62zgz5cO
    Click on the Summarize by dropdown list to select a value:

    kTof kpxlfaaIOfbBnj43O v4DADqEI fGQyF2hfC2j1pq9U4OxQ2VsNSDsEPdIMWrfNWED60ax U5EqTunPxvwwyCYiUywEOFb7SB u0XSf 6WU0N3RW IveSuAUdMFGFuyMnUD
    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.

    QoKHTw7T aoUzduIa2 ILk7jNfW m QOIpPkVcU9N5rXyxaQ V3 5UhbfKDZLIJ6OK
  • 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:

    FEc4kjdLNYGDi LDHvBLEq4PAWo QQxbUKo07yzEz G9KpsPS1UUN3O2QOP 5f62rpf6A0de9mlXjUj3bBtmqpsMvUJheM6E dBpZJJG4LnIIy2ZsmWhopA KQvOb56ggGm3Dflr

    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.

    Irydyn Yw9qk1OQei6iS1K0 Q0oSRwy6gPiW8RRZFK4S8JRinOpSg8I zSwrg bYjA1JVAfbktPvURyor49KC54dGXkeSftj6I7yufoCCQhcO0BWBN099uNkN69kTsXybXLaApMJ

    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:

    hYseE9rlBlSuR2 aJ0x6zl

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.

9T3 C8G22eoIqx6H3wMic
Errors in Pivot Table due to Missing Column Headers

  1. Make sure you leave no cell blank. If any value is missing, your pivot table will show a division by zero error:
division by zero error in google sheets
  1. 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:
sorting issue in google sheets

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:

yu9SFFRcXgSrs82nU5mwleOU5


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.

Leave a Reply