Copy-paste the dataset to one sheet, the next, and also the next. Then a new formula to the same sheets one by one, you guessed it, again. You didn't take the Excel life to be so clerical now, did you? No sir! You will be thrilled to find out that such days are behind you, thanks to Excel's grouping and ungrouping worksheets feature. What you apply to one sheet will be automatically applied to others. What sorcery is that?
That's the sorcery you're learning today. This tutorial will explain why and how grouping sheets can be of use to you, how to group and ungroup a selection or all of the worksheets, and how to know which sheets, if any, are grouped.
Let's get (un)grouping!
Why Would Worksheets Need Grouping in Excel?
Grouping worksheets in Excel allows the user to work on multiple sheets simultaneously. What "work" can be done is pretty impressive but like everything, there are limitations to the actions you can take on grouped worksheets and we'll talk about those later.
One of the prime benefits of grouping sheets is that they can be edited at the same time. The cell that is selected on the active worksheet, will be selected in all the grouped sheets. Therefore, changes made to a cell or range in the active worksheet will also be made to the same cell or range in all the sheets of the group. This is particularly helpful for sheets containing data of a similar structure that needs identical information to be added. Let's test that through an example.
Below in the case example, we have lists of fruits available from 2 vendors, their price lists on different sheets.
We have received a price list from a third vendor and aim to add their list of fruits to the main sheet and end sheet. Creating a group of sheets 1 and 5, we have added the list to column D in Sheet1.
Now if we select Sheet5 (the name of the selected (and active) sheet becomes green in the sheet tab), the list has copied itself there too at the same location as Sheet1.
This method of editing makes incorporating an important update or change in data speedy and convenient. Similarly, a formula can also be entered using grouped sheets that will also be mirrored in other sheets of the group in the same cell location.
It still makes sense to edit sheets together even if they aren't similar. Accountancy firms require all papers created by the auditor to be signed in their initials as the header along with other details. The working papers in a spreadsheet can be grouped, leaving out the ones with data provided by the client, and the required details can be added. Similarly, headers and footers can be added simultaneously to all the sheets.
Grouping makes moving and copying worksheets very easy too. A dispersed group can be moved to be aligned together. In our case example below, we have grouped sheets 1 and 5. To shift them together to the start of the tabs, click and drag any sheet tab of the group to where you want the group to be. The small black arrow is indicative of where the sheets will be moved to.
Now both sheets are at the start of the tabs:
Note that the sheets have been ungrouped after moving.
Groups of sheets can also be moved or copied to other open workbooks. Grouping helps print selective sheets. But some tasks cannot be carried out on a group of sheets. The features that are unavailable to worksheet groups are grayed out in the Ribbon (given that there is a group). Several features in tabs other than the Home tab are also grayed out.
Other actions in the tab options that you can quickly perform by grouping the sheets are deleting and hiding the sheets and changing the color of multiple tabs.
Now that you know why you should work with grouped sheets, how do you group them?
How to Group Worksheets in Excel
A group can be of at least two or more and up to all the worksheets in the file. Worksheet groups are created by selecting the worksheets. That's all! There's a quick shortcut to selecting all the sheets while lesser sheets must be manually selected. Both methods are given below.
Group Selected Worksheets
Grouping selected worksheets is done manually. An easy way to remember is that it is like selecting multiple items on your computer; Ctrl and click. See the details on how to group selected worksheets below:
Sheet1 is selected in our example case, and we want to create a group of sheets 1 and 5.
- Make sure the active worksheet is one of the sheets that you want in the group (as we have selected Sheet1).
- Hold down the Ctrl key and click on the sheet that you want to group with the active sheet (we are selecting Sheet5).
- The selected sheet tab will also turn white (like the active worksheet) from gray.
- Continue to hold down the Ctrl key and select more sheets for the group.
- When done, release the Ctrl
- All the selected sheets will form the group:
- For selecting consecutive sheets, instead of Ctrl and clicking each sheet, you can use Shift + click to select all the sheets from the active sheet up to the clicked sheet. Let's show you how to do that using our case example.
- We want all the sheets from Sheet1 to PriceList3 selected.
- Select the sheet that you want as the first sheet of the group.
- For us, that is Sheet1 so we have made Sheet1 the active worksheet by simply clicking on the sheet tab.
- Press and hold the Shift key and click on the tab of the sheet that you want as the last sheet in the group.
- We will click on the sheet named PriceList3.
- Using the Shift key selects all the sheets from the active worksheet to the selected sheet:
- Let go of the Shift key when done.
Note: If in the group that you are forming, the number of unselected sheets is lesser than the selected sheets, you can select all the sheets first and then unselect the rest.
Group All Worksheets
Grouping all the worksheets can be done in a couple of clicks. The method is shown below:
- Right-click the sheet tab of any worksheet and click on the Select All Sheets option in the menu.
All the sheets in the workbook will be selected creating a group of all the worksheets. The tab that is right-clicked will become the active worksheet. All the sheet tabs will be white:
How to Identify If Sheets are Grouped
So you have learned how to group worksheets in Excel. How do you know if you did it right? One sign should be the one we've mentioned above; the grouped sheets will have white tabs instead of gray (as an unselected tab would be). Another sign is found in the title bar; there will be "Group" mentioned after the file name.
If you look at the previous sections, you will pick out that before the sheets were grouped, there was nothing after the file name in the title bar but after grouping, you can see "Group" there. And those are all the signs you need. Consider your group formed!
How to Ungroup Worksheets in Excel
Why should you know how to ungroup worksheets in Excel? Isn't a group a good thing? You may be ungrouping sheets so you can form a different group or stop the worksheets from continuing with the group aspects. After you are done with the work that you grouped the sheets for, be mindful of ungrouping them so that you don't continue to change the data in all the grouped sheets.
The process of ungrouping worksheets in Excel is the exact backward of grouping them. Like grouping, you may want to ungroup certain sheets or all the sheets. Read below to find out how to do both.
Ungroup Selected Worksheets
Ungrouping selected worksheets is done in reverse of selecting them. Again, a good way to remember it is to think of unselecting items on a computer, i.e. Ctrl and clicking a selected item. Here's how to apply it in Excel for ungrouping worksheets:
In this scenario of our case example, we have selected all the sheets other than the last sheet.
- Confirm that the active worksheet will be part of the remaining group.
- Now we want to unselect price lists 2 and 3 to ungroup them.
- Keep the Ctrl key pressed while you click on the sheet that you want to ungroup.
- Since we want price lists 2 and 3 out, we will click on PriceList3 and then PriceList2 (in whichever order, it doesn't matter) while holding down the Ctrl key.
- Release the Ctrl key when you've unselected the sheets for ungrouping.
- As soon as the sheet is clicked, it is unselected, returning to the gray tab from white:
Note: You cannot ungroup or unselect the active worksheet so do check that you keep the active worksheet as the one you want remaining in the group. E.g. we wanted PriceList2 ungrouped so we can't have it as the active worksheet because we won't be able to ungroup it. That is why we have selected Sheet1.
Ungroup All Worksheets
All worksheets can be ungrouped in two ways; using a sheet tab's right-click menu or by clicking on an ungrouped or inactive sheet tab. Consequently, there will be no group in the workbook. Only the active worksheet will remain selected. The following details are on how to ungroup all worksheets in Excel.
Single Click
Ungroup all the worksheets with a single click. Where you need to click depends on whether the group consists of selected or all worksheets.
Group of selected sheets: Click on any ungrouped sheet tab to ungroup all the worksheets.
In the example case below, we're selecting Sheet5 as it is the only ungrouped sheet.
All the sheets will be ungrouped:
Group of all sheets: Click on any inactive sheet's tab and the sheets will be ungrouped.
Below, we can select any sheet other than the active sheet which is Sheet1 to ungroup all the sheets in one click.
And again, this ungroups all the worksheets.
Sheet Tab Options
Right-click any sheet tab in the group and select the Ungroup Sheets
Immediately all the sheets will be ungrouped. No tab other than the active tab will remain selected and the title bar will also be devoid of "Group":
Caution with Grouping and Ungrouping Worksheets
Grouping worksheets in Excel makes collective and simultaneous tasks highly convenient but be very careful of how you use grouped sheets. Since grouping sheets renders no overt change to Excel's interface, it can slip your mind that a group exists and will continue to change sheets jointly.
Don't forget to ungroup the sheets to avoid unintentionally overwriting data. Also, be watchful that you don't accidentally ungroup sheets by clicking on an ungrouped or inactive sheet tab by mistake. You may not know what data went where and when exactly the ungrouping took place.
The caution is not to discourage use but encourage careful usage of grouping and ungrouping sheets in Excel.
Are you ready to group your worksheets to get work done more efficiently? We hope we made that easy for you. While you're exploring your newfound skill, we'll add the details around another trick to give your Excel aptitude that easy-going flair. Ready? Tricky? Then go!
About Mehwish Javaid