How to Group Worksheets in Excel

  • Home
  • / How to Group Worksheets in Excel

Do you ever need to work on more than one Excel worksheet at the same time? 

Surely, it takes a lot of time because a worksheet can have a huge amount of data including text, numbers, characters, dates, and much more. If you group these sheets together, it would be highly helpful to you. Grouping worksheets in Excel allows you to make changes to the same range of cells all over the sheets. But the question is how to group worksheets in Excel? 

Here in this post, you will get to know about the grouping of worksheets and the whole procedure.

At times, you may have to edit the same cell in multiple sheets or you may have to apply the same format to the sheets. You can group all the worksheets to make the desired changes all at once without losing or damaging the content. 

Overview of Grouping Worksheets in Excel  

The key thing you will need for grouping the worksheets is the change that your sheets must be set up similarly. The same setup is mandatory because when you apply one formatting to one sheet, the same format will be applied to other sheets on the same location of each sheet.

Suppose, you have entered a value in the C5 cell on one of the grouped sheets, that value will also be entered in the cell C5 on all other worksheets that exist in the group. Also, you need to consider the type of data your sheets are having, otherwise, this procedural idea is excellent. 

How to Group Worksheets in Excel 

Let’s get begin with the whole procedure that explains how you can easily manage grouping worksheets in Excel. Below you will find some useful steps to follow for grouping the sheets: 

  1. Press and hold the CTRL key to group your sheets in Excel. Now, click on the sheet tabs one by one and after the last tab, release the CTRL-key. 
  2. When you need adjacent grouping of the worksheets, click on the first sheet tab. Press and hold the SHIFT key. Now, click on the last sheet tab. 
  1. Right after grouping the worksheets, you can simply edit all of them simultaneously. Apart from that, you can even compute some other values besides different functions that are applied on all the other worksheets that exist in the group, automatically.  

Let’s assume that you need to compute the amount of commission on the bases of the commission percentage, which is column C in the given example, and sales column D including East, West, North, and South. 

Below are some quick steps for the fastest way: 

  1. Group the sheets together. 
  2. Add the formula given below in the cell E2, and copy this formula using cell E5: =C2*D2
  3. That’s it! You can see the formula is placed in all the grouped sheets in the same cell. 

How to Group All Worksheets Together

Follow the steps given below:

  1. At first, you have to right-click on the sheet tab visible in the workbook.
  1. Right after clicking on it, you will have a menu bar containing numerous options.
  1. From those options, you have to choose the “Select All Sheets”.
  1. Now, you can see all of the sheets that exist in the workbook are grouped together.  

How to Group Some Selected Worksheets

Sometimes, you may need to group some particular worksheets only to make different editing. This option is quite helpful to hide some worksheets. Rather than applying the function one by one, you can do it simply all at once. When you are having different worksheets for grouping, you have to follow the steps given below:

  1. Choose a sheet you need to be grouped with other sheets.
  2. Press and hold the CTRL key from your keyboard.
  3. With your mouse choose all the sheets you wanted to be in the same group. Continue pressing and holding the key while doing this.
  4. Release the key once all the sheets are selected.

 Once the above steps are completed, all of your sheets are grouped together.

Advantages of Grouping Worksheets in Excel

Identically formatted sheets need grouping together so that you can save time and sources. Right after the grouping, you can add the same value or data in other sheets likewise. While editing, you may add the same or different data, including formulas, values, or sometimes you may have to apply the same formatting to all the sheets simultaneously. You don’t need to open each sheet one by one to make changes or apply to the format.

Below are some examples that may help you understand the phenomenon clearly.

  • Enter new data or you can make changes in the already existing data from different worksheets at a time.
  • Make a print of the selection of worksheets.
  • You can move, copy, or delete any worksheet from the group.
  • You can manage the header, footer, and page layout freely.
  • You are allowed to execute the same calculations used in the same regions and cells.
  • You can then make corrections of the same typo or mistakes from the multiple sheets.  

How Would You Know If the Worksheets are Grouped in Excel?

You can differentiate a worksheet whether it is grouped or not. Generally, you have two visual signals that show grouping existence.

In a group, the sheet tabs appear with a white background. If the sheet tabs are having a gray background, it shows they are not in the group.

If the worksheets are not in the group, they will simply disappear and if the word group is visible in the workbook it means they are a group.  

How to Ungroup Worksheets in Excel

Once you have made the required changes in the sheet, you can simply ungroup them. For this, you have to follow the steps given below:

  1. Right-click a sheet tab given in the group. 
  2. From the context menu, choose Ungroup Sheets. 

Or else, you can click a sheet tab not in the group to ungroup tabs.

Write your comment Here