To create summary sheet from multiple worksheets – Shortcut SUM across sheets in excel – Awesome Trick

Summary sheet from multiple worksheets

Sometimes we have huge number of sheets in excel and we want summary sheet which indicate total of all data in multiple sheets. Normally we use + sign and go to each tab like below –

The above process is very time consuming when you have more number of sheets available in your excel. Also it chances to forget one or more tab when we use this old process. Let’s move on. Today I am going to show you how you can easily prepare summary sheet within just 10 seconds by using SUM formula. There is some trick need to use with SUM formula to get total of all sheets.

Let’s Start –

It is very simple when first and last sheet names are in single word. 

First type =SUM(  in cell where you want total of all others sheet’s cells. Then go to first sheet and select cell like below -

IMPORTANT

Then delete cell address (here A1) and explanation mark ( ! ) from formula.

Type colon ( : ) and go to last sheet and select same cell and then hit Enter.

Then simply use fill handle to update formula in other cells.

If your sheet name contains more than one word to create summary sheet from multiple worksheets

If tab name contain more than one word then above trick not work. You’ll get error.

Then what to do??

Either you can rename first or last sheet to one word and use above trick then again rename them as previously to create summary sheet from multiple worksheets

OR delete below things from formula –

1. First sheet cell address
2. Last - Single inverted commas ( ‘ ) from which automatically add in First sheet cell address
3. First - Single inverted commas ( ‘ ) from which automatically add in Last sheet cell address

Off course first rename option is better. I always prefer that option.

In that way you can easily prepare summary sheet within less than 10 seconds.

Note – In case of hidden tab – Above formula also calculate values which available on hidden tab. So make sure you don’t have hidden tab or your hidden tab should be after last sheet.

If you are facing any issue then leave your comments.

Tutorial Video -  

Hope you understand. Thank you. :)

Thank you !! Enjoy Excelling !! Have a Nice Day !!