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.
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.
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)
Now we will notice the same formula is applied automatically to Cell C10 of those sheets if we go to other 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.
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.
All the sheets will be grouped like in the image below.
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.
- 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.
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.
The sheets of the provided index will be grouped.
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.
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.
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.
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.
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.
- 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.
Sub UngroupSheets()
ActiveSheet.Select Replace:=True
End SubAfter executing the VBA macro, all the sheets will be ungrouped.
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
