Create a pivot table using the PIVOT query in Google Sheets.
Syntax=QUERY(data, query, [headers])
data – The range of cells to perform the query on.
query – The query to perform.
The value for query must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
headers – [Optional] The number of header rows at the top of data.
Sample usage=QUERY(Dataset, "Select D, SUM(C) GROUP BY D PIVOT B")
//This creates a pivot table from the range named “Dataset” with column D as the rows, column B as columns, and the sum of C as the values of the pivot table.
Objective
How to create a pivot query in Google Sheets and its examples.
Use of PIVOT Query in Google Sheets
The PIVOT function in Google Sheets helps us to transfer data from row level to column level. We can do this by using the QUERY function with the PIVOT clause to create a pivot table.
Steps to create PIVOT Query in Google Sheets
Step 1: Setting up the data range
- For ease and simplicity we’ll create a named range to that we can reference the named range instead of selecting the range manually.
- Select the entire data range by pressing Ctrl + A (Windows) or Cmd+A (Mac).
- Go to Data->Named ranges
- Give a suitable name to your data range. Here I am giving it a Dataset.
- Click on Done to complete creating a named range.
How to create Pivot tables using PIVOT Query in Google Sheets
In the next section, we’ll learn how to create a pivot table with the PIVOT query in Google Sheets.
- To create a pivot table using the pivot query in Google Sheets use the following formula.
=QUERY(Dataset, "Select D, SUM(C) GROUP BY D PIVOT B")Figure 3: Query to pivot table by column B
- The above formula selects column D as Rows, Column B as Columns and SUM(C) as the data inside the pivot table.
- The created pivot table using the QUERY function is shown below.
Example to create pivot table using AVG()
- We can create a pivot table using the AVG command to display the average values of a particular column in the pivot table.
- An example of a pivot query with the AVG command is given below.
=QUERY(Dataset, "Select D, AVG(C) GROUP BY D PIVOT B")Figure 5: Query to pivot table about column B
- The above formula will display the average values of column C in the pivot table, as shown below:
Using PIVOT query in Google Sheets on multiple columns
- We can also create pivot tables of multiple columns. An example of a query to create a pivot table with multiple columns is given below.
=QUERY(Dataset, "Select A, SUM(C) GROUP BY A PIVOT B,D")Figure 7: Query to pivot table on columns B and D
- This command will display column A as Rows, Columns B and D as columns, and SUM(C) as the data inside the pivot table, as shown below :
See Also
Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.
Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.
Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets
How to use Filter views in Google Sheets | Simple 2-min Guide 💻: Learn how to use Filter view in Google Sheets and its variations.