Grouping worksheets together in Excel can be really helpful if you need to execute the same tasks on multiple sheets at the same time. This is only possible when you have multiple sheets in a workbook that contain different data but has the same structure.
For instance, Let’s say you have a workbook for student attendance that has multiple worksheets (one for each day). If all those worksheets are grouped together, and when you add student names in column A of one sheet, those names will be automatically added to column A in those sheets. Same way when you do calculations or formatting or any other changes in one sheet, it will immediately reflect in all the sheets.
If your sheets are grouped together, any edits you make on one worksheet will be automatically reflected on all other worksheets in the same group at the same cell location. After you are done reading this article, you will be able to easily group worksheets and ungroup worksheets in Excel.
Benefits of grouping worksheets in Excel
If the worksheets in your workbook are in the same structure, grouping them together can save you a lot of time and make your workflow more efficient. Once the sheets are grouped together, you can enter data, edit data, and apply formatting to all the sheets at once without having to edit each one individually
- You can enter or edit data on multiple worksheets at once.
- You can print out a group of worksheets at the same range and cells.
- You can correct the same mistake or error on multiple sheets.
- You can set up the header, footer, and page layout on multiple worksheets.
- You can apply the same formatting to a selection of worksheets at the same time.
- You can move, copy, or delete a group of worksheets.
How to Group Selected Worksheets in Excel
If you wanted to, you can group only some specific worksheets and edits all of them at once with ease.
In the following example, a workbook of sales data has multiple worksheets for different years. All these sheets have the same structures showing sales of agents for each quarter.
To group consecutive worksheets, first, click the first sheet tab, hold down the
Shift key, and click the last sheet tab. That’s it, now all selected sheets are grouped. When sheets grouped (the color changes from light gray to white background), it will look like as shown below.
To group non-adjacent sheets (non-consecutive) in Excel, hold the
Ctrl key and click all the sheet tabs you want to group one by one. After clicking the last sheet tab, release
In our example, we want to add names in column A and SUM formulas to do calculations in column E in multiple worksheets at the same time.
Once the worksheets are grouped, any change or command you perform in one of the worksheets will immediately be reflected in all of the other worksheets in the group. When we enter names and formulas in the 2015 tab, it will reflect at the same locations in other tabs as shown below.
Also, do remember, once grouped, clicking any unselected sheet outside the group will ungroup the worksheets.
How to Group All Worksheets in Excel
If you wish to group all the spreadsheets in a workbook, you can do it with a couple of mouse clicks.
To group all the worksheets, just right-click on any sheet tab and choose ‘Select All Sheets’ in the context menu.
Now, all the sheets in the workbook are grouped together.
Note: When you group all worksheets, switching to another sheet tab will ungroup them all. If only some worksheets are grouped, not all, you can easily switch between them without ungrouping worksheets.
Important! Once you are finished editing your group, be sure to ungroup the worksheets so you can start work on each sheet again individually.
How to Know If Worksheets are Grouped in Excel?
There are a few ways that can help you notice when you have grouped sheets in Excel:
- The sheet tabs in a group have a white background color while the tabs outside the group appear in the light grey background color as shown below.
- If you have any grouped worksheets in your workbook, the word ‘Group’ will be added to the name of the workbook.
How to Ungroup Some Selected Worksheets in Excel
If you intent to ungroup some only specific worksheets, hold the Control (
Ctrl) key, click on all the sheets that you want to ungroup, and release the
Doing this will ungroup the selected sheets while keeping all other sheets grouped.
How to Ungroup All Worksheets in Excel
When you’ve made all the desired changes, you can easily ungroup the worksheets.
To ungroup all the worksheets, simply right-click on any sheet tab in the group and select ‘Ungroup Sheets’ in the context menu.
This will ungroup all the sheets in the workbook. Now, you can continue working on each worksheet individually.
That’s how you group and ungroup worksheets in Excel.