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.
8:11
13:15
I have also selected the following columns and grouped them
B:D
F:H
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.
Automated 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.
Warning
You can’t Undo the Auto Outline feature – you have to manually take out the grouping.
If someone has hidden the first row in a sheet, you may have problems getting it back because you cannot select the row before it. In this case, select cell A1 as explained in How to unhide top rows in Excel and then unhide the row as usual, for example by pressing Ctrl + Shift + 9 . If none of the above tips has worked for you, there is a chance that the hidden rows are a result of filtering. In this case, clear the filters, as explained in How to remove filter in Excel .