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

0
90
QUERY Function Similar to Pivot Table in Google Sheets

Honestly, I fell in love with Google Sheets QUERY function as it’s 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’ve a realistic data of Diesel consumption of certain number of heavy vehicles with me. Only part of that data we are going to use for our tutorial purpose here. From this data, I’m going to make a vehicle wise summary of diesel consumption using Pivot Table as well as Query Function.

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

The same Pivot Table Report we can generate in Google Sheets using QUERY Formula and that is what we are going to learn! The below report I’ve generated using Google Sheets 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 tough task to make such a report in Google Sheets. With Pivot Table, it’s just a child’s play. But using Query, there is something new 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 easily. It’s a tricky part and that is what we are going to learn here.

We have the sample data with us. From that let us see how to make a summary using Pivot Table first. Then we will follow that report.

Steps to Summarise Data Using Pivot Table

Our sample data is spread across A2:C16. See the first screenshot above. To create a pivot table summary, first select this data range and then go to Data Menu > Pivot Table.

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

pivot table report editor settings - diesel consumption

To learn more about Pivot Table you can refer our related tutorials below.

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

Also there is one more tutorial regarding Pivot Table that also may interesting to you.

Create Age Analysis Report Using Google Sheet Pivot Table

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

The Use of QUERY Function Similar to Pivot Table

Let’s learn the use of QUERY function similar to Pivot Table. See the screenshot below. You can see four coloured squares on the image. Each squares represents Query formulas. With one QUERY formula we can summarise our data similar to Pivot Table. But in order to get the Grand Total on the right hand side and also on the bottom of the report, we require three more Query formulas. So there are total four Query formulas and later we will nest them all with Array.

Query formulas to exactly match Pivot Table

I will explain you the same with formulas and sample screenshots.

Four formulas to make you possible to use QUERY Function Similar to Pivot Table in Google Sheets.

1. QUERY formula # 1 for Summary Report.

2. QUERY formula # 2 for Grand Total on the Bottom Side of the Summary Report.

3. QUERY formula # 3 for Grand Total on the Right Hand Side of the Summary Report.

4. QUERY formula # 4 for Grand Total on the Right Side Bottom Corner (value 1918 above) of the Summary Report.

If you are not familiar with using Google Sheets Query Formula, please check our below tutorial.

Learn Query Function with Examples in Google Sheets

1. QUERY formula # 1 for Pivot Table Like Summary Report

=query(A2:C16,”Select C,sum(B) group by C pivot A”)

In the above formula, Column C contain the vehicle numbers. We need to summarise or group the data based on vehicle numbers. If you check the Pivot Table Report on the top of this page, you can understand it.

We should Sum column B as it contain filled diesel quantity in Gallon. 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 should use 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

See, now this table using Query formula is matching exactly 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.

2. QUERY formula # 2 for Grand Total on the Bottom Side

Here is the Query Formula to Get Grand Total on the bottom side of the report.

=transpose(query(A2:C16,”Select sum(B) group by C label sum(B) ‘Grand Total'”))

The above query formula will return the below result.

Query Grand Total on the Bottom

This is vehicle number wise summary. So we grouped the sum by Column C. Here also we have used Transpose function. Why? Because, this is vehicle number wise summary and on our above generated report, vehicle numbers are appearing on columns. Later we will join this Query formula with the first formula above.

3. QUERY formula # 3 for Grand Total on the Right Hand Side of Pivot Table Report.

From the report which we have generated by the first formula or the Pivot Table report itself on the top, we can understand that on the right hand side of the report is the date wise Grand Total. So this query formula will do that part and no transpose command this time.

=query(A2:C16,”Select Sum(B) Group by A label Sum(B) ‘Grand Total'”)

4. QUERY formula # 4 for Grand Total on the Right Side Bottom Corner (value 1918 above) of the Report

From the title itself you can understand what is the purpose of this formula. It’s just to sum Colum B, no vehicle wise or date wise summary here.

=query(A2:C16,”Select sum(B) label Sum(B) ””)

QUERY Function Similar to Pivot Table – Final Part 

We have four formulas in our hand. 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

No! Why?

The above output is perfectly matching with Pivot Table report. But we can’t use Query formulas this way. The reason, when you change the master data, that will cause errors in your formulas. Array formulas require sufficient rows or columns to expand.

So what is the solution? We should nest the formulas with Array (Curly Brackets). If you are not familiar with using Curly Brackets to make Array, first go to our below tutorial to understand the use of Array.

How to Use Curly Brackets to Create Arrays in Google Sheets

Hope you are back with a clear picture about the use of Array. Then 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 together using Curly Brackets. With curly brackets; we can combine data from two different ranges along side or one under another.

Hope you enjoyed this tutorial and learned the usage of QUERY function similar to Pivot Table. If you want to lay your hand on my sample data and above formulas, HERE is the link. Please make a copy and use.

LEAVE A REPLY

Please enter your comment!
Please enter your name here