Excel FILTER Function Explained (7 Examples) | Filter and Extract Data Easily
In this video, I will show you what is the new FILTER function in Excel (using 7 examples).
Get Office 365 - microsoft.msafflnk.net/Vk9OR
Note that the FILTER function is only available in Office 365 (Home, Personal and University edition) at the time of recording this video. It will likely be made available to all Office 365 users.
Download the FILTER Function Examples file: bit.ly/2UMoqRF
The new Excel FILTER function allows you to quickly filter the dataset based on one or multiple conditions and then extract this data separately.
The following FILTER function examples are covered in this video:
1. Filter Data Based on Text Condition
2. Filter Data Based on Numeric Condition
3. Filter Data with Multiple Criteria (AND)
4. Filter Data with Multiple Criteria (OR)
5. Filter Data To Get Above/Below Average Records
6. Filter Only the EVEN/ODD Number Records
7. Sort the Filtered the Data With Formula
You can read more about this here: trumpexcel.com/filter-function/
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear/Tools I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: kzread.info...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Пікірлер: 148
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.
@taiebulislam6442
3 жыл бұрын
Hi submit, Your insights are precise, specific, fruitful, logical and meaningful.
@schooloflawandjurisprudenc6918
3 жыл бұрын
hope we can see how did you create the drop down for the options of category
@blessedsone3723
8 ай бұрын
Hello Sumit Bansal, you are a great trainer. I wish you could make a video on creating a drop-down list in Excel that behaves as follows: the list displayed items are from non-adjacent columns, and each row has a different list item. I could send you and email of my Excel sheet to help you understand what I am talking about better.
Great Tutorial - Takes you from basic to complex tasks in a matter of minutes and with surprising simplicity. Thanks Sumit
Thank you, this was a nicely paced thorough explanation that will stimulate more ideas how to use it.
Excellent teaching sir
Wow! The new FILTER() and SORT() functions are amazing! Thank you for this very clear and concise introduction to these powerful features in Excel!
@trumpexcel
4 жыл бұрын
Thanks for commenting. Yes, these new functions make a lot of stuff really easy. I remember doing the same thing that FILTER does with helper columns and a combination of formulas. Now it's so easy
@Nikhil_Kumar1
2 жыл бұрын
@@trumpexcel Hey Sumit, What if the "Sales"column is in the 25th column of dataset, Is there any other way apart form " Columns Function" to get the Sales Columns.? Need Dynamic.
Very concise and helpful, thank you very much!
Excellent explanation Sumit...keep this rolling...God bless you
@trumpexcel
4 жыл бұрын
Thanks for commenting... Glad you found this useful!
As always your videos are so well presented and explained. Thankyou!
Thank you, I enjoyed this video and will be able to apply this in my daily work.
That helped me with my finding multiple matches in a list of about 11,500 entries using a search. I'm really glad I was able to sign up for the O365 pilot test or this would not have worked! Loved this video and information and I gave it 3 thumbs up...meaning I gave it a thumbs up, then came back later and gave it another thumbs up which cancelled the first, so I gave you another thumbs up. :0) I work with several MASSIVE spreadsheets and this is going to be so useful.
SUMIT you are one of the best in this area. appreciate your hard work.
Really good explanation, clear and precise, Thank you very much
This is EXACTLY what I needed! :D Thank you!
This is really awesome. Thank you!
thank you so much for your info, very interesting and helpful
Excellent. Thanks for sharing.
Clearly presented. Thank you.
Thank you so much. Amazing contents
Thank you for the examples they're very useful and they are explainned very clean
Top !! Très très bonne vidéo merci !!
Really helpful tips. I appreciate your effort. Thank you so much.
Thank you so mauch for this excellent video
Excellent!!! Super Useful 👍👍
@trumpexcel
Жыл бұрын
Glad you found the video useful :)
Thank you for this very helpful video. I did use the multi-condition and the sort part and was great. Can you please add another video where you can bring a limited number of rows from the main data? That would be great! (i.e the output of the filter function has 200 rows but we need the first 50) Also, I did another addition to that by bringing only the column that you need from a wide dataset. =filter(filter(array,include),{1,1,1,1,0,0,0,0,0,1,0,1,0}) so for my sample, the dataset has 13 columns and 1 stands for the columns that we need to bring in the final data and 0 for the ones that we don't need! Keep going!
Great presentation on this function. Thank you. It definitely will be useful.
@trumpexcel
4 жыл бұрын
Thanks for commenting Don... Glad you found the video useful!
Very helpful video, explanation also easy to understand, thank you 👍
Found useful. Thank you.
Very nice explanation
Great and really useful examples
Thank you so much!
If i would get help for this FILTER function from Excel help it would only shows the basic help, but you gave so many type of filter using method. It is SUPERB ..... NEED MORE EXCEL TIPS..
@trumpexcel
4 жыл бұрын
Thanks for commenting.. Glad you found this video useful.. More Excel tips coming in the following weeks (make sure you're subscribed to the channel so you don't miss any) :)
Thank you very much for this great video, for this valuable information, for this extensive explanation, and for this great technique.
@trumpexcel
4 жыл бұрын
Thanks for commenting... Glad you found the video useful!
Excellent explanation !!! Thank you!!!
@trumpexcel
3 жыл бұрын
Glad you found the video useful!
very helpful video thank you
Many thanks Sir
Thank u so much for this educational tutorial.......
@trumpexcel
4 жыл бұрын
Thanks for commenting Mahmodul... Glad you found the video useful!
Thank you sir, very nice
Thanks a lot Respect Sir
Well explained
Hi Sumit.. excellent.. great examples of using the new FILTER function. Thanks for sharing them. Thumbs up for Trump Excel!
Excellent explanation by eliciting cause and how that formulae is above is solve.
Great Job !
maizing.. thanks
VERY GOOD TUTOR AM SUSCRIBING RIGHT AWAY
Great job dear
Well done Trump, there is something I like ton confirm from you Sir
Amazing man
Tysm!
Great and unique
Very good !
@trumpexcel
4 жыл бұрын
Glad you found the video useful!
Thanks
Deserves one like
Great video. Thanks for sharing. A quick question - is there a way we can get columns headings as well depending on the columns choosen, whilst using filter formula? Thanks again.
very good sir
This is amazing. Is there any way to extract the data from the filtered table? I want to copy the information in the table but the cells are all empty so I can't. I know I can use pivot tables but my team isn't Excel savvy and this is would be much simpler.
Lifesaving god dude
Is there a way to filter a “ word” with in a sentence based on volume? What would be the formula for that? If yes, could you make a video on that please? Love how you explain things...thank you!
Thank you very much, very good video, a question about how I can use the filter function to select only visible cells. I told you that assigning an ordinal number to a column of data, but I can't find how to select only the filtered cells through some function.
This video is very useful. Can I use it on larger data. Is it helpful
Hi, great vid but i need additional help, my original data have different fill color and i need that color to be seen also on filter results but somehow i do not see it. i filtered my data on other sheet (i tried applying rules on 2nd sheet but it is not working, i think catch is in filter formula that i wrote, filter is working but on filtered values i do not have original fill color but it is blank(white)) need help pls THNX
Does FILTER function allow rearranging the resulting columns? That is, have results appear Region, Name, and Sales instead of Name, Region, and Sales. Thanks!
If you want to have a NOT or Exclude condition, eg all Sales over 10000 but not in the US, do you use the "-" operator?
Nice
Thanks alot for ur valuable effort, Please tell me how can I add the Filter function to my excel 2019 ??
Can the filter function be used in a separate workbook to another workbook
Any way to filter from another workbook that is on my server? The workbook on my server updates info every 30 minutes so I would like to access that data from another workbook. Basically have one of my employees enter the criteria on one sheet and it pull that data from the workbook on the server.
Sir, pls guide me if I hav more than 10 criteria. Can we provide range as criteria???
Well done Trump, there is something I like to confirm from you Sir. I am a Sales person and I use Excel App to handle my Customers Order, my customers wants me to give them exactly as they ordered but I filtered based on my Price list. So I do I go about this Sir
Hello Sumit, I found a solution for my problem from one of the forums which is compatible to 365. Since I am using Excel 2019 i don't have the filter option. The solution I got contains Filter in the formula. Can you help me to edit the formula and make it compatible to excel 2019.?
hi sir! how I can add this function in Excel 2016 with VBA =FILTER(array, include, [if_empty])
Can we use and, or function instead of specify * and + respectively
How will you use filter function, if you only want one of the two conditions not both like XOR Function? Please let me know, Thanks in advance
Please guide me how to select data from multi sheets with same workbook by using filter function.
It is very helpful video. Can we use it in excel 2007.
@trumpexcel
4 жыл бұрын
No you can't. This function is only available with Excel in Microsoft 365
Hi, good morning... How to use filter only 1 or 2 column values only?
If I want to see just middle or last column except for the first column, how can I do it?
TrumpExcel, the video is very helpful. Is it possible to use the filter function to extract data from multiple worksheet in the same workbook? If so how?
In 08:16 of the vide in the example of how to use the OR function in the formula although in the example >10000 is in the formula the results in line 7 has a value less than 10000 (actually is 5306). is there a problem with the syntax?
@abeljacobi4221
3 жыл бұрын
Only one condition needs to be true with OR, so the row with sales value 5306 was output because the other condition was true (Region = "US").
I wanted to use this in my course work to show credit scores for customers with a credit score of 600 or more, is this possible please?
I need help on this I want to filter data from multiple worksheet using the formula but giving error. =FILTER('1-NOV-21:30-NOV-21'!A8:A56,'30-NOV-21'!L8:L56=D6,"")
Sir, I have installed office 365 in my laptop recently, but could not get this =Filter formula in my excel. Please guide me, tks
hi ... i am having ms 2019 excel and the FILTER function is not displaying rather it is showing FILTERXML function. how to FILTER function? Please assist
Hi, Can we also choose only a select few columns in our output based on this function, instead of fetching the entire row? Say for example, I want to fetch only the Name and Region, or Region and Sales or Name and Sales columns only. Is that possible? Thanks.
@rohitprabu3431
Жыл бұрын
You got reply
Can I use this function in horizontally in excel?
in 8:11 min, the formula is: =FILTRO(A2:C11,(B2:B11="US")*(C2:C11>=100005)), * e no +. signal + only field equal ( some colun ).
Nice video. Getting "function isn't valid" error for a Filter function. Can anybody sort this out?
Does anyone knows how to search entire workbook and return all records which match a certain value from a drop down list with or without a Macro? e.g. if we have a list of many large organization in a worksheet and have a list of all of their employee, first name, last name, phone, email, title,..... ( many per organization) in another worksheet. How one can see the records of all employees in an organization by selecting the organization's name from a list or drop down box, etc. I would appreciate if your insights.
hello sir I have knowledge about to basic Excel and I want to increase the knowledge of excel in advance level. So Sir guide me how to increase the knowledge of excel step by step. I am confuse how to start advance excel. What is the best thing to learn in the beginning?
Very good Please usually your link Of work book i can't able to open
@trumpexcel
4 жыл бұрын
Try this link - www.dropbox.com/s/v2r8zqcfmpwbvn9/Filter%20Function%20Examples.xlsx?dl=0
Hello Sumit, Thanks for the explanation, is it possible to filter with 3 criteria using * operator, for example filter sales greater than 10000, and by region to include Asia and US only, I have tried it but I get the #CALC! error, any idea how i can come around this ?
@ankursharma6157
4 жыл бұрын
Hi Bayar, It is Possible to FILTER with 3 Criteria. Important: if Asia and US are in same Column, then You are required to use +, as Sumit shared in the Video. =FILTER(A2:C11,((B2:B11="Asia")+(B2:B11="US"))*(C2:C11>10000)) Hope, this helps. For any further support, please contact Me on goodnessshallprevail@gmail.com. I will be Glad to support. Thank You!
@bayarahmed2436
4 жыл бұрын
@@ankursharma6157 thank you ! I got the CALC error because i did not use the double brackets. thanks again, it works perfectly
If I have an Excel 365 workbook with new functions like "Filter",....... does this workbook work/function on Excel 2019 as well?
@trumpexcel
3 жыл бұрын
FILTER function isn't available in any version other than 365 :/
Sir Filter function not showing ms 2019 Pls help
Bro can't filtered more than 40 rows in using filter formula How to find that issue bro
Puneet Bro excellent.But not available in window 7 that we use
@trumpexcel
4 жыл бұрын
Thanks for commenting Kirandeep.. This function will be available for Office 365 only. BTW, the name is Sumit :)
@kirandeepchoudhry9232
4 жыл бұрын
@@trumpexcel Sorry for name
@trumpexcel
4 жыл бұрын
@@kirandeepchoudhry9232 No worries :)
I did this and returned some correct values but there are values that returned ########## values on some
Separate data from one column to another column on basis of data pattern
👍
Sir, Download link to practice file is not working. I am using Office 365. Please suggest.
@trumpexcel
3 жыл бұрын
Just checked... working for me. Try this again - bit.ly/2UMoqRF
Filter write not come my excel = please give me function name
Subtotal filter