Grouping or Ungrouping Sheets in Microsoft Excel

 

In this article, we will learn Excel grouping and Excel Ungrouping sheets in Microsoft Excel 2010.

Grouping and Ungrouping sheets in excel is a way to perform a task quickly in one sheet & it will automatically update in the selected sheets. It saves time & eliminates any error if you want the graphical objects to be in the same position in every sheet as you perform in the master sheet. To prevent this, you can use grouping feature in sheets.

Consider you are working on large number of sheets in a workbook, if you want to perform any action on one sheet & you want the selected sheets to be able to perform the same task at the same time, you can use grouping feature in Excel.

By using this grouping/un-grouping property of Excel, you can work with flexibility to work on an individual sheet or you can work with multiple sheets at same point of time. Worksheets can be combined into a group. Any changes made to one worksheet in a group will be made to every worksheet in that group.

 

How to group sheets in Excel?

To select the multiple sheets in a workbook, you need to follow the below-mentioned steps:

  • Click on the Sheet1 tab.
  • Press and hold the SHIFT key on your keyboard.

img1

  • As a result in above snapshot, you will find that the name of the workbook in title bar has been changed to Group.
  • The sheets are now grouped.
  • You can perform any actions required, and these will be replicated in each selected sheet.

 

To select Sheet1 & Sheet3 a group of non-neighboring worksheets:

 

  • Click on the Sheet1 tab.
  • Press and hold the CTRL key on your keyboard & select Sheet3.

img2

  • The worksheets are now grouped.

 

To select all the sheets in the workbook:

 

  • Right-click on any worksheet tab & click on Select All Sheets

img3

Note:- Excel shows you worksheets that you select by turning their sheet tabs white (although only the active sheet’s tab name appears in bold) and displaying [Group] after the file name of the workbook on the Excel window’s title bar.

How to ungroup in Excel?
To Ungroup All Worksheets when you finish your group editing, follow these steps:

  • Right click to grouping worksheet in the Excel, then select Ungroup Sheets

img4

The worksheets will be ungrouped. Alternatively, you can simply click a non-selected (that is, grayed out) worksheet tab. And all the grouped sheets in the workbook will be ungrouped.



6 thoughts on “Grouping or Ungrouping Sheets in Microsoft Excel

  1. I can see how this works, but I have a spreadsheet that I no longer want the files grouped, which is easy after opening. But every time I open the file the sheets are grouped, and I can’t find a way to save the workbook without the grouped sheets re-appearing when I open a later time.

    • Hi Pete,

      Kindly share more details about this spreadsheet. This spreadsheet is used by multiple user or not, or whether this spreadsheet contains macro in it or not.

      There can be chance that some macro code must be running which result in grouping of sheets before closing or before opening. You can check following two procedure in thisWorkbook in VB Editor and edit the macros according to your requirement.

      Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Private Sub Workbook_Open()

      Regards
      Ramandeep Singh

  2. Excel document is blurred when open on windows 10 and some of windows 7 machines, office 2016, the excel document has some embedded sheets … I doubt so! Any suggestions, when we double click on the excel sheet it opens with the original document with all tabs, my client requirement was just to send out the output excel sheet and not the whole sheet …

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>