Excel Group Ungroup tool

How to use the Excel Group Ungroup tool to hide and unhide rows and columns in a safe way

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.

Related Links: Filter Data into groupsGroup Data via Pivot Tables

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)

1_Group Ungroup Toolbar
Where to find the Excel Group Ungroup tool

This allows you to automatically group any series of rows and/ or columns. When you use this function your spreadsheet will look like this.

2_Group Ungroup View
What the Excel Group Ungroup Tool looks like on a spreadsheet

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,
  • Group
  • 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,
  • 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
  • Group,
  • UNGROUP,
  • 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