Home ยป Functions

Group and Ungroup Worksheets in Excel

Do you know what is grouping and ungrouping multiple worksheets in Excel? In this blog, we would learn how to group multiple worksheets in Excel. We would also learn how the grouping worksheets help to quickly perform the same function or task in multiple worksheets at one go. There are many more advantages to using this feature in Excel.

Grouping Sheets, as the name in itself, is self-explanatory. It means to group one or more excel sheets. Ungrouping Sheets means releasing the grouping of the grouped worksheets.

Need to Group and Ungroup Sheets in Excel

When you have multiple worksheets to with having the same data structure, and you wish to enter the same text, formula, or perform the same task or apply identical cell formatting to an individual cell or range of cells in all those worksheets at one go, then the grouping feature in Excel would be a life-saver and a very handy tool. Below are some of the advantages of grouping sheets in Excel-

The above list of advantages of using group worksheets feature is just an inclusive one. There are many more things that you can perform using this feature.

Let us now deep dive into this feature and explore its advantages.

Sample Data

Below is the sample data that we would be using through out this blog.

Sample Data - Group and Ungroup Sheets in Excel

In the above image, you can see that there are five worksheets having sales figures five products (A, B, C, D, E) in same cell range (A1 to B6).

As you read go through this blog, practice as well. Download the sample file using Download button below for practice.

How to Group Excel Worksheets

At an onset, let us first learn some quick ways to group worksheets in Excel.

Simply, press and hold the Ctrl key on your keyboard and start clicking on the individual worksheet tab to be grouped. That’ all, you would notice that as you go on selecting the worksheets (holding Ctrl key), the worksheets get on grouping.

Below demonstration is self-explanatory:

Grouping Worksheets in Excel

In a similar manner, if you want to group adjacent worksheets at one go, then you need not to individually click on each of the adjacent worksheets. Simply, select the first worksheet, press and hold Shift key on your keyboard and click on the last worksheet.

Grouping Adjacent Worksheets in Excel

One important point to know is that when the worksheets are grouped in a workbook, the word [Group] is added to the workbook name on the title bar as shown in the image below.

Title Bar - Grouped worksheets

Group All Worksheets At Once

One way to group all the excel worksheets at one go is by clicking on the first worksheet of the workbook, then press and hold the Shift key and finally, click on the last worksheet, as demonstrated below:

Group All Worksheets At Once

Yet there is another way as well. Right click on any of the worksheet tab and click on the option – Select All Sheets

Select All Sheets
Group and Ungroup Worksheets in Excel

Add Same Value, Formula, etc. in Multiple Sheets at Once

Once the worksheets are grouped, it is so simple to add the same value, formula, and even apply the same cell formatting in the same cell in the grouped worksheets. Let us first format the header row of each of the worksheets, and do some other formatting stuffs with the sales data.

Format Same Cells in Grouped Sheets

You can write same text and same formula in a cell for all the grouped worksheets, like this-

Same Value and Formula in All Sheets

How To Ungroup Excel Sheets

It is pertinent to note that the worksheets once grouped does not automatically ungroup itself. You need to manually ungroup the worksheets in Excel by any of the following ways –

First way is by clicking on any of the sheets in the workbook (other than the grouped ones).

Like in the below image, sheets ‘Sales 2016’, ‘Sales 2017’, and ‘Sales 2018’ are grouped. To ungroup them, I have selected the worksheet named ‘Sales 2020’.

Ungroup Worksheets in Excel 1

When all the worksheets are grouped, and you do not have any ungrouped worksheet to click, then you can simply add a new worksheet, to ungroup the others. As in the below image, all the worksheets are grouped, so by clicking on the ‘Insert Sheet’ button, all these grouped worksheets gets ungrouped.

Ungroup Worksheets in Excel 2

The last method to ungroup the worksheets is by right-clicking on any of the grouped worksheets and select the option – Ungroup Sheets

Ungroup Worksheets in Excel 3

With this we have reached to the end of this blog. Share your views and comments in the comment section below. Your critics and appreciations are valuable.

Leave a Comment