In Excel for Microsoft 365 on Windows, you can protect a worksheet after creating row or column groups, but sheet protection does not offer a built-in permission that lets users expand and collapse those groups. If people need that control on a protected sheet, use a carefully designed VBA workaround or choose a layout that does not depend on interactive outline controls.
What worksheet protection allows
Excel’s Protect Sheet feature is meant to stop selected changes to a worksheet, such as editing locked cells, formatting cells, or inserting and deleting rows. When you choose Review > Protect Sheet, Excel presents specific permissions, including selecting locked or unlocked cells and, depending on the workbook, using AutoFilter or sorting. There is no checkbox for using outline symbols or changing row and column group levels.
This distinction matters because grouping is more than a visual format. An outline adds controls to the worksheet margin that show or hide detail rows or columns. Although you can create the outline before protecting the sheet, the ordinary protection options do not grant users a separate right to operate those controls while the sheet remains protected.
Protection also is not the same as encryption or access control. It helps prevent routine edits in a workbook; it is not a way to keep sensitive information confidential from someone who can access the file. If the data itself must be restricted, do not rely on a hidden or protected worksheet as the security boundary.
Create the outline before protecting the sheet
For a worksheet that users only need to read, group the detail first and then protect the sheet. In Windows Excel, select the row numbers or column letters to group, then choose Data > Outline > Group. If prompted, choose Rows or Columns. Repeat for other sections. The outline buttons beside the row or column headings can then collapse or expand the selected detail while the sheet is unprotected.
For example, a monthly report might show one summary row for each department and several transaction rows beneath it. Group each department’s transaction rows so the summary is easier to scan. After checking that the plus and minus controls reveal the intended rows, go to Review > Protect Sheet, set a password if appropriate, and select the permissions users need for the rest of the worksheet.
Protecting the sheet is still useful for preserving formulas and presentation, even if it prevents people from operating the outline controls. Make the summary view the one users should see when the file opens, and save the workbook in that state. That gives readers a useful default, but it does not give them a protected-sheet method to change the outline level themselves.
Why there is no grouping checkbox
The Protect Sheet dialog grants a defined set of actions; it does not let you add arbitrary exceptions for every worksheet feature. For example, “Format rows” is not the same as permission to expand or collapse grouped rows. Similarly, allowing users to select locked cells does not grant access to outline controls. Changing those selection or formatting options will not solve the grouping limitation.
Workbook structure protection is another separate setting. Review > Protect Workbook can help prevent changes such as adding, deleting, moving, or renaming sheets, but it does not provide a way to operate a worksheet’s outline while that worksheet is protected. Protecting the workbook structure and protecting a sheet address different actions; neither adds an “allow grouping” permission to the sheet dialog.
It is also worth separating grouping from filtering. The Protect Sheet dialog can include an option for using AutoFilter, but a filter hides records according to criteria; it is not a row outline and does not show or hide a pre-grouped section. Enabling filtering may be useful for a table, but it does not make the outline plus and minus buttons available.
Workaround: use a macro to change outline levels
If users must reveal or hide details without editing the worksheet, a macro can briefly unprotect the sheet, change the outline level, and protect the sheet again. One approach is to provide separate macros for the summary and detail views. The example assumes the worksheet is named Summary and uses ChangeMe as the sheet password; replace both with the actual sheet name and password. It sets row outline levels, so the rows must already be grouped.
Option Explicit
Public Sub ShowSummaryRows()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")
ws.Unprotect Password:="ChangeMe"
ws.Outline.ShowLevels RowLevels:=1
ws.Protect Password:="ChangeMe", UserInterfaceOnly:=True
End Sub
Public Sub ShowDetailRows()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")
ws.Unprotect Password:="ChangeMe"
ws.Outline.ShowLevels RowLevels:=2
ws.Protect Password:="ChangeMe", UserInterfaceOnly:=True
End Sub
Row level 1 displays the top summary level; row level 2 displays the next outline level, which in a simple two-level outline includes the grouped detail. More deeply nested outlines may need additional levels. If the workbook also contains column groups, the code can be adapted with the Outline.ShowLevels method’s column-level argument. Test the result with the workbook’s actual outline rather than assuming every report has the same number of levels.
To use the example, save a backup first, add the code in the Visual Basic Editor, save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only when you trust the file. Users can run a macro from Developer > Macros. A button can also be assigned to a macro, but check the sheet’s object-protection settings so users can activate it without being able to alter worksheet objects. VBA does not run in Excel for the web.
The code re-protects the sheet with the password and UserInterfaceOnly set to True, which allows macros to make changes while the sheet remains protected. However, Protect also accepts other permissions, such as allowing filters; if your existing setup relies on them, include the same permissions in the macro’s Protect call. UserInterfaceOnly is not retained when a workbook is closed, so code that depends on that setting should set it again when the workbook opens. A macro can fail if its password or sheet name is wrong, so test it on a copy and confirm the sheet ends in the intended protection state.
What the macro workaround does and does not do
This approach does not add a native “allow grouping” permission. It gives users a controlled alternative: they run a macro that performs the outline change on their behalf. That can be appropriate for a report with a few clearly defined views, such as “summary” and “show detail.” It is less convenient if users need to open and close many individual groups in different combinations.
Macros also bring practical trade-offs. The workbook must be saved in a macro-enabled format, users need an environment that can run VBA, and organizational security settings may block macros. Excel for the web cannot execute VBA, so web users cannot rely on this solution. On Mac, desktop Excel supports VBA, but availability and macro-security prompts can differ from Windows; test the workbook on the actual devices people will use.
Do not treat a password written into VBA as strong security. A person with access to the workbook and enough technical knowledge may be able to inspect or work around the code. Sheet protection and a macro password are conveniences for controlling everyday edits, not substitutes for secure storage or permissions managed through an organization’s file-sharing system.
Alternatives when users need flexible detail
If the main requirement is letting people explore the data, consider whether a protected outline is the right interface. A few alternatives can provide a clearer or more robust experience:
- Use a PivotTable. Users can expand or collapse grouped fields in a PivotTable, and the Protect Sheet dialog includes a permission for using PivotTable reports in supported situations. Test the exact workbook because PivotTable interaction and worksheet protection settings can affect what users can do.
- Separate summary and detail views. Put a concise summary on one sheet and the underlying records on another. Protect each sheet according to what users should edit, and use links or navigation controls to move between them.
- Use a table with filters. If users need to find records by category rather than reveal preset sections, an Excel table with AutoFilter may fit better. Protect the sheet with the relevant filter permission and test the expected filtering workflow.
- Provide a read-only summary by default. If users do not actually need to change the displayed detail, set the outline to the desired level before protecting and saving the workbook.
- Leave the sheet unprotected but lock only what is necessary. This may suit a workbook where accidental formula edits are a concern but users genuinely need interactive outlines. Consider whether unlocked input cells and clear instructions are enough for the intended audience.
Choose based on the task rather than the appearance of the controls. Outlines are useful for collapsing a known hierarchy; filters are useful for selecting records; PivotTables are useful for summarizing and exploring structured data. Trying to make one mechanism behave like another often creates extra maintenance without giving users the interaction they expect.
Common problems and checks
If the group controls disappear or appear unavailable after protection, first confirm that the rows or columns are actually grouped: the outline symbols should be visible when the sheet is unprotected. Then check whether the sheet is protected. Changing “Select locked cells,” “Select unlocked cells,” or “Format rows” is not a substitute for an outline permission.
If a macro does nothing, check that macros are enabled, the workbook is open in desktop Excel, the sheet name matches exactly, and the password in the code matches the sheet password. Confirm that the groups exist and that the requested outline level is valid for the structure. Run the macro on a copy before using it with a shared or important workbook.
If filters stop working after a macro runs, the macro may have re-protected the sheet without restoring the original filter permission. Review the protection settings and add the required option to the Protect call. Also test what happens after closing and reopening the workbook: settings such as UserInterfaceOnly need to be reapplied during each Excel session if the macros depend on them.
In Excel for Mac, look for the protection command on the Review tab; labels and dialogs can vary slightly by release. Excel for the web has a more limited feature set, and it cannot run the VBA workaround. If the workbook will be shared across desktop and browser users, agree on a workflow that works for everyone rather than assuming a desktop macro will be available online.
Frequently Asked Questions
Can I expand grouped rows on a protected Excel sheet?
Not through a built-in Protect Sheet permission. Create groups before protection for a fixed display, or use a tested macro or a different workbook design if users need to change the outline.
Does allowing users to format rows let them expand groups?
No. Formatting rows and operating outline controls are different actions. The Protect Sheet dialog does not include a separate permission for expanding or collapsing grouped rows.
Can I group rows after protecting a worksheet?
Ordinary sheet protection restricts changes to the worksheet, and there is no checkbox that specifically permits users to create or edit row groups. Set up the outline before protecting, or have a macro perform the change after temporarily unprotecting the sheet.
Will the VBA workaround work in Excel for the web?
No. Excel for the web does not run VBA macros. Users need desktop Excel with macros enabled, or you need to provide another way to access the information.
Does Protect Workbook enable grouping on a protected sheet?
No. Workbook structure protection governs actions such as changing the workbook’s sheet structure. It does not add outline controls to the Protect Sheet permissions.
Is a protected sheet secure for confidential data?
No. Worksheet protection is intended mainly to prevent routine edits, not to secure information from people who can access the workbook. Use suitable file permissions and secure storage for sensitive data.
Related
- Excel Power Query Group By Column: Steps and Examples
- Vending Inventory Spreadsheet: Track Stock and Reorders
- Excel Power Query: Get Data, Clean It, and Load It Easily
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Drop-Down List to an Excel Column Step by StepTo add a drop-down list to an Excel column, select the cells that should contain it, then choose…
- Sort by Row in Google Sheets: How to Reorder Data SafelyTo sort columns by the values in a row in Google Sheets, select the full range of columns…
- Excel: Hide Duplicates Based on One Column in a ListTo hide entire rows when a value repeats in one column, add a helper column, mark the first…
- What Column Number Is V in Excel? Find It in SecondsIn Excel, V is column number 22. Column letters run from A=1 through Z=26, so V is the…
- How to Insert a Number Line in Word: Two Easy MethodsTo insert a number line in Microsoft Word, go to Insert > Shapes, draw a horizontal line with…
- Excel Find and Replace: Change Cell Fill ColorYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, open Options, choose a fill format beside Format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.