How to Group Worksheets in Excel

Worksheet

You can easily group worksheets in Excel, so you do not have to waste your time and work on them individually.

Grouping worksheets together in Excel can be helpful if you need to perform the same tasks on multiple sheets at the same time. This is only possible when you have multiple sheets in a workbook that contain different data but follow the same layout and structure.

For example, if you have a workbook for student attendance that has multiple worksheets (one for each month). If all those worksheets are grouped together, and when you add student names in column A of one sheet, those names will be automatically added to column A in those sheets. Same way when you do calculations or formatting or any other changes in one sheet, it will immediately reflect in all the sheets.

If your sheets are grouped together, any changes you make on one worksheet will be automatically applied to all other worksheets in the group in the exact same location on the worksheets. After you are done reading this article, you will be able to easily group worksheets and ungroup worksheets in Excel.

Benefits of grouping worksheets in Excel

If the worksheets in your workbook are in the same structure, grouping them together can save you a lot of time. Once the sheets 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 edit each one individually.

  • Enter or edit data on several worksheets at a time
  • Print out a selection of worksheets at the same range and cells.
  • Correct the mistake on multiple sheets.
  • Set up the header, footer, and page layout.
  • Apply formatting to a selection of worksheets all at once.
  • Move, copy, or delete a group of worksheets at the same time.

How to Group Selected Worksheets in Excel

Sometimes, you may only want to group some specific worksheets and edit all of them at once.

In the example below, a workbook has multiple worksheets for different years. All the worksheets have identical structures showing sales of agents for each quarter.

To group consecutive(adjacent) worksheets, click the first sheet tab, hold down the Shift key, and click the last sheet tab. That’s it, now all selected sheets are grouped. When sheets grouped (the color changes from light gray to white background), it will look like as shown below.

To group non-adjacent sheets in Excel, hold down the Ctrl key and click the sheet tabs you want to group one by one. After clicking the last tab, release Ctrl key.

In our example, we want to add names in column A and SUM formulas to do calculations in column E in multiple worksheets at the same time.

Once the worksheets are grouped, any action or command you perform in one of the worksheets will be reflected in all of the other worksheets in the group. When we enter names and formulas in the 2015 tab, it will reflect at the same locations in other tabs as shown below.

Note: Once grouped, clicking any unselected sheet will ungroup the worksheets.

How to Group All Worksheets in Excel

If you want to group all the worksheets in an Excel workbook, you can do it with a couple of clicks.

Right-click any sheet tab and choose ‘Select All Sheets’ in the context menu.

Now, all the sheets in the workbook are grouped together.

Note: When you group all worksheets, switching to another sheet tab will ungroup the worksheets. If only some worksheets are grouped, you can browse through them without ungrouping worksheets.

Important! Once you are finished with the formatting and making data changes for your group, make sure to un-group worksheets so you can begin to work on each sheet again individually. 

How to Know If Worksheets are Grouped in Excel?

There are a few visual cues that can help you notice when you have grouped sheets in Excel:

  • The sheet tabs in a group have a white background while the tabs outside the group appear in light grey background.
  • If you have grouped worksheets in the workbook, the word ‘Group’ will be added to the name of the workbook (at the end of it).

How to Ungroup Some Selected Worksheets in Excel

If you want to ungroup some specific worksheets, hold the Control (Ctrl) key, click on all the sheets that you want to ungroup and release the Ctrl key.

This will ungroup the selected sheets but keep the rest of the sheets grouped.

How to Ungroup All Worksheets in Excel

When you’ve made all the desired changes, you can easily ungroup the worksheets.

To ungroup all the worksheets, simply right-click on any sheet tab in the group and select ‘Ungroup Sheets’ in the context menu.

This will ungroup all the sheets in the workbook. Now, you can continue working on each worksheet individually.

That’s how you group and ungroup worksheets in Excel.