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!!!
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😍
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.
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*" :)
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.
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 :)
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.
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 "
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!
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 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.
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.
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 :)
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 ;)
Thanks heaps Leila. You've done a fantastic job sharing your knowledge on TH-cam. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.
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.
Wow... so far ur’s is the only channel that I hv come across on TH-cam 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 🙏
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
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.
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.
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!
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.
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!
Your Videos (Tutorials) has been great help to my profession & never try to miss them, by the way , I'm a Civil Eng professional frm Nepal. This time, I want a Project management plan based on new version MS project, as I've prepare work plan for a multi storey Building. thanks.
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
Hello Leila, I have been a secret admirer of your tutorials for about 5/6 months now. I find them very clear and useful to me. I am in project mgt/admin and will like something to manage my Purchases and Requisition, Invoices, Worker's Payroll and Inventory /Stock Taking Can you refer something that would be of benefit? Thanks and keep up this beautiful work.
Thanks for the tip it is golden! But it seems like the wildcard doesn't work if you insert the "Other_items" in between the sheets after you write the formula with the wildcard
Grab the file I used in the video from here 👉 pages.xelplus.com/3d-formulas-file
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!!!
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😍
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.
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*" :)
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.
Cr
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!
Great
Thanks bro
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.
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.
Glad you like the tip Chris.
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 "
Absolutely agree. These visual reminders are very helpful. And Shift is a quick way to reference. Thanks for your input!
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!
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.
Thank you for the suggestions John!
@@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.
yes, i apply this same way as John Ford. it is easier and less prone to errors. eila, thanks for this video
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.
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 :)
Oh my.. U are the best Excel Teacher ...who knows students requirement. Thanks Leila. U hve saved so much of my time and stress.
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 ;)
Great tip, thanks!
Thanks heaps Leila. You've done a fantastic job sharing your knowledge on TH-cam. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.
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.
Glad you like it. That's exactly how we used it in my corporate past too :)
Wow... so far ur’s is the only channel that I hv come across on TH-cam 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 🙏
I've done multiple sheet calculations similar to this but those hacks were ridiculous! Thank you!
Ohhhhh, The more videos I see the more I fall in love with you. You are the best!
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
I'm glad to hear that. It's great you could implement your knowledge to be more efficient.
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...
Glad it's helpful Faisal.
Thanks, First way can be don by holding shift but bonus was amazing.
Love it.
Thanks for the tip.
Lelia your videos are awesome..i like the way you explain things...
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!
Sometimes it's the little tricks that can save the average person a LOT of time. This is one of them! Thanks!
Glad it was helpful!
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
th-cam.com/video/L3WTCh1dRKg/w-d-xo.html
Thanks for the 3-D fun, Leila!!!
You‘re very welcome Mike :)
Hi Sir Girvin, can I use 3D references with SUMIFS ?
Thanks in advance.
Thanks Leila for 3D formulas. I am big fan of yours as your videos have something different all the time.
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.
My pleasure!
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.
I have watched many videos...but this is something outstanding
Thank you! Cheers!
Marvelous, it reiterates that we need to keep our thinking cap on while working with excel
I never take it off :)
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.
Thank you Leila and Bob for these tricks.
You are very hardworking and passionate for sharing of your knowledge and skills.
Your videos are clear, well planned and very informative. Thank you!
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!
Glad it helped James.
Bob's trick is classic Bob - simple but very effective.
Thank you for sharing this super neat trick! I can't wait to share it with my colleagues!
Many did well but you excel Leila… bonus is awesome…!!! Thank you .. I love it
Glad you like it!
Thanks so much for the 3-D formula, Leila, you saved lots of my time.
Glad it was helpful!
Great! Your videos are really useful and very easy to understand !
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 :)
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.
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!
Very useful formula for complex sheets.
Great tip! I am thinking of color coding like sheets to give a hint that an errant sheet was inserted in the range.
Really appreciate your research and generous to bring us Madam Leila
Omg. So many years with the wrong way. Thx a lot excel Queen!!!❤️❤️❤️❤️
You‘re welcome 😊 better late than never.
Wow, awesome. Bob's idea is so nice!
Sum based on part of Sheet Name s is really awesome..I never know about it as of now.. Thanks Bob and Leila..
Thanks for that video. You simplify my daily task on excel. BIG Thanks !!!
My pleasure. Glad it's helpful.
Leila, that's going to change the way I work with Excel! Thanks for sharing this!
And Thanks to you leila for being such a beautiful teacher! Always love your videos👌
That's good to hear :)
I seriously am at a loss of words. This is an awesome trick. Thanks a lot.
Glad you like it :)
OMG!! What a bonus is it?, thank you Bob. And thank you leila.
Great trick! Thanks!
Note to myself:
=SUM(‘Prod Game:Prod Productivity’!B3)
=SUM(‘Prod Game:Prod Productivity’!B3:B8)
=SUM(‘*Prod*’!B3)
Wow Thanks..! you made future journey more easy..!
Your Videos (Tutorials) has been great help to my profession & never try to miss them, by the way , I'm a Civil Eng professional frm Nepal. This time, I want a Project management plan based on new version MS project, as I've prepare work plan for a multi storey Building. thanks.
Cool trick! Thanks for sharing! Nice video as usual!
Thanks From Turkey. Very nice tip.
This is going to save me a sheetload of clicking. Thank you
Hilarious
This is a great tip for me. Thanks, Leila.😄
Thanks for video, i always love your teachings
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
Great Trick Leila. Very Helpful :) :) Excel Summit 21 - 22 April in London.....wish I can be there.
Glad you like it John. Looking forward to seeing you in London.
Thanks LG for another cool trick, we always learn something new from you.
Thumbs up 👍
very useful and valuable content and so smart on your way of explanation as usual ❤❤❤
Wonderful trick, thanks Leila
Very nicely explained.
So awesome! Can this in anyway be combined with dynamic arrays (like with filter)
Brilliant and useful tip as always! Thanks Leila & Bob!
Our pleasure Steve :)
Wow! Now "stars shining bright above..." me🎶 🤣 I didn't know I can use * in formulas calling sheets! Thanks for this and for all of you. 👏
Thanx Leila..u r an excellent teacher and off course beautiful too.....
Once again Miss Leila......... Brilliant
thank you for your informational content..it always help me from my work..lovelots!
So glad!
really great work leila....
I really loved this technique
Awesome video. Thank you Leila, new subscriber here. This will save a lot of time.
Glad you like it. It's great to have you here.
Wow Lelia, that is an amazing discovery. Thanks to Bob as well for that! You make me look good!!!! 8-)
Sorry I can't speek full english!
But about this video clip,
Very very nice !
Thank you so much for your teaching of excel.
Thanks again!
You, Ma'm, just saved hell lot of my time. Thanks.
Glad to help!
Great video, with great comments, tips and tricks!
!(: Thanks :)!
That's Great. It will save huge time of mine. Thanks a lot.
Hello Leila, I have been a secret admirer of your tutorials for about 5/6 months now. I find them very clear and useful to me.
I am in project mgt/admin and will like something to manage my Purchases and Requisition, Invoices, Worker's Payroll and Inventory /Stock Taking
Can you refer something that would be of benefit?
Thanks and keep up this beautiful work.
Thanks Leila. Love the bonus tip.
That’s a really neat trick.
I love it thank you so much Ms. Leila
Thanks for this helpful video.
Thank you very much, Ms Leila and Sir Bob.
Thanks for the tip it is golden! But it seems like the wildcard doesn't work if you insert the "Other_items" in between the sheets after you write the formula with the wildcard
Thanks a ton Laila ....very useful
What a time saver! Thanks Leila and Bob 😃
Nice trick....Thanks for sharing with us .
Thanks Leila. Very useful
That is so beautiful!!. Thanks for sharing
Thank you! Cheers!
Thanks for your great videos
This was amazing indeed... Thanks a lot for sharing
What on earth, never knew, that is insanely cool and useful
Glad you like it :)
That's a really cool trick. Thanks, Leila!
Glad you like it Andrew.
Amazing trick...! thank you Leila, thank you Bob.
Our pleasure :)
Thank you so much for sharing.