Hiding rows and columns is a common process in spreadsheets. Excel’s grouping feature can make the process easier and make it more apparent when rows or columns have been hidden.
Grouping inserts small icons around the Excel grid to allow you to hide and unhide rows and columns quickly and easily. These icons also identify that rows/column have been, or could be, hidden. The Group icon is on the Data Ribbon tab.
There are two keyboard shortcuts to speed up the process of applying or removing grouping.
To add grouping you select the range and press Shift + Alt + right arrow.
To remove grouping select the range and press Shift + Alt + left arrow.
See the image below that I will use to demonstrate how to use grouping. I’ve attached the file at the bottom of the blog post.
Select the rows 2 to 6 and press Shift + Alt + right arrow. You select the rows you want to hide. This will add icons to the left of the grid.
Clicking the small minus sign to the left of row 7 will hide the rows above.
The icon will change to a plus sign to allow you to quickly unhide the rows.
After you have applied a grouping using Shift + Alt + right arrow you can then use the F4 key (repeat) to apply other groupings. I have selected the following rows and grouped them.
I have also selected the following columns and grouped them
The result below is shown after I have used the minus signs to hide the rows and columns.
Note the small numbers in the top left corner. These allow you to hide and unhide all the groupings at similar levels at once. This is especially useful if you’ve created a hierarchy of grouping.
If you have an existing table that has SUM or SUBTOTAL formula in it you can use Excel’s Auto Outline feature to add grouping based on those totals.
Click inside the table involved – it works best if you have no blank rows or columns – click the Data Ribbon tab click the Group icon drop down and select Auto Outline.
All the work is done for you in an instant. I removed the previous grouping and used Auto Outline on the example table – see image below.
Note the extra grouping level for rows for Total Revenue. Note the warning below.
You can’t user Undo with the Auto Outline feature – you have to manually take out the grouping.