PIVOT Query in Google Sheets: 2 Minute Guide

Reading Time: 3 minutes

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.


Use case of PIVOT function

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
Selecting dataset for creating a named range for Google Sheets query pivot
Figure 1: Selecting dataset for creating a named range
  • Give a suitable name to your data range. Here I am giving it a Dataset.
  • Click on Done to complete creating a named range.
Going to Named Ranges to name our data range for the Pivot query in Google Sheets
Figure 2: Going to Named Ranges to name our data 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.
Create a PIVOT TABLE using SUM() - pivot query in Google Sheets
Figure 4: Create a PIVOT TABLE using SUM()

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:
Example of pivot table displaying average values of a column - Google Sheets pivot query
Figure 6: Example of pivot table displaying average values of a column

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 :
Example of PIVOT table with multiple columns - Google Sheets pivot query
Figure 8: Example of PIVOT table with multiple columns

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.

X
Need Google Sheets dashboards? Get expert analysts on-demand.