Excel Magic Trick 1539: Extract Daily Sales Records With Formula. FILTER Function (Dynamic Array).
HTML-код
- Опубликовано: 8 ноя 2024
- Download Excel Start Files: excelisfun.net...
Entire page with all Excel Files for All Videos: excelisfun.net...
In this video learn how to extract sales records based by day with a formula. Learn about the New Office 365 Dynamic Array Functions: FILTER, SORT and UNIQUE. Compare and contrast this new method to the way we used to accomplish it in the old days with complicated Array Formulas.
![Kodak Black - Catch Fire [Official Music Video]](http://i.ytimg.com/vi/A4ch-olIuZo/mqdefault.jpg)
Filter has become my all-time favorite function! Love the video. Thank you Mike :)
I agree. Why? Well lots of reasons but the number one reason is that it replaces all those crazy "One Lookup Value, Return Multiple Matched / Records" Formulas that we used to do. My poor book, is so OLD and Out-Dated : ( But the ease with which we can do things is wonderful!!!! Thanks for the love, Leila : )
There is an innate elegance in the new array formulas... thank you Mike!
Yes, I agree with you about the elegance, like beautiful Spanish Flamenco Dancer performing Data Analysis : )
Thumbs definitely up! I do understand the case shown in the video, but don't understand why somebody has given it thumbs down ;(.
Thank you for your support. I do not know why the humans click thumbs down, but I suspect that they are confusing their right to communicate with their duty to make the world a better place ...
@@excelisfun True!
@@excelisfun I think they confused the thumbs down with the download button 😂😂
@@Sal_A , Too funny!!!!! : )
Malina C. 1zz
Super! Great Video! I have found FILTER to be a valuable addition to the Excel toolkit. Yesterday I noticed your reply to a comment on EMT1538 regarding left facing lookup and the link you provided. From the link, I was sure it was an oldie and I certainly wasn’t wrong! Your duel width Bill Jelen was fantastic. Your discussion of LOOKUP was probably about what you would say if you made the video today. But Bill’s segment was hilarious!! His exuberance at having discovered a use for MATCH (with INDEX) when he originally thought it to be a pointless, useless function brought emotional transparency to a new level. Watching his segment was like watching an interview with a dog who had just caught his first car and was ecstatic about how good the tires tasted (including the hot air filling) when he had previously thought that tires only existed to help a car escape his ravenous appetite!! The video is a reminder of how much excel has changed since the time Excel2007 was considered to contain the sharpest tools in the shed!! I give that early Mike G video the highest award I can give to a vintage video: “Well Worth the POPCORN” But where was the 16:9 aspect ratio? Oh year, it has been a long time in video years since it was created! MEGA (Make Excel Great Again)!!
You are a poet and word smith, Richard!!! Your ability to create illuminating similes, analogies and metaphors is awesome. I love this: "a dog who had just caught his first car and was ecstatic about how good the tires tasted (including the hot air filling)".
I love doing the Mr Excel and excelisfun Duels. They are more fun than is legally allowed : )
Yes, the videos and Excel have changed a lot over the last decade.
The good news is that the prospects for our Excel future are bright: Elegant (like a beautiful Spanish Flamenco Dancer performing Data Analysis), Efficient (easy, resource lite AND fun) and Edifying Excel Solutions (we learn some cool tricks as we create and the user learns lots of rad and useful things) here we come!
And I even gave that vintage video a "thumbs up" when I finished my popcorn!! I look forward to future EIF duels! At least your duels with Jelen have happier endings than Hamilton-Burr!!LOL
@@richardhay645 Even? I thought it was an innate duty ; )
@@richardhay645 Nice: Hamilton-Burr reference...
Poor Alex! Whose Errant Volley disclosed the Inherent Folly in “Ready, Shoot, Aim”. And while Burr called it Poetic Justice, Girvin and Jelen made sure to ensure that History didn’t Rhyme!!!
These dynamic array formulas are smart!
They are smart!!! FILTER replaces so many crazy lookup formulas : ) Thanks for the support, NoShodowOfDoubt : )
You are one the best teachers I have seen.
Thank you for educating!!
You are welcome for the education, PR!!!!
I've always been a big fan of the underused Advanced Filter, going to be using it less often soon I can see, thanks Mike
I am like you, I love Advanced Filter, and there still might be some uses, but as i say in the video, FILTER is just so simple. It really is amazing : )
Yet another EXCELlent video by Amazing Mike. Thank you so much. Loving it.
Glad you are loving it, Syed! Lots more love this week and on through this month : )
Great solution thanks Mike!
You are welcome, Chris : )
The new functions are certainly terrific - simple, efficient and clean. I sure wish MS would allow arrays in the Data Validation list so we could avoid having to create formulas like the one in J9.
I wish too!!! Thanks DRSteele : )
Mr. Excel, You're genius 👏👏👏
Bill "Mr Excel" Jelen is a genius!!! I agree. --Mike "excelisfun" Girvin
For me You're Mr. Excel and the Excel king 🤗
@@sabrinamimouni8306 Thank you, Sabrina! I will try and continue to be a good Teammate to try and bring free, fun and efficient Excel Learning to the World! I am happy to be on that Team with Bill "Mr Excel' Jelen in trying to accomplish this goal : )
Superb!
Thank you very much, Dinesh!!!! I love seeing you here in the comments. Your videos are great and I always have a link to your RUclips Channel on my excelisfun Homepage!!! Thank you for your support, Dinesh : )
just when you thought life in Excel can't get easier... it always does!
Thanks Mike. I wish my company would make the move to Excel 365 so I can use these new outrageous functions.
It is just astounding what is possible, WRH!!!! Thanks for your support over the decade, krn14242!!!
Thanks Mike. Very nice !!! Always learning from you :)
You are welcome for the always learning, John!!!!
I thank you very much teacher, it motivates a lot to learn
Value if empty always useful for the blank date. I always like to save my worksheets with no filter selected so that data isn't shown. For some reason! Anyway, great video, the dynamic arrays do raise a huge dilemma between pivot tables and data modelling and just importing data to the worksheet and using tables with dynamic arrays given the speed of the latter and probably reduced complexity. I tend to organise all my data these days as if I'm going to be using the data model (dimension and fact tables), but I could see myself accessing that data via arrays in many cases now, expecially in places I would have used cubevalue formulas previously. An interesting video might be one where you setup your data in fact and dimension tables and create similar reports as a comparison between pivotted data models and dynamic array filtered data. If you're bored!
Good thinking... But DAX and Standard PivotTable solutions can do a lot of things that Dynamic Arrays can't do, and vise versa. For Dynamic Arrays, simple Grand Totals at the bottom, are very difficult, whereas with Standard PivotTables & DAX it is easy to get the formula to make different calculations at different levels or grains. Here is an example of trying to make Dynamic Arrays have Totals: ruclips.net/video/wHeXjYQGvX8/видео.html On the other hand, Dynamic Arrays just spill and update instantly, even when new criteria or conditions are introduced. The lovely TRUTH is that we have all these wonderful tools: Spreadsheet Formulas, Dynamic Arrays, Standard PivotTAbles, Power Query, DAX, Power Pivot, Data Model PivotTables, Power Bi and each is wonderful and has there place : )
so Easy.Waiting for office 365 in my pc.Thanks as Always.
I hope the wait is small, Finance in 5 Minutes!!!
ExcelIsFun Same feelings here.
Such a neat solution.
Yes, indeed : ) Thanks, Vida!
Thanks alot Mike
You are welcome a LOT, ogwal!!!!!
Wow, you are amazing. 👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍
Glad you find the video amazing, Arun!!!! Thanks for your support : )
Wonderfull as usual .... THanks alot Mike
You are welcome a lot, Hussein!!! Thanks for the support : )
Excellent ❤
Thanks 😊
Thank you very much Mike :)
You are welcome very much, nimrodzik1!!!!
Thank you very much, 💗
You are welcome very much!
Hello! I'm new here and I'm amazed of the content you make! Can you please tell me if there is any way to "upgrade" my office or at least somehow be able to use functions like filter/unique and etc. ? I'm using student license now so I think I should be able to upgrade it.. thanks in advance
Show...nice. Thanks Mike.
Glad it is nice for you, Luciano!!!!
Hello mike sir,
we can also use SORT function with nesting filter
= SORT((FILTER)sort colomn, asending or desending order).... For...
Sorting filtered data by column I am using this with Google sheets..
And we can also use less than or greater than conditions with filter function for filtering desired data data.... please make another video on this topic
Yes, you can do both. I will 100% make more videos, but I have a few months of videos already planned. After that ... I already have about five FILTER videos posted, they are here is this playlist: ruclips.net/p/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx
I wish my language was strong so that I could use all your teachings 😂
I am Iranian.
Thumbs up!
Thanks, Teammate!
Hi Mike.. another great video on the new FILTER function. I was curious if I could use your UNIQUE(SORT()) formula to directly populate the List Source of the Data Validation drop down. Unfortunately.. no go. I tried entering the formula directly and got the old "There's a problem with this formula" error message. Then I used Name Manager to give it a range name and put the formula in the Refers to: field. Name Manager accepted the formula and it will spill properly if referencing the named range in a worksheet cell, but when using it as a list source, I got the message: "The source currently evaluates to an error." So, no luck there either. I tried the same with a form control and activeX combo box and neither would accept any form of reference or named range for the ListFillRange or Input Range. The combo box controls won't even accept the $J$9# reference. So at the moment. an extracted range on the worksheet and a data validation drop down pointing to the first cell in the spilled range with a # is the way to go. Let's hope MS adds this functionality in the future. Learning something new with each of your videos. Keep them coming. Thanks and thumbs up!
PS - I agree with Malina C. below -- don't understand how anyone could thumbs down your videos? Bad hair day.. maybe.. haha!! :))
Yes, we all want Data Validation to accept that formula. I did a video on this topic a couple months ago. However, in that video i did not try the VBA Combo Box. i hope they will fix it soon. Thanks very much for your support in many ways, including the Thumbs Down Avoidance : )
Fantastic
Glad it is fantstic for you, Shanes!!!!
Nice
Glad it is nice for you, Anil!!!
Oh my God I really wanna buy the office 365 insider please send me link Mr. Mike and tank u for the video
I am sorry i do not have a free link for it...
Still only have excel 2016 :( but I’ll keep this for future reference, if my job ever decides to upgrade.
In a few years Office 365 will be ubiquitous. Hopefully your job will do it sooner, though : )
Hi , I have a question, I am trying to use Power Pivot for rolling forecast model, I have done a model in which once I upload volumes, prices & cost it can give me results for Gross Margin forecast by month and by SKU, now I am struggling to combine this model with actuals, as actuals change everymonth. Calculation in my model are dax functions whereas actuals are static and sitting in different cube.
Any ideas or any advice will be greatly appreciated.
I do not have a video on this topic, but will by about June. In the meantime, the DAX Masters Russo and Ferrari can come to the resue at this great web site: www.daxpatterns.com/budget-patterns/
Very nice and filter formula is not showing the my excel only filter xml forumula show how get only filter forumula pls tell me
Microsoft has only released it into Office 365 Insider Edition. In a few months it will be in all of Office 365. Microsoft says the only version that will ever have this is Office 365, not Excel 2019, 2016 or any other. If you have Office 365, simply go to your File, Account and then switch your Office 365 to Insider : )
thanksssssssssssssssssss
Can this function available in office 2019
Microsoft says no. That is why bloggers like Bill Mr Excel Jelen and me tell everyone to NOT buy Excel 2019. It just is not the correct version to but and is almost pointless because it is missing many new features that Office 265 does have.
Where is your video on the old method? I use office 2016
How would one go about sorting the results by the sales amount in the table produced: F11:H11 and following / =FILTER(fSales,fSales[Date]=F9)?
You can wrap SORT around the FILTER.
Sorry for not being able to figure it out, but that's what I'm having the problem with. I can't figure out what to put for : =SORT(FILTER(fSales,fSales[Date]=F9),???). H9 and H# don't work.
Make that H12 not H9
@@excelisfun Sorry for not being able to figure it out, but that's what I'm having the problem with. I can't figure out what to put for : =SORT(FILTER(fSales,fSales[Date]=F9),???). H12 and H# don't work.
I figured it out: =SORT(FILTER(fSalesAN,fSalesAN[Date]=F9,"None"),3,1)
hi,
I need help to create two drop-down menus each one in a different worksheet, so that when you call the first address's one; second evidence appears.
thank you
Here is one video on this: ruclips.net/video/-78bH0RZ7MA/видео.html
Thanks Mike... please ask Bill Gates to release dynamic arrays to the rest of us.
Please Bill Gates, will you release the Dynamic Arrays to the world! We need them!!!!!!!! : )
@@excelisfun Hi Mike, it´s always a pleasure to watch your videos. They have been very helpful. Unfortunately I dont have the functions available. Is there a way that I could get the Excel that you are using? Please let me know if there is chance to get it or the version of it
Thank you Mike :)