How to Group Worksheets in Excel (Step-by-Step)

Sumit Bansal

When working with multiple worksheets in Excel, sometimes you may have a need to edit the same cell in all the worksheets (or apply certain formatting the same cell/range in all the sheets).

You can easily group worksheets in Excel so that you can make the changes at one go (instead of going to each sheet one by one and making the changes).

To give you an example, suppose you have a workbook that has multiple worksheets (one for each month). Now, in this data, you want to add a new item (Laptop) in cell A7 in all these sheets.

With the ability to group worksheets in Excel, I can group all the sheets first and then I need to make this entry once and it will automatically be added to all the sheets.

Let’s see different scenarios where you can group sheets in Excel and some important things to keep in mind.

This Tutorial Covers:

Group Some Selected Worksheets

Sometimes. you may only want to group some specific worksheets and make changes in these.

I often do this when I have to hide a few worksheets. Instead of hiding these one-by-one, I simply group these sheets and then hide all these at once.

In case you have a lot of worksheets and you want to group a few of these, follow the steps below:

Select any one of the sheets that you want to be grouped.
Hold the Control key on your keyboard
Use your mouse/trackpad to select all the sheets that you want to be grouped (keep the Control key pressed while doing this)
Leave the control key

The above steps would group all the selected worksheets.

You will also be able to visually see the sheets that have been grouped (the color changes to light gray as shown below).

Once you have the sheets grouped, anything you do in the active sheet also gets done in all the other grouped sheets. For example, with these sheets grouped, if I enter any text in cell A1 in the active sheet, it will also be added in cell A1 in all the other sheets that are grouped.

This can be helpful when you want to enter the same formula in the same cell in multiple sheets. Or make a formatting change in all the sheets. So instead of doing it one by one, you can just group these sheets and do what you want once in the active sheet only.

Note: When you group worksheets in Excel, Group word is added in front of the workbook name. This is also a quick way to check whether the workbook has any grouped sheets or not.

You can also use this to enter formulas, change/apply cell formatting, add/delete rows and columns, etc. All these changes will be applied to all the grouped sheets.

Pro Tip: If you want to group consecutive sheets in Excel, you can select the first worksheet, hold the shift key and then select the last worksheet. This will select all the worksheets in the middle.

Caution: When you group some selected worksheets, these remain grouped even if you click on some other sheets that are grouped. For example, if you have grouped the Jan, Mar, and Apr sheets and you go from Jan sheet to Mar sheet, these will still stay grouped.

Ungroup Some Selected Worksheets

If you have already grouped some or all of the worksheets and you want to ungroup some specific worksheets, you can do that as well.

Below is how to ungroup some selected worksheets:

Hold the Control key
Click on all the Sheets that you want to ungroup
Leave the Control key

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

Group all Worksheets

In case you want to quickly select and group all the worksheets at one go, you can use the below steps:

Right-click on any of the worksheet tabs
Click on ‘Select all Sheets’ option

Another quick way to group all the worksheets in Excel is to use the Shift key:

Select the first worksheet (the left-most tab)
Hold the Shift key
Click on the last sheet in the workbook (the right-most tab)
Leave the Shift key

Ungroup all Worksheets

If you want to ungroup all the worksheets in one go, use the below steps:

Right-click on any of the grouped worksheet tab
Click on Ungroup Sheets

Another way to ungroup all sheets is to click on any sheet tab (other than the active sheet tab).

Caution when Grouping Sheets

When you group worksheets, it’s possible that you forget to ungroup these sheets and continue to work on it. This would mean that anything you do when the sheets are grouped will happen in all the grouped sheets.

This is especially the case when you group some specific sheets only. In this case, even when you go to other sheets (which is also part of the group), this would not ungroup the sheets.

How to avoid this?

You can be more cautious when working with grouped sheets. There are a few visual cues that can help you notice that the sheets are in grouped mode:

The name of the workbook get the word [Group] added to the name (at the end of it)
The grouped sheets are in a slightly different color (light gray)

Another thing you can do to make sure you don’t lose any work because of this is to create a backup copy. In the worst-case scenario, it will allow you to get back your work (before you grouped the sheets).

Despite all the visual cues, I have seen people (including myself) mess up because they forgot to ungroup the sheets. So be extra cautious when using the sheets grouping functionality

Hope you found this Excel tutorial useful.

You may also like the following Excel tutorials:

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.