Excel 3D Formulas Explained (Sum Across Multiple Sheets)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/3d-formulas-file

[@LeilaGharani]

Thank you for your comments and input. As mentioned by some members of our community, you can use the shift key to select the last sheet. So start typing "=SUM(" then click on the cell you want - then hold down the shift key and click on the last sheet.

[@mikaelungstruppetersen2539]

I think your formula is better. Bobs formula WOULD have been better if the formula didn't change when executed. When you have a new sheet you still have to be sure it is placed right even though its name is right for the formula. Therefore I think yours is more foolproof but perhaps Excel in time will change so the formula stays with "*Prod*" :)

[@atsrikumar1307]

Ma'am, I am very impressed. Your teachings increase my enthusiasm to learn new things. You have changed my inactive mind in to active. God Bless You!!!

[@harishkenchugonde8702]

As the number of sheet increases, solving using "SHIFT KET" Is a difficult task and creates confusion practically.. But what maam has taught is more clear and practically no issues.. Especially Bob's 3d formula😍

[@omerkatzavian7829]

Thanks for the video But the first part is possible to do by selecting the cell in the first sheet - pressing the shift button and then selecting the last sheet In my opinion it is much simpler :)

[@மின்விரிதாள்_விரிப்போம்_வாங்க]

Exactly!

[@abdanomer]

Great

[@mayankhb]

Thanks bro

[@johnnyz7752]

I love watching your videos. People say that, “Those who cannot do, teach.” But that is not true for you. It’s obvious that you are perfectly capable of being productive using MS Excel, what’s really impressive is that besides your skill and abilities, you are very capable as a teacher as well. I know a man who can make Excel sing, but he cannot teach or explain what he’s doing to someone else to save his life. You are “Excel”ent at both, pun intended. Thank you for all of the information you’ve shared with all of us who have enjoyed watching and learning from your videos. One question about 3D formulas. Can’t you select the cell where you want your formula to reside, press the “=“ key, then select the first sheet and cell with first value, hold down the shift key and simply select the last sheet within your range of sheets. Close your parenthesis and press ENTER? Easy and still dynamic.

[@LeilaGharani]

Thank you John for the very kind words. Yes - you are absolutely right. You can just select the sheets as well. I got carried away with the syntax explanation :)

[@excelisfun]

Thanks for the 3-D fun, Leila!!!

[@LeilaGharani]

You‘re very welcome Mike :)

[@mountagadiallo6433]

Hi Sir Girvin, can I use 3D references with SUMIFS ? Thanks in advance.

[@mtroanoke]

Thanks for explaining about the quote mark around the name of a tab including a space. It solved an issue I've been having with one of my spreadsheets!

[@o.christ_5th496]

Hey Leila I’ve another tip : you create two empty sheets and put all sheets you want to sum in between, that way even if you have a new sheet, Excel will add them in the selection. The syntaxe of the formula will be : sum(aaa:zzz!B5) aaa and zzz are the first and last sheet of the selection and B5 the cell.

[@garcialovesme]

Sometimes it's the little tricks that can save the average person a LOT of time. This is one of them! Thanks!

[@LeilaGharani]

Glad it was helpful!

[@binrueiyang2197]

Thanks!

[@LeilaGharani]

Thank you!

[@titanmanification]

This is a great tip! I hated the multi-sheet click. Didn't even think that the range type would work on sheets. Now I know. Thanks Leila.

[@LeilaGharani]

Glad you like the tip Chris.

[@MrDanielduranc]

Ohhhhh, The more videos I see the more I fall in love with you. You are the best!

[@stevemansfield8530]

Bob's trick is classic Bob - simple but very effective.

[@dheedoc]

Oh my.. U are the best Excel Teacher ...who knows students requirement. Thanks Leila. U hve saved so much of my time and stress.

[@funnyferns979]

This channel is awesome! I was taught this by someone years ago, but I never knew it was called “3D formulas.” We use it to add up individual product or regional P&Ls into a total P&L.

[@LeilaGharani]

