How To Change Excel's Group Outline Direction Settings

By Chris Newman •  Updated: 10/08/21 •  2 min read
How to change excels groupings and change the direction that they collapse

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.

Examples of Excel Outline views expand and collapse rows

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

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.