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-
- You can enter the same text, value, etc. in the same cell or range of cells in multiple worksheets
- You can enter the same formula or perform the same calculations in the same cell or range of cells in multiple worksheets
- Delete multiple worksheets at once
- Move multiple worksheets at once
- Printing of the same area in multiple worksheets at once
- Find and Replace a value in multiple worksheets at once
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.
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).
Also Read: Group and Ungroup Rows in Excel
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:
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.
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.
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:
Yet there is another way as well. Right click on any of the worksheet tab and click on the option – Select All Sheets
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.
You can write same text and same formula in a cell for all the grouped worksheets, like this-
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’.
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.
The last method to ungroup the worksheets is by right-clicking on any of the grouped worksheets and select the option – Ungroup Sheets
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.