Table of Contents
Creating a Pivot Table in Google Sheets Query is a great way to quickly summarize and explore large amounts of data. It allows you to quickly organize, analyze, and summarize data in a visual way. To create a Pivot Table, select the range of cells containing the data you want to analyze, then click ‘Data’ and select ‘Pivot Table’ from the drop-down menu. From here, you can select a range of rows and columns to summarize the data, add filters, and make any other adjustments you need. Once you are satisfied with the results, you can click ‘Create’ to generate the Pivot Table.
You can use the following syntax to create a pivot table using Google Sheets Query:
=query(A1:C13, "select A, sum(C) group by A pivot B")
In this example, we choose column A to represent the rows of the pivot table, column B to represent the columns of the pivot table, and the values in column C to be displayed inside the pivot table.
The following examples show how to use this syntax in practice.
Example 1: Create a Pivot Table Using SUM()
We can use the following formula to create a pivot table that displays the total sales by product and by region for a certain company:
We would interpret the values in the pivot table as follows:
- The total sales of product A in the East region was 388.
- The total sales of product B in the East region was 0.
- The total sales of product C in the East region was 476.
- The total sales of product D in the East region was 0.
And so on.
Example 2: Create a Pivot Table Using AVG()
We can use the following formula to create a pivot table that displays the average sales by product and by region for a certain company:
We would interpret the values in the pivot table as follows:
- The average sales of product A in the East region was 388.
- The average sales of product B in the East region was 0.
- The average sales of product C in the East region was 238.
- The average sales of product D in the East region was 0.
And so on.