I've never seen someone explore the filter function in depth! Your explanation is brilliant. I recognized your voice and then I saw your name, you teach on another channel! Thank you very much!
Thank you so much for the in-depth coverage of the FILTER function. You’ve got a new sub! The new feature of data validation automatically picking up the unique list of values is new to me and is indeed a very useful feature.
Dear Deb, To filter non-adjacent columns it is easier to use the CHOOSECOLS function: =FILTER(CHOOSECOLS(Results,1,2,5),(Results[Exam]=H5)*(Results[Month]=H9),"No Records") 🤗
@@JoseAntonioMorato hello, yes that’s mentioned in the video. You can use CHOOSECOLS if you have M365 only. It’s not available in any other version. FILTER is slightly more widely available.
Es hat sich für mich absolut rentiert ein weiteres Video zum Thema Filter-Funktion anzuschauen. Du erklärst sehr gut und strukturiert, alle möglichen Aspekte werden beschrieben. Vielen Dank und ich freue mich als neuer Abonnent auf Deine weiteren Beiträge.
Great video! Filter has replaced most of the other functions I use. Definitely my #1 function! For the part Non - Adjacent Columns, an other way is to use the CHOOSECOLUMN function or create 3 separate filters for each column..
Thanks for the comment. Yes you could replace the first FILTER with CHOOSECOLS, if you have Excel 365. The function isn’t available in any other version :)
Selecting columns with a nested filter shows a nifty trick (the embedded array). However, you can only display the columns in the relative order of the original source data. Sometimes that's fine, but using CHOOSECOLS is much more versatile, allowing you to specify which columns to return and in what order. But then, that's not demonstrating the use of FILTER as a highly versatile/adaptable function.
Thank you for your reply! Yes, you are right but how can I count specific rows in column D without adjusting its filter, while filters are applied in columns A, B, and C? Column D shows two different row counts unless I filter it manually. I know this isn't a private tutorial, but this would be useful for anyone analysing data. Also, I want to commend you on the excellent courses you’ve provided online. I try to watch them all when I can.
Hi! Great video! Although when I'm following it along, in the bit of the "One or another but never both", my results are different. I get people working in London with a profit greater than 40k in my filtered results. Same formula you used.
@@weiwang2000 hello, yes I mention that in the video. There’s an annotation that says you can use CHOOSECOLS. Need to remember that CHOOSECOLS is only available in Excel 365. FILTER is more widely available :)
Hi, I have a question about creating batch folders in file explorer using excel. Are there any way to prevent the system from auto arrange base on alphabet (for example I have 4 names: d a c b, now I want to create batch folders in the exact same order d-a-c-b not a-b-c-d). Thanks
Dear Madam/All, I am working on a table and need to find the total number of rows in a column using a function, such as SUBTOTAL, SUMIF or SUMPRODUCT with the filter applied. However, the functions only work when the filter is not in use. When I apply filters to different columns, the results don’t update accordingly. How can I get the total number of rows with the filter applied? I would greatly appreciate any help or advice on how to get the total number of rows with the filter applied. Thank you!
@@AntoF1 hello, you need to use SUBTOTAL with COUNT or COUNTA. In a cell somewhere type =SUBTOTAL(3,A1:A10) replace my cell refs with a column in the filtered table.
We have been changing the links to go through to my website. I’ve just tested it and it downloads the correct file for me. The link should be to cellmates website not Google drive. If you see Google drive, please refresh your browser.
I've never seen someone explore the filter function in depth! Your explanation is brilliant. I recognized your voice and then I saw your name, you teach on another channel! Thank you very much!
@@Luciano_mp amazing! Thank you so much for the brilliant feedback! Yes that’s me on ‘the other’ channel :) thank you for watching - appreciate it!
Thank you so much for the in-depth coverage of the FILTER function. You’ve got a new sub! The new feature of data validation automatically picking up the unique list of values is new to me and is indeed a very useful feature.
You’re welcome and thank you for subscribing!
excellent tutorial 😊
🙏
Thx you for your awesome work and simple but effective explanation. Keep following and recommending to other members
Thank you so much! I really appreciate that 😊
Very nice update for unique values in drop-down.
I know right? No more messing around creating a unique list first!
Dear Deb,
To filter non-adjacent columns it is easier to use the CHOOSECOLS function:
=FILTER(CHOOSECOLS(Results,1,2,5),(Results[Exam]=H5)*(Results[Month]=H9),"No Records") 🤗
@@JoseAntonioMorato hello, yes that’s mentioned in the video. You can use CHOOSECOLS if you have M365 only. It’s not available in any other version. FILTER is slightly more widely available.
FANTASTIC. This is by far the best video I have watched on the "FILTER" function, and I have watched a number of them. Thank you so much, madam.
@@MichaelBrown-lw9kz you’re welcome! And thank you so much for your kind comments :)
Es hat sich für mich absolut rentiert ein weiteres Video zum Thema Filter-Funktion anzuschauen. Du erklärst sehr gut und strukturiert, alle möglichen Aspekte werden beschrieben. Vielen Dank und ich freue mich als neuer Abonnent auf Deine weiteren Beiträge.
Great video! Filter has replaced most of the other functions I use. Definitely my #1 function!
For the part Non - Adjacent Columns, an other way is to use the CHOOSECOLUMN function or create 3 separate filters for each column..
Thanks for the comment. Yes you could replace the first FILTER with CHOOSECOLS, if you have Excel 365. The function isn’t available in any other version :)
What versions include FILTER that DON'T include CHOOSECOLS? I didn't know there were any unless it's 🤮 Mac.
@@ExcelTL Excel 2021.
@@ExcelTL TIP: If you google the function and go to the MS site, it says the versions the function is available on at the top.
@@cellmatestraining Why would anybody use Excel 2021? I guess price, but not worth losing out on really useful features in 365.
Amazing explanation, thank you very much.
Regarding selecting columns I would add the options to use the CHOOSE function or the CHOOSECOL function.
@@Ezra-b1w yes I mentioned that in the video :)
Brilliant, only found your channel today, fantastic content, comprehensive and so easy to follow.
@@clivegamble379 amazing! Thank you and welcome! Appreciate the follow! :)
8:34 what you call curly brackets are just brackets or curved brackets () . Curly brackets are {}.
Ah, you're right! Just a slip of the tongue, thanks for pointing it out though. Hope you enjoyed the video! 🙂
😂 I even zoomed in to make sure those were in fact NOT curly brackets.
@@cellmatestraining yes, always keen to learn more excel
Such a good function!
So good!!
Brilliant, thanks.
You’re welcome! :)
I had no idea of this filter with operations option! Excellent new tip & presentation!
A lot of people don’t! 😊
Crystal clear
@@adnanozkara3576 great!!
I thought I knew quite a bit about the FILTER function but seems every day is a school day! Thank you for the video. New subscriber
Awesome! Thank you and welcome!
Selecting columns with a nested filter shows a nifty trick (the embedded array). However, you can only display the columns in the relative order of the original source data. Sometimes that's fine, but using CHOOSECOLS is much more versatile, allowing you to specify which columns to return and in what order. But then, that's not demonstrating the use of FILTER as a highly versatile/adaptable function.
I agree! The vid is focused on FILTER but CHOOSECOLS is a better option if you have 365! Anything older and you don’t have the function!
great video, thanks :)
You’re welcome! Thank you for watching :)
Wow...Thak you!
You’re welcome!
Thank you for your reply! Yes, you are right but how can I count specific rows in column D without adjusting its filter, while filters are applied in columns A, B, and C? Column D shows two different row counts unless I filter it manually. I know this isn't a private tutorial, but this would be useful for anyone analysing data. Also, I want to commend you on the excellent courses you’ve provided online. I try to watch them all when I can.
@@AntoF1 have you tried AGGREGATE with the ignore hidden rows option selected?
Hi! Great video! Although when I'm following it along, in the bit of the "One or another but never both", my results are different. I get people working in London with a profit greater than 40k in my filtered results. Same formula you used.
@@felge1977 have you used the correct operator? Should be 40000 :)
Thank you thank you so much for this video
I'm so glad you got value from it!
"choosecols" may be used to output non-adjacent columns in the filtered array.
@@weiwang2000 hello, yes I mention that in the video. There’s an annotation that says you can use CHOOSECOLS. Need to remember that CHOOSECOLS is only available in Excel 365. FILTER is more widely available :)
Hi, I have a question about creating batch folders in file explorer using excel. Are there any way to prevent the system from auto arrange base on alphabet (for example I have 4 names: d a c b, now I want to create batch folders in the exact same order d-a-c-b not a-b-c-d). Thanks
Quite useful for academic purposes. If I want a total of e.g.English how do I do ?
@@deepakadroja6065 you would need the SUMIF or SUMIFS function.
@@cellmatestrainingThanks for quick response 🙏🪴🙏incorporate with Filter function?
Thanks
You’re welcome!
Dear Madam/All, I am working on a table and need to find the total number of rows in a column using a function, such as SUBTOTAL, SUMIF or SUMPRODUCT with the filter applied. However, the functions only work when the filter is not in use. When I apply filters to different columns, the results don’t update accordingly. How can I get the total number of rows with the filter applied? I would greatly appreciate any help or advice on how to get the total number of rows with the filter applied. Thank you!
@@AntoF1 hello, you need to use SUBTOTAL with COUNT or COUNTA. In a cell somewhere type =SUBTOTAL(3,A1:A10) replace my cell refs with a column in the filtered table.
Hi , example file is not same with video file. Could you update it again ?
We have been changing the links to go through to my website. I’ve just tested it and it downloads the correct file for me. The link should be to cellmates website not Google drive. If you see Google drive, please refresh your browser.
This is confusing - I don't understand/see the use of curly brackets?
@@jeniceprovost3322 I said curly bracket instead of bracket. Follow what you see on screens