Quickly and easily hiding and unhiding rows and columns in Excel

Excel's Grouping technique

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.

Table

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.

Select rows to group

Clicking the small minus sign to the left of row 7 will hide the rows above.

Grouping Applied

The icon will change to a plus sign to allow you to quickly unhide the rows.

 Rows hidden

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.

All grouping applied

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.

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.

Auto Outline applied

Grouping example file

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *