Grouping Worksheets in Excel

In a recent training session on Excel basics, one panicked participant reported that she was suddenly unable to access or use any tools on the Insert tab of the ribbon. The majority of the icons there were gray. When the workshop assistant arrived on the scene, the participant and the assistant discovered the reason for the problem by looking at the top of the Excel window. The status of the worksheet displayed in the title bar:


The participant had accidentally grouped multiple sheets in the workbook together. How did she do this? And why does Excel make this functionality available? (In other words, are there reasons I would want to do this on purpose?)
Let’s start with the second question first – why group? Grouping allows a number of tasks to be performed on all sheets in a group at the same time. You could give all the grouped sheets the same header and/or footer simultaneously. You could run spell check on all the grouped sheets at the same time. You could send all the grouped sheets to the printer at once. You could even enter text in a cell and have that exact text entered in the same cell in all of the grouped sheets at once. This could be helpful in setting up a workbook to contain several sheets with similar content and format.
As may seem obvious from our participant’s experience in the classroom, grouping sheets is not at all difficult to do. Whenever you select more than one worksheet, those sheets are grouped. You can select multiple sheets by clicking a sheet’s tab at the bottom of the window, then holding the shift key or the ctrl key, and clicking other sheets’ tabs. You can also just right-click on a sheet’s tab and choose “Select All Sheets” if you want to group all the sheets in the workbook. When the sheets are grouped, you will see [group] in the title bar of the workbook. If you save and close a workbook with sheets grouped, they will still be grouped when the workbook is opened again.
The conveniences and the dangers of grouping sheets are the same: whatever you do to one of the grouped sheets you are doing to all of the grouped sheets. Whenever you group sheets to perform a task, it is important to remember to ungroup before continuing to work with sheets individually. There are a couple of ways to ungroup sheets. You can right click the tab of one of the grouped sheets and click “Ungroup sheets” from the menu. You can also remove grouping by clicking on a tab of a sheet that is not in the group, or by clicking on any single sheet’s tab.

One Comment

  1. Ciekawostki

    I used to work a lot with Excel, with huge ammount of data and VBA for running tasks… and I thought that I’m an expert… but didn’t realise that grouping can let you edit few sheets in a time! Great post. Thx.

Leave a Reply

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