Sanjida Mehrun Guria
When you are working on a bulk of numbers in separate worksheets you may find it really difficult to input the same data or formula on each one. In this case, it is useful to group two worksheets. It not only saves time but also helps to work on the same formulas or data with a similar format. Let’s have a look at a simple and easy guide to group two worksheets in Excel.
How to Group Two Worksheets in Excel: Step-by-Step Procedures
For grouping two worksheets in Excel, we have taken an example of a garment factory’s sales report. Here the dataset (B4:F11) shows the amount of sales of each clothing type in the first six months. The years 2018-2021 have been separated into individual worksheets.
Now, follow the methods below to group two worksheets in an Excel file:
Step 1: Select Worksheets to Group
- At first, press the Ctrl key and Left Click on the first worksheet that you want to include in a group.
- Again, press the Ctrl key and Left Click the other worksheet to include in the group.
Step 2: Release Selection
- Now release the Ctrl key. You will see that the two worksheets are in a single group now. It will look like this:
- The other worksheets outside the group will appear in gray while the worksheets in a group will appear in white.
Step 3: Proof of Method
We can check if the grouping is working or not. Let’s follow the steps below:
- In the beginning, let’s use the SUM function to calculate the total number of pants sold in 2019.
- Insert the formula below on cell C11 in the worksheet of 2019.
=SUM(C5:C10)
- Next, press Enter. It will show the total amount of sales.
- Then, drag the right corner of cell C11 up to F11. It will show the total sales amount of other items as well.
- Now check on the other worksheet 2021 in the group.
- You can see the total amount of sales in the second worksheet as well in a different dataset without typing the same formula again.
- You can also notice that the name of the workbook has added the word Group which proves that the selected worksheets are in one group.
How to Ungroup Two Worksheets in Excel
After you have made your required changes in the two worksheets, you can ungroup them by following these steps:
- Right-Click on one of the worksheets of the group first.
- After that, choose Ungroup Sheets option in the context menu.
- Or you can simply click on any other sheet tab outside the group. It will immediately ungroup the two worksheets.
Read More: How to Ungroup Worksheets in Excel
How to Group More Than Two Worksheets in Excel
If you need to group more than two worksheets, you can follow these methods below.
Method 1: Use Excel Context Menu to Group More Than Two Worksheets
Here, we will group more than two worksheets using the same data set above. Let’s go through the procedure:
- First, Right Click on any sheet tab of your workbook.
- Choose the Select All Sheets option from the context menu then.
- Finally, this will group more than two worksheets in Excel.
Method 2: Group More Than Two Worksheets with Ctrl & Shift Keys
This is another process to group more than two worksheets in excel with the similar dataset we used in this article. Follow the below process to see how to do that.
- Press the Ctrl key and Left Click on the 1st worksheet.
- Then, select other worksheets that you want to include in the group.
- Release the Ctrl key.
- Alternatively, you can press Shift and Left Click on the first worksheet.
- After that, select the last worksheet to include in the group.
- Now release the Shift key.
- That’s it! All the worksheets are grouped.
- This process is helpful when you need to group consecutive sheets at a time.
Things to Remember
You need to keep in mind some cautions while working on two or more grouped worksheets.
- After completing all the changes that you need to do in grouped worksheets, do not forget to ungroup them. Especially, when you are working on specific sheets. Because, when you go to the other sheets among the grouped worksheets, this would not ungroup them. It may cause a loss of data in the long run.
- To make sure that you don’t lose any work, create a backup copy. In the worst-case scenario, it will allow you to get back your work (before you grouped the sheets).
- Make sure you are grouping the worksheets that have a similar dataset.
Download Practice Workbook
Here is the sample workbook for you to download and practice.
Conclusion
In this article, we have seen the process of how to group two worksheets in Excel. We also explored methods of grouping more than two worksheets and ungrouping them successfully. There is a practice workbook above. Give it a try and let us know your insightful suggestions.
