Categories
Excel

Group and Ungroup Worksheets

It’s fairly ordinary to have the same data appear in multiple worksheets simultaneously. You can use Excel’s tool for grouping so that data in one workbook can be entered into multiple worksheets at the same time.

Why You Group Worksheets

When you enter data into one grouped worksheet, that data is also automatically entered into all of the worksheets in the group. This is useful for data that is common to every sheet of the workbook, for example:

  • Writing headers and footers
  • Writing headings for tables
  • Printing multiple worksheets with a single command.
  • etc.

Group Worksheets

There are two ways to group worksheets in Excel: by selecting them individually or by selecting all of them at once.

To group worksheets individually, follow these steps:

  • Press and hold the Ctrl key (the Shift key on the Macintosh) on your keyboard.
  • Click the Name tabs of the worksheets you want to group at the bottom of the Excel window.
  • Release the Ctrl key when you are done.

The selected worksheets will have a white background, while the unselected ones will have a gray background. The name of the workbook will also change to include [Group], see the following figure:

Group worksheets individually

To group all worksheets in a workbook, follow these steps:

  • Right-click any worksheet Name tab at the bottom of the Excel window.
  • Click Select All Sheets from the context menu.

All the worksheets in the workbook will be grouped together. See the following figure:

Group All Sheets

Once the worksheets have been grouped, you can perform any tasks you want on them. The changes you make on one worksheet will be applied to all the other worksheets in the group in the same location. For example, if you enter a formula or write some text in cell A1 on one of the grouped worksheets, it will appear in cell A1 on all the grouped worksheets as well.

Ungroup Worksheets

To ungroup your worksheets, you can do one of the following:

  • Select one worksheet that is not part of the group or
  • Right-click any worksheet Name tab in the group and click Ungroup Sheets from the context menu, or
  • Press and hold the Ctrl key (Shift key in Mc) and click any worksheet Name tab in the group.
Ungroup Worksheets

Although this method is easy, it means you need to remember to group and ungroup your sheets as needed or else you will inadvertently overtype data from another worksheet. It also means simultaneous data entries will occur regardless of the cell you are in at the time. For example, you might want simultaneous entries to occur only when you are in a particular range of cells.

Understanding Workbooks and Worksheets: