This tutorial demonstrates how to group and ungroup worksheets in Excel.
If you have multiple worksheets that have the same layout and formatting, grouping these sheets together can be useful; any editing changes done in one sheet are applied to all the other sheets in the group at the same time. Any changes that are made, be it formatting or formulas, are 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 time-saver!
Group 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 grouped together.
Group 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 CTRL 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.
Group 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 the worksheets are grouped, the menu bar indicates Excel is in Group mode.
The easiest way to ungroup worksheets is to click on the worksheet tab of a sheet that is not in the group. This immediately ends group mode.
Alternatively, right-click on any of the grouped sheet tabs and click Ungroup Sheets.
Edit Multiple Sheets at Once
In group mode, when you edit one of the sheets or apply a formula, it automatically applies the change(s) to the rest of the sheets in the group.
- Group the sheets that need formatting and/or calculations.
- Format the first sheet and enter any calculations. In the example below, the cells are formatted and the SUM Function calculates the totals for each month.
- Ungroup the sheets and then click on one of the previously grouped sheets to see the result.