Hello Kenji, Greetings to you. I have a request for you, if you can please show the formula to get TOP 5 from a raw database.(count and sum) Thank you in anticipation.
thank you so much for sharing your knowledge..i'm amazed!btw,i'm just confused why sometimes there are 2 or 3 close parenthesis?but most of the times it's just 1 close parenthesis at the end of the formula. please enlighten me...thanks in advance!
Thank you for such a detailed and clear tutorial. I have my Master data on a secondary worksheet and am searching on my main worksheet. my question is, am I able to delete from the results or connect a button in some way to Identify where the singular result is coming from on my secondary sheet to delete that entire row ???
Could you please provide guidance on how to edit data within Excel filtered results? I'm interested in learning methods to manipulate and update data while working with filtered views in Excel. Any assistance or instructions you can offer would be greatly appreciated. Thank you!
Love the videos as always Kenji! Some other approaches I wanted to share: for 3 you could also use the drop function to remove the first column and for 4 you could use averageifs or sumifs although that would no longer be in a filter video 😅
Thanks for the video. I have improve it with the help of ChatGPT, to filter by a dinamic colection of values (countries for example). In Excel in Spanish would be: =FILTRAR(B3:F32; ESNUMERO(COINCIDIR(D3:D32; I2:INDIRECTO("I"&CONTARA(I:I)+1); 0)); "Sin resultados")
Thanks for this video.This is really helpful. I would like to ask how many condition can add in filter function? I think this is better than countif or sumif function. But can we do filter function for three or more condition like in countifs or sumifs? Thanks
Hello Respected Sir 🙏 I am your new subscriber. You are a great teacher that I am from India and I understand very easily your style of teaching. Please add Power BI lessons and Chat GPT in excel. Thankyou ❤️
Hi Kenji, Thank you in advance; your tutorial video is amazing. But why doesn't it work on the filter formula on my laptop? The formula is exactly the same as yours. I really hope you answer my question 🙏
Hi Kenji, Do you know how to do a filter similar to the choosecol, but that it will (group or ungroup/hide or unhide) the columns you don't want to see?
Good day... The problem with filter if the cell formats like date or amount with ", " are lost... What's the solution for that one??? Thank you in advance
Is there alternative to CHOOSECOLS?? When using this function is a large set of data, and trying to get data from only few columns, counting cols no is difficult
Excellent video as always Kenji! I still have a tendency to format as table and just do pivotting, so this was great for me :) As someone working in finance though, I have to admit, it sticks out when your number formatting in the the input is good, but the output is bad :D Devil is in the details right?
I dont have some features/functions in my excel. eg.choosecols, vstack. I have updated my excel 2021 to the latest version yet dont have these features. Are they exclusive to a different excel version?
Why not convert range to table and reference table columns? It is dynamic and adjusts as you add or remove columns. I see so many Excel tutorial videos where the array is selected and it drives me crazy 🤪
Its because most people aren't familiar with tables so if you're already teaching them something new (the fitler function) then teaching them 2 new things at once can be even more confusing
Does any one know how to include more than one country in the filter condition? Like for example i want to filter all the records which are of the country India or United states
Hi, Kenji. This is a problem I cannot solve with my filter formula. It is a multiple criteria filter with choosecols. My problem is when choosecols is used the "if_empty" doesn't work; when removed, "if_empty" works. here is my formula =CHOOSECOLS(FILTER(ORDERS!$B$4:$Q$319,(ORDERS!$D$4:$D$319>=G14)*(ORDERS!$G$4:$G$319="Yes")*(ORDERS!$D$4:$D$319
What if you’re trying to keep a KPI dashboard looking neat and tidy, and don’t know how many rows will be returned? Can it be combined with a function to keep it contained like in this example? ua-cam.com/video/nnTaJULIpEM/v-deo.html
👉 Take our Excel course: www.careerprinciples.com/courses/excel-for-business-finance
Hello Kenji,
Greetings to you.
I have a request for you, if you can please show the formula to get TOP 5 from a raw database.(count and sum)
Thank you in anticipation.
Thanks!
Thank you for your support! 🙏
You are amazing. I hope you see this. You are helping me I knew nothing about Excel and now I have my first Data Strategist job!!!!
I love your clear explanations and the casual drops of keyboard shortcuts. I get to learn how things work AND get free tips & tricks!
thank you so much for sharing your knowledge..i'm amazed!btw,i'm just confused why sometimes there are 2 or 3 close parenthesis?but most of the times it's just 1 close parenthesis at the end of the formula. please enlighten me...thanks in advance!
Thank you for such a detailed and clear tutorial. I have my Master data on a secondary worksheet and am searching on my main worksheet. my question is, am I able to delete from the results or connect a button in some way to Identify where the singular result is coming from on my secondary sheet to delete that entire row ???
Could you please provide guidance on how to edit data within Excel filtered results? I'm interested in learning methods to manipulate and update data while working with filtered views in Excel. Any assistance or instructions you can offer would be greatly appreciated. Thank you!
Love the videos as always Kenji! Some other approaches I wanted to share: for 3 you could also use the drop function to remove the first column and for 4 you could use averageifs or sumifs although that would no longer be in a filter video 😅
Thanks for the video. I have improve it with the help of ChatGPT, to filter by a dinamic colection of values (countries for example). In Excel in Spanish would be: =FILTRAR(B3:F32; ESNUMERO(COINCIDIR(D3:D32; I2:INDIRECTO("I"&CONTARA(I:I)+1); 0)); "Sin resultados")
Awesome stuff! Im trying to get the choosecols to work on the last example. Not getting it quite right.
Great sir.. thank you so much for this to the point training... your style, substance and moderate speed are impressive.
Thanks for this video.This is really helpful.
I would like to ask how many condition can add in filter function?
I think this is better than countif or sumif function. But can we do filter function for three or more condition like in countifs or sumifs?
Thanks
Hello Respected Sir 🙏
I am your new subscriber. You are a great teacher that I am from India and I understand very easily your style of teaching. Please add Power BI lessons and Chat GPT in excel. Thankyou ❤️
Thanks Kenji. I like your bonus section. This is what I have been looking for!
Thank you very much Kenji. This is some great stuff being shared here.
Nice to see you back here again
Hi Kenji,
Thank you in advance; your tutorial video is amazing.
But why doesn't it work on the filter formula on my laptop? The formula is exactly the same as yours.
I really hope you answer my question 🙏
what to do if I want to get two countries data, multiple criteria in sale column?
Can we use a “Table Name “ with the Filter Function rather than “Range “. Eg “Table 1” rather than “ A1 :Z100 “. ?
The bonus one was the only one I was not aware of. Thanks!
Thank you so much, your videos are crystal clear and informative and very nice edits.
Hi Kenji, Do you know how to do a filter similar to the choosecol, but that it will (group or ungroup/hide or unhide) the columns you don't want to see?
Thanks buddy - Very useful learned 3 & 4
does filter function has only to specific version of MS Office? mine is 2019 but no filter function.
same here
Very inspiring. Thank you very much 🙂
thank you for watching!
hello sir, please advise how to add borders (dynamically) in the result of filter.
Love your content and easy to follow as you break it down that even a noob like me can follow
Subscribed! Thank you for sharing such useful information and techniques.
Great explanation - easy to follow - Thank you.
Good day... The problem with filter if the cell formats like date or amount with ", " are lost... What's the solution for that one??? Thank you in advance
Is there alternative to CHOOSECOLS?? When using this function is a large set of data, and trying to get data from only few columns, counting cols no is difficult
The bonus is what I needed.
Thank you very much!
You are such a wonderful teacher.
Absolutely brilliant trainer!
Very Helpfull and super easy to understand.
are slicers also considered as filters?
Can we use filter function for sum of two criteria without writing it seperate conditions.
Excellent video as always Kenji! I still have a tendency to format as table and just do pivotting, so this was great for me :)
As someone working in finance though, I have to admit, it sticks out when your number formatting in the the input is good, but the output is bad :D
Devil is in the details right?
Haha that's a great point! Thank you for sharing your thoughts :)
your videos are so helpful, thank you!!
Such a great function!! and explanation!!
Thanks Kenji. What version of excel is this? Excel doesnt seem to recognise the filter function
Hi Kenji good day can you help me on how to create a formula if this is the scenario....
15% of gross sales or 10,000.00 whichever is higher.
Thanks Kenji, really appreciated your tools
Is it possible to filter for two countries in the same column? I could not seem to get it to work.
Yes, noramlise to Country A & B in that column, use filter to filter off using the same key word country A & B
@@fei7954 Thank you for the explanation.
for the last one does the search with the wildcard * work?
I was gonna try exactly that. Something like ::
*=FILTER( **_data.range_** , **_company.name.range_** = "*family*" , "No match found!")*
I tried it. NOPE, IT DOESN'T WORK! 😞
I dont have some features/functions in my excel. eg.choosecols, vstack. I have updated my excel 2021 to the latest version yet dont have these features. Are they exclusive to a different excel version?
Superb Kenji ! It's really Good
How can I use the filter function to mirror all that into another sheet?
How does this work with a growing table where entries are added regularly
thank u so much, great explanation.
can we use VBA for all these stuff?
Valuable lesson!
Great video, Kenji! I've really appreciated your explanation for the filter function on a not so long video!
Thank your for the lovely comment!
Kenji has been my best teacher
Can pls give in version of xls this functions are available
How can I find a specific word in column then filter it, please
Can you make a dynamic dropdown in here
assume you want to have multiple selections for example we want the records both country is france and India.how to do it using this formula
simply great
but i can not do calculations based on the filtered date range
like sumiff
How can I filter the table without a certain value?
Why not convert range to table and reference table columns? It is dynamic and adjusts as you add or remove columns. I see so many Excel tutorial videos where the array is selected and it drives me crazy 🤪
Its because most people aren't familiar with tables so if you're already teaching them something new (the fitler function) then teaching them 2 new things at once can be even more confusing
@@KenjiExplains valid point 🤔
I still think it's better if formatted as table too. On the long run the learners will appreciate more
I didn’t quite follow the 2nd one when you say you put a “+” sign for or. It is only working for the industry but United States 🇺🇸 remains static
I must be a weirdo who thinks level 3 was more complex than level 4. Ended up using level 3. Worked great!
What are the possibile reasons that it doesnt extract an entire table of values that meet the criteria for me? Instead it just shows one row
I want to know how to add a date, month, and year to this filter formula
You are Amazing! THanks!
Happy to help!
awasome, thanks
THank you!
Choosecols is not available in my excel
Thank you Kenji!
Thank you
Awesome video.. Thanks 🙏
Glad you liked it!
Great video 👍🏾
Does any one know how to include more than one country in the filter condition? Like for example i want to filter all the records which are of the country India or United states
Mr. kenji I became your fan dude
How to filter from a list of values in a range like Country IN (Austalia, Canada).... something like this.
There's no FILTER function in my excel. When I type FILTER the only function that comes up is FILTERXML. What should I do?
if =filter is not recognize?
Adv filter is also good
Thanks Kenji
Sir, i am from Bangladeshi, i have fans for you. I need a complete data analytics vedio step by step, thanks
Interesting!
Glad you think so!
Wow So amazing
Hi, Kenji. This is a problem I cannot solve with my filter formula. It is a multiple criteria filter with choosecols. My problem is when choosecols is used the "if_empty" doesn't work; when removed, "if_empty" works. here is my formula =CHOOSECOLS(FILTER(ORDERS!$B$4:$Q$319,(ORDERS!$D$4:$D$319>=G14)*(ORDERS!$G$4:$G$319="Yes")*(ORDERS!$D$4:$D$319
Thanjq😮boss
Very good, but you could have used wildcards instead of SEARCH, and selected each column followed by a comma instead of CHOOSECOLS.
Thanks! Have you tried to wildcard? It doesn't work in this situation unfortunately
How to use these formulas in Google sheets
Done subscribe.
Use tables
What if you’re trying to keep a KPI dashboard looking neat and tidy, and don’t know how many rows will be returned? Can it be combined with a function to keep it contained like in this example? ua-cam.com/video/nnTaJULIpEM/v-deo.html
Love your videos, great to learn from. You just go too fast. 😂
👍👍👍👍👍
I am only getting single results? When i use search formula 😅
❤
Like always😂
Haha thank you for watching always :)
@KenjiExplains "Your uploading time and my UA-cam scrolling always seem to match somehow."
Why does the thumbnail say to stop using the filter function when this entire video is about using the filter function?
*Filter tool not filter function. I think you misread that
Good examples, thank you.
Great video!