Quickly Hide Rows & Columns with Groups and Outlines in Excel

Bottom Line: Learn how to use the Groups and Outline features of Excel to quickly hide and unhide rows and columns.  This makes it easy to collapse and expand details of summary reports.

Skill Level: Beginner

Video Tutorial

Watch on Youtube

Download the Excel File

The Excel file that I used in the video tutorial can be downloaded here:

Grouping Rows or Columns

Groups and outlines allow you to quickly hide and unhide rows or columns in an Excel spreadsheet.

Outline example showing groups that you can expand or contract

The Groups feature creates row and column groupings in the Headings section of the worksheet. Each group can be expanded or collapsed with the click of a button.

Here's an image that shows the report with collapsed rows:

Example of collapsed rows

And here's how it looks when the rows are expanded:

Example of expanded rows

Why Groups? Make Your Reports Easier to Read

Groups are a fast way to manage rows and columns that you hide and unhide frequently.

If your reports have a lot of rows/columns of data, they can easily start to feel overwhelming to some readers. Groups make it much easier for users of your worksheets to navigate your summary reports and only view detail rows/columns as needed.

How to Create Groups

To create a group, simply select the rows or columns that you want to group together. Then:

  1. Go to the Data tab.
  2. Go to the Outline drop-down.
  3. Click on the Group button.
Steps for creating outlined groups in Excel
Click to enlarge

The keyboard shortcut for creating groups is Shift + Alt + .

Outline Levels

When you create groups, you'll notice that in the upper left-hand corner of the spreadsheet some boxed numbers will appear. These indicate the levels of the outline.

Outline levels explained

Clicking the Number 1 box will collapse all of the groups. Clicking it again will expand the groups at the first level.

If you have groups within groups then you will see additional numbered levels. Clicking each number will allow you to expand and collapse the data to the level you want. As you click subsequent numbers, you'll be able to see more and more detail of the outline.

Removing Groups

To remove groups, you follow almost the same process as before. Start by select the rows or columns that you want to ungroup.

  1. Go to the Data tab.
  2. Go to the Outline drop-down.
  3. Click on the Ungroup button.
Steps for removing outlined groups in Excel
Click to enlarge

The keyboard shortcut for removing groups is Shift + Alt + .

If you are interested in learning more keyboard shortcuts, you can do so here: 5 Keyboard Shortcuts for Rows and Columns in Excel.

If you want to remove all of the grouping in all rows and columns simultaneously, instead of clicking on the top half of the Ungroup button, click on the bottom half, which shows you a drop-down menu. Select Clear Outline from that menu and all of the groupings you've made will disappear.

Clear Outline feature

Using Auto Outline

Excel has a feature that will group rows and columns based on information it pulls from formulas. For example if you use a SUM formula, it will group all of the rows that are added together for that sum, because it assumes the data in those cells is similar enough to group together.

Before you use the Auto Outline, you'll want to make sure you clear any groups that you have by using the Clear Outline feature mentioned above. Then go to the Data tab, the Outline drop-down, the Group drop-down, and select Auto Outline.

Auto outline in Excel

I've found that the Auto Outline feature tends toward overkill in the number of groupings it usually makes. So if you use Auto Outline, you may want to ungroup some of the groups if it feels like too much.

Another option to pare down the number of groupings from Auto Outline is to just select a set of rows or columns before running Auto Outline. Then it doesn't outline the entire sheet, just the parts that you are interested in grouping.

Checkout the video above for more on Auto Outline.

The Subtotal Feature

One other option available to you in the Outline drop-down menu is Subtotal.

The Subtotal feature button in menu

In addition to grouping together similar data, the Subtotal feature also inserts subtotals for those data sets in whatever columns you want them to appear.

Subtotal feature example

I explain more about the Subtotal feature in this tutorial: How to Use the Subtotal Feature and the SUBTOTAL Function in Excel.

Conclusion

Grouping and outlining makes it easier to navigate your reports and sheets. These features are especially helpful if you have other users who will be accessing them.

Here are the keyboard shortcuts again. These are two that I use frequently and recommend committing to memory.

Group selected rows/columns: Shift + Alt +

Ungroup selected rows/columns: Shift + Alt +

Bonus: Press Shift + Space to select entire rows or Ctrl + Space to select entire columns before grouping/ungrouping to prevent the Group window from coming up first. See this post on 5 Keyboard Shortcuts for Rows and Columns in Excel to learn more.

Please leave a comment below with any questions or suggestions. Thanks!

11 comments

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

  • Great post! I have enjoyed adding groupings to sheets for some time now to make them easier to manage, and the shortcuts you gave are very helpful.

    I also use the Alt, A, H combination to hide specific groupings. It’s pretty easy to remember (H is for hide). Alt, A, J can be used to unhide. You have any cell in the grouping selected and use these shortcuts to hide/unhide the entire grouping, which is the advantage over the Ctrl (+ Shift) + 9 and Ctrl (+ Shift) + 0 shortcuts in this scenario. Again, if you first press Shift + Space or Ctrl + Space to select rows or columns, you won’t have to deal with the ‘Group’ window when using these hiding and unhiding key combinations.

    The other related feature I frequently use is to go into the dialogue on the ‘Outline’ group to change the ‘Summary rows below detail’ and ‘Summary columns to right of detail’ options. Users frequently prefer to see the expanding and contracting boxes above and to the left of the detail (in the case of a header). I actually sometimes feel like this should probably be the default behavior of the grouping in Excel. But, I understand why it isn’t, such as in the case of the subtotal feature.

  • I have found Subtotal very useful. Is there a way to have Subtotal “count” the number of rows and show that count on each subtotal row? Thanks.

  • Hi! Any info on how to open the collapsed rows faster than clicking one plus at a time? I have a worksheet in which I filtered one column and it collapses everything and I have to unclick each plus to run a filter on another column.

  • When using a shared excel (office 365) the group fold and open is reflected to everyone viewing or editing the file . the file changes while working – making it very hard to work with . is there any way to prevent this (working offline will do it but then sync issues arise) . Thanks!

  • Is there a way to group then hide tabs such that when you click the “master” tab it unhides a selected number of hidden tabs assigned to the “master”?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter