When working with spreadsheets of data in Google Sheets, you’re often switching between different levels of information, and you might find yourself wishing you could control the visibility of data you don’t need so that you can make the presentation clearer. There is a way to do exactly this, by grouping rows and columns so you can easily collapse and hide them or expand them into view.
Grouping Rows
In the example below and in this linked example Sheet, we have individual sales data for a range of products, with quarterly subtotals, and an annual total. To see the quarterly sales information more clearly, we would like to hide the data for the individual products.
To do this, we’ll create groups for the data we don’t need to see. Begin by selecting some rows that you would like to hide.
With the rows selected, right-click anywhere inside the selection and select ‘Group Rows 2 – 9’ (or whatever row numbers you have selected) from the menu.
This will create a group for the selected rows, which you will see depicted by a thin bracket on the left of the selected rows, along with a small button with a minus sign inside it. This button lets you collapse or expand the group.
If you click the minus button, the group of rows will be collapsed and hidden from view. The button remains visible (this time with a plus sign) so that you can click on it again to expand the rows.
Repeat these steps for each of the other quarterly product data so that you end up with only the quarterly sales figures showing.
Now we’re showing only quarterly sales figures, and the data is much easier to read!
Step-by-Step Video
Layered Groups
Let’s say we want to be able to show only annual sales figures as well. We can create another group, alongside the groups we just created, to toggle on and off everything except the annual sales data.
Select all of the rows corresponding to individual product data and quarterly sales figures. Do not include the row with the annual sales figures at the end, or the header row at the top.
With the rows selected, right-click anywhere inside the selection and select ‘Group Rows 2 – 31’ (or whatever row numbers you have selected) from the menu.
This will create a new group that allows us to collapse all the quarterly sales data and only show annual sales data, alongside the groups that we created before.
Now if you click the button for the new group, only the annual sales figures will be showing.
Grouping Columns
It is also possible to create groups for columns, using the same steps as we used for rows. Select the columns that you would like to hide.
Right click anywhere inside the selection and click ‘Group Columns C – D’ (or whatever columns you have selected) from the menu.
This creates a group for the columns, depicted above them by a thin bracket and a toggle button, which we can click to collapse or expand the columns as we did with the rows.
Group Options
Right clicking on the group bracket brings up a menu that gives us some options about how to display the group. For example, if you would like the toggle button to appear at the bottom of the group, right click on the group bracket on the left of the rows, and click ‘Move +/- button to the bottom’ from the menu.
You can see there are several other useful options on the menu that give you control over how your data is being displayed.
That’s it!
Grouping rows and columns in Google Sheets gives you control over what is displaying in your spreadsheet, giving you the ability to highlight different levels of information at different times without the view being cluttered with data you don’t need to see. Hope this tutorial has been useful for you and your business!
