How to group and ungroup worksheets in Excel

Advantages of grouping worksheets in Excel

When you are working with a set of identically structured sheets, grouping them together can save you a lot of time. Once the worksheets are grouped, you can enter the same data, make the same changes, write the same formulas and apply the same formatting to all the worksheets at once without having to switch through different sheets and edit each one individually.

Here are just a few examples of what you can do to a group of worksheets:

• Add new or edit the existing data on several worksheets at a time.
• Perform the same calculations with the same regions and cells.
• Print out a selection of worksheets.
• Set up the header, footer, and page layout.
• Correct the same typo or mistake on multiple sheets.
• Move, copy, or delete a group of worksheets.

In the screenshot below, we are setting up a table with the same data, formatting and layout for the 4 grouped worksheets: East, North, South and West.

How to group worksheets in Excel

To group sheets in Excel, hold down the Ctrl key and click the sheet tabs of interest one by one. After clicking the last tab, release Ctrl.

To group adjacent (consecutive) worksheets, click the first sheet tab, hold down the Shift key, and click the last sheet tab.

For example, here's how you can group two worksheets:

Once the worksheets are grouped, you can edit them all in one go. Also, you can perform calculations that will automatically reflect on all the worksheets in the group.

As an example, suppose we want to calculate the amount of commission based on the commission percentage (column C) and sales (column D) on the following sheets: East, North, South and West.

Here's the fastest way:

1. Group the 4 sheets.
2. Enter the below formula in cell E2, and copy it down through cell E5:
   =C2*D2

Done! The formula will appear on all the grouped sheets in the same cells.

How to group all worksheets in Excel

To group all the worksheets in a workbook, this is what you need to do:

1. Right-click any sheet tab.
2. Choose Select All Sheets in the context menu.
   

How do you tell if worksheets are grouped in Excel?

There are two visual signs of grouped worksheets in Excel:

The sheet tabs in a group have a white background; the sheet tabs outside the group appear in gray.

The word Group is added to the name of the workbook; as soon as the worksheets are ungrouped, it disappears.

How to ungroup worksheets in Excel

After you've made the desired changes, you can ungroup the worksheets in this way:

1. Right-click any sheet tab in the group.
2. Choose Ungroup Sheets in the context menu.
   

Or you can simply click any sheet tab outside the group to ungroup tabs.