How To Hide And Group Rows And Columns In Excel

You have an Excel table with some unimportant rows, but you don’t want to delete them. In such case, you might want to “hide” them. There are two options of hiding rows (and columns): Either right-click on the row (or column) number and click on “Hide” or use the grouping function in order to create a group.

The Hide function (not recommended)

unhide all rows, unhide, hide, rows, columns, excel, all rows, all columns

Many people love the “Hide” function for hiding rows or columns, because it is very easy to use: (the numbers are corresponding with the image)

Mark the row(s) or column(s) that you want to hide.
Right-click on the row number or column letter and click on “Hide”.

Unhide (all) hidden rows and columns

Unfortunately, hiding rows and columns has one big disadvantage: Hidden rows or columns are very hard to be seen. It’s only symbolized by a thin double line between the row or column number (3). A better way for hiding rows or columns is the Group function (4).

So, how to unhide all hidden rows?

Select the whole area in which you suspect hidden rows. Alternatively, select the whole worksheet in the top left corner. Now right-click on left-hand side with the row numbers (5) and click on “Unhide”.

Unhide all hidden rows and columns on all sheets at once

unhide, all, worksheets, rows, columns, excel — Unhide all columns on all worksheets at once.

Do you want to show all hidden rows (and columns) on all the worksheets in your Excel file at the same time? It works almost the same way like for one worksheet except that you select all worksheets before.

Select all worksheets with hidden rows or columns.
Select all columns, either by clicking on the top-left corner or by pressing Ctrl + A on the keyboard.
Right-click on any column header (the letters A, B, C on top of each column) if you want to unhide columns. Right-click on any row number on the left-hand side if you want to unhide rows.
Click on “Unhide”.

The Grouping function (recommended)

Grouping rows (or columns) has a big advantage: A little plus sign is shown, when you group rows. Because of that you can immediately see which rows are hidden. The hide function on the other hand only shows a “double” line which is not obvious and you could miss it easily.

Follow these steps for applying grouping:

Select the row or column you want to group.
Click on “Group” on the Data ribbon. Alternatively, use the keyboard shortcut Alt + Shift + Arrow right for setting a Grouping or Alt + Shift + Arrow left for removing a Grouping

Grouping has one more feature: It allows you to set up Grouping levels. Let’s say, rows 3 to 5 and 7 are on level 2 and rows 6 and 8 (saying Calculation 1 and 2 in above picture) are level 2. If you press the small 1 in the top left corner, all groups on level 1 will be shown (see number 3 on the above picture).

Please note: If you run into some problems applying, opening, closing or removing grouping, please refer to these articles:

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Learn more

Download Free Trial

Add more than 120 great features to Excel!

Expand or close all grouping on all worksheets simultaneously

In many cases you want to collapse all groupings or expand them. For example, before sending out a workbook, you might want to close all groups so that the workbook looks more “tidy” on the first impression. There are two methods for achieving this.

Method 1: Use a VBA macro for collapsing or expanding grouping

The following two VBA macros can either collapse all groupings or expand them.

Use these lines of code for collapsing everything to grouping level 1 (minimum).

Sub CollapsToMinimum()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
    Next ws
End Sub

If you want to expand to show all groupings, expand the row and column levels to number 8 like in the following VBA code.

Sub ExpandToMaximum()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
    Next ws
End Sub

Copy and paste this VBA code into a new module and press play. If you need assistance, please refer to this article.

Method 2: Use “Professor Excel Tools” for grouping, ungrouping, hiding and unhiding

hide, unhide, manager, rows, columns, add-in, excel, grouping — Hide or unhide rows and columns comfortably with Professor Excel Tools

If you got a large Excel file, you might want to expand or close all groups to the same level at the same time on more than on worksheet. Or expand them the maximum level or collapse them to the minimum level. For such task we created a function in our Excel add-in ‘Professor Excel Tools‘.

The function doesn’t only work on groupings, but also on hidden rows and columns:

Define the grouping level: Minimum, maximum or the number of the level.
Unhide all hidden rows or columns.
Choose if you want to apply this action on rows, columns or both.
Choose and which worksheets you want to use the hiding or unhiding action on: All worksheets, selected worksheets or the current worksheet only.

‘Professor Excel Tools’ is free to try. There are more than 120 other great functions included. You can download without any sign-up.

This function is included in our Excel Add-In ‘Professor Excel Tools’