Do you need to group or ungroup the sheets in your Excel workbook?

Grouping sheets in Excel can be a great way to perform the same tasks across similar sheets quickly.

When you group sheets together, any actions you perform on the active sheet in the group will be applied to all the grouped sheets at once instead of having to do them individually.

You’ll also want to make sure you ungroup sheets after so you don’t accidentally change them unintentionally.

This article will cover how to group and ungroup Excel worksheets to get the most out of your Excel work!

How to Group Worksheets

When you want to group sheets to work on them simultaneously it’s an easy process and there are several options to get it done!

Group Sheets with the Ctrl Key

Using the Ctrl key is the best way to group sheets together when you want to select a few non-consecutive sheets from your workbook.

When you hold the Ctrl key and then click on another sheet it will be added to the set of currently grouped sheets.

1. Select the first sheet you want to group.
2. Hold the Ctrl key.
3. Click on the sheet tab of any sheet you want to add to the group.

💡 Tip: You can also use the Ctrl key to remove a sheet from the group. Hold the Ctrl key and click on a grouped sheet to ungroup it.

Group Sheets with the Shift Key

Using the Shift key to group worksheets is a better option when you want to group many consecutive sheets in your workbook.

When you hold the Shift key and select another sheet, all the sheets between the active sheet and the sheet selected will be grouped.

1. Select the first sheet you want in the group.
2. Hold the Shift key.
3. Select the last sheet you want in the group.

All the sheets in between will be grouped!

Group Sheets with the Right Click Menu

Suppose you want to group all the sheets in your workbook. There is also an easy option for this.

There is a command to group all sheets that can be found in the right-click menu.

1. Right-click on any sheet tab in the workbook.
2. Select the Select All Sheets option from the menu.

That’s it! All the sheets are now grouped!

Group Sheets with VBA

It’s possible you might want an even easier solution to grouping sheets.

You could create a button that runs VBA code to group your sheets.

This way the button is always easily accessible from your sheet and obvious to use.

This will also allow you to easily group sheets based on some logic such as if the sheet name contains a certain text string. For example, you might want to group all sheets that contain the text 2022.

This way you can automatically group the sheets Jan 2022, Feb 2022,…, and Dec 2022 without the need to select each sheet.

Press Alt + F11 to open the VBA code editor. Then go to the Insert menu and select the Module option.

Sub GroupSheets()
Dim sht As Worksheet
Dim MyText As String
MyText = Application.InputBox("Enter text to group sheets by")

For Each sht In Worksheets
    If sht.Name Like "*" & MyText & "*" Then
        sht.Select Replace:=False
    End If
Next sht
End Sub

Add the above code to your module in the VBA code editor.

The above VBA code will prompt the user to enter a text string to search for in each sheet. The code will loop through each sheet and check if the sheet name contains that text.

If the sheet contains the text, then it will select the sheet to add to the grouping.

When you run the code, the result will be all sheets containing the entered text will be grouped.

How to Determine if Sheets are Grouped

There are two easy ways to tell if any of your sheets in the workbook are grouped.

The Group tag will appear at the top of the workbook next to the file name to indicate you are working with grouped sheets.

You’ll also notice the grouped sheets are all white in color.

If these don’t appear in your workbook, then your sheets are not grouped.

How to Ungroup Worksheets

You definitely won’t want to leave your sheets grouped after you finish working on the grouped sheets.

This can cause problems because you might accidentally make changes across the sheets that you didn’t intend to make.

Ungrouping your sheets is a necessity and is also quite easy to accomplish.

Ungroup Sheets by Selecting a Single Sheet

Selecting a sheet is one of the easiest ways to ungroup sheets.

When all your sheets in the workbook are grouped, all you need to do is select any other sheet than the active sheet and the sheets will be ungrouped.

If only some of the sheets are grouped, then you can select any of the ungrouped sheets and the grouping will be removed.

Ungroup Sheets with the Right Click Menu

Ungrouping is also an action that will always be available from the right-click menu regardless of which sheets are grouped.

1. Right-click on any of the grouped sheets.
2. Select the Ungroup Sheets option from the menu.

All sheets will be ungrouped.

Ungroup All Sheets with VBA

Ungrouping your sheets can also be done with VBA.

Sub UngroupSheets()
ActiveSheet.Select Replace:=True
End Sub

The code is quite simple. It selects the active sheet which will remove any grouping in your sheets.

Conclusions

Grouping sheets allows you to perform the same tasks across multiple sheets at the same time. This is an easy way to save time when you have to make the exact same changes in each sheet.

Grouping sheets is easy and can be done with the Ctrl or Shift keys, the right-click menu, or with VBA.

Ungrouping sheets is equally important when you’re done with your work so you don’t accidentally make unintended changes and this can be done with similar methods to how you grouped sheets.

Have you used grouped sheets yet? What did you use them for? Let me know in the comments!