How to Create a Pivot Table using Query function

Creating a pivot table using the QUERY function in Google Sheets is a powerful way to analyze and summarize your data. Here are the steps to create a pivot table using the QUERY function:

1. Organize your data: Make sure your data is organized in a table format with headers for each column.
2. Select an empty cell: Click on an empty cell where you want the pivot table to be placed.
3. Start the QUERY function: Type =QUERY( in the selected cell.
4. Select the data range: Click and drag to select the range of data you want to include in the pivot table, or type the range (e.g., A1:D100).
5. Add a comma: After selecting the data range, add a comma to separate the range from the query string.
6. Input the query string: Type your SQL-like query string within double quotes. The query string should include a SELECT statement, a GROUP BY statement, and an optional PIVOT statement. The SELECT statement defines the columns you want to display in the pivot table, the GROUP BY statement groups the data by one or more columns, and the PIVOT statement pivots the data based on a specific column.
7. Close the QUERY function: Add a closing parenthesis ) at the end of your query string.
8. Press Enter: Press Enter to execute the QUERY function and create the pivot table.

Example

Let's create a pivot table using the following sample data:

Follow the steps above to create a pivot table that summarizes the total sales by product and region:

1. Organize your data with headers in columns A through D.
2. Select an empty cell, for example, cell F1.
3. Start the QUERY function by typing =QUERY( in cell F1.
4. Select the data range A1:D5, or type A1:D5.
5. Add a comma after the data range.
6. Input the query string: "SELECT B, C, SUM(D) GROUP BY B, C PIVOT A"
7. Close the QUERY function with a closing parenthesis ).
8. Press Enter to create the pivot table.

The resulting pivot table in Google Sheets should look like this:

This pivot table shows the total sales for each product and region, broken down by salesperson.