I have a confession to make – I miss the Excel-like grouping and subtotal functionality in Google Sheets. But fear not, my friends! I’ve got a workaround that will help us replicate this feature in Google Sheets while we wait for Google to implement it officially.
Grouping and Subtotal in Spreadsheets
In Excel, there’s a built-in feature called Subtotal that allows users to total several rows of related data by automatically inserting subtotals. It’s a handy tool that gives you the freedom to choose where the subtotal appears and the aggregation function to use.
Let’s dive into Excel first to get a clear picture of what we’re talking about.
Sample Data for Subtotal and Grouping in Google Sheets and Excel
Before we proceed, let me show you the sample data I’ll be using for our examples. It’s a list of the world’s 20 largest apple-producing countries and their production in metric tons (MT). Please note that the data is for illustration purposes only.
Excel Grouping and Subtotal Example
Now, let’s focus on Excel to understand the concept of row grouping and subtotal.
- Select the data range A1:C21.
- Go to the Data menu and click on Subtotal in the Outline group.
- Follow the settings provided and click OK.
That’s it! You can now collapse the row grouping, and it will look neat and organized.
The built-in grouping and subtotal features in Excel make it a breeze to implement. But what about Google Sheets?
Grouping and Subtotal in Google Sheets
Now, let me guide you on how to achieve grouping and subtotaling in Google Sheets using a workaround.
I’ll be using the SUMIF function and the newly added row grouping feature in Google Sheets.
First, let’s see how this workaround works.
Steps involved in Subtotal and Grouping in Google Sheets
- Insert new rows below each group. You can refer to the image above to see the newly inserted rows.
- Insert a helper column after column B and enter the appropriate text strings in the designated rows.
- Apply the following SUMIF array formula in cell E3:
ArrayFormula(if(len(A2:A),sumif(A2:A&" Total",C3:C,D2:D),)) - Format the subtotal cells to remove unwanted zeroes using conditional formatting.
- Group the rows by selecting the desired rows and applying the grouping.
Voila! You have now successfully replicated the grouping and subtotaling feature in Google Sheets.
If you want to place the subtotal at the first or last row of each group without the manual steps, you can use my AT_EACH_CHANGE named function. This custom function allows you to insert different aggregation functions such as average, count, min, max, etc., in addition to the sum.
I hope that Google will soon include a built-in Subtotal data menu option in Sheets. Until then, this workaround will serve us well.
That’s all for now! If you’re interested, you can check out the related resources listed below for more Google Sheets tips and tricks.
Conclusion
Okay, I admit this workaround isn’t perfect. It requires some row inserting and an additional helper column. However, the output somewhat mimics the Excel Subtotal feature, which is a win in my book.
I remain hopeful that Google will hear our pleas and include a built-in Subtotal data menu option in Sheets in the near future. Until then, let’s make the most of what we have.
Keep exploring and mastering Google Sheets, my friends!
Related Resources:
- SUBTOTAL Function in Google Sheets: A Complete Guide
- Formula to Insert Group Total Rows in Google Sheets
- Blank Cell between Two Totals in Query Total Row in Google Sheets
- Inserting Subtotal Rows in a Query Table in Google Sheets
- How to Subtotal Up to the First Blank Cell in a Column in Google Sheets
