# Sum All Sheets Faster

May 04, 2017 - by Bill Jelen

A faster way to build an Excel 3D formula, also known as a spearing formula. I usually type `=SUM(Jan:Dec!H10)`, but you can do this using the mouse.

## Watch Video

• Sum a rectangular range on all worksheets
• =SUM(
• Click on Jan sheet
• point to the range
• Shift + Click on Dec sheet
• press Enter
• Or, type =SUM(Jan:Dec!B4)
• Bonus trick: Adding Total Row & Column in one click of AutoSum

### Auto-Generated Transcript

• Learn Excel from MrExcel Podcast Episode 2090
• the 3d reference with two sets of colons
• hey welcome back to the mr. cell net
• cast I'm bill jealous question actually
• came up in my seminar in Bloomington
• Indiana and I usually talk about these
• 3d references where we have all of these
• different month names and then show how
• to build a Spearing formula that will
• add up cell b4 on each of the sheets
• from January through December but what
• came up today was no we just want one
• form that's going to sum the whole thing
• and yeah that is possible equal sum and
• I want to come back here and click on
• January choose all of the cells let's
• get this out of the way choose all the
• cells I want to sum from the January
• sheet and then shift-click on to summer
• and watch this formula right now right
• now it's pointing at one rectangular
• range on one sheet when i shift-click on
• December I rewrites it into a
• rectangular range on all of this sheets
• from January through December I mean
• don't you have to click the closing
• parenthesis
• just press ENTER and it goes and grabs
• all of those numbers all the way across
• cool cool formula like that with 2 : 2 :
• in the sheet name and a colon in the
• rectangular range we're pulling back now
• what I usually show is just this little
• formula here where we have to do equal
• sum of January : December exclamation
• point B for like that regular 3d
• reference but it also works 3d reference
• pointing to an entire range like that
• hey let's just do one more bonus trick
• here since this trick was so short if
• you have to add totals across the bottom
• and down the left down our hand side
• most people come here hit the autosum
• copy across and then here hit the
• autosum and copy across check out this
• awesome awesome way I just choose all
• the numbers plus one extra column plus
• one extra row then hit the auto sum and
• adds the totals all the way around the
• outside why did the number change
• because these numbers are still ran
• between but a cool couple of tricks
• 3d references absolutely there in the
• book PowerShell MrExcel the 2017 2017
• edition click that I on the top right
• ok episode wrap up we're going to set my
• rectangular range on all worksheets
• start off by typing equals sum click on
• the January sheet highlight the range
• with the mouse and then shift-click on
• the December sheet press Enter don't
• even use a closing parenthesis or if
• you're looking for just a single cell
• equals some January : December
• exclamation point and the cell that we
• want a little bonus trick there at the
• end adding the tilde row and column in
• one click of auto sum well thanks for
• stopping by we'll see you next time for
• another net cast from MrExcel