If you have a dataset that contains different segments, like regions, date periods, products or any other category, then it can be useful to use Excel’s grouping options so that you can dynamically hide or unhide different parts of your dataset.
Download File
If you would like to follow along download the attachment below.
Example Dataset
The dataset used in this walk through of the Group and Ungroup features contains sales by cities in various countries in Europe in the rows, and months separated into quarters in the columns.
In order to use groupings it is best practice to ensure that your data is consistent and to use clear labels for grouped sections.
Where to Find Group
The Group option can be found in the Data tab.
You can also use the keyboard shortcut Shift + Alt + Right Arrow to group data.
You can Group Data in either columns or rows.
Group Columns
To group columns highlight the columns you want grouped.
Press Shift + Alt + Right Arrow to group data.
This will add a collapse/expand button (a small box with a minus sign when expanded, and a plus sign when collapsed), as well as a number showing the level of grouping.
You can group multiple sets of columns by repeating the same process of highlighting the columns you want grouped and pressing Alt + Shift + Right Arrow to group data.
If you do want to group multiple sets of columns you will need to make sure there is a gap between them, otherwise it will create one contiguous group rather than separate groups.
Multiple Groupings
It is possible to create layers of groupings that work with each other. Excel allows up to a maximum of eight levels of grouping.
So far we have grouped the quarters within the year, but we can also group the whole year above the quarters.
To do this select the whole range that you want grouped and press Shift + Alt + Right Arrow to group data.
This will add a grouping above your previous groupings.
Clicking the number 2 on the side will collapse the grouped quarters. To open any of the grouped quarters you can click the plus sign. To open all quarters click the number 3.
Clicking the number 1 on the side will collapse the grouped Year. Clicking the plus sign or the number 2 will show you the grouped quarters, clicking the number 3 will expand all grouped areas showing the months in the quarters.
Group Rows
Grouping rows works in the same way as columns.
Start by highlighting the rows that you want to group and then press Shift + Alt + Right Arrow.
This will group the highlighted section adding a grouping line and numbered groupings.
Like with column groupings if you want multiple groupings you will need to make sure there is a gap between groups.
You can also group above grouped rows by highlighting an area that already has groupings.
This adds another number to the grouping and another grouping line.
Auto Outline
Depending on how your data is structured you can use the Auto Outline feature to automatically add groupings.
To find Auto Outline go to the Data tab and click the dropdown arrow next to Group.
Once selected this will look for any total columns or rows and apply groupings to those areas. It can be a quick and easy way to apply groupings to your dataset but depending on how your dataset is set up it may not return the exact groupings that you want so always check that Auto Outline has applied groupings correctly.
How to Ungroup Data in Excel
Â
Where to Find Ungroup
The Ungroup option can be found in the Data tab.
You can also use the keyboard shortcut Shift + Alt + Left Arrow to ungroup data.
You can ungroup data in either columns or rows.
Ungrouping Columns
To ungroup columns you can highlight the columns that you want ungrouped and then press Shift +Â Alt + Left Arrow.
If you select a whole area to remove columns it will remove from the first grouping downwards.
In the below example where the whole grouped area of columns B to R are highlighted, pressing Shift + Alt + Left Arrow will first remove grouping one, pressing Shift + Alt + Left Arrow again will remove grouping 2 and 3.
Ungrouping Rows
The same applies to ungrouping rows.
Highlight the rows you want removed and then press Shift + Alt + Left Arrow.
To remove all grouped rows select the whole range.
Pressing Shift + Alt + Left Arrow will remove the highest level of grouping first, and then the second and third level.
Clear Outline
If you want to remove all groupings in one go you can use the Clear Outline feature.
This is located in the drop down from Ungroup in the Data tab.
Hide Groupings
As Excel allows up to eight levels of groupings adding multiple groupings may reduce the size of the viewable cells on a worksheet.
If you want to hide the grouping bars and numbers but not remove the groupings or the impact of any applied groupings you can press Ctrl + 8.
Conclusion
Grouping data can give you the flexibility to hide or display individual parts of a dataset.
Data can be grouped or ungrouped by going to the relevant sections in the Data tab, or you can use shortcuts and press Shift + Alt + Right Arrow to group or Shift + Alt + Left Arrow to ungroup.
You can group rows or columns up to eight times, and remove individual groupings or all groupings.
Adding groupings will reduce the number of cells that you can see on screen as it will add grouping bars and numbers, but you can hide and unhide these by pressing Ctrl + 8.
Comments