How to Ungroup Worksheets in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

Download Practice Workbook

You can download the practice workbook from here:

 


Conclusion

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.


Related Articles


<< Go Back to Grouping | Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo