Document Editing Problems

How to Expand and Collapse Groups in a Protected Excel Worksheet

Maira Mehtab Sep 21, 2026 869 views

Question details

The user wants to allow themselves and others to expand and collapse existing row groups in a protected Excel worksheet without relying on macros.

Product: Excel
Device & OS: not provided
Scenario: Sharing a protected workbook where users need to view hidden grouped rows without unprotecting the sheet or encountering macro security prompts.
Observed behavior: By default, protecting a worksheet disables the ability to click the outline plus and minus icons, preventing users from expanding or collapsing grouped rows.

Before you start

Ensure you have the password for the currently protected worksheet, as you will need to temporarily unprotect it to adjust the specific user permissions.

Solution 1Recommended

Enable Format Rows and Columns During Protection

Modify the protection settings to explicitly allow formatting rows and columns, which natively enables expanding and collapsing groups without the need for VBA macros.

In current versions of Excel, you do not need to rely on complex macro scripts to retain basic outline functionality. By simply granting users the permission to format rows and columns when setting up sheet protection, the existing grouping buttons (plus and minus signs) remain fully interactive.

Unprotect the worksheet

Navigate to the 'Review' tab on the Excel ribbon and click 'Unprotect Sheet'. Enter the current password if prompted to remove the active protection.

Access Protection settings

Still on the 'Review' tab, click 'Protect Sheet' to open the protection options dialog box where you can specify user permissions.

Allow row and column formatting

In the list under 'Allow all users of this worksheet to:', scroll down and check the boxes next to 'Format rows' and 'Format columns'.

Apply and confirm protection

Enter your desired password, click 'OK', re-enter the password to confirm, and click 'OK' again. The sheet is now protected, but users can still interact with the existing row and column groups.

Limitation on Creating New Groups: While this method allows users to expand and collapse existing groups, they will not be able to create new groups or ungroup existing ones as long as the worksheet remains protected.

Manage Protected Sheets Easily

Use WPS Spreadsheet to Manage Protected Data

WPS Office provides a highly compatible Spreadsheet tool that allows you to seamlessly protect your worksheets while keeping essential interactive features, like expanding and collapsing groups, available to your team.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the grouped rows you wish to protect.
2. Access the Review tab: Go to the 'Review' tab located in the top ribbon and select 'Protect Sheet'.
3. Adjust permissions: Check the options for 'Format rows' and 'Format columns', enter a password, and click 'OK' to secure your document while keeping groups interactive.

Fully compatible with Microsoft Excel (.xlsx) file formats and protection protocols.Intuitive sheet protection interface with granular permission controls.Free and lightweight alternative for everyday document and spreadsheet editing.No macro programming required to retain outline grouping functionality.

Frequently Asked Questions

Do I need to write a VBA macro to allow expanding groups on a protected sheet?

No, in current versions of Excel, simply allowing users to 'Format rows' and 'Format columns' during the sheet protection process is sufficient to let them use the expand and collapse buttons on existing groups.

Why can't I create new groups after checking 'Format rows'?

The 'Format rows' permission only allows modifying the visibility and formatting of existing rows. The actions to Group or Ungroup data structures are considered structural changes and are intentionally disabled by Excel in protected sheets.

Will other users need to enable macros if I use this permission method?

Because this solution relies entirely on Excel's built-in protection settings and does not use VBA scripts, other users will not face any macro security warnings or need to enable macros to view the groups.

How do I hide the plus and minus grouping buttons completely?

To hide the outline symbols entirely, go to the Data tab, click on the Outline dialog box launcher (or Outline Settings), and uncheck the 'Show outline symbols if an outline is applied' option.

Document & File Issues