In Microsoft Excel, grouping data makes it easier to preserve consistent formatting, but ungrouping it may be necessary if you want to make sheet-specific changes. In this tutorial, you will learn 2 examples to remove grouping in Excel.
How to Remove Grouping in Excel: 2 Examples
In the following reading, you will find answers on how to remove grouping from a group of rows and worksheets.
1. Remove Grouping from a Group of Rows
In this section, you will see how to remove grouping from the data that were grouped manually and automatically. The first two examples remove the manual groupings. The last one removes automatically created grouping.
The following image is a dataset of manual grouping.
1.1 All Grouped Rows
For removing grouping from all rows simultaneously, clear the outline. To do this, follow the steps below.
📌 Steps:
- First, go to the Data tab >> Outline >> Ungroup >> Clear Outline.
Finally, here is the result. It removes the grouping.
💬 Notes:
- There is no data loss when you remove the outline in Excel.
- A cleared outline might leave some collapsed rows hidden after you remove the outline.
- After you remove the outline, you cannot restore it with the Undo button or shortcut (Ctrl + Z). In this case, you will have to redraw the outline.
1.2 Selected Rows
The following steps will remove grouping from specific rows without removing the entire outline:
📌 Steps:
- First, select the rows (5 to 8) from which you want to remove grouping. Then, go to the Data tab >> Outline >> Ungroup >> Click on Ungroup. An Ungroup dialog box will pop up.
- Now, make sure that Rows are selected. Then click on OK.
Finally, it removes grouping from the selected rows (5 to 8).
💬 Notes:
Rows that are not adjacent to each other cannot be ungrouped at the same time. The above steps must be repeated for each group separately.
1.3 Rows Automatically Grouped by SUBTOTAL Function
Under the grouped data, you will often see a “Subtitle” row, which indicates the automatic creation of groups by functions, like SUBTOTAL. The following image shows the rows automatically grouped by the SUBTOTAL function.
To remove this kind of grouping, just follow the steps below.
📌 Steps:
- First, select any cell of the group. Then, go to the Data tab >> Outline >> Subtotal. A Subtotal dialog box will pop up.
- In the lower-left, of the Subtotal dialog box, click on the Remove All box.
Finally, it returns ungrouped data.
Read More: How to Create Multiple Groups in Excel
2. Remove Grouping from Worksheets
Grouped sheets’ tabs will be highlighted in similar colors and the active sheet’s tab will have bold text on it. Choose “Ungroup Sheets” from the pop-up menu when right-clicking one of the grouped sheet tabs. It will ungroup the sheets.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In this tutorial, I have discussed 2 examples to remove grouping in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
