In an Excel workbook, you created three worksheets in which you listed the first-quarter sales figures for your company’s several business locations. For a better overview, you entered the figures for each month on a separate worksheet. That means there is a worksheet for January, another for February, and a third for March. Each of the three tables are laid out and positioned identically on their respective worksheets.
Now you notice that when you created the tables, you omitted one flavor of ice cream. You therefore want to insert the missing row into all three worksheets as quickly as possible. For this purpose, you can use Group mode which enables you to edit multiple worksheets simultaneously.
In this learning module “ Excel groups”, you will be shown how to turn on Group mode and how to use it for editing multiple worksheets.
Excel groups: HOW DOES IT WORK?
If you want to work on multiple worksheets at the same time, you can do so, provided the elements you want to work on are positioned identically on all worksheets. To switch into excel Group mode, first select the first worksheet’s tab in the row of tabs. Then press the [Shift] key. While holding the [Shift] key down, click the last tab in the row of worksheet tabs that you want included in the group. Now, when you release the [Shift] key, the first worksheet you selected, the last worksheet you selected and all worksheets between them will be selected.
In fact, the multiple worksheets you selected now belong to a excel group. You can recognize this by observing two details:
- The tabs of the selected worksheets in the group appear on white backgrounds, for example
- In the title bar, at the top of the worksheet, the word [Group] appears in square brackets, just after the filename: .
You can use the [Ctrl] key to select and group multiple adjacent or nonadjacent worksheets, too. This works in a fashion similar to how you use the [Ctrl] key to select multiple adjacent or nonadjacent cells on a worksheet. In this case, after pressing the [Ctrl] key, while folding it depressed, use your mouse to select any worksheet tabs you want included in the group, one after another. When you release the [Ctrl] key, your group will have been built.
Because your worksheets are now grouped, any action performed on one of them is immediately reflected in all others in the group. For example, if you insert a new row into the first worksheet of the group and enter data there, this row and data will automatically appear in the same position on all other worksheets in the group.
To cancel excel Group mode, you can simply click the tab of any unselected worksheet. If no worksheet is outside the excel group, however, right-click any tab of a worksheet that belongs to the group. Then, in the selection list that appears, choose the Ungroup Sheets command:
If you now click through the ungrouped worksheets, you can observe: not only have they been ungrouped, but the new data row has been identically inserted into each worksheet previously in the group.