Transcripts for the above video clip:

Summing Sheets

Excel gives you the ability to add together a number of sheets to get a result.  So, for example, you will see on the sheet shown we have some totals for Revenue and some other items. You will notice there is also a sheet 2, a sheet 3 and a sheet 4.  What you could do is say with C12 highlighted = go to sheet 2 and click on C12, put +, C3 the same, and if you press ENTER you will get the total. You can do that for as many sheets as you want. There is however another way you can do it. You click in a cell (C12) and type in =SUM(    then go to the first worksheet and click on C12 and now holding the SHIFT key down, go to the last sheet which will be  added- at the moment we just want to add sheets 2 and 3. When I click on it – you’ll see the formula changes in such a way that you see Excel is going to add all the C12s from sheet 2 to sheet 3- close the brackets and when I press ENTER you’ll see the total is correct (it has added the C12’s in both sheets). Now you can copy down and all the information has been copied across from the 2 sheets. The real power of this however is in the semantics – you’ll see the formula is saying that it is summing  from sheet 2 to  summing sheet 3 and the cell reference is C12. If you open the 4^th sheet- you’ll see the total in C12 is 30 000 – this number is not currently included in the calculation. The numbers in sheet 1 are 300, 0, 300 and 30; on sheet 2 it is 100, 0, 100 and 10; in sheet 3 it is 200, 0, 200 and 20. When you click on sheet 4 and drag it across so it lies between sheet 2 and sheet 3. If you look at the total in sheet 1, you will see that it has included the C12 total of sheet 4 in the calculation. This is because of the way the formula is written – it is saying please sum sheets from 2 to 3 and anything in between. If you don’t want sheet 4 in the sum then click on the worksheet name and drag it outside the area and if you look at sheet 1 you will see we are now back to the original calculation. This is a great way to do consolidations where you are not sure whether  the divisions are  in or out – it allows you to change your calculations a lot more easily than clicking into a cell and deleting or adding on references.