Glad you like it. That's exactly how we used it in my corporate past too :)

[@mukundjoshi598]

Wow... so far ur’s is the only channel that I hv come across on KZbin which shares real cool stuff about excel. U r sharing the things that I had dreamed to find out on my own. Rest all are still sharing 20 shortcuts in excel kinda kids stuff. But I feel lucky to hv come across your channel. My optimism levels with what all I can do with excel and data are growing by each day which helps me in my data Analytics job. Many thanks to you and keep up sharing your knowledge. Truly admired. Thank you 🙏

[@MrSupernova111]

I've done multiple sheet calculations similar to this but those hacks were ridiculous! Thank you!

[@GyanOnTube]

Lelia your videos are awesome..i like the way you explain things...

[@montyearl1688]

Thanks heaps Leila. You've done a fantastic job sharing your knowledge on KZbin. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.

[@enriquedominguez9709]

Thank you Leila and Bob for these tricks.

[@fordfactor]

I wanted half my life back when I found these. That wildcard feature is cool: I'll look forward to using it! I would suggest: 1. It is easier to type =SUM( then select the first sheet in the range (Prod Game), then select the cells in that sheet (cell B3), hold Shift key down and click on the last sheet tab in the range (Prod Productivity), and Excel makes the formula automatically. No removing commas, colons etc. 2. I like to add a blank worksheet before the first and after the last sheet in the range (called say Start and End), and make the 3D formula include those sheets e.g. Start!B3:End!B3. Then if I insert a new worksheet between those two, it is automatically included in the 3D reference. The blank sheets won't affect the result. In this example, you have to make sure you add it between Prod Game and Prod Productivity.

[@LeilaGharani]

Thank you for the suggestions John!

[@karencrouch5671]

@@LeilaGharani I was going to suggest the Shift click method as well. I have also used this with other functions like AVERAGE, it will work with anything that uses ranges. I love the wild card though, that's a great trick.

[@danezuzu5768]

yes, i apply this same way as John Ford. it is easier and less prone to errors. eila, thanks for this video

[@wayneedmondson1065]

Hi Leila.. 3D formulas are great. One trick I use is to bracket the sheets subject to the formula with a left and right blank sheet with the tab names: "START >>" and "

[@LeilaGharani]

That's good spreadsheet design Wayne. Many thanks for your input!

[@CrazyFaizy]

I had been trying to understand about 3-D Reference for 6 months but no one have maked me understand. Thanks a lot Leila you have described nicely...

[@LeilaGharani]

Glad it's helpful Faisal.

[@azizahmadzaheer5827]

Thanks so much for the 3-D formula, Leila, you saved lots of my time.

[@LeilaGharani]

Glad it was helpful!

[@kamlesh.hariyani]

Thanks Leila for 3D formulas. I am big fan of yours as your videos have something different all the time.

[@vinay94095]

I have watched many videos...but this is something outstanding

[@LeilaGharani]

Thank you! Cheers!

[@cleopatrabonne7582]

Hi Leila, so grateful for your tutorials. They have helped me really simplify my spreadsheets. I do food purchases for families in different areas and so putting this into a spreadsheet has helped me especially when I have to break down the food lists into kg's, litres and grams per family, per area. Now that its perfect I can just email the list to the company and have them pack it all up, ready for me to collect. Making up spreadsheets with formulas can be very addictive

[@LeilaGharani]

I'm glad to hear that. It's great you could implement your knowledge to be more efficient.

[@Softwaretrain]

Thanks, First way can be don by holding shift but bonus was amazing. Love it.

[@LeilaGharani]

Thanks for the tip.

[@MichaelGoldner]

Thank you for sharing your knowledge! I used 3D formulas to consolidate cost center data quite a while ago. For this I used the following trick: I put the sheets between to tabs with the name >. This made the formula easier to read and more robust. Tabs that I wanted to exclude were just dragged out of the section.

[@jasminpineda5399]

Cr

[@ameen885]

This is so cool. I have to consolidate the budgeted p&l of 32 branches. This really made my task simple. Thank you so much

