It’s not as difficult as you would imagine to group worksheets. In Excel, there are two simple ways to join worksheets together. There is also a very simple way to select all the worksheets in an Excel workbook.
Here are the simple ways to group worksheets in Excel:
- Hold down the Ctrl key while clicking the sheet tabs of relevance one after another in Excel to group sheets. Release Ctrl after clicking the last tab you want to add to your grouped sheets.
- Click the first sheet tab, then hold down the Shift key while clicking the last sheet tab to group nearby (consecutive) worksheets.
- To group all the sheets in a workbook, right-click any sheet tab and choose “Select All Sheets” from the popup menu.
The selected sheets in the red box are grouped.
To group all the worksheets in a workbook, this is what you need to do:
- Right-click any sheet tab.
- Choose Select All Sheets in the context menu.
Note that, when all of the sheets in a workbook are grouped, changing to a different sheet tab ungroups the worksheet. You can browse through the grouped sheets without ungrouping them if only a few worksheets are grouped.
What are some of the advantages associated with grouping excel sheets together?
Excel users want to organize worksheets for a variety of reasons. When working with a series of identically constructed sheets, grouping makes things go faster.
- You can conduct the same procedures on all of the sheets in a group when you group them together. Some of the things you can accomplish using grouped sheets are as follows:
- You can enter the same data in the same cell places across all sheets.
- Existing data on many spreadsheets can be edited at the same time.
- The same formulas can be applied to all of the sheets at the same time.
- You can format all of the sheets in the same way (for uniformity).
- A group of sheets can be moved, copied, or deleted at the same time.
Once the worksheets have been combined, you can enter the same data, make the same edits, write the same formulas, and apply the same formatting to all of them at once, rather than switching between sheets and editing each one separately.
For example, suppose we want to calculate the total amount of sales made between 8 pm and 10 pm each day of the week across all four cities of ACCRA, TEMA, SEKONDI & KUMASI
- Select and group all 4 sheets.
- Input the formula =SUM(G2, H2) into cell I2 because we want to find the sum of the sales. You could input whatever operation you want into the formula bar to solve the problem of your choice.
- The formula appears on all the group sheets
To replicate the formula to the remainder of the cells in column G, press the Enter key and double-click the fill handle of cell I2.
If you now select any of the sheets, the total sales will be automatically calculated.
You didn’t have to enter the formula four times and copy it to the other rows for each of the four sheets. It only had to be done once. Working by grouping worksheets is that simple and quick.
How to tell if worksheets are grouped in excel
- The word Group is appended to the workbook’s name on the Excel title bar, but it vanishes after the worksheets are ungrouped.
- Sheet tabs in a group have a white background, while those outside the group have a gray background.
How to ungroup worksheets in Excel
After you’ve made the necessary adjustments, ungroup the worksheets as follows:
- Right-click any of the group’s sheet tabs.
- In the context menu, select Ungroup Sheets.
- Alternatively, choose anyone from the tabs that aren’t part of the group. The group edit mode will be disabled as a result of this. If, on the other hand, all of the sheets in a workbook are grouped together, clicking on any one worksheet tab from the group will ungroup all of the sheets.
- Also, if you only want to ungroup one worksheet from a group, hold down the SHIFT key on your keyboard while selecting the sheet from the grouped tabs.
Using VBA to ungroup sheets in Excel
If you’re running Excel macros and notice that certain sheets are clustered together, you can use a VBA macro to ungroup them by programmatically choosing any sheet.
You can use the following syntax as an example:
Substitute the name of the sheet you want to ungroup for “Sheet1.”
[Video] How to group or ungroup worksheets in Excel
If you like more visual lessons then here is a video for you. From this video, you will learn the various ways by which you can group or ungroup Excel worksheets.
You may also be interested in learning about other Microsoft Excel tricks and how to solve various problems you may come across when using Microsoft Excel, check out our Excel category