Excel Dynamic Arrays: 4 Complex Filter Examples

Excel's Dynamic Arrays are fascinating ... and complex. Here is a second video where I dig into the logic of the FILTER function.

You'll see examples of the FILTER function, filtering on 2 criteria where I play with the logical operators:

Greater Than
Less Than
Equal To
Not Equal To
Smaller Than or Equal To

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:

My book: Guerrilla Data Analysis 2nd Edition

[bhporders]

I have watched a million videos about the FILTER function and you're the only vlogger who has explained operators other than + and *. And in just 8 minutes. Amazing!! Bravo!! Thank you.

[excelisfun]

Yes, sir, I am playing a lot!!!! It is so much fun : ) Thanks for the play : )

[vida]

What an interesting operators' discovery and good examples to explain them

[sasavienne]

That is awesome. Thank you. 👍 You and Mike and Leila are all amazing. Thanks once again.

[stevennye]

I rewatched this while working on a big project, this was very helpful.

[wayneedmondson]

Hey Oz.. excellent FILTER magic! At first glance, it is easy to think of using only * and + for AND and OR. You've shown a whole new set of options.. like multiple dimensions.. great! Thanks and Thumbs up!

[excel-in-g]

How did I miss these videos?
Crazy hot stuff! XL on 🔥!!

[billywiswall]

Thank you for your examples of using operators, this is the area I make most mistakes when using them. Please make more videos with operator explanation and these new array functions. Very helpful!

[MSniping]

Man I love these videos. The only thing I struggle with is it really only is efficient in small subsets of data. I have noticed in large datasets these functions struggle.

[excelisfun]

What is so strange about the minus is that it does not follow the set operator (Relational Algebra) "Difference/Minus/Except Operator", which is like Left-Anti Join in Power Query or the EXCEPT DAX Function in the Data Model. The Minus in Filter is doing an Exclusive OR like the XOR Function in Excel Spreadsheet functions...

[wesszep]

=filter((table 1, (blessing [this year] ="Great things to happen" )*(table 1, (blessing [Available] ="Yes" )
Happy New Year OZ
May this year brings you good health
and happiness for you and your family.
And hope to see many more enjoyable videos
:).

[hassanosseiran]

Hi Oz,

How do you get the customized background on the ribbon?

[wasimsuperb]

I have a sheet with data or table on top where i have shopname, contact and address .. and on bottom have the same data or table with same information  ..i want when i write shopname on bottom the contact and addresses will come automatically  on their respective column and rows ...how i can achieve in excel...please see attached sheet also for reference