An alternate way to summing sheets in excel allowing you to go through the sheets.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Summing sheets in Excel
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. However, there is another way for summing sheets in excel.
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 4th 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.