Next level FILTER Function tricks | Excel Off The Grid
Vložit
- čas přidán 30. 06. 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-...
File Reference for this video: 0214 FILTER function tricks.zip
★ About this video ★
This video includes amazing FILTER function tricks that you can use to take your spreadsheets beyond the next level.
INCLUDES:
0:00 Introduction
0:17 FILTER Recap
1:42 Multiple Conditions
3:01 If_empty Array
3:52 Partial Match
5:06 Based on List
6:10 Using Slicers
8:15 Automate Excel
8:40 Show All Items
10:03 Specific Columns
11:04 User Column Selection
12:00 Dependent Drop Down
13:09 Wrap-up
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders
File Reference for this video: 0214 FILTER function tricks
HATS OFF to Excel OFF the Grid! Much, but very very much appreciated, Mark! You are absolutely a genius! It is so hard to thank you enough!
Thank You - that is so kind of you to say. I really appreciate it. 😁
Watched Best video On FILTER functions ever.Thank you for Your Contribution to our Success
Excellent video Mark. As always, going a little deeper, where others don't go. Very professional. Thank you so much.
It took a little while to prepare and edit, but hopefully it was worth it.
Brilliant Mark- I’ve seen all of these before but not all in the one place- great Video
Glad you enjoyed it 👍
Really like the return all option, also the use of choosecols, I still head straight to index or worse offset for a lot of things where these 'newer' funcs could be used.
INDEX is still the most important Excel function. So, it’s a solid choice.
I toyed with showing INDEX, but decided that it would better to show another newish function.
Brillant explanation as always. Have been using this function since its release, but learn a lot from your logical process steps. Thanks a lot.
Great stuff. It’s always useful to pick up new tricks.
Good roundup and a couple of new tricks for me :)
Ctrl + Enter keeps the current cell selected so you don't have to leep moving up again
Thanks.
I know about Ctrl + Enter... I just don't use it. I'm not even thinking about what the next thing is until I've seen the result of the previous step. So, for me, it's an irrelevance.
Ridiculously easy to understand!! Outstanding as usual
Thank You 😁
Making us understand that true and false logic in filter open lots of different permutations and combinations which can be used. its really insane😂
It’s all about True/False.
The same techniques work on the new GROUPBY and PIVOTBY functions.
Absolutely amazing and in depth. I learned many things, I am happy to say!
Filtering based on a list will be a big game changer for me, thank you very much for this video
Great news - I’m glad you’ll be able to put it into action.
Excellent step-by-step, from the ground up, logical, walk through explanation! I have been playing with developing a new streamlined method of searching and filtering data and this is giving me some ideas. Much thanks! (Just subscribed as well.)
Great stuff - lots of new things to try here. So you’ve got some fun days ahead. 😁
@MMark, outstanding! My favorite is Ex8 Filter/Choose specific columns. Thanks for sharing!!!!
Yes, that is very useful. Gives you a lot of control. But… the formatting can be tricky.
one of the best videos on FILTER... thank you very much Mark! Cherian in Abu Dhabi!
Wow, thanks! 😁
Thank you!
Another superb video Mark, I learnt a lot.
Great stuff - thanks Peter. 😁
Wow -
Normally I consider myself fairly good using Excel, but this is a bit like playing in the Sunday league and watching Premia league.
The part using Slicers, and the capability to let users choose columns, are new to me, thanks
One question, I see that you wrap choosecols inside filter, but I guess that it works as well wrapping filter inside choosecols?, I find it easier to do it like that!.
Thanks - I'm glad I should share something new.
In terms of CHOOSECOLS inside/outside the FILTER. Either will work.
I went with the inside because in theory, it should be faster as it does the easier task (i.e. CHOOSECOLS) to reduce the columns, then uses the FILTER (the harder task) on a smaller array. But I have no idea if it actually makes any difference.
For filtering by a list, I recommend ISNUMBER with XMATCH. It works better because you can use it inside of LET. XMATCH can use arrays only whereas COUNTIF/S needs at least a range to work.
Yes, that’s very true about COUNTIFS not taking arrays.
Is there a reason to use XMATCH over just MATCH? Does it provide any advantages?
No, in this particular instance you could just use the old MATCH
Magistral video!.. Saludos y gracias por compartir.
Thank you. I'm happy you've found it so useful.
Estoy completamente de acuerdo. Los videos de Mark son entre Los mejores.
Another Epic tutorial, I knew right away that it would be outstanding one from Mark, specially when it come to play arround with the filter as you have cracket advanced filter with vba.
You are absolutely an Excel Legend, always going extra mile. I couldnt agree more, that your Excel skills are unmatched! Keep it up.
By the way, do you have any tutorial on Excel lambda().
I cant thank you enough for your awesome tutorials. ❤
Thank you for your kind comments.
Some of my videos use LAMBDA, but not a specific video about it.
My next course will be about LAMBDA, so I will go deep there.
Perfect 👍
Thanks 👍
Awesome. I've been looking for "filter" functions examples and that's the best one. Great video!!!!
Awesome, thank you!
Great video!
Thanks!
Wow! super extra extremely useful! Thanks!
Glad it was helpful! 👍
One of the best tutorials I have seen so far on the filter function 👌
Wow! Thank you. I appreciate that.
Thank you for sharing these excellent techniques.
You are welcome!
Super video as always👍❤. Thanks Mark.
Kebin - Thank You as always. I appreciate your support.
Excellent video! I often use the filter function, but still learned a lot with these tips! Thanks!
Great news - I hope you can put some of the techniques to good use.
This is extremely helpful. Very well done! Thank you!
Glad it was helpful!
Thx for great examples and summary of FILTER function. So clearly explained :))) Bravo!
Thank you. I hope you can put them to good use.
As always nice video 🎉❤
Thank you! 😃
wide, deep, quality material !
thanks a lot for sharing !
- -
q: why in Ex7 SEQUENCE(ROWS) technique is used instead of more "consistent" for conditionals/booleans, say, Example7[Type]=Example7[Type]
Thank You.
For Ex7 - there are probably 10 ways to do that, and I had to pick one. So that’s the one I went with. No specific reason.
@@ExcelOffTheGrid
ok :)
Thank you Mark.Great explanation
Thank you. 😁
Much better tutorial than anyone else. Kudos ❤
Thank you - that is very kind of you to say. 😁
Thank you for sharing this amazing video. Good job! I have a question. Could you also do an example with a filter function (dropdown list or slicer) and a scrollbar? Idea is to display in a dashboard only 10 rows and by scrolling down with the scrollbar you see the rest items. One more specific thing. The database has more column I want to show in a dashboard. So I only want to certain columns.
While you can link a scroll bar value to a cell, you can’t set the length of the scroll bar based on a cell.
Therefore, this requires VBA to achieve it. So there is a lot more nuance, which would need to be covered.
Mark amazing video! one question though: is it me or it is counter-intuitive that on minute 5:41, the arguments of the COUNTIFS function are inverted? Meaning shouldn't the criteria range (property column on the table) be first and then the criteria, which is your list?
Good spot.
Yes it is counter intuitive. I usually call this a “Reverse COUNTIFS”.
We want the function to spill for each item (known as a scalar), therefore we need to provide multiple scalars in the criteria argument.
Thank u sir ❤❤
Amazing depth Mark ❤❤❤❤
Thank you 😁
Mark, very helpfully and very well presented
Thank you, I'm glad it was helpful!
Excellent content 💯
Appreciate that, thank you. 😁
so love this chanel❤
Thanks. I hope we can keep delivering quality content.
Can you share the solution for Example 6 for older Excel version (without Lambada function). Thanks a lot.
Specific Columns Function = Pivottable anywhere
Partly...yes. It does't perform any type of pivoting or aggregation, so a slightly different outcome.
Thank you for this! FILTER is pretty much my favorite function! I often use =UNIQUE(CHOOSECOLS(FILTER(....))) to pull specific filtered columns; I had not considered =FILTER(CHOOSECOLS...)) before. Do you know if both formulas work about the same? Or is one more efficient than the other? I always want to make sure I'm using the most efficient formulas! Thanks!! Great video!
I don’t think it makes much difference.
In theory using CHOOSECOLS first would be more efficient as it reduces the size of the array before doing more complex calculations.
But I have no idea if that is true.
@@ExcelOffTheGrid Thanks for the response!
Great content! Re. filling no match values across the entire row. The formula fills down the first column rather than across all columns. Any thoughts to fix this?
Please ensure You are using a comma and not a semi-colon.
Comma - to spill the values in columns.
Semi-colon - to spill the values in rows.
Example: {1, 2, 3, 4, 5}
Hope, this helps.
Thanks for your reply. I'm actually using the French version which uses semi colons rather than commas. I've tried both punctuations but still no success. I'm doing something wrong...
I think with French settings it is the \ character.
How to use slicer with use column selection function combine?
You would need to create a disconnected Table - check out this video czcams.com/video/v-vrEtCMKiI/video.htmlsi=GEi0KKc8T169W4eF
When I entered the formula for filtering with a list, I get a #VALUE! ERROR could anybody plese help?
why not so clear
Nice. But I rather use the old fashion way.
What do you class as the old fashioned way?