Grouping and ungrouping sheets

Excel 2016 2013 2010 2007 2003
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 365

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 365

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.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more