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