Usually, no: protecting a worksheet in Excel for Microsoft 365 on Windows prevents users from expanding or collapsing grouped rows or columns using the outline controls. To allow that while keeping the sheet protected, use a small VBA setting—EnableOutlining—and apply it whenever the workbook opens; the standard Protect Sheet dialog has no checkbox for outline controls.
What sheet protection does to outline groups
Excel’s outline feature lets you group rows or columns and show or hide the detail beneath a summary. For example, a monthly report might show one row per quarter while keeping the underlying three months available. The plus and minus buttons beside row numbers, or above column letters, let readers expand and collapse those details without changing the group itself.
Worksheet protection is a separate feature. It restricts actions on a sheet, such as editing locked cells or changing certain worksheet objects. When you protect a worksheet, Excel disables the outline controls for users, even if you leave them permission to select cells. The Protect Sheet dialog does not offer an “expand groups” or “use outline” permission.
This restriction is easy to mistake for a broken group. The group may still exist, and the outline levels may still be present, but the person viewing the protected sheet cannot use the controls normally. Allowing formatting rows is not the same as allowing outline expansion: that permission concerns formatting, not expanding and collapsing a group.
Create and check the groups before protection
Set up the outline before protecting the sheet. In Excel for Microsoft 365 on Windows, select the rows or columns to group, then choose Data > Group. If Excel asks whether to group rows or columns, choose the appropriate option. The outline buttons should appear beside the row or column headings. Use the numbered outline level buttons to check that the summary and detail views look as intended.
Before protecting, also check whether outline symbols are set to display. On Windows, go to File > Options > Advanced, scroll to Display options for this worksheet, select the relevant sheet, and make sure Show outline symbols if an outline is applied is checked. If the symbols are hidden, users may not see the controls even when the group exists. This display setting does not override sheet protection; it only controls whether the symbols are visible.
Once the groups and summary layout are ready, choose Review > Protect Sheet and configure the permissions you need. If you protect the sheet at this point, users generally lose the ability to expand and collapse the groups. The next section describes the VBA approach when that interaction is required.
Allow expansion with VBA
Excel exposes a worksheet property named EnableOutlining. Setting it to True allows users to use outline symbols on a protected worksheet. In practice, set the property after protecting the sheet. Because the setting does not reliably carry over as a permanent workbook choice between sessions, run the setup again when the workbook opens.
For example, if the worksheet is named Summary, open the Visual Basic Editor, double-click ThisWorkbook in the project pane, and add this event procedure. On Windows, press Alt+F11 to open the editor; on some Mac keyboards the equivalent is Option+F11, sometimes with the Fn key, or open the Visual Basic Editor from Excel’s Developer tools.
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Summary")
.Protect Password:="replace-this", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
Replace Summary with the exact worksheet tab name and replace the sample password with the password used for that sheet. The UserInterfaceOnly:=True argument keeps protection in place for users while allowing VBA macros to make changes to the sheet; it is useful when the workbook also has macros that update protected cells, but it is not the permission that enables outline controls. The EnableOutlining line is what permits users to use the outline symbols.
The workbook must be saved as an Excel Macro-Enabled Workbook (.xlsm) for the VBA project to be retained. When another person opens it, they may need to enable macros, depending on their Excel security settings and the workbook’s source. If macros are blocked, the open event will not run and the outline controls may remain unavailable on the protected sheet.
What this workaround does—and does not—permit
Enabling outlines is a targeted convenience, not a way to remove worksheet protection. It is intended to let users expand and collapse groups while other protection rules continue to apply. Users can then reveal details for reading without being given general permission to edit locked cells or alter the outline structure.
Protection is not a strong security boundary for sensitive information. It helps prevent accidental changes and can limit ordinary editing, but it should not be treated as a way to keep confidential data secret from someone who has the workbook. If users must not see the detail rows at all, hiding them or collapsing the outline is not sufficient access control: a person with the file may be able to reveal or inspect the data by other means. Do not distribute information to someone who is not meant to have access to it.
Also distinguish worksheet protection from workbook-structure protection. Protecting the workbook structure restricts actions such as adding, deleting, moving, hiding, or renaming sheets. It does not grant a permission to expand groups on a worksheet. If the problem is specifically the plus and minus outline buttons, check sheet protection and the outline setup rather than changing workbook-structure settings.
Save, reopen, and verify the behavior
After adding the event procedure, save the workbook as an .xlsm file, close it, and reopen it with macros enabled. Test using the actual user workflow: click a plus or minus outline button and check that the detail rows or columns appear and disappear. Also verify that a locked cell remains protected and that any intended unlocked input cells can still be edited.
Testing after reopening matters because a workbook can appear to work during the same Excel session in which the VBA property was set, then behave differently after it is closed. The Workbook_Open event reapplies the setting each time the file opens. If the sheet’s name changes, update the name in the code. If the sheet uses a different password, update the password argument too; otherwise the protection call may fail.
For a shared workbook, ask a colleague to check the file on the version and platform they use. Excel for the web does not run VBA, so this macro-based method cannot establish the setting there. A recipient who opens the workbook in a desktop Excel app with macros disabled may also find that the outline controls are not enabled on the protected sheet. Be clear with recipients that desktop Excel and enabled macros are required for this workflow.
Troubleshooting groups that will not expand
If the buttons are missing or clicking them has no effect, work through the likely causes rather than removing protection immediately:
- Confirm that the rows or columns are grouped. Select within the intended range and check the outline level buttons and group controls. If necessary, recreate the group with Data > Group.
- Check that outline symbols are displayed. On Windows, review the Show outline symbols if an outline is applied setting under File > Options > Advanced for the correct worksheet.
- Check whether the sheet is protected. If it is, the normal Protect Sheet permissions alone do not authorize outline use. Apply the VBA setting after protection.
- Check the macro setup. Confirm the code is in the ThisWorkbook module, the worksheet tab name matches exactly, the file is saved as
.xlsm, and macros are enabled. - Check the protection password. If the workbook has a different password from the one in the event procedure, the protection call may not complete as intended.
- Check the Excel app. The VBA approach requires desktop Excel that can run VBA. It will not run in Excel for the web.
As a diagnostic, an owner can temporarily unprotect the sheet and see whether the outline buttons then work. If they do, that points to the protection restriction rather than a missing group. Protect the sheet again after the check; leaving it unprotected changes the intended editing controls.
Alternatives when macros are not suitable
If your organization blocks macros or the workbook must work in a browser, there is no equivalent checkbox in the standard Protect Sheet dialog that grants outline expansion. You will need to choose between keeping the sheet protected and letting users operate the outline controls, or changing the design to avoid that interaction.
One option is to provide separate, clearly labeled summary and detail sheets, with only the sheets that need editing protected. Another is to distribute a read-only report or a version with the detail already visible or already summarized, depending on what readers need. You can also keep the data in a separate workbook and provide only the approved view. These are design alternatives, not ways to make hidden rows secure.
For a workbook used only in desktop Excel where macros are allowed, the EnableOutlining event procedure is usually the closest fit: it preserves sheet protection while permitting outline controls. For a browser-first workbook, plan around the limitation instead of assuming the desktop VBA setting will transfer to Excel for the web.
Frequently Asked Questions
Can I allow expanding groups from the Protect Sheet dialog?
No. The standard dialog has no specific permission for using outline symbols. The usual workaround is to use VBA to set EnableOutlining to True on the protected worksheet.
Does allowing users to format rows let them expand groups?
No. Row-formatting permission and outline expansion are separate actions. Checking a formatting option does not provide the missing permission for the plus and minus group controls.
Will the VBA method work in Excel for the web?
No. Excel for the web does not run VBA macros, so the Workbook_Open procedure cannot set the property there. Use desktop Excel with macros enabled, or redesign the workbook for a browser-based workflow.
Do users need the sheet password to expand a group?
With the VBA setup applied, users can use the outline symbols without being given the password simply to expand or collapse the groups. They still cannot use that fact to edit cells that remain locked under sheet protection.
Why do the groups stop working after I reopen the file?
The outline permission needs to be applied again when the workbook opens. Make sure the Workbook_Open code is in ThisWorkbook, macros are enabled, the workbook is saved as .xlsm, and the worksheet name and password in the code are correct.
Related
- Excel Power Query Connection Only Explained
- Stampin’ Up Inventory Spreadsheet: What to Track
- Spreadsheet Free Budget Planner: A Simple Excel Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets in Microsoft Edge: Setup, Features and FixesYes. Google Sheets works in Microsoft Edge: open sheets.google.com, sign in to a Google Account, and create or…
- Google Sheets Merge Cells Shortcut and Other MethodsGoogle Sheets has no dedicated default keyboard shortcut that merges cells directly. In a browser, press Alt+/ on…
- Excel Frequently Not Responding? Causes and FixesIf Excel frequently shows “Not Responding,” first give it a short time to finish, especially if it is…
- How to Autofill in an Excel Table: Steps That WorkTo autofill in an Excel table, enter a value or formula in a cell, then drag its small…
- Excel Scatter Plot: Color Points by ValueIn Excel for Microsoft 365 on Windows, the reliable way to color scatter-plot points by a value is…
- How to Disable External Links in ExcelTo stop Excel from updating external workbook links, open File > Options > Trust Center > Trust Center…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.