Download ArticleDownload ArticleThis article was co-authored by How.com.vn Staff. Our trained team of editors and researchers validate articles for accuracy and comprehensiveness. How.com.vn's Content Management Team carefully monitors the work from our editorial staff to ensure that each article is backed by trusted research and meets our high quality standards.
The How.com.vn Tech Team also followed the article's instructions and verified that they work.
This article has been viewed 265,637 times.
Learn more...
Although grouping data in Microsoft Excel can be a useful way to preserve consistent formatting, ungrouping it may be desirable if you wish to make sheet-specific changes. To ungroup sheets, right-click on one of the sheets and select "Ungroup" or press ⇧ Shift while clicking one of the grouped sheets. Similarly, ungrouping rows or columns is done by selecting the desired data range and choosing "Ungroup" from the "Data" tab (or using Windows/Mac keyboard shortcuts). Don’t forget to save your work before making changes to your groups!
Steps
Identify the sheets that are grouped. The tabs for grouped sheets will be highlighted in a similar shading or color and the text on the tab of the active sheet in the group will be bold.
Right-click one of the grouped sheet tabs and choose "Ungroup Sheets" from the pop-up menu. The sheets will ungroup and you can make changes without affecting all sheets.[1]Advertisement
Alternately, Press ⇧ Shift while clicking an active sheet within the current group.[2]
Regroup worksheets after making changes (optional). Press and hold Ctrl (Windows) or ⌘ Cmd (Mac) and left click the worksheet tabs you want grouped. The sheets will be grouped upon releasing the key.
Determine if your data were grouped manually or automatically. If your data was grouped with the “Group” button then it was done manually. Groups are sometimes created automatically by functions, like Subtotal, which can be identified by a “Subtitle” row under the grouped data.
Click the “+” button to expand the group (if hidden). This button is placed to the left of the spreadsheet. If the group is already expanded, a “-” is shown instead. Expanding will reveal any hidden groups or rows.
Click and drag to highlight all of the rows or columns in the group.
Click the "Data" tab. This is located in the top menu bar and will bring up a set of data-specific controls.
Determine if your data were grouped manually or automatically. If your data was grouped with the “Group” button then it was done manually. Groups are sometimes created automatically by functions, like Subtotal, which can be identified by a “Subtitle” row under the grouped data.
Click the "Data" tab. This is located in the top menu bar and will bring up a set of data-specific controls.
Click the "Subtotal" button. This button is on the right of the data toolbar in the "Outline" section and will bring up a dialog box.
Click the "Remove All" button. This button is in the lower left of the dialog box and will ungroup all data and remove the subtotals.
Expert Q&A
Video
Tips
- You can save the file as a new version before working with the subtotal or grouping features in your spreadsheet. This will keep you from having to return it to its previous state after ungrouping and removing subtotals.Thanks
References
- ↑ http://www.officetooltips.com/excel/tips/grouping_and_ungrouping_sheets.html
- ↑ http://www.howtoexcelatexcel.com/excel-tips-tricks/grouping-and-ungrouping-worksheets-in-excel/
- ↑ https://support.office.com/en-us/article/Outline-group-data-in-a-worksheet-21147cfc-44c9-42c4-8bce-62fb3e067d73l
- ↑ https://exceljet.net/keyboard-shortcuts/ungroup-rows-or-columns
About This Article
Reader Success Stories
"Worksheet grouping has just happened when we got an updated version of Excel for Mac. I have tried to undo this for months and never could figure it out. This article was perfect. Thank you."..." more
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
