Mynda, you have a knack to explain with right examples and this video is no exception! Thank you for helping us learn so much about Excel and related tools, especially the new features! Great work!
@MyOnlineTrainingHub2 жыл бұрын
Thanks for your kind words, Vijay! 🙏
@TP0145632 жыл бұрын
I liked how you went straight to point, no additional/unnecessary talking, and no music. Very clearly articulating how the functions work, with their different scenarios. Thank you !!!
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked the video 🙏
@sachinrv1 Жыл бұрын
Hi Mynda, I just love to watch your videos on Excel bcoz you keep things simple and explain wonderfully.
@MyOnlineTrainingHub Жыл бұрын
Thanks so much!
@hectorbello21042 жыл бұрын
Thanks for sharing; incredible how little I know of excel functions, and each time I watch one of your videos I get the feeling is a never ending endeavor.
@MyOnlineTrainingHub2 жыл бұрын
The never ending learning is part of Excel's appeal 😊
@a.achirou654710 ай бұрын
Thank you, Mynda for this outstanding presentation. That's impressive the way you introduce effortless those functions!!
@MyOnlineTrainingHub10 ай бұрын
Thanks so much! 😊🙏
@stevevuoso84112 жыл бұрын
This channel is a straight up goldmine.
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much, Steve! Please spread the word 😉
@aiasaiascon3894 Жыл бұрын
Borrowed by @VijayArjunwadkar but TRUE - Mynda, you have a knack to explain with right examples and this video is no exception! Thank you for helping us learn so much about Excel and related tools, especially the new features! Great work!
@MyOnlineTrainingHub Жыл бұрын
Thanks so much!
@mohammadj.shamim93422 жыл бұрын
Thank you so much Excel sensei. You have really widened our view and capabilities of using Excel. Much appreciated and thank you very much. 😊.
@MyOnlineTrainingHub2 жыл бұрын
You're very welcome!
@hussainakhtar1094 Жыл бұрын
Amazing Video.. Can't compare your grip on excel functions... your explanation is very smooth... very impressive
@MyOnlineTrainingHub Жыл бұрын
Thanks for your kind words!
@IvanCortinas_ES2 жыл бұрын
Powerful tutorial Mynda. As always, a brilliant explanation. Thank you very much!
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much, Ivan 🙏
@frozenade2 жыл бұрын
Combining xlookup with a filtered vstack is quite exciting, thank you for a great video!
@MyOnlineTrainingHub2 жыл бұрын
Glad you enjoyed it!
@bradleybeauclair8282 Жыл бұрын
I have these new formulas now. Previously, I had Microsoft 365 and Business (2x Microsoft accounts). I had Windows 10 Pro and Windows 11 Pro Preview on another PC. I was a Microsoft Office insider on both PCs and tried everything to get these new formulas somewhere. Well, now I have Windows and 11 Pro on a new PC and after joining Office insider- they worked after the update.
@MyOnlineTrainingHub Жыл бұрын
Enjoy them, Bradley!
@michaeldavenport9268 Жыл бұрын
Outstanding! You have just saved me SO MUCH time. Thank you!
@MyOnlineTrainingHub Жыл бұрын
Wow, that's awesome to hear!
@josepepe7412 жыл бұрын
I always enjoy your videos. Direct to the point and very good explained.
@MyOnlineTrainingHub2 жыл бұрын
Glad you like them, Jose!
@miteshvohra69482 жыл бұрын
Please send some blessings along with your videos for "Office for Mac" users.
@MyOnlineTrainingHub2 жыл бұрын
These functions work on Mac too.
@AshwaniSharma-fc2nz2 жыл бұрын
Very much helpful Maam
@darrylmorgan2 жыл бұрын
Hi Mynda!Great Tutorial/Demonstration Of These Cool New Functions...Thank You :)
@MyOnlineTrainingHub2 жыл бұрын
My pleasure, Darryl 🙏
@tmb88072 жыл бұрын
Thanks for the video as always. Really like your straightforward style. It's nice to have the flexibility these new functions provide but I have to admit I'm struggling to think of situations where I'd use them over Power Query. Maybe for some really quick and dirty stuff!
@MyOnlineTrainingHub2 жыл бұрын
Yeah, I think I’d use PQ first too, but PQ requires a refresh to show changes, so they’re good if you want to avoid that step.
@tmb88072 жыл бұрын
@@MyOnlineTrainingHub Good point! Cheers.
@iziaurrahman Жыл бұрын
As always your videos are very useful. Thanks a lot for sharing this.
@MyOnlineTrainingHub Жыл бұрын
So nice of you 🙏
@williamthatsmyname2 жыл бұрын
The functions haven't filtered to my version of 365 yet, but I hope they arrive faster than they did for the xlookup one 😭
@MyOnlineTrainingHub2 жыл бұрын
Fingers crossed, William 🤞
@nanettereyes Жыл бұрын
You made me love Excel more!
@MyOnlineTrainingHub Жыл бұрын
Awesome to hear! 🥰
@gonzalodangelodj2 жыл бұрын
great examples! I am starting to think the uses I can benefit my files with.
@MyOnlineTrainingHub2 жыл бұрын
Great to hear!
@sachin.tandon2 жыл бұрын
Great tutorial! Very powerful time-saving functions!
@MyOnlineTrainingHub2 жыл бұрын
Glad you like them 😊
@GothicPrincezz2 жыл бұрын
Very nice!! Definitely will use this since I have monthly data that I pull for sales.
@MyOnlineTrainingHub2 жыл бұрын
Great to hear!
@captvo Жыл бұрын
Your tutorials are easy to grasp and clearly articulated! Keep it up and keep Excelling ! For the headers, when working with Tables, I find it easier by just adding the header row once --> 𝐕𝐒𝐓𝐀𝐂𝐊(𝐓𝐚𝐛𝐥𝐞𝟏[#𝐇𝐞𝐚𝐝𝐞𝐫𝐬],Table1,Table2,Table3).
@MyOnlineTrainingHub Жыл бұрын
Thanks for your kind words!
@johnmacleod29412 жыл бұрын
Mynda - I thought it would be the very next example, but was wondering if "Socks" appeared in more than one sheet, would V/X lookup return an array? Guess I'll explore! Thanks for all you do on this channel!!
@MyOnlineTrainingHub2 жыл бұрын
Yes, it’ll spill multiple results with XLOOKUP 👍
@Ugk8712 жыл бұрын
Cool. I just wonder we can do UNIQUE rows in Power Query as well
@tmb88072 жыл бұрын
Yes - select the entire table (Ctrl + A) then use Remove Rows >> Remove Duplicates under the Home tab.
@ruroshinzynaruto Жыл бұрын
Valueable info as alawys , thank you.
@MyOnlineTrainingHub Жыл бұрын
My pleasure!
@kaptenpingpong60892 жыл бұрын
Thank You...
@MyOnlineTrainingHub2 жыл бұрын
My pleasure 😊
@erikguzik8204 Жыл бұрын
At around the 5:45 mark you want to include the headers, if the headers are the same as the tables you are stacking use HSTACK to get the header row then stack it on top of the tables like this (if using tables, its easier) =VSTACK(HSTACK(Table1[#Headers]),VSTACK(Table1,Table2,Table3))
@MyOnlineTrainingHub Жыл бұрын
Thanks for sharing, Erik. You could just write it like this without HSTACK and the extra VSTACK: =VSTACK(Table1[#Headers]Table1,Table2,Table3)
@hjk4575ma Жыл бұрын
This was a great help!
@MyOnlineTrainingHub Жыл бұрын
Great to hear 😊
@AsaelCosentino2 жыл бұрын
FINALLY!!!!! Finally, Microsoft find a formula to that...amazing!
@MyOnlineTrainingHub2 жыл бұрын
Glad you like it!
@SyedMuzammilMahasanShahi2 жыл бұрын
Thanks for the share.
@MyOnlineTrainingHub2 жыл бұрын
Our pleasure!
@gratianmisquith172 жыл бұрын
Amazing loved these functions
@MyOnlineTrainingHub2 жыл бұрын
Great to hear 😊
@arbazahmad71772 жыл бұрын
Great explanation 👌 about new functions. When, these new functions will rollout with Office 2021.
@MyOnlineTrainingHub2 жыл бұрын
Thank you! They won't be available in Office 2021. You need a Microsoft 365 license to get new features.
@travelindiaindia92172 жыл бұрын
Hi I watch regularly your videos. I have question trough which tool help me to extract high and low value from multiple excel sheets. Look forward to hear from you soon. Thanks &Regards
@MyOnlineTrainingHub2 жыл бұрын
You can use VSTACK with MAX and MIN to get the high and low values.
@sergijuarros75692 жыл бұрын
Excellent explanation Mynda, thank you very much! I wonder if one can combine the FILTER function with VSTACK to calculate the sum of the category "Clothing", resulting in a more elegant function than using SUMPRODUCT: =SUM(FILTER(VSTACK('2020:2022'!D2:D6),VSTACK('2020:2022'!B2:B6)="Clothing"))
@MyOnlineTrainingHub2 жыл бұрын
Yes, SUM(FILTER works too 👍 Lots of ways to skin the Excel cat! 😊
@letschill47782 жыл бұрын
Fantastic walkthrough. Can I possibly ask you how I can also solve the following? I have the following formula: =IF(IFERROR(VLOOKUP([@Name],sheet1!A:A,1,FALSE),"No")="No","No","Yes") I need the formula modified to execute the same for 3 sheets within my excel file (sheet1, sheet2, sheet3)
@MyOnlineTrainingHub2 жыл бұрын
I'm not sure if you want a separate formula in each sheet, or one formula referencing all sheets. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@AlymTeaches2 жыл бұрын
As usual, another awesome video!
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it 😊
@Devi.KrishnaK2 жыл бұрын
I'm too busy reading these comnts and not paying attention again.....and I'm Nice tutorialgh
@MyOnlineTrainingHub2 жыл бұрын
Thank you!
@nadermounir82282 жыл бұрын
Nice video 📹 👍
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much!
@prosenjitchandrapaul2 жыл бұрын
Just awesome, thankyou
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it!
@prosenjitchandrapaul2 жыл бұрын
@@MyOnlineTrainingHub hey, i am using right now 365 but this option not available so i would like to go 365 beta version but i could to go any video you have. thanks
@MyOnlineTrainingHub2 жыл бұрын
@@prosenjitchandrapaul These functions are being rolled out. Check if you have updates to install. If they don't appear, give it a few days and check again.
@ufuksakar60802 жыл бұрын
A very useful video you are verl successful girl Thanks 👏👏👏
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much 🙏
@FTSociety10 ай бұрын
Amazing!!!
@MyOnlineTrainingHub9 ай бұрын
Thanks so much!!
@phillunder3162 жыл бұрын
Just a comment: The description of how to ignore things has a header page where "ignore" is misspelled as "ingore." Just a heads-up.
@MyOnlineTrainingHub2 жыл бұрын
😁 typos 🤦♀️
@RogerStocker2 жыл бұрын
I really love all this new functions, im really sad that in our company we are still in the pre-λ-era. 😥😢😭
@MyOnlineTrainingHub2 жыл бұрын
Hopefully not for too much longer, Roger.
@Ola-iu6zc Жыл бұрын
Great work Mynda. If I use VSTACK to consolidate my data, can I use the range created in power query? I tried but I got null. Please help. Thanks
@MyOnlineTrainingHub Жыл бұрын
Yes, but since spilled arrays don't work in tables, you have to give the spilled array a name and then create a new blank query and use =Excel.CurrentWorkbook() to get the data.
@Ola-iu6zc Жыл бұрын
@@MyOnlineTrainingHub I am very grateful for the timely response. It's working now. Thanks for always there for us. You are great.
@Otisawide11 ай бұрын
How to keep the black cell still blank instead of “0” using the Vstack? Perhaps on a row only one column does not have an empty cell because there is not a fit criteria.
@MyOnlineTrainingHub11 ай бұрын
You could use a custom number format to hide the zeros: www.myonlinetraininghub.com/excel-custom-number-format-guide#_Toc474757758
@deinbleiz Жыл бұрын
Awesome!
@MyOnlineTrainingHub Жыл бұрын
Glad you think so! 🙏
@bimmerboytripp249 Жыл бұрын
wowwwww you are so smart
@MyOnlineTrainingHub Жыл бұрын
Thank you!
@KV21A2 жыл бұрын
one More Awesome Video 🙏
@MyOnlineTrainingHub2 жыл бұрын
Thank you!
@anubhav19888 Жыл бұрын
Are all data you are using are in TABLE format?
@MyOnlineTrainingHub Жыл бұрын
Not all. You're welcome to download the example Excel file from the link in the video description.
@samkab6779 ай бұрын
Please in minute 3.03 In output of formula b118. How to know each element belongs to which name column.is there a formula for this?
@MyOnlineTrainingHub9 ай бұрын
In this example I had already added the column headers in row 117, but you could also bring them in automatically by increasing the first range in VSTACK to include row 109, which are the header labels for the first range.
@hussainakhtar1094 Жыл бұрын
I have just combined another layer with "=SORT(VSTACK({"Year","Category","Product","Sales"},FILTER(VSTACK('2020:2022'!A2:D6),VSTACK('2020:2022'!A2:A6)"")),4,-1)"
@MyOnlineTrainingHub Жыл бұрын
Great to see you're making use of VSTACK!
@TheJoshtheboss Жыл бұрын
Is there any point or benefit using this over Power Query?
@MyOnlineTrainingHub Жыл бұрын
Power query requires a refresh, formulas instantly update.
@TheJoshtheboss Жыл бұрын
@@MyOnlineTrainingHub Awesome. Thanks.
@shadeburst2 жыл бұрын
A very clear video, but the purpose of these functions seems to be to kludge around bad data design.
@MyOnlineTrainingHub2 жыл бұрын
Indeed! I must tell people daily that their data should be in a tabular layout!
@tmb88072 жыл бұрын
Pragmatic, I’d say. Much as we want nicely formatted data to work with, we very often don’t get it.
@hatefuleightyseven29622 жыл бұрын
Never before has the word 'exciting' had to do so much heavy lifting.
@MyOnlineTrainingHub2 жыл бұрын
Depends how much info Excel you are 😉
@bettyedwards65602 жыл бұрын
Is there a reason that VStack and HStack do not show up as available functions/formulas for me, even though I have MS Office 365? I have a definite use for the VStack, but can't seem to get my Excel to recognize it. Thanks!
@MyOnlineTrainingHub2 жыл бұрын
These functions are being rolled out to the current channel. You might need to update Office or you might be on the semi-annual channel, in which case you’ll have to wait a bit longer, or switch to the current channel.
@drewbbc70742 жыл бұрын
Very nice
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Drew!
@chrism90372 жыл бұрын
These are so cool, thanks Mynda!
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Chris 🙏
@shawnward34942 жыл бұрын
I always love your videos, but this one i am struggling with because the =VSTACK(("NAME","REGION","REVENUE"),A2:C6,F2:H7) sits there just like this on the cell returning no results. What am I doing wrong. Hope you are enjoying your first month of spring :)
@MyOnlineTrainingHub2 жыл бұрын
You need to surround the column heading array in curly braces, not parentheses.
@shawnward34942 жыл бұрын
@@MyOnlineTrainingHub Thank you very much Mynda
@reyt73494 күн бұрын
How do you do Stack using created Table Ranges and want to eliminate spaces and zeros?
@MyOnlineTrainingHub3 күн бұрын
You can use FILTER to exclude spaces and zeros and then stack the filtered arrays.
@reyt73493 күн бұрын
@@MyOnlineTrainingHubis not working for me. I am using =vstack(tbl_1,tbl_2,tbl_3) Then if I used the filter I cannot use it like yours by referencing column a
@reyt73493 күн бұрын
@@MyOnlineTrainingHub Thank you for replying too. That is so nice of you
@MyOnlineTrainingHub22 сағат бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@gertcoetzer11152 жыл бұрын
hi Will you do a =@GL some time?
@MyOnlineTrainingHub2 жыл бұрын
Not sure what you're referring to.
@gertcoetzer11152 жыл бұрын
@@MyOnlineTrainingHub global formula looking something like this =@GLCurrentPeriodEvo
@MyOnlineTrainingHub2 жыл бұрын
Sorry, that still doesn't give me enough information. Perhaps you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@priyankachourasiya97122 жыл бұрын
Hello mam please make a VBA macro playlist I need this VBA macro coarse in your channel because your language is very easy and good so please do this what I said please 🙏🏻🙏🏻
@MyOnlineTrainingHub2 жыл бұрын
Thanks for the suggestion and kind words!
@priyankachourasiya97122 жыл бұрын
@@MyOnlineTrainingHub so mam you will make playlist or not ???
@priyankachourasiya97122 жыл бұрын
You didn't say ??
@MyOnlineTrainingHub2 жыл бұрын
Probably not, to be honest.
@rusektor2 жыл бұрын
It's a pity that VSTACK/HSTACK return "0" if cell is empty. One way to get around it is: =LET(val;VSTACK(F1:J1);IF(LEN(val)=0;"";val))
@MyOnlineTrainingHub2 жыл бұрын
Hi Johny, that formula only works if the last table in the stack is the one that has empty rows. If it's one of the middle tables you'll see blank rows instead of the zeros, but you can achieve this with number formatting too.
@ppanigrahi2 жыл бұрын
Apart from the insider channel, has anyone got these? I haven't. Which version/ channel?
@MyOnlineTrainingHub2 жыл бұрын
Hi Prasanta, they're being rolled out to the 365 current channel. Check if you have an update available. If not, check again in a few days and so on until you get them.
@mhnchannel9828 Жыл бұрын
not available in excek 2021, how to solution ms ?
@MyOnlineTrainingHub Жыл бұрын
You need a Microsoft 365 license for these functions.
@fahadpalladium22 жыл бұрын
Neat 👌
@MyOnlineTrainingHub2 жыл бұрын
Glad you think so!
@Kevin-vv1fw2 жыл бұрын
Bonza!
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Kevin!
@ejirodiemijenyo33532 жыл бұрын
Wow... I viewed this video first...lol
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it 😊
@monithakur42662 жыл бұрын
I could not download excel files
@MyOnlineTrainingHub2 жыл бұрын
Please try another browser as no one else is reporting issues. Be sure to read the instructions on the page. If you're still having trouble, please reach out via email so we can help troubleshoot: website at MyOnlineTrainingHub.com
@ryangriffiths405 Жыл бұрын
Anyone else notice that vstack just stopped working? Just returns a 0 in the formula cell.
@MyOnlineTrainingHub Жыл бұрын
Still works for me. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@ryangriffiths405 Жыл бұрын
@MyOnlineTrainingHub thank you i will. Can't post an example because of my work but it's so strange. On a single page it works. But when you reference a second page or 3d ref then it returns a 0 in the cell the formula was entered in. Thanks for the reply and videos always very helpful