How to Group Worksheets in Excel: Step-by-Step (2022)
It is often the case that you have many identically structured sheets in your Excel workbook. And to such sheets, you might want to perform the same action on some or all of these sheets.
It could be adding formatting, adding, or deleting data, setting up a header/footer, or anything. So do you need to go to each sheet and do the same over and over again?
Not really. You can group sheets in Excel and have everything done in one go! 😊
Sounds interesting? Learn how to group worksheets in Excel by reading this guide.
Download our free sample workbook here to practice the examples explained in the guide below.
How to group specific worksheets
The image below shows the income/expense data of a company (for the first quarter).
This company runs three segments. – food, garments, and electronics. in the lookup column.
Within each of these three sheets, we have the same data for all three segments. Now, is the time to calculate the net profit (per month) for each segment.
Simple, let’s make a new column for Net Profit (Income minus expenses).
Don’t do that for each segment individually. Instead, do this.
1. Select the sheets that you want to group.
Here, we only need to select sheets for all three segments.
2. Press down the Control (Ctrl) button and select each of these three sheets.
An alternative shortcut to this. To select consecutive sheets is to hold the Shift button, select the first sheet and then select the lasts sheet.
Doing so selects all the sheets between and including the first and the last sheet.
Kasper Langmann, Microsoft Office Specialist3. All three sheets are now selected and grouped.
How do you know they are grouped? Navigate to the top of your Excel workbook. You’d see the ‘Group’ suffix to the workbook’s name.
Not only that – but the grouped worksheet tab turned white while the other sheet tabs remain grey.
4. Make any edits to any of these sheets.
For example, write the following formula in Cell E2 of Sheet: Food Segment.
= C2 – D2
This formula translates to ‘Income minus expenses’ for January. Drag and drop the same for the next two months.
Time for some magic? 😄
5. Go to the other two sheets to find the same edits there.
That is how grouping in Excel works!
How to ungroup specific worksheets
Once you’ve grouped specific multiple worksheets in Excel, how can you ungroup them?
There are two ways how you can ungroup specific multiple worksheets.
i. Click on any ungrouped sheet
1. Here are the grouped sheets in Excel.
We have grouped the second, third, and fourth worksheets. Note how these three have turned white.
Whereas, the first and the last worksheet remain ungrouped and are grey.
2. To ungroup them, simply click on any grey sheet (ungrouped sheet).
Doing so, the grouping is removed. And all the sheets turn grey.
Pro Tip!
Release the Ctrl button and click on any sheet outside the selection. This will ungroup the sheets.
However, once grouped, you can navigate between the grouped sheets without holding down the Control button – the grouping would not be removed.
ii. Ungroup from the context menu
To ungroup sheets from the context menu, follow these steps.
1. Right-click on any grouped sheet.
2. This will launch the context menu, as shown below.
3. Click on the option to ungroup sheets.
This removes the grouping from all the sheets.
How to group all worksheets
To group all the worksheets in a workbook, follow these steps.
1. Right-click on any sheet.
2. This will launch the context menu.
3. Click on the option to ‘Select All Sheets’.
All the worksheets tabs turn white and so, all the sheets are now grouped.
How to ungroup all worksheets
How to ungroup all the grouped sheets in Excel?
Again, there are two ways how you can ungroup all worksheets.
iii. Click on any grouped sheet
When you’ve grouped all sheets of a workbook in Excel, clicking on any sheet ungroups them all.
1. Click on any grouped sheet, and the grouping would be removed.
iv. Ungroup from the context menu
1. Right-click on any grouped sheet.
2. This will launch the context menu, as shown below.
3. Click on the option to ungroup sheets.
There! All the sheets are ungrouped.
Make sure to remove grouping after use
It is easy to group sheets in Excel. However, grouping makes little difference to the interface of Excel.
And so, there is a high chance that you might forget to ungroup the sheets.
This way whatever edits you make to any of the grouped sheets would be applied to all other grouped sheets.
To check if you’ve ungrouped the sheets in Excel, take a quick look at the sheet tab. Are they grey or white?
All worksheet tabs, except for the active worksheet, should be grey.
That’s it – Now what?
In this article, we learned grouping and ungrouping some and all worksheets in Excel (and many side tips too!).
Grouping worksheets to edit them simultaneously is one of the very amazing features of Excel. And surprisingly, this is yet not known to many users out there.
However, this and many more amazing Excel features would only help you explore this spreadsheet software better when you’re a pro at operating core Excel functions.
Like the VLOOKUP, SUMIF, and IF functions. Don’t fret if you know nothing about them yet.
Click here to sign up for my free 30-minute email course that teaches you these functions (and more!).
Conclusion
Enjoyed learning about grouping / ungrouping sheets in Excel? If yes, you’d love to learn about the following other similar features offered by Excel.
Learn how to make worksheets in Excel here. Also, what do you think is better? Google Sheets or Excel?