Google Sheets pivot table tutorial - make your job easier

In this article, you will learn about creating Google Sheets pivot table and charts from pivot tables. See how to create a pivot table from multiple sheets in a Google spreadsheet.

This article is intended not only for those who are just starting to use pivot tables in Google Sheets, but also for those who want to do it more efficiently.

Further on you will find the answers to the following questions:

What is Google Sheets pivot table?

Do you have so much data that you're getting confused from the amount of information? Are you overwhelmed by numbers and don't understand what's going on?

Let's imagine that you're working in a company that sells chocolate in several regions to different buyers. Your boss told you to determine the best buyer, the best product and the most profitable region of sales.

No reason for panicking, you don't have to start recalling how to use heavy-duty functions like COUNTIF, SUMIF, INDEX, and so on. Take a deep breath. Google spreadsheet pivot table is a perfect solution for such a task.

Pivot table can help you in presenting your data in a more convenient and understandable form.

The main handy feature of a pivot table is its ability to move the fields interactively, to filter, group and sort the data, to calculate the sums and the average values. You can switch lines and columns, change detail levels. It enables you not only to modify the appearance of the table, but also to take a glance at your data from another angle.

It's also important to note that your basic data is not changing - no matter what you do in your pivot table. You just choose the way it is presented, which allows you to see some new relationships and connections. Your data in the pivot table will be divided into parts, and a huge volume of information will be presented in an understandable form that will make analyzing data a breeze.

How to create a pivot table in Google spreadsheets?

This is how the sample spreadsheet data for pivot table looks like:
Sample spreadsheet data for pivot tables.

Open the Google sheet that contains your basic data of sales. It's important that your data which you will be using is arranged by the columns. Each column is one data set. And each column must have a headline. Furthermore, your source data should not contain any merged cells.

Let's build a pivot table in Google Sheets.

Highlight all the data which you will use for creating your pivot table. In the menu, click Data and then Pivot table:
Creating pivot table.

Google spreadsheet will add a new list into your document. The only thing left to do is to customize the contents and the appearance of your pivot table.

Open a newly created list with your pivot table. It does not contain any data yet, but there is a panel "Report Editor" on the right. Using that panel, you can add fields of "Rows""Columns""Values" and "Filter" them:
Add pivot table.

Let's take a look at how to work with a pivot table in Google Sheets. To add a row or a column to your pivot table Google sheet, simply click "Add field" and choose the fields you need for the analysis:
Add data to pivot table.

For example, let's calculate the sales of different types of chocolate in different regions:
Sample pivot table.

For the "Values" field we can specify how to calculate our totals. They can be returned as a total sum, minimum or maximum sum, average sum, and so on:
Values in pivot tables.

The "Filter" field enables you to estimate the total sales for a certain day:
Filter data in pivot table in Google Sheets.

Google Sheets pivot table has the ability to show even more complex data combinations. To check it out, you just click "Add field" and add the data to "Rows" or "Columns".

And so, our pivot table is ready.

How do you use a pivot table in Google spreadsheets?

At the most basic level, pivot tables answer important questions.

So, let's go back to our boss' questions and look at this pivot table report.

Who are my best customers?
Best customers in pivot table.

What are my best-selling products?
Best selling products in pivot table.

Where are my sales coming from?
Best regions in pivot table.

In about 5 minutes, Google Sheets pivot table gave us all the answers that we needed. Your boss is satisfied!

Note. The total volume of sales is the same in all our pivot tables. Each pivot table is representing the same data in different ways.

How to create a chart from pivot table in Google Sheets?

Our data becomes even more visually appealing and clear with pivot table charts. You can add a chart to your pivot table in two ways.

The first way is to click "Insert" in the menu and select "Chart". Choose the type of the diagram and change its appearance. Then press the "Insert" button, and the chart will be displayed on the same list with the pivot table:
Building Chart in pivot table.

Another way to create a diagram is by clicking "Explore" in the right bottom corner of the Google sheet. That option will allow you not only to choose the most well-constructed diagram from the recommended ones, but also to change the appearance of your pivot table:
Explore analysis.

As a result, we have a pivot chart in Google spreadsheet that shows not only the purchase volumes of our customers, but also gives us information about the kinds of chocolate the customers prefer:
Pivot table and pivot chart.

Your diagram can also be published on the Internet. To do this, in the menu click "File" and choose "Publish to the web...". Then select the objects you want to post, specify if you want the system to automatically update when the changes are made and press "Publish":
How to publish pivot chart in google spreadsheet.

As we can see, pivot tables can make our job easier.

How to create a pivot table from multiple sheets in Google spreadsheet?

It often happens that the data, which is necessary for the analysis, is spread out into different tables. But pivot table can be built by using one data span only. You can't use the data from different tables to make a pivot table. So, what's the way out?

If you want to use several different lists in one pivot table, you should combine them in one common table first.

For such a combination, there are several solutions. But taking into account the simplicity and accessibility of pivot tables, we can't but mention the Merge Sheets add-in, that is of huge help when it comes to combining several data spreadsheets into the one.

We hope that our short review of the abilities of pivot tables has illustrated you the advantages of using them with your own data. Try it yourself, and you'll quickly realize how simple and convenient it is. Pivot tables can help you to save time and increase productivity. Don't forget that the report, which you have made today, can be used tomorrow with the new data.

Note. In contrast to Excel, pivot tables in Google spreadsheets are refreshed automatically. But we advise you to check your refreshed pivot table periodically in order to make sure that the cells it has been created from have not changed.

Have you worked with pivot tables before? Don't hesitate and share your progress or questions with us below!

7 Responses to "Google Sheets pivot table tutorial - make your job easier"

  1. Charles Arehart says:

    Thanks for this. I'm viewing it on a mobile device and am not seeing any link to the sample sheet you're using. Do you have one? That would better help us appreciate and demonstrate what you're kindly describing here. Even so, I do appreciate the article.

    • Mary Trifuntova (Ablebits.com Team) says:

      Hello, Charles
      Thank you for your feedback.
      Here is a link you can use for testing pivot tables in Google sheets. You can make a copy of this spreadsheet and try work with existing pivot tables or create new ones.
      If you have any other questions don’t hesitate to contact us. We’ll be happy to help!

  2. Will Hannele says:

    Cannot really follow and learn without the datafile.
    Please provide a copy.

    Thanks

    • Mary Trifuntova (Ablebits.com Team) says:

      Hi Will
      You can make a copy of this spreadsheet and try work with existing pivot tables or create new ones.
      Feel free to contact us if you have any other questions.

  3. Jiro says:

    Very helpful! Thanks much!

  4. Inigo says:

    Hi, I am used to working with Excel and having charts that I create with Pivot tables being dynamic. however with sheets I find that the charts do not change dynamically based on what data is showing in the pivot table . is this the case? Is there a way of dynamically changing the chart based on variable data? Please tell me this is possible.

  5. Jamie says:

    Can you use pivot tables on mobile devices and if you can how?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
60+ professional tools for Excel
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard