An alternate way to summing sheets in excel allowing you to go through the sheets.
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”– Absa Capital