How to Automatically Collapse Excel Outline Groups on Open
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.

- 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.
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).
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.
Open your Excel workbook and press 'Alt + F11' on your keyboard to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, locate your workbook's name and double-click on 'ThisWorkbook'.
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).
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.

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. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor'.
- 3. Apply Workbook Macro: Double-click 'ThisWorkbook', insert your outline collapse script, and save the file in a macro-supported format.

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.




