Excel's Grouping is a very useful tool to display the data in a compact view. The feature allows you to hide the less prior data to let the major values remain visible. In this article, we are going to show how to group columns in Excel.
The Grouping feature, also known as "Outline", is a way of hiding columns or rows with detail data to reveal the relevant ones. Although the feature applies the regular hide/show column or rows features, the outline controls remove manual column/row selecting process.
You can find the feature in the Outline section under the Data tab of the Ribbon.
To learn about the Subtotal feature in the Outline section you can check out our How to use subtotal feature in Excel tutorial.
You can group columns in two ways:
- By using Auto Outline
To group columns manually, you need to select cells or columns you want to group and click the Group button in the Outline section.
Alternatively, you can use Shift + Alt + Right shortcut. Beware though, each time you apply the shortcut, Excel will add a new outline level. To roll it back either use Shift + Alt + Left shortcut or Ungroup button.
Unfortunately, outlining feature does not support multiple range selections.
If you select a range instead of columns, Excel will ask you which aspect you want to group:
Auto Outline feature
The Auto Outline feature may group your data perfectly with a single-click action. However, the feature doesn't provide the expected results always. So, be ready to use the manual approach.
To make Auto Outline work, you need to provide at least these assumptions:
- Data should be in a tabular form.
- There should not be blank columns.
- There should be a summary column with formulas after each group of detail columns.
Once the data is OK, select any cell in your data and click Auto Outline in Data > Outline > Group menu.
Be warned that Excel can outline the data in rows if you have a total row as well. Here how our sample data is grouped:
You can ungroup your data in a similar way. Select the cells or columns you want to ungroup and either follow the path Data > Outline > Ungroup or press Shift + Alt + Left key combination.
Alternatively, you can use the Clear Outline command in Ungroup menu to remove all groupings.