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.
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.
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.
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.
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.
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.
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.
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.
