HomeGoogle DocsSpreadsheetHow to Use QUERY Function Similar to Pivot Table in Google Sheets

How to Use QUERY Function Similar to Pivot Table in Google Sheets

Published on

Honestly, I fell in love with Google Sheets QUERY function as its usefulness is above words. You can use QUERY Function Similar to Pivot Table in Google Sheets.

I am going to exactly match a Pivot Table report with a Query Function Report! Before going to the tutorial, see what sample data we have for this purpose.

I have got realistic data of Diesel consumption of a certain number of heavy vehicles with us. We are only going to use part of that data for our tutorial purpose here.

From this data, I’m going to make a vehicle-wise summary of diesel consumption using Pivot Table and as well as using Query Function.

DEMO DATA:

sample data for query pivot use

Our summary using Query Formula will look like the below image. This summary I have prepared using Google Sheets Pivot Table.

Google Sheets Pivot Table - Diesel Consumption Report

We can generate the same Pivot Table Report in Google Sheets using QUERY Formula, and that is what you are going to learn!

See the below report, which I’ve generated using Google Sheets’s powerful Query function.

Query Formula Output similar to Pivot Table Output

Both the reports are perfectly matching, and both are dynamic.

That means, when you change your source data, that will reflect on both the reports.

It’s not a difficult task to make such a report in Google Sheets.

With the Pivot Table, it’s just a child’s play. But using Query, I think there is something new I can offer to you.

Report generation with Query is also not a difficult task. But the real problem lies with the Grand Total below reports.

Using Query, you can’t do it effortlessly. It’s a tricky part, and that also you can learn in this tutorial.

From the above-shared sample data, let us see how to make a summary using Pivot Table first.

Summarise Data Using Pivot Table

Our sample data is spread across A2:C16. Please see the first screenshot above.

To create a pivot table summary, first of all, select this data range and then go to the Data Menu > Pivot Table.

Enter the data range and cell ID to insert the report. Then click Create.

The below Pivot Report Editor settings will generate a pivot table summary report as shown above.

pivot table report editor settings - diesel consumption

Update:- Google has updated the “Report Editor.” So, you may see slightly different settings on it.

To learn more about Pivot Table, you can refer to my related tutorials below.

How to Create a Pivot Table Report to Summarize Data in Google Doc Spreadsheet.

Now let’s go to the most important part of this tutorial.

QUERY Function to Generate Report Similar to Pivot Table

Let me explain the use of the QUERY function similar to the Pivot Table. Please see the image below.

Query formulas to exactly match Pivot Table

You can see four colored rectangles on the image. Each rectangle represents Query formulas.

With one QUERY formula, we can summarise our data similar to Pivot Table.

But to get the Grand Total on the right-hand side and below/bottom of the report, we require three more Query formulas.

So there is a total of four Query formulas, and later we will nest them all with the help of Curly Brackets/Array.

I will explain the same with formulas and sample screenshots.

The four formulas to make it possible to use QUERY Function Similar to Pivot Table in Google Sheets.

  1. Summary Report – QUERY formula.
  2. For Grand Total on the Bottom Side of the Summary Report – QUERY formula # 2.
  3. For Grand Total on the Right Hand Side of the Summary Report – QUERY formula # 3
  4. QUERY formula # 4 for Grand Total on the Right Side Bottom Corner (value 1918 above) of the Summary Report.

Please follow the link if you are not familiar with using Google Sheets Query Formula – Learn Query Function with Examples in Google Sheets.

Step 1 – QUERY Formula to Generate Pivot Table Like Summary Report

Please refer to the demo data on the top.

Formula:

=QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A")

In the above formula, column C contains the vehicle numbers.

We need to summarise or group the data based on vehicle numbers.

If you check the Pivot Table Report at the top of this page, you can understand it.

We should Sum column B as it contains the filled diesel quantity in Gallon.

I hope, now, you can read the above formula.

With the above formula in use, the result will be as below.

query as pivot table 1

But this is not as per our pivot table report.

You can compare this table with the pivot table on the top of this page.

There you can see that date appear on rows and vehicle numbers on columns. So we may use the Transpose function with the above QUERY formula as below.

