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




About this tutorial:

Video duration: 5:46
Download Excel Start Files:
Entire page with all Excel Files for All Videos:

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.

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

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

    Annamaiah G

    (November 30, 2019 - 2:41 am)

    Very nice and filter formula is not showing the my excel only filter xml forumula show how get only filter forumula pls tell me

    Tom

    (November 30, 2019 - 2:41 am)

    Where is your video on the old method? I use office 2016

    Wayne Edmondson

    (November 30, 2019 - 2:41 am)

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

    Tornike Kvinikadze

    (November 30, 2019 - 2:41 am)

    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

    LAdams

    (November 30, 2019 - 2:41 am)

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

    Mohamed Chakroun

    (November 30, 2019 - 2:41 am)

    thanksssssssssssssssssss

    Shanes Mohammed

    (November 30, 2019 - 2:41 am)

    Fantastic

    Sabrina MIMOUNI

    (November 30, 2019 - 2:41 am)

    Mr. Excel, You're genius 👏👏👏

    Luciano

    (November 30, 2019 - 2:41 am)

    Show…nice. Thanks Mike.

    SAIF UR RAHMAN

    (November 30, 2019 - 2:41 am)

    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.

    Anil Dutt Bhargava

    (November 30, 2019 - 2:41 am)

    Nice

    krn14242

    (November 30, 2019 - 2:41 am)

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

    Richard Hay

    (November 30, 2019 - 2:41 am)

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

    Leila Gharani

    (November 30, 2019 - 2:41 am)

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

    فاروق السامرائي

    (November 30, 2019 - 2:41 am)

    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

    nimrodzik

    (November 30, 2019 - 2:41 am)

    Thank you very much Mike 🙂

    Fabio Gambaro

    (November 30, 2019 - 2:41 am)

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

    imran ali

    (November 30, 2019 - 2:41 am)

    Can this function available in office 2019

    Syed Muzammil Mahasan Shahi

    (November 30, 2019 - 2:41 am)

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

    Phone Excel Time

    (November 30, 2019 - 2:41 am)

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

    PR

    (November 30, 2019 - 2:41 am)

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

    Dinesh Kumar Takyar

    (November 30, 2019 - 2:41 am)

    Superb!

    Tulsidas Jamnani

    (November 30, 2019 - 2:41 am)

    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

    Matt Schoular

    (November 30, 2019 - 2:41 am)

    Thanks Mike… please ask Bill Gates to release dynamic arrays to the rest of us.

    Excel Bear

    (November 30, 2019 - 2:41 am)

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

Leave a Reply

Your email address will not be published. Required fields are marked *