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. Note the warning below.

Auto Outline appliedWarning

You can’t Undo the Auto Outline feature – you have to manually take out the grouping.

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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Quickly and easily hiding and unhiding rows and columns in Excel

  1. 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 .