Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders File Reference for this video: 0214 FILTER function tricks
Cool data validation trick for displaying desired columns. I would have never thought of that. I have done validations choices based on another validation selection, but not the column thing. Thanks.
I LOVE how you show how to build the function using the answers that Excel is calculating. Very few take that perspective but it makes so much more sense... Thank You!
Hi. In example 4, if we use the formula "=FILTER(array[Column], BYROW(array[Column], LAMBDA(x, AND(ISNUMBER(SEARCH(SPLITTEXT(search_value, " "),x))))))", then we can search Non-adjacent text/values/words. It's very effective. Even Excel filter search does not produce non-adjacent output.
The FILTER function in Excel is incredibly powerful and helpful for organizing and analyzing data. It allows you to extract specific information based on defined criteria, making it easier to focus on relevant details without manually sorting or searching through large datasets. Whether you're working with lists, tables, or complex data sets, the FILTER function saves time by dynamically updating as the data changes. It's a must-have tool for anyone looking to streamline their workflow and improve data analysis efficiency. Thank you Excel Off The Grid!
The last part was awesome because I used to use offset with an embedded counta to change validation lists without blanks. The filter function seems so much easier. Thanks for the video!
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.
This is one of the best videos on the Excel filter function I've seen. You covered techniques that will set my spreadsheets apart from others. Thank you!!!🥰
WhoA !! Absolutely Insightful and very demonstrate the power of combining functions (Filter , Search , Slicers in tandem) and making complex tasks much easier
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.
Your tutorials are amazing. I just discovered this channel, and I have to say this is one of the best Excel channels and I have been subscribed to a lot of them. Could you please make a video with advanced examples of using -- (double minus) in front of arrays in various scenarios? A lot of Excel Pros are using -- in sophisticated formulas, but I haven't come across a detailed video tutorials of its usage in an advanced formulas. Thank you in advance.
Thanks for those kind words, I appreciate that. Usually the double minus is used to coerce text to a number (same as *1) or to force a non-spilling function into an array (such as EOMONTH th-cam.com/video/IEfPe8Beh4E/w-d-xo.htmlsi=So7CNupVAiF5izWN) It doesn’t have any special properties in itself.
Watch it again... then you'll understand to 60%. Then watch it again.... After a while you'll understand 100% and they will become boring old FILTER techniques which you can't grasp why everybody doesn't understand them. 😂
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.)
I haven't come across such a superb video which lucidly teaches super complex use of extraordinary powerful filter function.it made me very confident in handling large data for my dynamic dashboard..thank you so much❤❤❤❤
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.
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.
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 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.
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.
EXCEL-lent video as always. Good job covering many different things that can be done... would add a BONUS item. what if you have two data tables. (yes i know use power query to merge them!!!!) but another way is to =Filter(Table1,Table1[S_Rep] = "Mary",Filter(Table2,Table2[S_Rep] = "Mary","Person not found")) this allows looking for a sale rep in two different tables with one formula (but yes use a cell reference, not a static Name) Just good to show that you don't just need to put text in the If not Found portion of the formula.
That's a nice idea I've not see anybody do that. You would need to know that that "Mary" wasn't in both Tables, otherwise it would not give you all the values.
@@ExcelOffTheGrid its not the best example for this, ill have to see if i can find one. too bad you can't put screenshots in comments. and im not that good at making YT vids like you guys do.
We've got various dynamic array vides on the channel. But nothing which is beginner to pro play list. All our structured/ordered content is included in our courses.
Hi Mark. Very good - but I got lost at the start. I was trying to follow along using my own data. I tried to create a named range - to match your “Example1” but this always messed up my results. Very frustrating to fall at the first hurdle. How did you create Example1? Thanks
It’s not a named range, it’s a Table. Select a cell in the range and click Insert > Table (or Ctrl + T). Tables are one of the most important parts of Excel, so you should definitely spend some time learning them.
I hv a excel workbook each with a table with same structure. Its a download of status on diffnt dates. I am required to calculate stat bet any two tables as selected by user using a drop down list. My question is how to pass on table references in formula. The stat should be visible side by side for selected tables.
Very informative and well done - thank you! After thinking about it, how would you go about expressing not equals? Being able to list all that doesn't match Road?
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.
Hello, Mark. Thanks for the great video though it has a lot to learn in a so short duration. I need your help with a filter formula. I use a filter formula to build my statement of account and when I use choosecols with it to choose the columns I need to show, the if_empty part does not work. Can you help with this? Thanks in advance
Wrap the CHOOSECOLS around the array in the FILTER function, instead of the result of the FILTER function. Then the if_empty is not calculated on. Example: =FILTER(CHOOSECOLS(Data,{1,2,5,9}),Data[Column]=A1,"No Value")
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...
If there is single column but we need to include "Jan", "Feb", "Mar", then how we will going to apply filter to extract all months specified in a single column??
Wouldn't the "show all items" be more easily/performantly accomplished by: =IF( I4 = "*" , Example7 , Filter( Example7 , Example7[Type] = I4 ) ) Fewer function invocations, and you only incur FILTER overhead when justified. And shorter formula :)
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders
File Reference for this video: 0214 FILTER function tricks
Cool data validation trick for displaying desired columns. I would have never thought of that. I have done validations choices based on another validation selection, but not the column thing. Thanks.
I watched almost every video tutorial on youtube about the filter function. Yours is the best! Thank you.
Why thank you, that is very kind of you to say. 😀
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. 😁
A comprehensive guide on filter function. I think you've covered everything
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.
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.
I LOVE how you show how to build the function using the answers that Excel is calculating. Very few take that perspective but it makes so much more sense... Thank You!
You’re welcome, I’m glad it was helpful. 😁
Brilliant Mark- I’ve seen all of these before but not all in the one place- great Video
Glad you enjoyed it 👍
Awesome. I've been looking for "filter" functions examples and that's the best one. Great video!!!!
Awesome, thank you!
Hi. In example 4, if we use the formula "=FILTER(array[Column], BYROW(array[Column], LAMBDA(x, AND(ISNUMBER(SEARCH(SPLITTEXT(search_value, " "),x))))))", then we can search Non-adjacent text/values/words. It's very effective. Even Excel filter search does not produce non-adjacent output.
Ridiculously easy to understand!! Outstanding as usual
Thank You 😁
one of the best videos on FILTER... thank you very much Mark! Cherian in Abu Dhabi!
Wow, thanks! 😁
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.
The best and most didatic explanation I've seen for Filter function. Thanks for sharing!
Wow, thanks! I’m glad it was helpful.
The FILTER function in Excel is incredibly powerful and helpful for organizing and analyzing data. It allows you to extract specific information based on defined criteria, making it easier to focus on relevant details without manually sorting or searching through large datasets. Whether you're working with lists, tables, or complex data sets, the FILTER function saves time by dynamically updating as the data changes. It's a must-have tool for anyone looking to streamline their workflow and improve data analysis efficiency. Thank you Excel Off The Grid!
Yes, FILTER is probably the best Excel function.
@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.
Thank you so much, straight to the point, easy to understand, learn't a few new tricks.
Thank you so much for the solution to filter based on another list, I spent 3 hours to meet you 🙂
Glad it was helpful.
Watched Best video On FILTER functions ever.Thank you for Your Contribution to our Success
Thanks - that’s so nice to hear.
The last part was awesome because I used to use offset with an embedded counta to change validation lists without blanks. The filter function seems so much easier. Thanks for the video!
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.
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.
One of the best tutorials I have seen so far on the filter function 👌
Wow! Thank you. I appreciate that.
Oh I think the slicer one with LAMBDA is going to help with what I'm trying to do at work!
That's a great method. I'm glad you like it.
Thank you for sharing these excellent techniques.
You are welcome!
This is one of the best videos on the Excel filter function I've seen. You covered techniques that will set my spreadsheets apart from others. Thank you!!!🥰
WhoA !! Absolutely Insightful and very demonstrate the power of combining functions (Filter , Search , Slicers in tandem) and making complex tasks much easier
Great video. You're really a Ninja. I learn too much from your videos. (from Brazil)
Thank you - I glad you liked it.
This is extremely helpful. Very well done! Thank you!
Glad it was helpful!
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.
Thank you Mark.Great explanation
Thank you. 😁
Much better tutorial than anyone else. Kudos ❤
Thank you - that is very kind of you to say. 😁
Your tutorials are amazing. I just discovered this channel, and I have to say this is one of the best Excel channels and I have been subscribed to a lot of them.
Could you please make a video with advanced examples of using -- (double minus) in front of arrays in various scenarios?
A lot of Excel Pros are using -- in sophisticated formulas, but I haven't come across a detailed video tutorials of its usage in an advanced formulas.
Thank you in advance.
Thanks for those kind words, I appreciate that.
Usually the double minus is used to coerce text to a number (same as *1) or to force a non-spilling function into an array (such as EOMONTH th-cam.com/video/IEfPe8Beh4E/w-d-xo.htmlsi=So7CNupVAiF5izWN)
It doesn’t have any special properties in itself.
I feel I got a lot smarter, althoug I only understood about 40% of it on the first watch. excellent video!
Watch it again... then you'll understand to 60%. Then watch it again....
After a while you'll understand 100% and they will become boring old FILTER techniques which you can't grasp why everybody doesn't understand them. 😂
Thx for great examples and summary of FILTER function. So clearly explained :))) Bravo!
Thank you. I hope you can put them to good use.
Wow perfect!
Thank you.
Another superb video Mark, I learnt a lot.
Great stuff - thanks Peter. 😁
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. 😁
Thank you for this very useful video, the way you explain is very easy to understand it.
Mark, very helpfully and very well presented
Thank you, I'm glad it was helpful!
Absolutely amazing and in depth. I learned many things, I am happy to say!
I haven't come across such a superb video which lucidly teaches super complex use of extraordinary powerful filter function.it made me very confident in handling large data for my dynamic dashboard..thank you so much❤❤❤❤
Glad it was helpful! 😁
Amazing depth Mark ❤❤❤❤
Thank you 😁
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.
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.
Thank you!
That was great, thanks. What a powerful function!
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.
Perfect 👍
Thanks 👍
Wow! super extra extremely useful! Thanks!
Glad it was helpful! 👍
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 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.
Great video!
Thanks!
Very nice video, learnt a lot from it.
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
Super video as always👍❤. Thanks Mark.
Kebin - Thank You as always. I appreciate your support.
Can you share the solution for Example 6 for older Excel version (without Lambada function). Thanks a lot.
You can try this method. But it’s not as good as the LAMBDA version:
th-cam.com/video/hOMgjI_Tlt8/w-d-xo.htmlsi=vsv5_c1CQ-KTJtfh
Excellent 🎉.. thanks for sharing 👍
Excellent content 💯
Appreciate that, thank you. 😁
Thank u sir ❤❤
Amazing!
EXCEL-lent video as always. Good job covering many different things that can be done... would add a BONUS item. what if you have two data tables. (yes i know use power query to merge them!!!!) but another way is to =Filter(Table1,Table1[S_Rep] = "Mary",Filter(Table2,Table2[S_Rep] = "Mary","Person not found")) this allows looking for a sale rep in two different tables with one formula (but yes use a cell reference, not a static Name) Just good to show that you don't just need to put text in the If not Found portion of the formula.
That's a nice idea I've not see anybody do that. You would need to know that that "Mary" wasn't in both Tables, otherwise it would not give you all the values.
@@ExcelOffTheGrid its not the best example for this, ill have to see if i can find one. too bad you can't put screenshots in comments. and im not that good at making YT vids like you guys do.
Really mind blowing do u have any playlist to beginner to pro videos , you really cool way u teaching awesome
We've got various dynamic array vides on the channel. But nothing which is beginner to pro play list. All our structured/ordered content is included in our courses.
@@ExcelOffTheGrid thanks for the reply for me byrow function not available in my version Excel is any alternative for that function
Hi Mark. Very good - but I got lost at the start. I was trying to follow along using my own data. I tried to create a named range - to match your “Example1” but this always messed up my results. Very frustrating to fall at the first hurdle. How did you create Example1? Thanks
It’s not a named range, it’s a Table.
Select a cell in the range and click Insert > Table (or Ctrl + T).
Tables are one of the most important parts of Excel, so you should definitely spend some time learning them.
I hv a excel workbook each with a table with same structure. Its a download of status on diffnt dates. I am required to calculate stat bet any two tables as selected by user using a drop down list. My question is how to pass on table references in formula. The stat should be visible side by side for selected tables.
As always nice video 🎉❤
Thank you! 😃
Excellence at work👌
How to use slicer with use column selection function combine?
You would need to create a disconnected Table - check out this video th-cam.com/video/v-vrEtCMKiI/w-d-xo.htmlsi=GEi0KKc8T169W4eF
Very informative and well done - thank you!
After thinking about it, how would you go about expressing not equals? Being able to list all that doesn't match Road?
If you're referring to the Partial Match example, just wrap NOT() around the ISNUMBER() to reverse the logic.
@@ExcelOffTheGrid ahhh, excellent - thank you!
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!
Hello, Mark. Thanks for the great video though it has a lot to learn in a so short duration. I need your help with a filter formula. I use a filter formula to build my statement of account and when I use choosecols with it to choose the columns I need to show, the if_empty part does not work. Can you help with this? Thanks in advance
Wrap the CHOOSECOLS around the array in the FILTER function, instead of the result of the FILTER function. Then the if_empty is not calculated on.
Example:
=FILTER(CHOOSECOLS(Data,{1,2,5,9}),Data[Column]=A1,"No Value")
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.
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.
so love this chanel❤
Thanks. I hope we can keep delivering quality content.
If there is single column but we need to include "Jan", "Feb", "Mar", then how we will going to apply filter to extract all months specified in a single column??
When I entered the formula for filtering with a list, I get a #VALUE! ERROR could anybody plese help?
Wouldn't the "show all items" be more easily/performantly accomplished by:
=IF( I4 = "*" , Example7 , Filter( Example7 , Example7[Type] = I4 ) )
Fewer function invocations, and you only incur FILTER overhead when justified. And shorter formula :)
Actually, I think the shortest format is just to use +(I4="*") as an additional logic check inside the include argument.
Specific Columns Function = Pivottable anywhere
Partly...yes. It does't perform any type of pivoting or aggregation, so a slightly different outcome.
why not so clear
He is very clear, improve your basics, you have a knowledge gap.
Nice. But I rather use the old fashion way.
What do you class as the old fashioned way?