Grouping and ungrouping sheets

Excel 365
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 the content of a particular sheet is sales in a particular month. A hard way is to create one sheet, format it, and then copy all formatting between sheets manually. The 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 to be 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 popup 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 popup 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 Layout commands
  • 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 months, for example, stop entering group content and ungroup the sheets before entering any content specific to any single month.

See also this tip in French: Comment grouper et dissocier des feuilles de calcul.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.