Excel's row/column grouping functionality is a handy way to consolidate or provide a hierarchical order to your spreadsheet data. The concept is pretty straight-forward, you select some rows or columns and click the Group button within Excel's Data tab.
This action adds expand/collapse buttons either to the side or top of your spreadsheet cells. Below are two common views in which you might organize your data with the Outline groupings.
Steps To Change Collapse Direction
By default, the Excel settings for groupings are set to "Summary Rows Below Detail" and "Summary Columns to Right of Detail". Here are the steps to change the vertical or horizontal direction of Excel's Outline Groups:
- Select the Data Tab
- Within the Outline group, click the dialog launcher button
- The two checkboxes within the Direction section of the Settings Dialog box will allow you to control which direction your outline groups expand/collapse
- Click the OK button
How To Change Settings With VBA
If you happen to need to modify these outline group settings with VBA code, the following snippets show how on the currently viewed spreadsheet.
Collapse Detail is Located Above the Total Row (Summary Row Below)
ActiveSheet.Outline.SummaryRow = xlBelow
Collapse Detail is Located Below the Total Row (Summary Row Above)
ActiveSheet.Outline.SummaryRow = xlAbove
Collapse Columns to the Right
ActiveSheet.Outline.SummaryColumn = xlRight
Collpase Columns to the Left
ActiveSheet.Outline.SummaryColumn = xlLeft
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!