PIVOT Query in Google Sheets: 2 Minute Guide

PIVOT Query in Google Sheets: 2 Minute Guide
Reading Time: 3 minutes
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 of Google Sheets helps you transfer data from row-level to column level.

Steps to create PIVOT Query in Google Sheets

Setting up the data range

  1. Selecting the data range:
  • We will use a dataset for querying. For ease of querying, we will name the range so we do not need to select the data range every time.
  • Click Ctrl + A (Windows) or Cmd+A (Mac) to select the entire data range.
  • Go to Data
  • Select Named Ranges
Selecting dataset for creating a named range
Figure 1: Selecting dataset for creating a named range
  1. Naming the data range :
  • Give a suitable name to your data range. Here I am giving it a Dataset.
  • Click on Done to complete the process of naming.
Going to Named Ranges to name our data range
Figure 2: Going to Named Ranges to name our data range

How to create Pivot tables using PIVOT Query in Google Sheets

  • You can create a pivot table using Google Sheet Query by using :

=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, as shown below :
Create a PIVOT TABLE using SUM()
Figure 4: Create a PIVOT TABLE using SUM()

Example to create pivot table using AVG()

  • You can create a pivot table using the AVG command to display the average values of a particular column in your pivot table.
  • The query will be as follows :

=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 average values of column C in the pivot table, as shown below :
Example of pivot table displaying average values of a column
Figure 6: Example of pivot table displaying average values of a column

Using PIVOT query in Google Sheets on multiple columns

  • You can create pivot tables on multiple columns by using the following formula :

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