How to Remove Grouping in Excel (2 Suitable Examples)

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.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


2 Examples to Remove Grouping in Excel

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.

Remove Grouping from a Group of Rows


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.

Remove Grouping in Excel from All Grouped Rows

Finally, here is the result. It removes the grouping.

Remove Grouping in Excel from All Grouped Rows

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

Remove Grouping in Excel from Selected Rows

  • Now, make sure that Rows are selected. Then click on OK.

Selected Rows

Finally, it removes grouping from the selected rows (5 to 8).

Remove Grouping in Excel from Selected Rows

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

Remove Grouping in Excel from Rows Automatically Grouped by 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.

Rows Automatically Grouped by SUBTOTAL Function

  • In the lower-left, of the Subtotal dialog box, click on the Remove All box.

Rows Automatically Grouped by SUBTOTAL Function

Finally, it returns ungrouped data.

Rows Automatically Grouped by SUBTOTAL Function

Read More: How to Create Multiple Groups in Excel (4 Effective Ways)


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.

Remove Grouping from Worksheets


Conclusion

In this tutorial, I have discussed 2 examples to remove grouping in Excel. I hope you found this article helpful. 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.


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo