This is INVALUABLE right now -- there's an analysis I was working on yesterday that requires making a unique, chronological dynamic array of dateranges from multiple tables and I was NOT aware of VSTACK but that is exactly what I need. You're an absolute GODSEND, Leila! ❤❤
Thank you so much for your kind words! I'm glad to hear that you found the video helpful and that the VSTACK function will be useful for your analysis. It's always great to hear that my videos are making a difference for people. If you have any other questions or topics you'd like me to cover, feel free to let me know.
"TAKE function"??? ... never heard of it. But ONLY 37 seconds 'in' and I was hooked; " ... If you're familiar with the OFFSET function, this TAKE function is a lot easier to write and remember....". I sat back with my morning hot coffee (and pound cake) and shook my head in wonder for the next 9+ minutes ... ideas!!! ... ideas!!! ... ideas!!! ... 🤯🤯🤯. As I have commented many times before, 'Thursdays With Leila' doesn't disappoint ... thank you ... thank you ... thank you ... 😍 ...😍 ...😍.
Already using that one, it is so powerful !! But I didn't know about the curly brackets option on the take function, very useful ! Another great video by the way !! Total fan of you !
Another great value function from Excel and of course, very well explained. Especially when you split the whole process into smaller fragments and that makes things very clear. Great work.
@@LeilaGharani thank you so much for the detailed explanation We need one suggestion from you We are working on a large excel file having more 30 to 40 mB files So considering above data can you suggest which laptop is helpful for team or which configuration is required
Hi Leila i really admire the way you teach but not just the way you teach and explanain the step by step of excel fucntions but you also look for further functionallyties to explode in a single function by combining them with other functions that is where your CREATIVITY comes out and makes the differances with other youtubers!!
What can I say but WOW. Excel is so powerful. The biggest problem is remembering how to use all these brilliant features. A brilliant tutorial from the Excel guru.
Hi Leila! I've been following your videos for years now and have taken some of your classes (I need to take the Power BI class soon!). I just want to say the way you present topics and produce videos has evolved so much! it's just amazing! I make a lot of videos internally for my company and I'll never be at your level ;-). Thanks for all you do!
Great Tutorial Leila on TAKE function! The function by itself is simple, but when combined with other functions, makes wonders, and you have shown it so perfectly and practically! Thank you so much!
Thank you for the valuable content you share . One suggestion I have is to consider toning down the emphasis on phrases like "to impress your boss" as we want to focus more on fostering a healthy and collaborative work environment and not impressing anyone at work. Keep up the great work! 🙌
Great video - I recommend Leila’s paid courses on line - I worked through a set of vba ones, which were great - but I also like these freebie videos that are always quite practical
Excellent use of the different functions combination. I love the way you just break it down to make it so easy to understand. Just comes to prove that Excel is so versatile and there are endless applications for it. Thank you Leila.
The first use case I thought of for this function is for dynamic chart ranges. Almost every chart I update at work pulls from the last 6 or 12 weeks of data, and using a take formula in the source data selection would make that process super smooth and dynamic, and it’s a lot more straightforward than using offset.
I learned some new functions along with the Take function. Vstack, Choosecol, Filter, Sortby, etc. were new to me. Thank you for such a wonderful video.
Cool! I need to look deeper into the region setting part and how many more excel functions that are likely to be affected by the different region settings. 💆🏼
Hey Leila, thank you so much for your content! I've been following you as of late to improve my Excel and Power BI skills, and I would say your channel is a hidden gem in the internet. The TAKE function was new to me, but I can't wait to use it now. Just one note on the last two examples: I believe the arguments for "highest/top" and "lowest" have switched signs, where "highest/top" should be negative and "lowest" should be positive. Just something you might want to comment on in the video, but it doesn't take away from its essence. Cheers!
This is great. I also like how you can combine DROP and TAKE to do something such as give me the minimum value for rows 1-30, 31-60, 61-90. I use this with a table showing daily balance to show me my minimum for every 30 days.
Hi Leila, just discovered your content a short while ago and already bought one of your Udemy Courses. I want to bring myself to learn some basic VBA and I am still overwhelmed with all the possibilities why I try to digest each lecture one day after another. This brings me hopefully more forward than waiting for "that one day" where I have time and do it all or most of it which will probably never come. Also in this YT video shown I like youre style of structuring/editing your videos. The "takes" 1 to 4 are great and always show me where I am within that time frame of the content shown. Thanks again, BR
I’ve been using TAKE/DROP with LET to reference an table and get the data and headers. One reference will give both. This allows for dynamic headers for reports
I used Index function do choose the collumns returned. Normally I put the filter function inside an index function to plot only the colluns that I wanted in the desired order. Now is a bit easier.
FYI, when we combine (stack) 2 different tables we will often have some duplicate names (e.g. for YoY data, many items/products/month names will be same). In that case, wrap the whole thing with UNIQUE() that way you'll get the right results but without repeating the names or months. Keep up the wonderful videos and trainings...always look forward to your new posts.
@@miless2111sutube You don't really "merge" or combine with TAKE, you stack them. But once you stack them you see that the same rows will repeat multiple times as explained above, but you can remove those duplicates in the same formula by nesting within UNIQUE function.
Great explanation with examples again! Thank you! But what if I want to use TAKE in FILTER function/formula? For instance, I want to take the last 5 rows from a table and want to filter this result (e.g. to return only >= 1000) in one formula? I mean =FILTER(TAKE(Table....
Hi Leila! Thank you so much for your great videos!!! They are useful and you have the talent to walk us through your reasoning process, thats invaluable. Great job!! Keep it going
Great video! I have used TAKE since the day it became available to insiders. This is the first video I have seen with a practical example of its use and capabilities. It rather flew under the radar when first introduced partly because of its bad name. I have commented on other channels that it should have been called KEEP! BTW. The single cell combined formulas you crested could make great LAMBDAS.
I have been using TAKE the past few months to ensure dynamic header row is included . =TAKE(tableName(#All,1) Then use VSTACK to put the filtered Table below the result of TAKE, and wrap all that with CHOOSECOLS. Then as you use different Table columns in different order, the TAKE function always changes the Header columns to match
The TAKE function won't necessarily be the one I'll need frequently but I very much appreciate the way you teach how to build even complex calculations by starting with a simple VSTACK and gradually refining it with SORT, CHOOSECOLS, TAKE or FILTER. That way I feel like I really understand what I'm doing 😁 However, there is one thing I haven't found yet: Let's say we are using FILTER in the last step instead of TAKE, resulting in a list of dynamic length. Now I would like to put line numbers to the resulting table. Is there an elegant way to do that dynamically?
These are fantastic examples! Is it possible to do a filter and then Top 5...for example if I have a metric I track across several months / years, can I first filter to the current month, sort on the metric and then take the top 3 months for this metric?
Quick question: can you insert the take function in lieu of the Grand Total column in a pivot table (not as a calculated field throughout the pivot table)? I am looking to calculate the trailing twelve month average in a pivot table that has months for each column. Thanks in advance.
Have a real good listen to the take 4 transition starting at 7:30. Unsavory conversation in the background. I doubt many people will notice, by be careful.
I typically use INDEX to return parts of tables or parts of row/column fields. The syntax for the INDEX function can get complex depending on which/how man rows/columns to return but INDEX has worked well for me in the sorts of applications shown here. Other than simpler syntax, do the functions shown here have other advantages over INDEX?
Hi Leila, first of all, thank you for putting up some wonderful tips and tricks. I got one problem for you so that you could try and include it in your next videos. I got a list of defined values (say25 of them). I got to select one or more of these values out of the 25 values so that I could arrive at close to a defined sum. I can use a identifier against each of the values so that I could pull it from sumif function.. Tried solver as well but couldn't solve it
I am breaking badly. 😆 This is a great new function and will start using immediately. By the way, I use OFFSET to dynamically chose date columns using MATCH, I'd try building a nested formula to replicate my preferred method. Thank you !!
Thanks for the Valuable video Leila. I have a question to ask. Can we have a table in excel, protect the sheet and still be able to enter the details wherever we have not locked the cells? Can you please let me know the possibilities?
Hi. I wish there was a feature that would allow us to protect the sheets but leave the table out of it. Unfortunately there isn't. The possibilities I can think of are: 1. Expand the table beforehand and unlock the cells (format cells option) in the expanded table so they they're not locked once the sheet is protected. 2. Use VBA to write a worksheet change event procedure that unlocks the table range and then relocks as needed
As usual a great video! Very clearly explained by using good examples and splitting the process into steps. Only the last function is not that practical without showing the year, because you cannot compare them. And I think that is what you want
Hi Leila, have you ever covered building a dynamic flow diagram in Microsoft Visio? Something where you can actually input a value in one flow stream, and it'll affect and update all the upstream and downstream flows between all the objects in the flow diagram network? Possibly it would need to use VBA, but not sure. Thanks!
Hi Leila please do a video on uk lotto using their results and using a formula to find patterns, formula for ending digits and digits allocation please🙏🙏
Grab the file I used in the video from here 👉 pages.xelplus.com/take-function-file
Its incredible how much Excel is evolving. I'm an expert Excel user and I feel that with so many new 365 functions I need to revamp my Excel skills.
There really was a lot of development the last couple of years.
Right? I've been using Excel since the mid-90s and its ever-expanding capabilities mean back to learning cool new stuff 😁
Same
This is INVALUABLE right now -- there's an analysis I was working on yesterday that requires making a unique, chronological dynamic array of dateranges from multiple tables and I was NOT aware of VSTACK but that is exactly what I need. You're an absolute GODSEND, Leila! ❤❤
Thank you so much for your kind words! I'm glad to hear that you found the video helpful and that the VSTACK function will be useful for your analysis. It's always great to hear that my videos are making a difference for people. If you have any other questions or topics you'd like me to cover, feel free to let me know.
I use excel everyday but I didn't know about Take, Choosecols and chooserows until today. Thanks for the great video.
"TAKE function"??? ... never heard of it. But ONLY 37 seconds 'in' and I was hooked; " ... If you're familiar with the OFFSET function, this TAKE function is a lot easier to write and remember....".
I sat back with my morning hot coffee (and pound cake) and shook my head in wonder for the next 9+ minutes ... ideas!!! ... ideas!!! ... ideas!!! ... 🤯🤯🤯.
As I have commented many times before, 'Thursdays With Leila' doesn't disappoint ... thank you ... thank you ... thank you ... 😍 ...😍 ...😍.
Thanks Chris! Hope you enjoyed the cake 😋
@@LeilaGharani I enjoyed the video more ... 😉
Thats great, if you don't mind please compare with offset function
Already using that one, it is so powerful !! But I didn't know about the curly brackets option on the take function, very useful ! Another great video by the way !! Total fan of you !
Thanks David
Ok, you definitely know how to teach things. Teaching has been my job for 40 years now, I know there is hard work behind this, very well done.
Another great value function from Excel and of course, very well explained. Especially when you split the whole process into smaller fragments and that makes things very clear. Great work.
Thank you!
@@LeilaGharani Actually, we should be thanking YOU.... Your videos are enormously helpful to us mere mortals.... 🙂
@@LeilaGharani thank you so much for the detailed explanation
We need one suggestion from you
We are working on a large excel file having more 30 to 40 mB files
So considering above data can you suggest which laptop is helpful for team or which configuration is required
Thanks!
Thank you!
clever and stringent explanation for using this functions hand in hand for solving a problem ... very smart!
Hi Leila i really admire the way you teach but not just the way you teach and explanain the step by step of excel fucntions but you also look for further functionallyties to explode in a single function by combining them with other functions that is where your CREATIVITY comes out and makes the differances with other youtubers!!
My love of Excel is only increased when you show so many cool ways to use it. Thank you for a great video!
Yay! Thanks, Valerie.
What can I say but WOW. Excel is so powerful. The biggest problem is remembering how to use all these brilliant features. A brilliant tutorial from the Excel guru.
CHOOSECOLUMNS +SEQUENCE =TAKE. For example, CHOOSECOLUMNS(Array, SEQUENCE(2)) returns the first two data columns. Thanks for the video.
Hi Leila! I've been following your videos for years now and have taken some of your classes (I need to take the Power BI class soon!). I just want to say the way you present topics and produce videos has evolved so much! it's just amazing! I make a lot of videos internally for my company and I'll never be at your level ;-). Thanks for all you do!
Great Tutorial Leila on TAKE function! The function by itself is simple, but when combined with other functions, makes wonders, and you have shown it so perfectly and practically! Thank you so much!
It can really be powerful. Glad I could inspire you 👍
Clear and easy to understand, truly insightful!
Thank you for the valuable content you share . One suggestion I have is to consider toning down the emphasis on phrases like "to impress your boss" as we want to focus more on fostering a healthy and collaborative work environment and not impressing anyone at work. Keep up the great work! 🙌
I just got to know about the TAKE function and its amazing what results can be achieved using the function. Well done for sharing this knowledge.
Oh the curly brackets part is so good!
Great video - I recommend Leila’s paid courses on line - I worked through a set of vba ones, which were great - but I also like these freebie videos that are always quite practical
Excellent use of the different functions combination. I love the way you just break it down to make it so easy to understand. Just comes to prove that Excel is so versatile and there are endless applications for it. Thank you Leila.
The first use case I thought of for this function is for dynamic chart ranges. Almost every chart I update at work pulls from the last 6 or 12 weeks of data, and using a take formula in the source data selection would make that process super smooth and dynamic, and it’s a lot more straightforward than using offset.
I learned some new functions along with the Take function. Vstack, Choosecol, Filter, Sortby, etc. were new to me. Thank you for such a wonderful video.
loving that alert at 3:33 🙂 brilliant set up!
Cool! I need to look deeper into the region setting part and how many more excel functions that are likely to be affected by the different region settings. 💆🏼
This is one of my favorite function. Yesterday I found out how to subtract the previous cell from the last cell with TAKE and DROP. Super excited.
Hey Leila, thank you so much for your content! I've been following you as of late to improve my Excel and Power BI skills, and I would say your channel is a hidden gem in the internet. The TAKE function was new to me, but I can't wait to use it now. Just one note on the last two examples: I believe the arguments for "highest/top" and "lowest" have switched signs, where "highest/top" should be negative and "lowest" should be positive. Just something you might want to comment on in the video, but it doesn't take away from its essence. Cheers!
Thank you Leila , I was having a bad day and this video just made it better.
This is great. I also like how you can combine DROP and TAKE to do something such as give me the minimum value for rows 1-30, 31-60, 61-90. I use this with a table showing daily balance to show me my minimum for every 30 days.
Glad it was helpful!
combination use of different formula together is really cool! Thank you Leila for such valuable video!
Amazing lecture Leila. Thanks for always being a source of inspiration to us all.
In minute 0:51, how is there the up and down arrows with colour, being part of the text in the cell? how did you colour them? thank you!
Thank You so much for all of awesome Yours tips 🙂 (btw curly brackets can write by Alt Gr + b for { and Alt Gr + n for } )
Hi Leila, just discovered your content a short while ago and already bought one of your Udemy Courses. I want to bring myself to learn some basic VBA and I am still overwhelmed with all the possibilities why I try to digest each lecture one day after another. This brings me hopefully more forward than waiting for "that one day" where I have time and do it all or most of it which will probably never come. Also in this YT video shown I like youre style of structuring/editing your videos. The "takes" 1 to 4 are great and always show me where I am within that time frame of the content shown. Thanks again, BR
LLMs are taking over those skills, don't waste your energy learning VBA.
nice you got 2.26.
keep it up you're amazing
I’ve been using TAKE/DROP with LET to reference an table and get the data and headers. One reference will give both. This allows for dynamic headers for reports
I used Index function do choose the collumns returned. Normally I put the filter function inside an index function to plot only the colluns that I wanted in the desired order. Now is a bit easier.
this one is PERFECT!!!!!!!!!!!!!!! thank you so much for the perspectives, different applications and formula combination examples!!
I appreciate the way you explain things. Thank you
Thanks a lot for the excellent explanations. Now, I can use the TAKE and VSTACK functions!
Yet another TAKE on your incredible, clean and crisp teaching videos!😅
Imparted very well Leila and the formulas are superb
Could you please do a video for the take and drop functions? I always get those mixed up.
TAKE only selects the rows or columns you specify from an array, DROP selects everything from an array except the rows or columns you specify
Thank you! This video helped me automate a tedious process in one of my monthly reports. Also, I loved the Breaking Bad references.
FYI, when we combine (stack) 2 different tables we will often have some duplicate names (e.g. for YoY data, many items/products/month names will be same). In that case, wrap the whole thing with UNIQUE() that way you'll get the right results but without repeating the names or months. Keep up the wonderful videos and trainings...always look forward to your new posts.
how would you combine items with the same key values? For instance staff costs YOY to be combined?
@@miless2111sutube You don't really "merge" or combine with TAKE, you stack them. But once you stack them you see that the same rows will repeat multiple times as explained above, but you can remove those duplicates in the same formula by nesting within UNIQUE function.
Leila, you are a STAR!!!
That pop up message that asks for new people to subscribe 🔥🔥🔥🔥🔥.
This is going to help me with the smallest of a range where using small returns zeros and I have to deal with them with custom formats.
This lesson is highly invaluable. I have created a report template in my head just now. Wow. Thanks
Glad it was helpful!
I am a Kenji explains follower, but I find your video also very helpful.
Awesome, thank you!
Great explanation with examples again! Thank you!
But what if I want to use TAKE in FILTER function/formula? For instance, I want to take the last 5 rows from a table and want to filter this result (e.g. to return only >= 1000) in one formula?
I mean =FILTER(TAKE(Table....
Great stuff! Thank you so much for sharing and teaching us
It's awesome how you use various formulas to achieve real life results, and you make it look so easy ❤
Beautiful video, but can you pls attach your dataset to download for training purpose pls?
Wow! Excellent video. I really enjoyed you demonstrating how to combine functions.
wonderful, curly brackets was simply superb, thanks for sharing
You are absolutely THE BEST!! Thanks Leila!
Wow!...Thats a lot of possibilities in with a few simple commands. Thanks Leila for this great demo and practical use!
Hi Leila! Thank you so much for your great videos!!! They are useful and you have the talent to walk us through your reasoning process, thats invaluable. Great job!! Keep it going
Great video! I have used TAKE since the day it became available to insiders. This is the first video I have seen with a practical example of its use and capabilities. It rather flew under the radar when first introduced partly because of its bad name. I have commented on other channels that it should have been called KEEP! BTW. The single cell combined formulas you crested could make great LAMBDAS.
I have been using TAKE the past few months to ensure dynamic header row is included . =TAKE(tableName(#All,1)
Then use VSTACK to put the filtered Table below the result of TAKE, and wrap all that with CHOOSECOLS. Then as you use different Table columns in different order, the TAKE function always changes the Header columns to match
Actually, CHOOSECOLS changes the columns, but using TAKE this way makes the Header row dynamic also.
Thanks for sharing, Bernie!
Please please do a full tutorial on dynamics 365 crm and creating flows within it
It's barely 8:00 am and I'm already inspired thanks to that tutorial.
Thank you so much Leila for all you do!
The TAKE function won't necessarily be the one I'll need frequently but I very much appreciate the way you teach how to build even complex calculations by starting with a simple VSTACK and gradually refining it with SORT, CHOOSECOLS, TAKE or FILTER. That way I feel like I really understand what I'm doing 😁
However, there is one thing I haven't found yet:
Let's say we are using FILTER in the last step instead of TAKE, resulting in a list of dynamic length. Now I would like to put line numbers to the resulting table. Is there an elegant way to do that dynamically?
These are fantastic examples! Is it possible to do a filter and then Top 5...for example if I have a metric I track across several months / years, can I first filter to the current month, sort on the metric and then take the top 3 months for this metric?
Great learning tips and new skills,thanks Leila❤
Oh My God… Is there a way to give Leila tons of likes together 🙏🏻. Again you saved me a lot of time in my daily work ❤🙏🏻🥰
Hi Leila as lovely as always, I can see the potential of this function combining with other text and position functions, awesome thanks for sharing 🤩😍
Quick question: can you insert the take function in lieu of the Grand Total column in a pivot table (not as a calculated field throughout the pivot table)? I am looking to calculate the trailing twelve month average in a pivot table that has months for each column. Thanks in advance.
Have a real good listen to the take 4 transition starting at 7:30. Unsavory conversation in the background. I doubt many people will notice, by be careful.
Thanks a lot for sharing some awesome tricks. I am getting an error when I choose curly brucket to find top and bottom.
I typically use INDEX to return parts of tables or parts of row/column fields. The syntax for the INDEX function can get complex depending on which/how man rows/columns to return but INDEX has worked well for me in the sorts of applications shown here. Other than simpler syntax, do the functions shown here have other advantages over INDEX?
Hi. Thanks. Great function. Can you use it to take date from, say, last Monday?
Hi Leila, first of all, thank you for putting up some wonderful tips and tricks.
I got one problem for you so that you could try and include it in your next videos.
I got a list of defined values (say25 of them). I got to select one or more of these values out of the 25 values so that I could arrive at close to a defined sum. I can use a identifier against each of the values so that I could pull it from sumif function..
Tried solver as well but couldn't solve it
Hi Leila! Thank you so much for your great videos!
Excellent Video! A quite useful formula indeed.
Will save me a lot of time !
Thanks for sharing
Hi Leila. My workbook has many sheets. Want to switch between them with an easy way (through drop down list created in each sheet). Thanks.
I am breaking badly. 😆 This is a great new function and will start using immediately. By the way, I use OFFSET to dynamically chose date columns using MATCH, I'd try building a nested formula to replicate my preferred method. Thank you !!
Let us know how you like it, Narciso.
Brilliant 👏 👏 👏 👏
Thank you
Awesome tutorial! As always. I love dynamic functions and formulas.
Thank you for such a video with all of these sorts of combinations.
You are so welcome!
Thank you. This is extremely formative for me.
Amazing as always, thanks!
Very nicely explained 👏
Thanks for the Valuable video Leila. I have a question to ask. Can we have a table in excel, protect the sheet and still be able to enter the details wherever we have not locked the cells? Can you please let me know the possibilities?
Hi. I wish there was a feature that would allow us to protect the sheets but leave the table out of it. Unfortunately there isn't. The possibilities I can think of are:
1. Expand the table beforehand and unlock the cells (format cells option) in the expanded table so they they're not locked once the sheet is protected.
2. Use VBA to write a worksheet change event procedure that unlocks the table range and then relocks as needed
Good presentation, thanks.
Super easy to follow tutorial and great examples to demo the power of this new function
Great video. How can I use take function to average rows ?
You always make it look so easy. Thank you
Do you have a video that creates a table or array and reference it by name like you did in this video?
As usual a great video! Very clearly explained by using good examples and splitting the process into steps. Only the last function is not that practical without showing the year, because you cannot compare them. And I think that is what you want
Oh and I love the curly bracket option
First time I seen this! ❤ Love the way you combined the Take with other functions.
This is great! Such a clear explanation as well, thank you!
Hi Leila, have you ever covered building a dynamic flow diagram in Microsoft Visio? Something where you can actually input a value in one flow stream, and it'll affect and update all the upstream and downstream flows between all the objects in the flow diagram network? Possibly it would need to use VBA, but not sure. Thanks!
Hi Leila please do a video on uk lotto using their results and using a formula to find patterns, formula for ending digits and digits allocation please🙏🙏
Teacher can you explain : how to use( if and or) formula in the Excel and how can do financial statements entire in the Excel..???
Εξαιρετικά. Θαυμάσια παρουσίαση.
Nice, clear demonstration.
love this! I wouldn't have known about this without you so thanks!