Group Worksheets In Excel

Article byDhanamma Pandula
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Group Excel Worksheets?

Group Excel worksheets is one of the useful features of Excel to consolidate the worksheets to work concurrently on the different worksheets. As a result, tasks are accomplished quickly using the group worksheets.

For example, consider the below workbook with 3 worksheets, Test 1, Test 2, and Test 3 respectively. Now, we can learn how to group these sheets.

Group Excel Worksheet Intro

Step 1: First, press Ctrl key and select the sheets – Test 1, Test 2, and Test 3.

Immediately, we can see the name of the workbook is indicated as a group as shown in the below image.

Group Excel Worksheet Intro - Output

Likewise, we can group sheets in Excel.

Key Takeaways

  • Grouping sheets in Excel is performed while working with multiple sheets with calculations.
  • Using grouping sheets in Excel, we can save a lot of time and work effortlessly.
  • It is because we can work on all the sheets simultaneously.
  • Grouping sheets in Excel enhances data accuracy and reduces potential errors.
  • Remember, we can group all the sheets or select consecutive sheets also in Excel.
  • The option, of grouping sheets in Excel, is widely used in educational institutions, managers, and in businesses.

Group Excel Worksheets Explained

  • Changes made in one worksheet can together change in other worksheets in Excel. Grouping saves a lot of time for Excel users to perform calculations or create complex tables with large data and edit and format the sheets.
  • It gives the best results to users when the same type of data is presented in the cells of the same addresses. Grouping also improves the accuracy of data and eliminates the error made by a human in performing the calculations.
  • Business owners and managers widely use it to update data in similar worksheets. In the present article, the following things are explained clearly.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

The following examples are provided for understanding how grouping works together in Excel. Grouping is used mainly in six ways:

Example #1 – Group Individual Worksheets

One must press and hold down the “CTRL” key on the keyboard for individual group worksheets and simultaneously select the sheets individually by clicking on each tab. After completing grouping, all the tabs presented in the Excel workbook are converted into white color. Finally, the file name is added with the group in the title bar, as shown in the figure below.

Group Worksheets in Excel 1

Example #2 – Ungroup Individual Worksheets In Excel

If we do not want a group of sheets together in Excel, we can ungroup them by again holding the “CTRL” key or clicking on other Excel sheets, removing the grouping.

Group Worksheets in Excel 11

Example #3 – Group All Worksheets

It is easy to group all the worksheets in Excel, following only two simple steps.

  1. 1.   In the first step, we must select any tab we wish by clicking on it.


    Group Worksheets in Excel 2

  2. Right-click on the worksheet tab and select the last option, “Select All Worksheets.”


    Group Worksheets in Excel 4

    The below figure helps understand the group’s process of all worksheets.

    Group Worksheets in Excel 3

Example #4 – Ungroup All Worksheets In Excel

Ungrouping worksheets together in Excel is easy by using any one of the following steps.

  • Step 1: Right-click on the tab and select the “Ungroup Worksheets” Excel option.
Group Worksheets in Excel 5
  • Step 2: Clicking on any other sheets immediately also ungroup every sheet in the workbook.
Example 6

Example #5 – Group Consecutive Worksheets

To group the consecutive worksheets in Excel:

  • The user must click the first worksheet to activate it.
Example 7
  • Press and hold the “Ctrl” button from the keyboard and select the last worksheet tab by clicking with the mouse.
Example 8

Example #6 – Ungroup Consecutive Worksheets In Excel

To ungroup consecutive worksheets in excel,

  • We must right-click on the first or last tab of the activated worksheet and select the “Ungroup Worksheets” Excel option.
Group Worksheets in Excel 9
  • Clicking on any other sheets immediately also ungroups every sheet in the workbook.
Group Worksheets in Excel 10

How To Use Group Worksheets In Excel?

The Excel sheet shows that the following sample data explains how to group worksheets in Excel.

Example - 1

Suppose we want to obtain the value of total car sales in February, March, April, and January.

So, first, we need to group all the sheets, group individual sheets, or select all worksheets at a time.

group worksheets example 1-1

Then SUM Formula in ExcelSUM Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more in the C11 cell of the January worksheet.

Example 1-2

The total sales value is automatically calculated in other worksheets and January month. The results are obtained, as shown in the below-mentioned figures.

Example - 2

The text and formula are automatically applied to other sheets if we add an average formula in the next row of total sales.

Example 1-4

Average sales are automatically calculated in other worksheets along with January month. The results are obtained, as shown in the below-mentioned figures.

Example - 3

Suppose we delete a row or column in the January worksheet after grouping.

For example, we have deleted the row header in the January worksheet in the below screenshot. The same changes are applied to other worksheets in Excel.

Example - 4

Important Things To Note

  • The user needs to use only an active tab to add formulas and apply the format to sheets to utilize the grouping method best. Using another tab cannot generate the required results.
  • The user should confirm whether grouping is applied or not by checking the file’s name in the title bar ([Group] is added).
  • We should present at least two sheets in the workbook to make a group of worksheets together in Excel.
  • Do not browse through the sheets not included in the group immediately after grouping the sheets. It leads to the ungrouping of the sheets directly.

Frequently Asked Questions

1. What is grouping Excel sheets mean?

Group Excel worksheets is used when we have to work simultaneously in multiple sheets. It saves a lot of time and is used to reduce the time spent to obtain results.

2. How to group multiple sheets in Excel?

For example, consider the below workbook with 3 worksheets, Semester 1, Semester 2, and Semester 3 respectively. Now, we can learn how to group these sheets.

Group Excel Worksheet - FAQ 2

Step 1: First, press Ctrl key and select the sheets – Semester 1, Semester 2, and Semester 3.

Immediately, we can see the name of the workbook is indicated as group as shown in the below image.

FAQ 2 - Output

Likewise, we can group sheets in Excel.

3. How to ungroup sheets in Excel?

We can ungroup sheets in Excel using

Simply select one of the grouped sheets
Right-click and click on Ungroup sheets option

We can see the sheets getting ungrouped.

This article is a guide to Grouping Worksheets in Excel. Here, we discuss how to group worksheets and use them concurrently to save time. Also, we discuss how to ungroup worksheets in Excel. You may learn more about Excel from the following articles: –