How to Group and Ungroup Worksheets in Excel

A few readers had shared their concerns about situations where some of their sheets seem to be stuck together. So, any changes they make to one sheet are also affecting other sheets in their workbook.

This often happens if the sheets were either accidentally or purposely grouped together.

When you need to perform the same set of operations to multiple worksheets in a Workbook, the quickest way to get the work done is by grouping the worksheets together.

However, if you don’t know how to ungroup these grouped worksheets, this can become a source of frustration.

In this tutorial, we will see how to identify grouped sheets, why people group sheets, why you would need to sometimes ungroup them, and how you can group and ungroup sheets.

Table of Contents

Benefits of Grouping Excel Sheets

There are a number of reasons why Excel users like to group worksheets. Grouping gets things done quicker when working with a set of identically structured sheets.

When you group a set of worksheets together, you can perform the same operations to all the sheets in the group. Here are some of the things that you can do with grouped sheets:

You can enter the same data to all the sheets at the same cell locations.
You can edit existing data on multiple worksheets at the same time.
You can put the same formulas to all the sheets at once.
You can apply the same formatting to all the sheets (for uniformity).
You can move, copy, or delete a whole group of sheets at the same 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.

How to Group Worksheets in Excel

Grouping worksheets is easier than you think. Here are two easy ways in which you can group worksheets together in Excel:

Hold down the CTRL key of your keyboard and click the tabs of each sheet you want to group together one by one. After you’re done click the last tab, you can release the CTRL-key.
If the worksheets you want to group are all adjacent to one another, as shown in the image below, then click the first sheet’s tab, hold down the SHIFT key on your keyboard and click the last sheet’s tab.
If you want to select all the sheets in a workbook, right-click any sheet tab and click on “Select All Sheets” from the popup menu that appears.

Let us take an example.

Suppose you have a set of sheets with sales information for different years and you need to calculate the total sales for each day.

All the sheets, in this case, have the same columns, so the fastest way to work would be to group them together:

Select Sheet1, press down the SHIFT key, and select Sheet4. This will select all four sheets and group them together. Now, whatever you do to one sheet, will get reflected in all 4 sheets.
Select cell G2 (for any sheet) and enter the formula: =E2*F2
Press the Return key and double click the fill handle of cell G2 to copy the formula to the rest of the cells of column G.

If you now select any of the sheets, you will find the total sales calculated automatically.

You did not need to enter the formula and copy it to the other rows four times for the four sheets separately. You only needed to do it once. That is how easy and fast it is to work by grouping worksheets.

Also read: How to Ungroup Sparklines in Excel

Why Ungroup Worksheets in Excel?

Grouping sheets can be a useful way to save time and uniformly perform a set of operations across multiple sheets. However, once grouped, any changes that you make on one sheet gets replicated to all sheets in the group.

This may lead to the possibility of making unwanted changes to data in some worksheets. Moreover, it becomes difficult when you want to make changes to just specific sheets.

In such cases, it may be desirable to (maybe temporarily) ungroup the previously grouped data.

How to Identify Grouped Sheets

Before you can ungroup worksheets in your workbook, you need to first identify if the sheets have been grouped or not. If they have been grouped, you need to know which ones, so you can ungroup them.

There are two tell-tale signs to identify grouped sheets:

The tabs of the sheets that are grouped together usually have a white background. Those that are outside the group have gray backgrounds. For example, in the image below, Sheet2 and Sheet3 have been grouped together.
You will find the word ‘Group’ added as a prefix to the name of the workbook in the Excel Title bar.

3 ways to Ungroup Worksheets in Excel

Here are two ways in which you can ungroup sheets in Excel:

The first method is very simple. Identify the tabs that are not part of the group and select anyone. This will disable the group edit mode. If, however, all the sheets of a workbook are grouped together, then clicking on any one worksheet tab from the group will ungroup all the sheets from the group.
The second way is to right-click on one of the worksheet tabs of the group and click on “Ungroup Sheets” from the popup menu that appears.
If you want to ungroup just one worksheet from a group, press the SHIFT key on your keyboard while clicking on the sheet you want to ungroup from the grouped tabs.

How to Ungroup Sheets Using VBA in Excel

If you are running macros on Excel and find certain sheets grouped together, you can ungroup them with VBA macro by simply programmatically selecting any sheet.

For example, you can use the following line:

Sheets("Sheet1").Select

Replace “Sheet1” with the name of the sheet you want to ungroup.

How to Regroup Sheets to their Original Grouping?

Once you’ve ungrouped the previously grouped sheets and completed working on them, you might want to optionally regroup the sheets to the original group.

To do this, all you need to do is press and hold down the CTRL key on your keyboard. Then click on the tabs that you want to be grouped back.

The sheets will get grouped back when you release the CTRL-key.

Conclusion

In this tutorial, we described how you can group and ungroup worksheets in Excel. It is such an easy tool, that if used cleverly, it can save a lot of time and keep errors to a minimum.

We hope you found this tutorial useful. Do let us know in the comments how you use the grouping and ungrouping technique to speed up your work.

Other Excel tutorials you may like: