This post will guide you how to allow “Group/Ungroup” rows on a protected Worksheets in your Excel. How to group or ungroup rows in a worksheet in Excel 2013/2016.
Assuming that you have lots of rows or columns and you want to group rows or columns or automatically created an outline to make your data easy to organize and declutter spreadsheets. You can use the below steps to group rows:
Step1: select a set of rows that you want to group together. Such as: Row1 to Row3.
Step2: go to
Data tab in the Ribbon, and click on the
Group command under the
Outline Group. And then select on
Group… from the drop-down menu list.
Step3: you should see that a set of dots next to each row would be created and a small box with a minus sign will be created and it can be used to expand the group.
If you want to ungroup rows and you just need to select the groups that you want to ungroup, and then click on the Data tab, and then click on ungroup button located in the Outline Group, and click Ungroup…. From the drop-down menu list.
If you have grouped rows in your unprotected worksheet, and you can click on the minus sign(-) or plus sign (+) to collapse the group. And you are not allowed to click minus sign to collapse the group.
If there is a way to group rows in a protected worksheet. The below steps will show you how to use an VBA Macro to protect worksheet that allow you to group rows. Just do the following steps:
Step1: open your excel workbook and then click on “
Visual Basic” command under
DEVELOPER Tab, or just press “
Step2: then the “
Visual Basic Editor” window will appear.
Step3: click “
Module” to create a new module.
Step4: paste the below VBA code into the code window. Then clicking “
Sub allowGroup() Dim mySheet As Worksheet Set mySheet = Application.ActiveSheet Dim myPW As String myPW = Application.InputBox("Type one Password to protect your worksheet:", "allowGroup", "", Type:=2) mySheet.Protect Password:=myPW, Userinterfaceonly:=True mySheet.EnableOutlining = True End Sub
Step5: back to the current worksheet, click on
Macros button under
Code group. then click
Step6: you need to type one password to protect your current worksheet, click on
Step7: Now you can click on the minus sign(-) or plus sign (+) to collapse the group on your protected worksheet.