Leila, I've done this with calculations on a sheet of mine with 21,000 lines of records but your method tells me I've got a lot to learn. I know it's a great video because I've replayed it a number of times and paused it to take notes. Love your work.
Super viddy, Leila. Functions UNIQUE, SORT and FILTER are so powerful that their creation represents a sea change in Excel. One drawback here is that there needs to be the helper-table for the dates because DataValidation won't handle the arrays...yet. [And thank you for pronouncing it ZED - I'm tired of hearing ZEE!]
Mam after wathing your every video I am prepare notes and use it's when I need, for me your lectures is very valuable for me, I am waiting your next video many times, you are really great....
dear Leila, I do not have a UNIQUE function available in my excel 365 nor in my company excel. any thought about how it can be replaced? btw I'm your great fun :)))
Wow, an incredible amount of knowledge shared in 9 minutes. This is powerful and impressive I am geeked to logon to work on a Sunday to improve some workbooks.
This is Brilliant Leila. Thank you so much... I have sports workbooks where I have the whole season's fixtures entered. I can now use this table to see who is playing in the next few days or whatever period I want to see.
Its great video but only drawback is you have to push you eye from every date while selecting from and to dates, however it should reflect as calender dates and that would be easy
Thanks Leila.. great showcase of the power of DAFs and your new course which I'm sure will deep dive into more tricks and techniques to get the most out of the new functionality. Looking forward to more. Thumbs up!
@@LeilaGharani Agreed.. although there is nothing like the satisfaction of getting a wild array formula to produce the result you want. In terms of getting work done fast, DAFs will really improve productivity. Looking forward to your course :))
If you would like to add more requirements/filters/constraints to your report, such that it only displays the values you want, say Peter Ramsy, you simply alter the cell with the filter function (G7) and under ''include'' you add an extra * (B5:B53 = "Peter Ramsy") - now you will only get the report for Peter Ramsy. Note that the range is for the Sales Agents column. This is helpful if you have more ''Peter Ramsy''s or same identical values that you only want those to be displayed.
Thank you Leila for a very informative video. The question I have is that can you reference specific elements of a spill array so that you can pair them up with data that you've keyed in next to the spill array and reference both in a subsequent Vlookup or index match function. Thank you
Hi Leila, thank you for all your videos, they help a lot. I wonder is you be kind to share a formula that I cannot make to work yet, I need to add total value of one code between dates, If I do it separayly just looking between dates it give the how many Code do I have, but no count the quantity of the code, the columns are DATE, CODE and QUANTITY. Can you help me with that, please?? thanks👍
I am very sad because in our company we have office 365 but IT department decided to not updating it yet so we can't use dynamic arrays. Its because of potential problems which sometimes occur after uptade. Until they will be forced to make the update by some director or owner of company, we have to wait. =/
Hi Leila; Wonderful video !! For your video lovers like me, you are more like a user's manual to Excel and trust me its coming very much handy :) Cheers !
I am watching your video quite frequently and have prepared notes so that the example of accurate use of formula and functions may be useful for my daily usage. Many of the functions I have used in routine files which have drastically changed the way I worked before. You make us understand so easily that we can learn it well. Can you provide the facility to download the example files you use in your videos because some of the functions are so complexed which are not easily understandable in one go i.e. offset or nested functions.
Please i want to know how to build a dynamic and interactive accounting system such that whenever I want transactions that occurred in a particular month, i can get it
I took an excel class at SMC but it was horrible. Firstly the class was only six weeks. But the idiotic professor assigned two quizzes and two homework assignments each week and about 200 pages of reading that no one had time to absorb any material in that short a time period. Everyone was just so busy trying to memorize for the quiz and work on the assignments. I can honestly say I remember nothing from her class. I will need up go back and relearn everything at a much easier pace with your videos
Hi Leila thanks for all the good videos recently I have watched over 5 videos and all good and informative. The challange i have after watching this video is when i go to prep from and prep to and poulate the dates from my date column only 33 cells get copied and i have data upto 44 cells also the dates dont come over correctly - What am i doing wrong
Very informative video but, not something I would use everyday. If I were presented that info and was told to give a report showing sales between dates I would automatically convert the data to a table and use a pivot table to extract the info.
Hello queen, thank u so much for this video, like every time, always special and useful solution, but i wonder if there is another functions that can replace the ( UNIQUE/FILTER & SORT ) for all formulas in this video.
Thank you Leila for your videos. I must say that I have learned a lot. I was recently working with stocks data and wanted to filter out specific date data of multiple years, let's say the last trading day of the year for the years in the range. I usually click the filter, and then have to manually click each day . Is there any better way? Looking forward.
Hi Leila, Thanks a lot for your videos they are really very helpful.however, I'm struggling to put a formula where I can put vlookup if a date falls between two dates. Can I do that? Or please could you help me with putting a formula for a query like this? Your help will be very appreciated.😁
hello leila, can you please make a video that look up values filtered between two dates and dependent on the person using that particular date? dates and names were all data validation in separate sheet..
Leila do you have any idea when this will be available for office 365 users? I am waiting since September 2018... I tried many times to become also an excel insiders user, but unfortunately that's not possible. It seems that Microsoft randomly offers this option to the users...
@@LeilaGharani Thanks Leila, actually the following link on your link is more helpful for Business, enterprise, and education customers that have an Office 365 plan : insider.office.com/en-us/handbook/for-work-or-school
Thank you for this channel! You have helped me so much. I spend many enjoyable hours listening and watching you to as I practice on my own. Still, I would like to know if you have any suggestions on how to obtain a future date with a nest if formula with variable start dates. For example, if I complete a job on and my expected pay will wing it's way to me either the 15th or the 30th of next month dependent on if I performed my task before or after the 15 of this month. For added measure, could I add another criteria?
Best Excel guides. I have a large list of date ranges (with start and end dates) with some overlaps; I want to count total number of unique days without counting overlaps multiple times. I've been brainstorming this for some time using sumifs(), but every idea comes short. Any hints? Thank you!
Great video, Leila! Like you said at the end, these dynamic array functions are still only available for Office Insiders. Just wondering if you know when they will be released in the main version?
@@LeilaGharani thanks for the info, i don't have the insider Fast version of 365 at the moment, i will look around to see how to get this version as mine is just Office 365
Grab the file I used in the video from here 👉 pages.xelplus.com/records-between-dates
Is the "#" only working on the Office Insider as well? Haven't had the chance to try it out yet, but I've never heard of it on the the Office 365...
@@PellegriniCon Yes - that's Excel's new calculation engine - dynamic arrays and # and spilled ranges will all come together.
Ladies and Gentlemen, To raise the slogan "Long live to the Queen of Excel" thanx sweet Leila.
Oh, that's so nice. Thank you!
To be honest, I didn't want to learn array functions on weekend. Your videos are straight to the point and easy to follow. Thank you excel queen !
Leila, I've done this with calculations on a sheet of mine with 21,000 lines of records but your method tells me I've got a lot to learn. I know it's a great video because I've replayed it a number of times and paused it to take notes. Love your work.
I'm glad you like the video Ron.
Leila, the videos you produce are simply excellent. Some of the best I have found on RUclips (and I've searched high and low)
Super viddy, Leila. Functions UNIQUE, SORT and FILTER are so powerful that their creation represents a sea change in Excel. One drawback here is that there needs to be the helper-table for the dates because DataValidation won't handle the arrays...yet. [And thank you for pronouncing it ZED - I'm tired of hearing ZEE!]
True, helper table is needed but hopefully we could solve this with name manager soon :)
it's just awesome. Filter function is truly a life saver. Thank for this informative video Leila.
You're welcome 😊
Mam after wathing your every video I am prepare notes and use it's when I need, for me your lectures is very valuable for me, I am waiting your next video many times, you are really great....
I'm happy the tutorials are helpful for you.
dear Leila, I do not have a UNIQUE function available in my excel 365 nor in my company excel. any thought about how it can be replaced? btw I'm your great fun :)))
Your channel is a godsend! So many things I didn't even know I wanted to learn LOL! Every video has something that appli directly to need
love the way you explain such complicated problems in simple way
I'm glad it's easy to follow.
Wow, an incredible amount of knowledge shared in 9 minutes. This is powerful and impressive I am geeked to logon to work on a Sunday to improve some workbooks.
Dynamic Arrays.. Super Awesome!! I like the side where you demonstrate Multiplication of TRUE & FALSE.. Great Video 👍🏻
Glad you like the video. Yes, this will change the Excel game completely.
Many thanks Leila for your usual useful videos
You're most welcome Mohamed. Many thanks for your support.
You are so good, very impressive!
This is Brilliant Leila. Thank you so much... I have sports workbooks where I have the whole season's fixtures entered. I can now use this table to see who is playing in the next few days or whatever period I want to see.
This is awesome, I have been doing this kind of thing the hard way! This method and functions will save me a lot of time. Thanks!!!
Thank you so much for excellent videos you make. I keep playing your videos several times. Wish you all the best.⭐️⭐️⭐️⭐️⭐️🌹🙏🥇
Oh so nice🙏🏻Tank you Leila!👏👏 For all great videos. This really help me in my work.
I'm so grateful for your videos and your courses👌🏼
Many thanks for the kind words Kenneth. I'm glad you find the tutorials helpful.
Can't wait for this course to be out. Signed up and patiently waiting ... 😊
It will be soon ... :)
Hello Leila,
how i can get Filter Function in my excel version? Currently im using Excel 2019 and i don't have "Filter Function".
Hi Leila, your biggest fan here :)
Please also cover power BI 🙏🏼🙏🏼🙏🏼
Leila, you are the best ! I like the way you explain, always when i have issues on excel i check out solutions through your channel, great job !
I'm very happy to hear that. Glad you find the videos informative.
madam you are teaching very very good and easily understanding thank you madam
It's my pleasure
Its great video but only drawback is you have to push you eye from every date while selecting from and to dates, however it should reflect as calender dates and that would be easy
Seriously so good knowledge sharing... It's really helpful and useful in my daily work
I'm really happy to hear it's useful in your daily work. That's how it should be.
Thanks Leila.. great showcase of the power of DAFs and your new course which I'm sure will deep dive into more tricks and techniques to get the most out of the new functionality. Looking forward to more. Thumbs up!
Thank you Wayne. It's amazing how much simpler things have become with dynamic arrays. I just hope Microsoft rolls it out soon...
@@LeilaGharani Agreed.. although there is nothing like the satisfaction of getting a wild array formula to produce the result you want. In terms of getting work done fast, DAFs will really improve productivity. Looking forward to your course :))
Brilliant explanation and demonstration of why/how to use a dynamic array in excel
Glad you like it Darren.
Leila, this is just an excellent video, I enjoy learning about this topic from you, thanks
Glad you enjoyed it!
If you would like to add more requirements/filters/constraints to your report, such that it only displays the values you want, say Peter Ramsy, you simply alter the cell with the filter function (G7) and under ''include'' you add an extra * (B5:B53 = "Peter Ramsy") - now you will only get the report for Peter Ramsy. Note that the range is for the Sales Agents column. This is helpful if you have more ''Peter Ramsy''s or same identical values that you only want those to be displayed.
This is great, it's like extra frosting on a cake! As always your tutorials are awesome! Thanks
Can’t wait for that course on dynamic arrays to be available, thank you Leila!
It will be out soon. Thanks for your support Billy.
you are a lifesaver ... love it !!! Thanks
Brilliant! Clear and detailed as usual. A great example of the new After Dynamic Arrays (ADA) period, starting in excel.
Thanks Roberto. Yes, a new era is about to start :)
Amazing, so easy to follow your videos. A true Excel-Queen as Daniel wrote .. 🙂
Thank you Leila for a very informative video. The question I have is that can you reference specific elements of a spill array so that you can pair them up with data that you've keyed in next to the spill array and reference both in a subsequent Vlookup or index match function. Thank you
Hi Leila, thank you for all your videos, they help a lot. I wonder is you be kind to share a formula that I cannot make to work yet, I need to add total value of one code between dates, If I do it separayly just looking between dates it give the how many Code do I have, but no count the quantity of the code, the columns are DATE, CODE and QUANTITY. Can you help me with that, please?? thanks👍
*Your are soft spoken, I like your voice.*I subscribed you right now*
Glad to have you here. Welcome to the community!
That UNIQUE function is so useful, keep up the great work!
It definitely is!
Thanks Leila. You are great
Glad you like it Robert.
I am very sad because in our company we have office 365 but IT department decided to not updating it yet so we can't use dynamic arrays.
Its because of potential problems which sometimes occur after uptade. Until they will be forced to make the update by some director or owner of company, we have to wait. =/
That's frustrating but let's just hope it will be rolled out soon.
I am searching exact thing for my problem, thanks for this video, i always like your video
Happy to help :)
Fantastic use of the Dynamic Arrays!
Thanks Oz - we need to find Satya!
Such and add-on to what I have been using in my report! It’ll make my life easier.
It definitely will Oliver. It's really a game changer.
I'm lost for words... On a further note, i subscribed for your online course :)
I'm glad to hear that George. The course will be out in June.
hi tanks for your bet exel toturials.very nice toturial
Glad you like the tutorial.
Thank you Leila. This is exactly that I've been looking for to use at work.
That's great you can apply it directly at work.
Hi Leila; Wonderful video !! For your video lovers like me, you are more like a user's manual to Excel and trust me its coming very much handy :) Cheers !
I'm very happy to hear that Sachin :) Many thanks for your ongoing support.
I am watching your video quite frequently and have prepared notes so that the example of accurate use of formula and functions may be useful for my daily usage. Many of the functions I have used in routine files which have drastically changed the way I worked before. You make us understand so easily that we can learn it well.
Can you provide the facility to download the example files you use in your videos because some of the functions are so complexed which are not easily understandable in one go i.e. offset or nested functions.
Glad you like the video. You can find the link to the workbook in the description of the video.
thanks to this great tutorial. how about if data to be extracted are from different tabs. thank you
Thanks Leila! Can't wait to have dynamic arrays in my Excel.
I hope it will be rolled out soon.
This is so useful. Ty so much sis
You're very welcome. Glad it's useful.
Great Excel guides and videos, really excellent work
👍Nice, great video mam "excel queen"👑
Thanks a lot
Please i want to know how to build a dynamic and interactive accounting system such that whenever I want transactions that occurred in a particular month, i can get it
I took an excel class at SMC but it was horrible. Firstly the class was only six weeks. But the idiotic professor assigned two quizzes and two homework assignments each week and about 200 pages of reading that no one had time to absorb any material in that short a time period. Everyone was just so busy trying to memorize for the quiz and work on the assignments. I can honestly say I remember nothing from her class. I will need up go back and relearn everything at a much easier pace with your videos
Lelia another great tutorial so clear thanks a lot
You're very welcome Nissim. Glad you like the video.
Awesome tutorial Leila ... thank you
You're very welcome.
great video as usual Leila. One question though. If I am not able to use the FILTER function, what other way would you recommend?
You can use filter function in google sheets or online Excel if you don't have office 365 or you can use the advanced filter function in the excel.
Hi Leila thanks for all the good videos recently I have watched over 5 videos and all good and informative. The challange i have after watching this video is when i go to prep from and prep to and poulate the dates from my date column only 33 cells get copied and i have data upto 44 cells also the dates dont come over correctly - What am i doing wrong
How to extract data within two dates by using excel old version
Do you have a solution for this for other versions of excel where the filter option is not available?
Very informative video but, not something I would use everyday. If I were presented that info and was told to give a report showing sales between dates I would automatically convert the data to a table and use a pivot table to extract the info.
Ur vedio super very useful all
Thanks for video!
You're very welcome.
Anyway I have never seen the coach like you who has the full command over each formula, function and DAX.
Hi Leila,
Please give a video class that how can I look up data from different sheets and workbook if possible so I need it so much.
Excellent video.
Glad you like it.
Hello queen, thank u so much for this video, like every time, always special and useful solution, but i wonder if there is another functions that can replace the ( UNIQUE/FILTER & SORT ) for all formulas in this video.
Very well explained!
Thank you Leila for your videos. I must say that I have learned a lot.
I was recently working with stocks data and wanted to filter out specific date data of multiple years, let's say the last trading day of the year for the years in the range.
I usually click the filter, and then have to manually click each day . Is there any better way?
Looking forward.
do you have similar video but using ms excel 2016? not office 365?
This is magnificent! Thank you for the help.
Awesome Leila, Lovely
Glad you like it Majid.
Thanks for your wonderful video
Awesome, very helpful video. Thanks Leila.
Hi Leila,
Thanks a lot for your videos they are really very helpful.however, I'm struggling to put a formula where I can put vlookup if a date falls between two dates. Can I do that? Or please could you help me with putting a formula for a query like this?
Your help will be very appreciated.😁
This is extremely helpful. You're the best!👌
Glad you like the video!
As usual so great work...stay blessed!
Thanks for the kind words Solomon.
hello leila, can you please make a video that look up values filtered between two dates and dependent on the person using that particular date? dates and names were all data validation in separate sheet..
Leila do you have any idea when this will be available for office 365 users? I am waiting since September 2018... I tried many times to become also an excel insiders user, but unfortunately that's not possible. It seems that Microsoft randomly offers this option to the users...
No sorry, there is no fixed ETA. However, it is not random. You can decide yourself to join. Check here: insider.office.com/en-us/join
@@LeilaGharani Thanks Leila, actually the following link on your link is more helpful for Business, enterprise, and education customers that have an Office 365 plan : insider.office.com/en-us/handbook/for-work-or-school
Thank you for this channel! You have helped me so much. I spend many enjoyable hours listening and watching you to as I practice on my own.
Still, I would like to know if you have any suggestions on how to obtain a future date with a nest if formula with variable start dates. For example, if I complete a job on and my expected pay will wing it's way to me either the 15th or the 30th of next month dependent on if I performed my task before or after the 15 of this month. For added measure, could I add another criteria?
Best Excel guides. I have a large list of date ranges (with start and end dates) with some overlaps; I want to count total number of unique days without counting overlaps multiple times. I've been brainstorming this for some time using sumifs(), but every idea comes short. Any hints? Thank you!
Great video, Leila! Like you said at the end, these dynamic array functions are still only available for Office Insiders. Just wondering if you know when they will be released in the main version?
No, sorry Andrew. Microsoft did not communicate an ETA for it yet.
شكرا جزيلا think you
Great stuff - unfortunately useless, as my version of Excel (Mac 2011) does not recognize any "UNIQUE" syntax.
Thank you Leila , question is there any way that can find 3rd date between start and finish date with formula ?
Very usefull video!! Thank you
Glad you like it.
Hi Leila, can you also do this without using filter and sort function? We only have excel 2016 hehe. Filter and sort function not available
Awesome. Thanks
Thank you very much, Leila.
You're very welcome.
Excellent again Leila the # (Spill) command is new to me and will come in very useful, Wonder when it will be available on Mac ? Thanks again
If you have Office 365 Insider Fast you will also have it in Excel for Mac.
@@LeilaGharani thanks for the info, i don't have the insider Fast version of 365 at the moment, i will look around to see how to get this version as mine is just Office 365
Hi! really helpful video however isit possible to change the values to a graph form?
Can you use this function on a difference worksheet instead of the same worksheet. Will this give you result?
Thank Leila, love the video, helpful as always, Herzliche Grüsse
🤗 🌞
Dankeschön :)
Is this only applicable in excel 365 ?
Excellent work...thanks
Glad you like it!
Thanks a lot
excuse me can you teach me how to search date with drop down list with dynamic calender? thank you
M'am if we use >= at 3:57 stage then we can also shot data by same date like 1/31/2019 to 1/31/2019
thanks
Yes, this is an excellent suggestion.