How to Ungroup Worksheets in Excel (5 Easy Ways)

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.

Download Practice Workbook

You can download the practice workbook from here:


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.


5 Quick Methods to Ungroup Worksheets in Excel

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.

How to Ungroup Worksheets in Excel


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

Steps:

  • 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.

How to Ungroup Worksheets in Excel

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.

Steps: 

  • Firstly, hold the CTRL Key.
  • Then, select the particular Sheet that you want to Ungroup.

How to Ungroup Worksheets in Excel

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.

Steps: 

  • Firstly, hold the SHIFT key.
  • Secondly, select any adjacent or non-adjacent sheet.

How to Ungroup Worksheets in Excel

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.

 Steps: 

  • Put the Mouse Pointer on any Adjacent Sheet.

How to Ungroup Worksheets in Excel

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.

Steps:

  • Firstly, you must Choose the Developer tab >> then select Visual Basic.

How to Ungroup Worksheets in Excel

  • Now, from the Insert tab >> select Module.

  • Write down the following code in the Module.
Option Explicit

Sub Ungroup_Worksheets()

Sheets("Dataset").Select

End Sub

How to Ungroup Worksheets in Excel

Code Breakdown

  • 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.

How to Ungroup Worksheets in Excel

  • Then, select Macro (Ungroup Worksheets) and click on Run.

How to Ungroup Worksheets in Excel
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.

Conclusion

We hope you found this article helpful. Here, we explained 5 different ways of how to ungroup worksheets in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Musiha
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo