Excel Tips: Making totals for multiple sheets

At times we are in situations where we have to add cells from multiple sheets of same structure. A typical example could be the department budget sheets, which has the same format for all departments. (Usually separate sheets are used for each department)

Usual practice:

A usual way to add all sheets and make a summary sheet would be to add each cell of different sheets one by one as shown in the picture.The formula can then be copied to all required cells in a simple fashion. This is quite OK and works perfectly fine if you have limited number of sheets.

However, think a scenario where you have several sheets, 30, 40 or sometimes even more. To make it more challenging, situations arises where new sheets are added after the initial formula has been entered. This means, the user has to either type the cell references of all those 30, 40 sheets manually or selecting the required cells one by one by moving from one sheet to other and to manually change the formula to add the new sheets. Regardless of how advanced user you are, this method is time consuming (depending upon the number of sheets) and requires manual verification to make sure all sheets are included. Sometimes it’s very annoying where you have to start from scratch if you missed to select the required cell and system will not allow you to continue due to formula error.

Do it this way …

Type “=sum” in the required cell, select the first sheet, keep pressing the Shift key and select the last sheet (this will highlight all the sheets in the range),select the cell which is being summed and press Enter. The final formula will look like “=SUM(Sheet1:Sheet8!C5)”.

This formula will add C5 cells from all the sheets in the selected range of sheets.

In case new sheets are to be added, just simply drag the new sheets in the rage of selected sheets and the formula will take care of this automatically. No need to edit the formula manually. It’s simple, quick & error free.

I will continue to add more tips which will be helpful in doing day to day tasks. If you have any specific issue, please do share and I will be glad to address it