In Excel, you can ungroup worksheets. There are a couple of methods for ungrouping Excel sheets. In this article, I’m going to show how to ungroup worksheets in Excel using 5 quick methods.
Why Do We Need to Ungroup Worksheets in Excel?
If you want to do the same tasks or formatting for some different sheets, then you can group the sheets. But when you want to do individual changes or to avoid unwanted data changes then you must ungroup the sheets.
Ungroup Worksheets in Excel: 5 Quick Methods
To make this article more understandable, we are going to use an Excel workbook where we have kept grouped worksheets to show you how you can ungroup worksheets.
1. Using Context Menu Bar to Ungroup All Worksheets
We can use the Context Menu feature to Ungroup All Worksheets. The steps are given below
- First, put the cursor in the grouped sheets then right-click on the mouse to bring the Context Menu.
- From the Context Menu select Ungroup Sheets.
Here, you will see all the grouped worksheets are ungrouped.
2. Use of CTRL Key to Ungroup Selected Worksheets
We can Ungroup selected sheets by using CTRL Key. Already we have the Grouped Worksheets. Now, we will Ungroup selected worksheets.
- Firstly, hold the CTRL Key.
- Then, select the particular Sheet that you want to Ungroup.
Here, you will see a particular worksheet (“Dataset”) which will be ungrouped.
3. Using SHIFT Key to Ungroup Worksheets
In this method, you can use the SHIFT Key to Ungroup Worksheets. For the explanation purpose, we have already grouped some sheets. Now, we will Ungroup some defined Sheets.
- Firstly, hold the SHIFT key.
- Secondly, select any adjacent or non-adjacent sheet.
As a result, you will see that the Grouped sheets are Ungrouped.
4. Employing Mouse Pointer Feature to Ungroup All Worksheets
You can employ Mouse Pointer Feature to Ungroup All Worksheets. Using the same worksheets, we will show this feature to you.
- Put the Mouse Pointer on any Adjacent Sheet.
Therefore, we will Ungroup All the Worksheets.
5. Use of VBA to Ungroup All Worksheets
We can use the VBA code to Ungroup All Worksheets. To execute any VBA code, we need to save the Excel file using .xlsm extension.
- Firstly, you must Choose the Developer tab >> then select Visual Basic.
- Now, from the Insert tab >> select Module.
- Write down the following code in the Module.
- Here, we have created a Sub Procedure Ungroup_Worksheets().
- Then used the Sheets Object to declare the sheet name which is “Dataset”.
- Next, used the Select method to select the sheet for that all other Grouped sheets will be ungrouped.
- Now, Save the code then go back to Excel File.
- From the Developer tab >> select Macros.
- Then, select Macro (Ungroup Worksheets) and click on Run.
Here, we will see the Ungrouped Worksheets from the grouped one.
Things to Remember
- By using the SHIFT key you can Ungroup adjacent or non-adjacent or any worksheets.
Download Practice Workbook
You can download the practice workbook from here:
We hope you found this article helpful. Here, we explained 5 different ways of how to ungroup worksheets in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.