Grouping and ungrouping sheets

Sheets can be grouped (connected) to facilitate creating or formatting two or more identical sheets. For example, you can create one sheet and copy it to two other sheets, or you can group three blank sheets and enter and format the content once-no subsequent copying required.

Sometimes it's needed to create several sheets with the same formatting. E.g., to create a workbook where content of a particular sheet is sales in a particular month. Hard way is to create one sheet, format it and then copy all formatting between sheets manually. Easy way is to group several sheets and format all of them simultaneously.

To group sheets, click one of the tabs that you want in the group and press the Ctrl key. With the Ctrl key held down, click the remaining tabs in the intended group of sheets. All the grouped sheets tabs will turn white, and the indicator [Group] will appear in the title bar after the workbook name:

Grouped sheets in Excel 2016

To group a consecutive series of sheets, click the tab of the first sheet you want grouped, and then hold down the Shift key as you click the last tab of the sheet you want in the series. All the sheets between the first and last tab will be included in the group.

You can group all the sheets in a workbook by right-clicking any sheet tab and choosing Select All Sheets from the shortcut menu:

Sheets popup menu in Excel 2016

To ungroup grouped sheets, click a sheet tab outside of the group, or right-click any of the grouped tabs and choose Ungroup Sheets from the shortcut menu.

The following items point out things that happen on all sheets when sheets are grouped:

  • Typing (including adding and deleting text)
  • Almost any type of formatting (including text, column, row, and cell), including the removal of formatting
  • Cut, copy, and paste (all methods, including right-drag and drag and drop)
  • Changes to the Page Setup command
  • Moving, copying, deleting, and coloring the tabs of sheets
  • Several of the options on the Advanced tab of Excel Options, including page breaks, formulas, gridlines, row and column headers, and zeros values.

Note: When entering group content, be sure to enter only the content that you want to be common to all the sheets in the group. If the group of sheets is intended to be quarterly sales reports for two or more month, for example, stop entering group content and ungroup the sheets before entering any content specific to any individual month.