Grouping Worksheets
Selecting Worksheets
Once you group a collection of worksheets, any changes you make to one worksheet are applied to all sheets in the group. To select adjacent worksheets, click the sheet tab of the first sheet in the group, press and hold down the Shift key, and then click the sheet tab of the last sheet in the the group.
To select nonadjacent worksheets, click a sheet tab of one of the worksheets and then hold down the Ctrl key as you click the sheet tabs of the other worksheets to be included in the group.
Entering Formulas in a Worksheet Group
Once you’ve grouped the worksheets, the worksheet tabs are highlighted meaning they are all selected. In the title bar, you’ll see the caption “Group.” Click the First Sheet button to return to the first sheet in the worksheet group. Enter a formula in a cell on any sheet and it is entered in that cell on every sheet. REMEMBER: any changes you make to one sheet are applied to the other sheets so if you delete or enter a value in one cell, values in that cell are entered or deleted in all the sheets.
Project 11: Entering Formulas in a Worksheet Group
- Open the Singers.xls workbook and on the documentation sheet put today’s date and your name. Save. Look at the sheets for the other months and see that they have expenses entered but not summed and no running totals.
- To group the worksheets, select the January worksheet and click the Last Sheet button to display the last tab in the workbook (December). Hold down the Shift key and click on the December tab and release the Shift key. The worksheet tables for January through December are highlighted meaning they are all selected. In the title bar, you’ll see the caption “Group.”
- January is the active sheet so enter in cell B8 the formula to total expenses =SUM(B9:B15) (it equals 14560) either by typing in the cell references or by pointing to the cell. Also, you can use the AutoSum button to enter the SUM function and drag the pointer over the cell range. See the value displayed in the active cell below as well as the formula displayed in the formula bar.
- In cell B16, enter =SUM(B17:B22) (it equals 35510).
- In cell B23, enter the formula =B16-B8 (it equals 20950). You are subtracting Expenses from the Earnings to find Net Income.
- In cell B6, enter =B5+B23 (you are adding the Starting Cash Balance to the Net Income).
The ending balance for January in cell B6 should be 21870. Since the worksheets are grouped, the formulas you entered into the January worksheet are also entered into the rest of the sheets in the group. Look at the other sheets to view formulas. REMEMBER: any changes you make to one sheet are applied to the other sheets so if you delete or enter a value in one cell, values in that cell are entered or deleted in all the sheets.
- Click the February tab—the ending cash balance is -2690. The starting cash balance in cell B5 is 0, which doesn’t carry over the cash from January into February so you’ll do that after formatting the group. Save your work and go on to Formatting a Worksheet Group.
Click here to return to Course Topics.