Grouping vs hiding rows or columns in Excel

Most users are familiar with hiding rows or columns, but may not have ventured into grouping rows or columns.

What is the Group Function and How to Activate it?

The group function basically “ties a range (columns or rows, can’t be both) together, and allows you to collapse them (and expand them later), showing only the last row or column”. It is in the “Data” Ribbon, on the right in Excel 2007 and 2010.


After grouping the rows or columns, you can collaspe (basically, hide) them by pressing the “square minus” box on the left. To expand (basicaly, hide), press the “square plus”.

Shortcut:
Excel 2003: alt d g g
Excel 2007 or 2010: shift+alt+right (the Excel 2003 shortcut also works)

Example of a group of rows

This is an example of a group of rows. The square buttons on the left and square brackets indicate which rows form the same group.

Uses and Advantages of Grouping Cells

The original purpose of grouping cells to group and hide details of various parts of big file with a data hierarchy, such as a budget. See below. The user can group and collapse (hide) all the details of each budget category all with a click of a button. This type of one click convenience is also why it is better than the normal hide function in many ways. (Hide needs 3 clicks for everything. On top of that, you have to make sure to highlight the correct rows or columns each time, where as grouping is just a click, and the same rows or columns are collapsed each time.) So the first advantage, and the biggest one, of using group is that it saves time and avoid errors.

Example hidden rows in Exxcel.

Second advantage is that the group function can have multiple levels of hierarchies – something not possible with the normal hide or unhide function. Basically, the user can have nested groups, where they can hide and unhide the top group and not affect the subsequent bottom groups. See below for an example.

Example of nested group of rows in Excel

Third advantage is visibility. For fatigue users or when the user zoomed out on the spreadsheet, it is sometimes hard to see which row or columns are hidden. Group however, due to the expand “square plus” button, cues the user that there is more data underneath.

So, there it is. For those who hide / unhide cells for various purposes, such as formatting or just hiding tedious and unimportant details, I would definitely use group instead.

The One Drawback of Grouping Cells

One drawback is if you are dealing with multiple worksheets, and want to group the same rows / columns on multiple worksheets at the same time, it is basically not possible. In fact, the entire grouping / ungroup function does not work. Excel just won’t let you do it. You can, however, hide and unhide rows for multiple worksheets all at once. That is the one major I have encountered for grouping data instead of hiding it.

2 thoughts on “Grouping vs hiding rows or columns in Excel

  1. uli says:

    Why sometimes is not possible to group cells? Is there a specific format that we must follow? I have a list of 452 employees that I want to group and my problem is that do multiple levels of grouping, when I’m grouping those, I’m unable to group some ranges of cells.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>