Grouping Sheets in Excel (All Things to Know)

Get FREE Advanced Excel Exercises with Solutions!

Are you eager to learn about grouping multiple sheets in Excel? We usually group sheets when multiple worksheets are identical in structure in a workbook. This helps us to apply the same formula or formatting in all the grouped sheets. This saves us a great deal of time and eases our tasks.

In this article, we will present you various examples of grouping specific or all sheets in an Excel workbook. We can use the Ctrl key to group specific sheets and the Shift key to group consecutive sheets. A VBA macro can help us to group specific sheets with a single click.

We will also demonstrate methods to check if we have grouped sheets in Excel. The Workbook title bar and sheet tab colors are indicators of whether we have grouped sheets in Excel.

After completing the necessary calculations in similar grouped sheets, we have to ungroup sheets in Excel. The Ungroup Sheets feature and a VBA macro can help us in ungrouping sheets.

While preparing this article, we used Excel 365 for our operations. However, you can find all the features used in this article in all versions of Excel.

Overview of Grouping Sheets in Excel


Download Practice File


How to Group Sheets In Excel?

To demonstrate the examples of grouping sheets in Excel, we will use five identically structured sheets like the following. Here, we have weekly sales data of some employees from various locations. We will calculate the total sales for each week by grouping sheets.

Worksheets to be grouped


How to Group Selected Sheets with Ctrl Key in Excel?

  • To group only selected or specific sheets, we have to press and hold the Ctrl key and click on the required sheets. To demonstrate the result of grouping sheets, we applied the following formula in Cell C10
=SUM(C6:C9)

Grouping selected sheets in Excel

Now we will notice the same formula is applied automatically to Cell C10 of those sheets if we go to other grouped sheets.

Applying formula in grouped sheets


How to Group Consecutive Sheets with Shift Key in Excel?

In the previous example, we had to click on all the required sheets to group them. However, if the required sheets are in consecutive order, we can accomplish this with one click only.

  • From the first required sheet, press and hold the Shift key and click on the last required sheet. This will group all the sheets between the active sheet and the selected sheet.

Grouping consecutive sheets in Excel


How to Group All Sheets in a Workbook?

  • We can apply any of the previous two methods to select all sheets in a workbook tool. However, a much simpler way is to right-click over any sheet tab and click on the Select All Sheets option.

Grouping All Sheets in a Excel Workbook

All the sheets will be grouped like in the image below.

Grouped Sheets


How to Group Sheets Using VBA Macro?

We can apply VBA macro to group specific sheets or group them based on conditions. For that, first, we have to open the Visual Basic Editor window by going to the Developer tab and clicking on the Visual Basic option.

  • An alternative way to open the Visual Basic Editor window is to use the keyboard shortcut Alt + F11.

Opening Visual Basic Editor from Developer tab for grouping sheets in Excel

  • Click on the Insert tab >> select Module option >> insert the following code >> Save the macro >> Run the sub-procedure or press the F5 key to execute the VBA macro.
Inserting VBA Code for Grouping Sheets in Excel

Click the image for a detailed view

Sub groupingSheets()
Set wb = ThisWorkbook

Dim i As Integer
Dim j As Integer

Dim indInp As String
Dim indArr() As String

indInp = InputBox("Enter index of the sheets to be grouped," _
& vbNewLine & "seperated by commas:")

indArr = Split(indInp, ",")

For i = LBound(indArr) To UBound(indArr)
    If Int(indArr(i) > wb.Sheets.Count) Then
        MsgBox "Enter Sheet Indices Properly."
        Exit Sub
    End If
Next i

For i = 1 To wb.Sheets.Count
    For j = LBound(indArr) To UBound(indArr)
        If i = Int(indArr(j)) Then
            wb.Sheets(i).Select Replace:=False
            Exit For
        End If
    Next j
Next i

End Sub
  • An Input Box like the following will appear. Enter the index of the sheets you want to group separated by commas.

Entering Index of the sheets to be grouped

The sheets of the provided index will be grouped.

