How to use the Excel Group Ungroup tool to hide and unhide rows and columns in a safe way.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
The Excel Group Ungroup Tool
The Excel Group Ungroup Tool allows you to hide and unhide selections of rows or columns in a safer way then the traditional hide and unhide.
Under the DATA tab as part of the Outline box you have the Group and Ungroup function.
(For Excel 2003 you use DATA/ GROUP AND OUTLINE/GROUP)
This allows you to automatically group any series of rows and/ or columns. When you use this function your spreadsheet will look like this.
The box with the ‘-‘ can now be clicked on and it will close. Also, the numbers at the top (1 and 2) in this case can be clicked on to either open or close all the relevant groupings in the spreadsheet.
NOTE: If you want the ‘+’ and ‘-‘ to appear above the selection choose the expand button shown on the blog link.
For more details watch the Excel Group Ungroup video tutorial.
Excel Video Tutorial Transcripts
The Excel GROUP UNGROUP tool provides an easy and transparent way to hide and unhide rows and columns
In this example we have some data about Sales People, the Date of their sales and what they have sold. What we’d like to do is hide some of this information so that we can look at the subtotals only. An easy way to do this is to use the GROUP and UNGROUP feature. So what you can do is;
- highlight what you want to be hidden
- click on Data,
- and then GROUP
- and what you’ll see is it sets it up in this way so that by pushing the little marker here, you can quickly hide and unhide this information
We can do the same to the rest of the report, so for example for this section here, you can similarly do it to columns so let’s say:
- we want to hide this section
- we can go Data,
- GROUP and we can hide it
So now suddenly you have a quick short report, which you can easily expanded on by the push of a button.
Another useful feature are these little buttons up here, which by pushing them you can easily open or close all the various groupings, so
- if we push 2, you’ll see all the groupings open up
- and if we push 1, they all close down,
This is extremely useful when you have grouped a number of rows or columns and you want to open or close them all at the same time. To remove the groupings it’s quite simple
- just open everything up,
- and then you can highlight either the section you want to UNGROUP, or the whole sheet,
- go Data
- it may ask you whether you want to do the rows or the columns, in this case we’re going to do the rows first
- say Ok, and you’ll see the groupings get removed,
- and similarly we do the same with the columns,
And now you’re back to the normal sheet you had in the beginning