Are you tired of manually calculating the percent distribution of the grand total in Google Sheets Query? Well, fret no more! In this article, I will show you how to achieve the same result as the Pivot Table ‘% of grand total’ using the Query formula.
Pivot Table Steps to Calculate Percent of Grand Total in Sheets
Before we dive into Query, let’s quickly go over the steps to calculate the percent of the grand total using a Pivot Table in Google Sheets. With just four simple steps, you can easily group your data and get the desired result.
- Select the data range you want to group.
- Go to the ‘Data’ menu and choose ‘Pivot table’.
- Follow the on-screen instructions to create the Pivot Table.
- Add the required fields and functions in the Pivot Table editor to calculate the ‘% of grand total’.
And voila! You have successfully calculated the percent of the grand total using a Pivot Table.
How to Calculate the Percent of Grand Total of Grouped Items Using a Query Formula
Now, let’s explore how you can calculate the percent of the grand total using a Query formula. If you’re new to Query, I recommend reading my Query function tutorial first for a better understanding.
To achieve the desired result, we need to utilize the ‘Group by’ Clause in Query. By grouping and summing the data, we can easily calculate the percent of the grand total. Here’s an example Query formula:
=query({B1:C5},"Select Col1, Sum(Col2) group by Col1")This formula will return the grouped item totals. However, we want to calculate the percent of the grand total, similar to the Pivot Table.
The percent distribution of the grand total can be calculated as follows:
- For the item “apple”: 250 / (250 + 200 + 250)
- For the item “mango”: 200 / (250 + 200 + 250)
- For the item “orange”: 250 / (250 + 200 + 250)
To get the second part of the calculation, we can use the SUM function. Here’s an updated Query formula:
=query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1")But wait! We’re not quite done yet.
To make the formula more robust, we need to add a few additional clauses and format the result as a percent.
Format Number to Percent in Query Result
To format the numbers in the Query result as a percent, you can manually select the numeric data range (e.g., I2:I) and format it using the ‘Format’ menu. However, if you prefer to avoid manual formatting, you can include the percent format within the Query formula. Here’s the updated formula:
=query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1 format Sum(Col2)/"&sum(C2:C5)&"'0.00%'")Adding Percent Grand Total Row to a Query Result
If you prefer to include a grand total percent row at the bottom of the Query result, you can use the following formula:
={query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1 format Sum(Col2)/"&sum(C2:C5)&"'0.00%'");"Grand Total",100%}By adding the string “Grand Total” and 100% within curly braces as an array, you can easily incorporate a grand total row into the Query result.
Finally, you can customize the labeling in cell I1 using the Label Clause in the Query formula. Here’s an example:
={query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1 label Sum(Col2)/"&sum(C2:C5)&"'SUM of Amount' format Sum(Col2)/"&sum(C2:C5)&"'0.00%'");"Grand Total",100%}To ensure that the formula covers infinite rows, you can modify it as follows:
={query({B1:C},"Select Col1, Sum(Col2)/"&sum(C2:C)&" where Col1 is not null group by Col1 label Sum(Col2)/"&sum(C2:C5)&"'SUM of Amount' format Sum(Col2)/"&sum(C2:C5)&"'0.00%'");"Grand Total",100%}And there you have it! With these adjustments, you can easily calculate the percent of the grand total in Google Sheets Query, even when dealing with grouped items.
I hope you found this article helpful. For more tips and tricks, visit Crawlan.com. Happy querying!
