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)
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”.
Have you ever built an app based on Excel?
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”.
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”.
Have you ever built an app based on Excel?
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:
- Why I can’t add groupings to rows or columns in Excel?
- You can further define the direction of groupings: Do you want to set the grouping direction above or left of data in Excel?
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
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
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’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Thanks for the tutorial and VBA samples. Do you have any recommendations on speeding up a file during expand all or collapse all on groups? I have a spreadsheet with 835 columns that are grouped in sections of roughly 5-20 columns. I can expand/collapse individual sections without a problem, but when I try to expand all or collapse all it takes forever and the file appears to the user that it is locked up. Any ideas on how to speed it up?
Umm, usually the VBA code and the add-in are quite fast. Is there any content in the columns to the right of the 835th column? And how many rows are you using?
Maybe just switching to manual calculation might help?
i deleted the grouped rows but the space is still there and is blank. I only need the data thats visable how do i copy and past without capturing the grouped rows now with no data?
Hello! Is there any possibility of still keep some rows unhide when pressing ungroup (press +). I do not want to unhide everything…