How to Group Worksheets in Excel.
If your Excel workbook has multiple sheets that are identical in layout and structure, you can simplify your work by grouping similar sheets. To be more specific, once you learn how to group worksheets in Excel, you can apply the change to the corresponding cells in all grouped worksheets by changing only one of the grouped worksheets.
For example, if you change the row height and width for one sheet, it will change for the grouped sheets as well.
Why Would You Want to Group Worksheets in Excel?
You can perform multiple tasks on grouped sheets, for example:
- modify or add data and insert formulas.
- Print them at the same time.
- Move, copy or delete information at the same time.
Since Excel does not allow you to add multiple sheets, you can group 10 sheets, right-click them and select the Paste option to insert 10 sheets at once.
How to Group Worksheets in Excel
We will use the following example to see how to group sheets in Excel.
Suppose you split your sales team into three groups based on what product they are selling, and you have sales data for those individual groups. Sales data is presented on three separate sheets, each of which contains data for one product. You want to calculate the commission for each employee without having to manually enter formulas on multiple sheets.
Instead of calculating commissions for each sheet separately, you can group the sheets.
- Press and hold the Ctrl key.
- Click the sheets you want to group. Grouped sheets turn white, while non-grouped sheets remain gray. You will see the word “Group” added to the title bar when you are on one of the grouped sheets.
- Add a formula to calculate the commission for one employee (column C, row 2), preferably in the first row, so you can drag the formula to subsequent cells.
Note that we have grouped all three sheets as we want to apply the changes to all sheets.
- Select the cell where you just entered the formula and drag the selected border to apply the formula to the entire range of cells. This will apply the same changes to all grouped sheets.
- Make sure the changes are also present in the other grouped sheets. If you have done everything correctly, column D on all three sheets should be filled in with the amount of the commission due to employees.
How to Group All Worksheets in Excel?
If you are dealing with a book with several dozen sheets, it may take you some time (and possibly aspirin) to select all the worksheets individually. Instead, Excel has a Select All Sheets option that lets you group all sheets at once.
All you have to do is right-click any sheet tab and click Select All Sheets.
Note. The catch with using this option is that when switching between sheets, all sheets are automatically undone (or ungrouped), unlike the last option (where we selected individual sheets), where you could switch between sheets without ungrouping them.
How to Move, Copy, Delete, Print, or Hide Grouped Worksheets
You can apply many Excel operations to grouped sheets, such as move, copy, or even hide sheets. This usually works the same way as for regular sheets.
Move or copy grouped sheets
By grouping the sheets:
- Right-click one of them and choose Move or Copy.
- Select a target book from the drop-down list and select the location where you want to move or copy the grouped sheets. If you want to copy, check the “Create a copy†box at the bottom and click “OKâ€.
Delete grouped worksheets
You can delete grouped sheets in the same way. When the sheets are grouped, right-click one of the grouped sheets and choose Delete.
Print grouped sheets
When the sheets are grouped, press Ctrl + P. Now you will see the print options. Scroll down to the Preferences section and select Print Active Sheets.
Before choosing Print, look at the preview on the right to make sure you are printing the correct sheets. After confirming this, select Print.
Hide grouped worksheets
You can also hide grouped sheets at once.
- Start by grouping sheets.
- On the top ribbon, select Home and choose Format in the Cells group.
- After selecting Format, you will see a drop-down menu. Choose Hide & Show> Hide Sheet.
This will hide all the grouped sheets.
How to Ungroup Worksheets in Excel
If you want to ungroup all the grouped sheets, just right-click any sheet and select Ungroup Sheets.
If you have not grouped all sheets in the workbook, clicking on any of the ungrouped sheets will also ungroup the grouped sheets.
If you want to ungroup multiple sheets, hold down Ctrl and click the sheets you want to ungroup.
Save Time by Grouping Worksheets in Excel
Grouping worksheets often saves time that you would otherwise spend copying and pasting formulas onto worksheets or performing other repetitive tasks. However, Excel is a multifunctional program, and there are always shortcuts to make Excel work easier. For example, if you are working with a workbook with a large number of sheets, there are several ways to quickly switch between them.
How to Group Worksheets in Excel
How to Group Worksheets in Excel