[@proeffect454]

kzbin.info/www/bejne/gmS6hXaeZsmFgck

[@prabhakaransankar476]

Wow, awesome. Bob's idea is so nice!

[@rina1592]

Once again Miss Leila......... Brilliant

[@PAC56]

Great tip! I am thinking of color coding like sheets to give a hint that an errant sheet was inserted in the range.

[@vivekphadke16]

Marvelous, it reiterates that we need to keep our thinking cap on while working with excel

[@LeilaGharani]

I never take it off :)

[@fadihafiz6102]

you are Great Leila , there is also another way to Try , = Sum select the first Cell in the first sheet then press Shift and select the first cell in the last sheet ;)

[@LeilaGharani]

Great tip, thanks!

[@rajanpradeepankarath8846]

Thanks Leila. I knew the 3D formula. I had been using wildcard filtering in Power Query as well. But never imagined I could combine it. Brilliant!

[@benhurjs7993]

Many did well but you excel Leila… bonus is awesome…!!! Thank you .. I love it

[@LeilaGharani]

Glad you like it!

[@mihaeladragomir8041]

Great! Your videos are really useful and very easy to understand !

[@blackknight6452]

OMG!! What a bonus is it?, thank you Bob. And thank you leila.

[@divaiokogito394]

Omg. So many years with the wrong way. Thx a lot excel Queen!!!❤️❤️❤️❤️

[@LeilaGharani]

You‘re welcome 😊 better late than never.

[@OG_GAMERS7]

Very useful formula for complex sheets.

[@anshuiway]

Marvelous!! I am really surprised 😊 to know about this type of SUM formula uses. I really appreciate your efforts. Thank you Mam for sharing this.

[@LeilaGharani]

My pleasure!

[@alessandrocosta7933]

Your videos are clear, well planned and very informative. Thank you!

[@AweshBhornya-ExcelforNewbies]

I seriously am at a loss of words. This is an awesome trick. Thanks a lot.

[@LeilaGharani]

Glad you like it :)

[@RaviSharma-vo1qo]

You, Ma'm, just saved hell lot of my time. Thanks.

[@LeilaGharani]

Glad to help!

[@muratyildiz6697]

Thanks From Turkey. Very nice tip.

[@JeanPierreWhite]

Nice. Consolidation of accounts is the application I was thinking of before you mentioned it. Accountants used to prefer Multiplan to Excel because 3d formulas were so easy in Multiplan. Nice to see Excel have that functionality.

[@dejabluek8297]

Thank you for sharing this super neat trick! I can't wait to share it with my colleagues!

[@jonasoliveira9366]

Leila, that's going to change the way I work with Excel! Thanks for sharing this!

[@manojjrkhiara240]

Very nicely explained.

[@adoaquino]

Thanks for that video. You simplify my daily task on excel. BIG Thanks !!!

[@LeilaGharani]

My pleasure. Glad it's helpful.

[@stevenng8556]

You are very hardworking and passionate for sharing of your knowledge and skills.

[@m.waqas27]

Bravo mam. Your true student.

[@suryaraochowdarykoduri]

Bob's method was terrific. Adding the asterisk on either side of the common sheet names is adding the formula for you. It is for that one instance. If you add more sheets after adding the formula with the sheet name containing the keyword "Prod" in this example, that will not be included dynamically into this formula. Hope that makes sense.

[@himanshushah13]

Wow Thanks..! you made future journey more easy..!

[@jamesthomas8249]

Worked great for my wage totals sheet. I used a blank emp sheet at the end and the 3D formula captured all from 1st to blank. Moving deleted emp sheets after blank and new emp before blank will ensure I'm capturing all the current data. Thanks!

[@LeilaGharani]

Glad it helped James.

[@saheerk5011]

Really appreciate your research and generous to bring us Madam Leila

[@josephwong1697]

This is a great tip for me. Thanks, Leila.😄

[@vijayarjunwadkar]

Cool trick! Thanks for sharing! Nice video as usual!

[@simoiyahector-morales3781]

