This tutorial will demonstrate how to group and ungroup worksheets in Excel.
If we find ourselves with multiple worksheets that have the same layout and formatting, grouping these sheets together can be useful; any editing changes done in one sheet will be applied to all the other sheets in the group at the same time. Any changes that are made, be it formatting or formulas, will be made on all the grouped sheets at the same time without having to switch to any of the other sheets. This can be a great timesaver!
How to Group Worksheets
Grouping Adjacent Worksheets
To group adjacent worksheets, first click the sheet tab of the first sheet to be in the group.
Hold down the SHIFT key, and then click the last sheet to be in the group.
All sheets between the first and last selected sheet are then grouped together.
Grouping Non-Adjacent Worksheets
To group non-adjacent worksheets, first click the sheet tab of the first sheet to be in the group.
Hold down the Control key, and then click the next sheet to be in the group.
Keep the CTRL key down and continue to click on the sheets you wish to be in the group.
Grouping All Sheets at Once
To group all sheets in a workbook at once, first right-click on any of the sheet tabs.
Click Select All Sheets to group all the worksheets in the current workbook.
Once we have our worksheets in a group, our menu bar will reflect the fact that we are in group mode.
How to Ungroup Worksheets
The easiest way to ungroup worksheets is to click on the worksheet tab of a sheets that is not in the group. This will immediately take us out of group mode.
Alternatively, right-click on any of the grouped sheet tabs and click Ungroup Sheets.
Editing Multiple Sheets at Once
Once we are in group mode, we can edit one of the sheets or apply a formula and this change will automatically be added to the rest of the sheets in the group.
Group the sheets that require formatting and/or calculations.
Format the first sheet as required and enter any required calculations. In the example below, we have formatted the cells and added a SUM Function to the totals for each month.
Ungroup the sheets and then click on one of the previously grouped sheets to see the result.