How To Change Excel's Group Outline Direction Settings

How To Change The Collapse Direction For Excel Outline Views

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:

  1. Select the Data Tab
  2. Within the Outline group, click the dialog launcher button
  3. The two checkboxes within the Direction section of the Settings Dialog box will allow you to control which direction your outline groups expand/collapse
  4. Click the OK button
Steps to Change Settings for Outline Groups in Excel

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)

Sub CollapseRowsBelow()

ActiveSheet.Outline.SummaryRow = xlBelow

End Sub

Collapse Detail is Located Below the Total Row (Summary Row Above)

Sub CollapseRowsAbove()

ActiveSheet.Outline.SummaryRow = xlAbove

End Sub

Collapse Columns to the Right

Sub CollapseColumnsRight()

ActiveSheet.Outline.SummaryColumn = xlRight

End Sub

Collpase Columns to the Left

Sub CollapseColumnsLeft()

ActiveSheet.Outline.SummaryColumn = xlLeft

End Sub

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!

Chris :)