Excel Magic Trick 1539: Extract Daily Sales Records With Formula. FILTER Function (Dynamic Array).

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.

[MalinaC]

Thumbs definitely up! I do understand the case shown in the video, but don't understand why somebody has given it thumbs down ;(.

[LeilaGharani]

Filter has become my all-time favorite function! Love the video. Thank you Mike :)

[FabioGambaro]

There is an innate elegance in the new array formulas... thank you Mike!

[richardhay]

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)!!

[davebowman]

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

[pr]

You are one the best teachers I have seen.
Thank you for educating!!

[SyedMuzammilMahasanShahi]

Yet another EXCELlent video by Amazing Mike. Thank you so much. Loving it.

[NoShadowOfDoubt]

These dynamic array formulas are smart!

[drsteele]

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.

[johnborg]

Thanks Mike. Very nice !!! Always learning from you :)

[ricos]

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!

[excelbear]

just when you thought life in Excel can't get easier... it always does!

[krn]

Thanks Mike. I wish my company would make the move to Excel 365 so I can use these new outrageous functions.

[fernando]

I thank you very much teacher, it motivates a lot to learn

[simfinso]

so Easy.Waiting for office 365 in my pc.Thanks as Always.

[HusseinKorish]

Wonderfull as usual .... THanks alot Mike

[arunk]

Wow, you are amazing. 👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍

[daniyalbaghi]

I wish my language was strong so that I could use all your teachings 😂
I am Iranian.

[tornikekvinikadze]

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

[tulsidasjamnani]

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