Grouped Sheets after executing the VBA macro


How Many Methods Are There to Check If Sheets Are Grouped in Excel?

There are two methods to check if sheets in a workbook are grouped: checking the Workbook title bar and checking the color of sheet tabs.


Can We Use the Title Bar of Excel Window to Check If Sheets Are Grouped?

If sheets in a workbook are grouped then the title bar of the Excel window will have the keyword [Group] in it.

Checking grouped sheets using title bar


Can We Use the Sheet Tab Color to Check If Sheets Are Grouped?

The color of sheet tabs (or leaf bars) will be white if we group the sheets in a workbook.

Checking grouped sheets using sheet tab color


What Are the Cautions While Grouping Sheets in Excel?

We must be careful while working with grouped sheets in Excel. We will get erroneous formulas and unwanted formatting in grouped sheets if the sheets are not identical in structure.


How to Ungroup Sheets in Excel?

After applying formulas or formatting to grouped sheets, we must ungroup them to avoid further simultaneous changes in those sheets. We can use the Ungroup Sheets feature or a VBA macro to ungroup sheets. Here are a few examples of ungrouping sheets in Excel.


How to Ungroup Selected Sheets in Excel?

  • To ungroup selected sheets, we have to click on any ungrouped sheet tab without holding the Ctrl key.

Ungrouping Selected Sheets


How to Ungroup All Sheets in A Workbook?

  • We can right-click over any sheet tab and select the Ungroup Sheets option if we have all the sheets in a workbook grouped.

Ungrouping All Sheets in Excel

However, a much simpler way to ungroup all sheets in a workbook is to simply click on any sheet tab without holding the Ctrl or Shift key.


How to Ungroup Sheets with VBA Macro?

We can also ungroup sheets using a simple VBA macro.

  • Go to the Developer tab and click on the Visual Basic option.

Opening Visual Basic Editor from Developer tab to ungroup sheets

  • Click on the Insert tab >> select Module option >> insert the following code >> Save the macro >> Run the sub-procedure or press the F5 key to execute the VBA macro.

Inserting VBA Macro for ungrouping sheets in Excel

Sub UngroupSheets()

ActiveSheet.Select Replace:=True

End Sub

After executing the VBA macro, all the sheets will be ungrouped.

Ungrouped sheets after executing VBA macro


What Are the Things to Remember?

  • The structure of the grouped sheets has to be identical for properly applying formulas and formatting.
  • After completing all the changes that you need to do in grouped worksheets, do not forget to ungroup them. Specifically, when you are working on specific sheets.
  • To avoid grouping unwanted sheets and adding erroneous formulas or formatting in them, create a backup copy of your workbook. In the worst-case scenario, it will allow you to get back to the point before grouping sheets.

Conclusion

This concludes our article to learn the process of grouping sheets in Excel. We used the Ctrl key to select specific sheets and the Shift key to select consecutive sheets. As a caution for unwanted changes in the grouped sheets, we used the Workbook title bar and sheet tab colors to check whether sheets are grouped.  We presented VBA codes to group or ungroup sheets with a single click. We hope these demonstrated examples were able to fulfill your quest for learning how to group sheets in Excel. Let us know your feedback in the comment section.


Frequently Asked Questions

1. How to Group Sheets in Excel on a Macintosh Operating System?

Answer: To group only selected or specific sheets in Excel on a Macintosh Operating System, we have to press and hold the Command key and click on the required sheets.

2. Is it possible to insert or delete rows/columns across grouped sheets?

Answer: Yes, it is possible to insert or delete rows/columns across grouped sheets. From any grouped sheet, right-click on the required row or column bar and select the Insert or Delete option based on your requirement.

3. Is there a limit to the number of sheets we can group in Excel?

Answer: No, there is no specific limit to the number of sheets we can group. However, as we can create only 255 sheets in Excel, the maximum number of sheets we can group is 255.


Grouping Sheets in Excel: Knowledge Hub


<< Go Back to Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Seemanto Saha
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo