Summing Values across Worksheets

Formulas Referencing Worksheet Groups

To sum values across worksheets you’ll insert a formula that references a worksheet group. In the first worksheet, click in the cell you want the sum to appear, type =SUM(. Do not press the Enter key.  A ScreenTip with the SUM function and its arguments appears. Group the worksheets and on the last sheet click the cell where you want the sum to appear and type ) (end parentheses to the formula you began on the first worksheet).  Click the Enter button  (the Green check mark on the Formula bar).  The first worksheet is redisplayed and the formula =SUM(Sheet1:Sheet3!B9) appears in cell B9 (or whatever cell you used)  The worksheet names would be whatever names you gave them, of course, rather than Sheet1 and Sheet3. To copy the SUM Formula to the other cells, click the cell where the SUM formula appears and then drag the fill handle down over the range to be filled. One of the advantages of consolidating data using formulas across the worksheets is that if you change the value in one worksheet, the consolidation formula will automatically show the result of the change.

To calculate the total expenses and earnings or amount of cash coming in, you’ll insert a formula referencing a worksheet group which includes the January sheet through the December sheet.

  1. To insert the total expenses and earnings in the Annual Sheet, click cell B9 and then type =SUM(.  Do not press the enter key.  A ScreenTip with the SUM function and its arguments appears.

summing values image

  1. Click the January tab.
  2. Click the Last Sheet button and hold down the Shift key, and then click the December tab.  Release the Shift key.
  3. Click cell B9, type ), and then click the Enter button (the Green check mark on the Formula bar).

summing values image

The Annual worksheet is redisplayed, and the formula =SUM(January:December!B9) appears in cell B9. The total artistic expenses for the year is 7,500.  Now you need to copy the SUM Formula to the other Expenses cells.

  1. If necessary, click cell B9 and then drag the fill handle down over the range B9:B15.  Excel fills in the rest of the expense values for the entire year.  The Expenses total for the choir is 163,700.
  2. Click cell B17 and then using the same method described above, enter the formula =SUM(January:December!B17) in the cell—(it equals 35,160).  You need to copy the formula to the other inflows cells—(the Inflows equal $164,950).
  3. If necessary, click cell B17 and then drag the fill handle down over the range B17:B22.  Excel fills in the total inflows for the year.   The choir received a total of 164,950 from contributions, concerts, and other sources of income.  The ending cash balance for the entire year in cell B6 is equal to the ending cash balance in the month of December as it should be.

summing values image

  1. Click cell B4 and then change the cell content to All Months. 
  2. Save your work.

One of the advantages of consolidating data using formulas across the worksheets is that if you change the value in one worksheet, the consolidation formula will automatically show the result of the change.

To change the events expense from January:

  1. Click the January tab.
  2. Click cell B11 and then change the value to 500.  Now see what effect this change has had on the annual cash flow.
  3. Switch to the Annual worksheet.  The ending cash balance has dropped to 1,670 and the total expense has increased to 164,200.  To verify the worksheet is operating properly, compare the ending cash balance for the year with the ending cash balance for December.
  4. Switch to the December sheet to view the ending cash balance for December which should be 1,670 as well.
  5. Switch to the Annual worksheet.
  6. Save your work.

Return to Course Topics.