Wonderful trick, thanks Leila

[@vijaygusain119]

And Thanks to you leila for being such a beautiful teacher! Always love your videos👌

[@LeilaGharani]

That's good to hear :)

[@krn14242]

Thanks Leila. Love the bonus tip.

[@markparry1922]

That’s a really neat trick.

[@XLGuy]

Amazing, this is why I keep believing that anything is possible in Excel. Thank you Leila for bringing such informations. 😎 I wish to be your protege, so much to learn from you.

[@tutorials--1234]

Excel CAN do anything... you can make it speak, you can control it with a bar-code scanner, I even have an Excel file that will make you a cup of coffee!

[@mnsoyan]

Great trick! Thanks! Note to myself: =SUM(‘Prod Game:Prod Productivity’!B3) =SUM(‘Prod Game:Prod Productivity’!B3:B8) =SUM(‘*Prod*’!B3)

[@Stepford]

Brilliant and useful tip as always! Thanks Leila & Bob!

[@LeilaGharani]

Our pleasure Steve :)

[@sarfarajkureshi6569]

Thanks for video, i always love your teachings

[@robertovelicaz7719]

What a time saver! Thanks Leila and Bob 😃

[@shadrackawunyo7786]

Thanks Leila. Very useful

[@osmanpathan9511]

That's Great. It will save huge time of mine. Thanks a lot.

[@dhunpagla3871]

Thanks a ton Laila ....very useful

[@bakthapuri]

Sum based on part of Sheet Name s is really awesome..I never know about it as of now.. Thanks Bob and Leila..

[@jaipalrana.]

I am using it for the last 6yrs... but using '*' sign to combine in such a way you used it is new for me. Thank :)

[@biswajitdey7339]

Great work mam. So tricky.

[@777kiya]

Great tutorial

[@ismailismaili0071]

I love it thank you so much Ms. Leila

[@Ali.Mostafa]

Wow! Amazing bonus tip!

[@oguzhanbasbug152]

Angel of Excel, thanks :)

[@LeilaGharani]

😘

[@Shubham-hg7pr]

really great work leila.... I really loved this technique

[@jasvirsingh9754]

Thanx Leila..u r an excellent teacher and off course beautiful too.....

[@Howdy1836]

This is going to save me a sheetload of clicking. Thank you

[@UmarAlFarooq]

Hilarious

[@DanielLamarche]

Wow Lelia, that is an amazing discovery. Thanks to Bob as well for that! You make me look good!!!! 8-)

[@MooseBme]

Great video, with great comments, tips and tricks! !(: Thanks :)!

[@kirandas1287]

Excellent video

[@mariaalcala5159]

Great video Leila! Thanks!

[@LeilaGharani]

You're so welcome!

[@mfzasr5665]

This is super duper fantastic

[@pravinjeewoonarain3563]

That is so beautiful!!. Thanks for sharing

[@LeilaGharani]

Thank you! Cheers!

[@rudi-gruber]

Thanks Leila - super bonus hack

[@m.arahman8990]

Thanks for this helpful video.

[@mirza1703]

Very useful formula

[@lambportuza]

Awesome video. Thank you Leila, new subscriber here. This will save a lot of time.

[@LeilaGharani]

Glad you like it. It's great to have you here.

[@not1AM]

Thanks for your great videos

[@yugalsharma3842]

Nice trick....Thanks for sharing with us .

[@andrewmoss6449]

That's a really cool trick. Thanks, Leila!

[@LeilaGharani]

Glad you like it Andrew.

[@jituadhyaru]

Awesome trick

[@44.7b.kaharaditya6]

Wow, very good trick

[@manojjrkhiara240]

Thank you so much for sharing.

[@kopytko998]

Very nice trick

[@golfdelta790]

one way to stop the accidental insetion of a tab is to have a "Start" and an "End" tab and you sum -- or any other function -- those between, doesnt stop it but can help. or you lock the stucture of the workbook to stop users changing it. Love the masking trick. pity it isnt dynamic to update the formula Great video