Pivot table in Google Sheets: How to create one

Pivot tables in Google Sheets - Featured Image

Written by Valentine Schelstraete

Nov 15, 2017

One of the prime reasons we use spreadsheets is store data. However, it isn’t as useful just to store the data if we are not able to derive insights from it. Google Sheets comes with tools that help us analyze the data and convert it into more meaningful insight. In this article, we explain how to create a pivot table in Google Sheets.

Why create a pivot table in Google Sheets?

Using a pivot table, we get to explore the underlying information and trends that the data contains. Let us understand this with the help of an example. Here’s the sales data that a retail company recorded across its stores and product line for the month of October 2017.

Products Details Sample Data in Google Sheets

To the naked eye, this is just a data log. We can’t really tell how the sales are doing with respect to stores and products. Sure, we may try and extract summary figures using formulas. But fortunately, we have a much simpler yet a sophisticated functionality that Google Sheets offers – pivot tables. They easily do all the heavy lifting of curating all the necessary insights from the existing data. In order to explore this, we navigate to Data > Pivot table.

Pivot Table in Google Sheets: Navigation to Insert Pivot Table

We will notice a brand new sheet opens up that contains the pivot table, along with the Report Editor sidebar on the right side of the window. Using the Report Editor, we can customize the report to our liking and needs. Please note that Google Sheets automatically intelligently selected the range from Sales Data worksheet. Because we trigged it while one of the cells within that continuous range was selected. Depending on the need, we can change the range using edit range option.

Customization of Pivot Table in Google Sheets

The beauty of the pivot tables is that it is highly customizable. We can create various kinds of reports from a single data source range, upon which these reports pivot upon. Let us try a few of them.

Report # 1: Sales by store

To obtain this, all we need to do is add Store field in the Rows section of Report Editor using the Add field option. Similarly, add Units and Total fields in the Valuessection. And we notice the corresponding report coming to life in the main area of the Pivot Table 1 sheet.

Sales by Store Data in Pivot Table

Report # 2: Sales by store for products B and D

To the previous configuration, we just add the Product field in the Filter section and apply the filter to only Products B and Product D. Accordingly we see the sales figures narrow down to only these two products across the stores.

Pivot Table in Google Sheets: Snapshot of Report 2 Example

Report # 3: Sales by product

From the existing setup, we remove Store field and add Product field in the Rowssection of Report Editor. We let the Units and Total fields in the Values section remain as is. But remove the Product field from the Filter section. Instantaneously, we see the summary of sales segregated by products.

Sales by Product Data in Pivot Table in Google Sheets

Report # 4: Total revenue by store and product

To obtain this, we ensure we have only Store field in the Rows section, Productfield on the Columns section and finally Total field on the Values section. In doing this, Google Sheets automatically produces the grid layout with corresponding numbers, as shown below.

Total Revenue by Store and Product in Pivot Table

It is obvious that such reports are extremely easy to generate with the help of pivot tables. Not just that, they are extremely flexible and can be adjusted to suit our needs in a few seconds. We can’t imagine generating the same output in as much time with the help of formulas!

Congratulations! This is how you create a pivot table in Google Sheets. And we encourage you to further experiment the pivot tables yourselves to get a good hang of it. The more we use them, the better we tend to like them. Because pivot tables offer such the ease, power and flexibility! Feel free to also read through the following blog post to learn how to insert a chart in Google Sheets.

You may also like…

Share This