=TRANSPOSE(QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A"))
query as pivot table 2

As you can see, now this table is almost matching with the Pivot Table report. But without the Grand Total part.

That’s all with this part.

Now let us learn how to get Grand Total below summary in Query similar to Pivot Table.

Step 2 – QUERY Formula to Generate Grand Total on the Bottom Side

Here is the Query formula to get the Grand Total on the bottom side of the report.

=TRANSPOSE(QUERY(A2:C16,"SELECT SUM(B) GROUP BY C LABEL SUM(B) 'GRAND TOTAL'"))

Please don’t forget to refer to the demo data on the top. The above Query formula will return the below result.

Query Grand Total on the Bottom

This is a vehicle number-wise summary (total). So I have grouped column C and totaled column B.

Here also I have used the Transpose function because we want the total horizontally, not vertically.

Please note that, in our generated report in step # 1, vehicle numbers are appearing on columns (there also used the Transpose).

Later we will join this Query formula with the first formula (Step: 1).

Step 3 – Generate Grand Total on the Right Hand Side of the Summary Report

From the report, which we have generated by the first formula (Step: 1) or the Pivot Table report itself on the top, we can understand that we must add one more Grand Total on the right-hand side of it.

This Query formula will do that part and no Transpose function this time.

=QUERY(A2:C16,"SELECT SUM(B) GROUP BY A LABEL SUM(B) 'GRAND TOTAL'")

I have grouped the dates in column A this time and totaled the Qty. in Gallon (column B).

Query total right and bottom in Sheets

Step 4 – Grand Total on the Right Side Bottom Corner

We just need to sum Colum B, no vehicle-wise or date-wise summary here.

=QUERY(A2:C16,"SELECT SUM(B) LABEL SUM(B) ''")

Step 5 – Nest QUERY Formulas to Make a Report Similar to Pivot Table

We have four formulas in our hands.

Can we use the formulas as below individually in four different cells?

combine query using array to get grand total on the right or bottom

Nope! Why?

The above output is perfectly matching with the Pivot Table report. But we can’t use Query formulas this way.

The reason is when you change the master/source data, that might cause errors in your formulas as it may require additional space to expand.

So what is the solution?

We must nest the formulas using Curly Brackets.

If you are not familiar with using Curly Brackets to make arrays, you may first go to my following tutorial to understand it – How to Use Curly Brackets to Create Arrays in Google Sheets.

I hope you are back with a clear picture of the use of Curly Brackets.

Here is the combined final formula.

={
   TRANSPOSE(QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A")),
   QUERY(A2:C16,"SELECT SUM(B) GROUP BY A LABEL SUM(B) 'GRAND TOTAL'");
   TRANSPOSE(QUERY(A2:C16,"SELECT SUM(B) GROUP BY C LABEL SUM(B) 'GRAND TOTAL'")),
   QUERY(A2:C16,"SELECT SUM(B) LABEL SUM(B) ''")
}

I’ve combined the above four formulas using Curly Brackets.

With curly brackets, we can combine data from two different ranges alongside or one under another.

I hope you enjoyed this tutorial and learned the usage of the QUERY function similar to the Pivot Table.

Want to lay your hand on my sample data and the above formulas? HERE is the link. You can make a copy and use it.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

22 COMMENTS

  1. Can you explain how to shift the grand total over when you have more than one grouping?

    Say I add a location field next to the date. How do I shift the grand total numbers over to line up with the columns?

    Thank you so much!

  2. How, in your example, can I sort the rows by desc the total number of vehicles?

    That’s the power of pivot tables. I’m not sure it can be easily replicated via the query function.

  3. This is amazing.

    Is there anything simple that can be added to the below formula to make sure the blank cells show as “0” instead of being blank?

    =QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A")

  4. It took me forever to figure out why you transposed the first array.

    I suppose only to make sure that the heading “vehicle number” appears at the top right; otherwise, you would get “Date of Filling.”

    Still, not sure why the column label appears and not the row.

  5. Thank you for the great example.

    However, I wonder if there is any advantage using Query function to pivot data comparing to making a pivot table.

  6. Hi, Thank you for explaining this nicely in the example.

    I am new to the Query function. However, from the example I understood and applied and created a report then I compared it with a pivot report just to match the figures.

    There were 75 records. The values of 73 records fully matched the Query result and pivot report. However, for 2 records the Query result is different than the Pivot result. I don’t know why query function displaying the wrong result for two records. Here is a formula

    =transpose(query($AB$4:$BI,"select AZ, sum(BI) where (BI > 0) group by AZ pivot AD"))

    Can you look at the formula? Maybe something needs to be added here.

  7. First of all the above explanation was really helpful.

    The only question I have is what if I used the order by clause to order the rows or columns in a particular ascending or descending order, in that case, my grand total will not match and I couldn’t find a way to sort the grand total based on the same field as used in row and column.

    • Hi, Mayank,

      I have added a new tab to my shared sheet labeled as “SORTing Grand Total”

      In that, I have modified the column C (vehicle numbers) from number to text. So that I can better experiment with sorting.

      Suppose I have sorted the vehicle number by descending order. The formula would be like this.

      See cell E3.

      =TRANSPOSE(QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A ORDER BY C DESC"))

      In the bottom grand total you must include the same sorting order.

      See cell E12.

      =transpose(QUERY(QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C ORDER BY C DESC LABEL SUM(B) 'GRAND TOTAL'"),"Select Col2"))

      Other two formulas (grand total on the right side and right side bottom) will remain the same.

      Best,

LEAVE A REPLY

Please enter your comment!
Please enter your name here