Got huge data in Excel ? Group columns or rows

By -

Quick Tip: Microsoft Excel has got this superb feature of grouping and ungrouping when your excel files has huge amount of data ( Excel files are always in corporate world ). All you need is to select cells which you want to group and then  Group > Rows or Cells from the Data Tab in Excel 2007.

Group Columns and Rows in Excel

Group Columns and Rows in Excel

This makes viewing of large excel files easier and this option is lot better than hiding cells or columns.



A Professional Tech blogger, Editor and Writer who talks about solving day to day problems of people who use computer. His expertise are in Windows 7, Microsoft Office, Software, Mobile Apps and Video Posts. You can find Ashish on , and .

16 Comments to Got huge data in Excel ? Group columns or rows

  1. Thanks for this article, it was a big time saver for me. This task was easy to do in Excel 2003, but it changed in Excel 2007 and I couldn’t find it for the life of me using the program help. Thanks again.

  2. Bill Casey

    In Excel 2007, go to the DATA tab at the top of your window (home, insert, page layout, formulas, {DATA}). On that tab, look almost all the way to the right. There, you will see a subsection named “Outline.” The Group button is there.

    Happy spread-sheeting!

    Bill

  3. Is there a way to change the grouping “+” and “-” sign to appear over the column that is immediately before the grouped columns (as opposed to being over the column after the grouped ones)?

    For example, I want to have columns H and I collapsed. The information they hold is related to column G but might not be something a person viewing the spreadsheet cares about initially. However, if they want to see more detailed information than what is stored in G they should see that there is a “+” sign above G and pop that open for the more detailed information. Right now it looks like column J is the one that is expandable because it has the “+” above it, which is slightly misleading.

  4. Ahmad Noor

    Hey bro, This method is great. I would like to know if there is any other method of doing the grouping.

    Example. I double click on a cell(heading) and the the rows related to it collapses showing all necessary information related to the heading.

    I know it can be done, just not sure of the name for the particular function. Its not drop down menu or combo box. It works exactly like the grouping data.

    Thanks and regards
    Ahmad
    Singapore

  5. I’m with Jadrian – I love grouping, and use it all the time. However, I was recently upgraded to Office 2007, and cannot for the life of me figure out where the “Grouping Options” went.

    Can anyone help out?

  6. @Ahmad
    In excel 2007 go to Data -> Outline -> Settings. In the Directions, uncheck ‘Summary Rows below detail’ OR ‘Summary columns to the right of detail’

  7. Does anyone know if there’s a maximum of groups that can be done?

    I keep getting the message of “cannot shift object of the sheet”.

    I’m using the 2007 version.

    Thanks!

  8. Hi, I have been using excel groping feature but as I was working on my file I cant no longer group or ungroup or even hidde or unhhide the data. I belive it has something to do with my file size 114,000kb

    What can I do?

  9. What really aggravatwes me is that you have to google to get any help, try and type “group cells” into excel help and you get everything but….GRRRRRRRRRRRRR!!!!!! I have just about had enough of this new version!!!!!

  10. Is it possible to do 3 level of groupings without an extra column for summary.i.e. the plus sign of level 1 grouping should be in the same column as level 2 grouping. Just like you have 1 2 and 3 levels on right top corner of the sheet?.

  11. Hi
    All

    To group /ungroup rows/columns in in excel use the following shortcut keys.

    steps

    1.Select the rows or colums , which u want to group / ungroup
    2. Shortcut to group press together SHIFT+ALT+RIGHT ARROW
    3.To ungroup press together SHIFT+ALT+LEFT ARROW

    Don’t foreget to say thanks if it is useful to you

  12. Is there a way to label my groups? When they are all collapsed, I can’t remember which data is in which group, a label would be great!

    Thanks!

  13. Thank you! I thought I was using the grouping button improperly (which I was). Once I changed the setting in Data->Outline everything started to move alot smoother :D

  14. @ Jadrien, @ Brian,

    These can be changed under the Outline settings.
    For 2007:
    > Open Outline Dialog box (click the small arrow at the bottom right corner of the Outline menu)
    > Settings window will open
    > Un-check the boxes “Summary columns to right of detail” (and un-check “summary columns to right of detail” if you want the same with rows)

Leave a Reply

Stats