See More About: , ,

Group and Ungroup Rows and Columns in Excel

by

Outlining and grouping rows or columns in Excel makes it easy to organize and declutter spreadsheets that have a ton of data on display. Say you have several rows with the sales figures by month and the last row is a summation of the data, rather than display all months you can group the data (months) and only display the row with the totals. This can be extremely useful when you are working with a spreadsheet that has multiple years of data, think of it, if you had 7 years of data by month – that would amount to 84 rows of months and 7 with totals …instead of dizzying someone with all that data you can group it and display the totals per year and show off your skills with Excel.

If you have any comments or questions please post them below.

Note: I used Excel 2007 when creating this tutorial.

Grouping Rows

I will show you the two methods that can be used to add comments to cells in Excel, one involves the right-click menu and the other the Office Ribbon menu.

group and outline rows in excela Group and Ungroup Rows and Columns in Excel

1. Select a set of rows that you wish to group together.

Note: You can do the same for columns.

group and outline rows in excel Group and Ungroup Rows and Columns in Excel

2. Click on the Data tab located in the Ribbon

3. Click on the Group button located in the Outline Group

4. Click on Group… from the menu.


group and outline rows in excel2 Group and Ungroup Rows and Columns in Excel

You will notice that Excel created a new margin with a set of dots next to each row, you will also notice that it created a small box with a minus sign indicating that the group is expanded.

a. Click on the minus sign (-) to collapse the group.

group and outline rows in excel3 Group and Ungroup Rows and Columns in Excel

a. When you clicked on the minus sign (-), it collapsed the group and changed it to a plus sign (+).

b. Notice how the margin has a 1 and 2. This indicates that there is a single level grouping. If you create additional groups within the one we just created, the margin would show a 1, 2, and 3. This helps in knowing the levels of grouping in a particular sheet. You can have up to 8 levels of grouping.

Ungrouping Rows

group and outline rows in excel4 Group and Ungroup Rows and Columns in Excel

1. Make sure you select the groups that you wish to ungroup, do this by selecting the rows or columns that are grouped.

2. Click on the Data tab located in the Ribbon.

3. Click on the Ungroup button located in the Outline Group

4. Click Ungroup… from the menu.

group and outline rows in excel5 Group and Ungroup Rows and Columns in Excel

a. You can Expand a group by clicking on the Show Detail button located in the Outline Group.

b. You can Collapse a group by clicking on the Hide Detail button located in the Outline Group.

See More About: , ,
Originally posted Jan 27, 2010

We close comments after 30 days on GilsMethod.com and ask that you post questions and issues on our Answers website.

This helps us better serve you by allowing us to keep track of unanswered questions that are usually hidden in long comment threads.

Answers.GilsMethod.com is a community-powered support website where you will be able to post questions, and other users including us will respond with expert advice.

So if you are having trouble with your PC, Mac, iPad, iPhone, or other piece of technology, head over to Answers.GilsMethod.com and ask away.

  • DDright

    Very helpful Gil. Thanks. The question I’m adding is, now that I’ve grouped rows, how do I dictate which one gets shown? I’m essentially “locking” every two rows together in a sheet so it’s sortable and those rows stay together. I’m grouping them, but then the bottom row is the one that shows up, and I want the top row to be the one that’s visible. (Or, if there’s another way to do this, do share!)

    • http://www.gilsmethod.com Gilberto J Perera

      I’m a bit confused, are you trying to hide one of the rows? Why are you
      grouping them if it’s only two rows? Maybe if I understand exactly what you
      are doing I can provide some advice. Thanks.

      Like the GilsMethod.com Facebook page for a
      chance to win an iPod Touch and other prizes.

    • Miss Rina

      I guess My quesitons would be equal with this, but in more detail – I am grouping an entire month of data with 90 rows, and it keeps only showing the bottom row as my Header Row.

      But mine is at the top… not the bottom, how do I can switch witch is the one for the title row…

    • http://www.gilsmethod.com Gilberto J Perera

      I’m a bit confused as to what you are trying to do, but for illustrative
      purposes lets use the following example.

      Row 1-90

      If you select rows 2-90 (assuming row one is your header) and you group the
      row, the only rows you will see are rows 1 and 91. So if you want to hide
      row 90, make sure to include it in the grouping. Let me know if this makes
      sense. Thanks.

  • http://pulse.yahoo.com/_YO4V2AGMFTRX2PK7KTJD7O2CEA kathy n

    Is there anyway to ungroup the same column (ex. A) over multiple tabs in Excel 2007?

    • http://www.gilsmethod.com Gilberto J Perera

      Kathy,

      Unfortunately that is not possible. You can only ungroup columns or rows from the tab you are working on.

  • Alissa

    Is there a way to expand a group without clicking on the + sign? What I'd like to do is click on the the row above a group, and have the group expand. For instance, these are my rows:

    Section I
    a.
    b.
    c.

    The group contains a., b., and c. I want to click on 'Section I' and expand the group below it.

    • http://intensedebate.com/people/gjperera gjperera

      Alissa – I played around with it and can't seem to accomplish what you are trying to do. The other way you can expand or collapse groups is by clicking on the numbers (1, 2, etc.) that appear at the top of the margin to the right. Perhaps there is a macro of some sort that can be created to do this…not sue though. Hope this helps.

  • Alissa

    Is there a way to expand a group without clicking on the + sign? What I'd like to do is click on the the row above a group, and have the group expand. For instance, these are my rows:

    Section I
    a.
    b.
    c.

    The group contains a., b., and c. I want to click on 'Section I' and expand the group below it.

    • http://intensedebate.com/people/gjperera gjperera

      Alissa – I played around with it and can't seem to accomplish what you are trying to do. The other way you can expand or collapse groups is by clicking on the numbers (1, 2, etc.) that appear at the top of the margin to the right. Perhaps there is a macro of some sort that can be created to do this…not sue though. Hope this helps.