Hello everyone! Did you know…if we wanted in Microsoft Excel a sum of multiple sheets we can do this in different ways depending on circumstances.
click to INTERACT
Way One for a sum of multiple sheets
We could enter “=”, select another sheet and click the cell, then key in the “+” sign and repeat this process (i.e. select another sheet and cell, then “+”) for each additional cell in each additional sheet you want to sum and press return at the very end.
Alternatively, if the cell you are adding is in the same position for each of the multiple sheets you could have entered “=SUM(“, click on the cell adding in the first sheet, hold down the shift key and at the same time press on last sheet of the multiple sheets being added through and press “)” and return.
Why not also check out the Consolidate command in the Data Ribbon under the Data Tools group – that’s another way in Microsoft Excel to get a sum of multiple sheets.
“The DOS Days”
I often think about my experiences with spreadsheets back in the 80’s. Back then Microsoft Windows was just in its infancy and I would instead have used spreadsheet packages under the old DOS operating environment. The strange thing about this was that for each spreadsheet file all the data would be on the one single spreadsheet. In today’s parlance – everything was on the one sheet. The reason why today we have multiple sheets is mainly for ease of navigation purposes. We can give a name to each sheet which meets our own specific categorisation and arrange our data accordingly, e.g. “Summary” or “Details”. In this way we can get to where we want to go that bit quicker.
When issuing this command to Microsoft Excel take note of the different options in the Consolidate dialog box. Ticking “Use labels in” “Top row” and / or “Left column” tells Microsoft Excel to use the column headings and / or the row labels to control how the numbers are accumulated. Such a ticking off means don’t consider position when doing that particular consolidation. Ticking “Create links to source data” allows us to go one step further by, instead of getting just values as being the result of the consolidation, you will now get both the values and their underlying formulas. Good for checking if Consolidate is working as intended!
click to INTERACT