Free Intermediate Excel Training Online Lesson Two: Grouping Sheets

2KO Africa welcomes you back to our free Intermediate Excel training online. We are based in Cape Town, and have been offering classes from beginner to advanced level for 20 years. Our style of training is varied, and students can learn online, in class with an instructor, blended learning or we can do the training at your premises for groups.

 

Lesson 2

Group Sheets in Excel to Make Simultaneous Changes

When you have a scenario where you have multiple sheets laid out in a similar fashion, but with different data, these sheets can be grouped for changes across multiple sheets - to be made simultaneously. The idea of grouping sheets together allows you to make changes to one cell, or a range of cells in one sheet, but the same cells in all grouped sheets will be changed in the same way, instantly.

 


When Should we Group Sheets?

There are at least two quick occasions that come to mind. The first one is when you need to create an Excel Workbook that will contain data for multiple sets / venues / towns / items on different sheets. Say for example you have a business selling shoes, with branches in Cape Town, Hermanus, Port Elizabeth and George. The shoes that are sold are the same brands throughout all stores. Then one can start a new workbook by laying out data framework on multiple sheets at the same time.


A second occasion is where you already have a layout with multiple sheets, all laid out or based off a template. The layout, being the same on all sheets allows for data entry and formatting to be applied to all sheets, or a subset of sheets - in one go.


How to Group Sheets

Press and hold down the Ctrl key, and then click the worksheet tab(s) you want to group.


Tip: If you want to group consecutive worksheets, click the first worksheet tab in the range, press and hold the [Shift] key, and click the last worksheet tab in the range. All sheets will be selected from the initial one to the last one clicked.


To group all sheets in the active workbook, right click any sheet tab, and choose Select All Sheets. To ungroup a series of worksheets which are already grouped, right click any sheet tab, and choose Ungroup Sheets.


How to know if Excel is in Group Mode?

While you're working in group mode, Excel displays the workbook's name with [Group] in the title bar, just next to the filename. Don't forget to ungroup sheets when you are finish applying changes to multiple sheets. To ungroup sheets, right-click on any sheet tab in the group and choose Ungroup Sheets, or click any tab not in the group. If you want to ungroup and stay at the current sheet, hold down the [Shift] key and click the active tab.


How to Duplicate an Existing Formula or Cell Value to Other Sheets

If you need to enter a formula (or value) into multiple sheets, group the sheets and enter the formula once on the active sheet. To copy an existing formula or value to another sheet in the same workbook, you can copy and paste normally. Or you can create a group:

 

  • Select the sheet that contains the formula or value you want to copy-the source sheet.

  • Hold down [Shift] or [Ctrl] and click the tabs you want to include in the group-the target sheets.

  • With a sheet group now active, select the cell that contains the formula or value.

  • Press [F2].

  • Press [Enter].


Pressing the Edit Key F2 activates the contents of the cell. When you press Enter, the contents of the active cell in the active sheet is pasted onto the corresponding cells in any other grouped sheets.


How to Delete a Group of Sheets

One of the benefits of grouping sheets, can be to quickly delete multiple unneeded sheets. In a sense this is similar to selecting multiple celles or ranges using the CTRL key. Deleting multiple sheets all at once is easy, and the sheets need not be in a contiguous selection. The CTRL key allows you to add non-contiguous sheets to a group, which can then be deleted one right-click on a Tab.


How to Print Multiple Sheets Quickly

To print multiple sheets in one go, start by creating a grouping of sheets. While in Group Mode, start the printing task as you normally would.
 

 

 

Practice on your own

In a New Blank Workbook do the Following:

 

  1. Add sheets so that you have 5 blank sheet

  2. Group sheets 1 to 4, but not 5


     

  3. In B1, type the word January and enter it

  4. Click sheet 5 (which will ungroup the sheets), and you should see B1 is still blank.

  5. Select sheets 4 or 3 or 2 or 1, and you will see the entry January in B1, on each of those sheetsNow select sheet 1 and then select all sheets

  6. Press the Edit Function Key, F2

  7. Then press the enter key and go back and click on sheet 5, and you will now see January is also on sheet 5

  8. Select sheets 1,3 and 5, but not 2 or 4

  9. Use autofill to drag January down to row 12, which will create a list of months up to December

  10. Check sheets 1,3 and 5 will contain the list, but sheets 2 and 4 will contain only January

  11. Delete sheets 2 to 5

 

To start lesson 3, click here

To go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here