VBA & Macro Problems

How to Automatically Collapse Excel Outline Groups on Open

Emma BrownEmma Brown Oct 12, 2026 872 views

Question details

The user wants to configure an Excel workbook so that grouped rows and columns automatically collapse to a default state whenever the file is opened.

How to Automatically Collapse Excel Outline Groups on Open
Product
Excel
Device & OS
not provided
Scenario
When sharing or reopening complex spreadsheets with grouped data, ensuring the view defaults to a collapsed outline makes navigation cleaner and more professional.
Observed behavior
By default, Excel saves the outline expansion state as it was when the file was last closed. A VBA macro is required to force a collapsed view upon opening.
Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you have permission to run macros on your device. You will also need to save your file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use the Workbook_Open VBA Event

Add a simple VBA script to the Workbook's Open event to programmatically hide or collapse specific rows, columns, or outline levels every time the file is launched.

This solution utilizes the built-in 'Workbook_Open' event in Excel VBA. Any code placed within this specific event will run automatically the moment the workbook is opened.

You can use the 'ShowLevels' method to collapse outlines to level 1 (fully collapsed) for rows, columns, or both.

1
Open the VBA Editor

Open your Excel workbook and press 'Alt + F11' on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

2
Access ThisWorkbook

In the Project Explorer pane on the left, locate your workbook's name and double-click on 'ThisWorkbook'.

3
Insert the Open Event Code

In the code window, select 'Workbook' from the left dropdown. This will automatically generate a 'Private Sub Workbook_Open()' block. Inside this block, type: 'Worksheets("Sheet1").Outline.ShowLevels RowLevels:=1, ColumnLevels:=1' (replace 'Sheet1' with your actual sheet name).

4
Save as a Macro-Enabled Workbook

Close the VBA Editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu to ensure your code is preserved.

Use the Workbook_Open VBA Event
Test the Macro: Close the workbook and reopen it. If prompted, click 'Enable Content' to allow macros to run. The groups on the specified sheet will now automatically collapse.
Advanced Spreadsheet Management

Automate Data Grouping with WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to easily automate tasks like collapsing outline groups on open. Enjoy a highly compatible and lightweight office suite for all your advanced spreadsheet needs.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
  2. 2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor'.
  3. 3. Apply Workbook Macro: Double-click 'ThisWorkbook', insert your outline collapse script, and save the file in a macro-supported format.
Seamlessly runs Microsoft Excel VBA scripts and macrosFully compatible with .xlsm, .xlsx, and .xls file formatsLightweight installation with rapid file loading speedsIntuitive interface that feels instantly familiar
microsoft office alternative - wps office

Frequently Asked Questions

Why didn't my Excel groups collapse when I reopened the file?

This usually happens because macros are disabled by default in the Trust Center. Ensure you click 'Enable Content' when opening the workbook, or adjust your Macro Settings under File > Options > Trust Center to allow macros to run.

Can I collapse groups on all worksheets automatically?

Yes. Instead of targeting a specific sheet, you can use a 'For Each' loop in the Workbook_Open event to iterate through all worksheets. For example: 'For Each ws In ThisWorkbook.Worksheets : ws.Outline.ShowLevels RowLevels:=1 : Next ws'.

What happens if I don't save my file as an .xlsm?

If you save a workbook containing VBA code as a standard .xlsx file, Excel will permanently remove the macro code. You must save it as an Excel Macro-Enabled Workbook (.xlsm) or an Excel Binary Workbook (.xlsb) for the automation to work.