HomeОбразованиеRelated VideosMore From: Danny Rocks

How to Use Advanced Filters in Excel

1009 ratings | 698058 views
In addition to the built-in AutoFilters in Excel, you can create Advanced Filters that allow you to extract unique records or calculate criteria based on external data. I do not use Advanced Filters as much as I did in the past because Excel 2007 greatly improved the built-in AutoFilters. However, you will pick up at least two good tips from this lesson - including how to extract Unique Records and copy them to a new worksheet. I invite you to visit my online shopping website - http://shop.thecompanyrocks.com - to see all of my resources and explore the many products and services that I offer. Danny Rocks The Company Rocks
Html code for embedding videos on your blog
Text Comments (134)
zohaib javed (1 month ago)
Askari Aziz (3 months ago)
Very nice Danny
Neags (4 months ago)
That last part, what is the benefit of doing that vs the remove duplicate feature (apart from maybe an extra step in having to copy the entire column)?
Maz H (5 months ago)
Really excellent tutorial. Thanks very much. You've made my day a lot easier!
Naseerkhankhel (8 months ago)
vishwanath math (10 months ago)
Hi, What do we do if we have a column with % values with both -ve and +ve values ? how do we filter on percentage values ?
Nurture Tech (8 months ago)
Hi, No issue ,you can directly apply filter on the data (Ctrl+Shift+L). Click on filter tab then go to number filter and you can select any one of the heading and can apply filter. Its simple as you apply filter normally. For More Information Visit: https://www.nurturetechacademy.in/
Bhavesh Padhiyar (1 year ago)
Very Good .
Rahul Agrawal (1 year ago)
pivot table can also be used for this
Bhavesh Padhiyar (1 year ago)
Alexander Nejadeh (1 year ago)
This video is quite helpful
Saurabh Sharma (1 year ago)
Amazing video to learn Advanced Filtering in excel, I specially liked the "Late Payments"" formula and how it was used in this example. I primarily use VBA to control excel, and this video has given a huge number of ideas to expand further on.
TheChirag99999 (1 year ago)
This video is extremely good and helpful, Analysis of data, stupendous task is getting lot easier! Thank You!
Daniel Rodrigues (1 year ago)
Thanks man! that tip to use the headers really helped! Good didactic.
Kahisawheel (1 year ago)
Thank you!
claude77573 (1 year ago)
I am confused about the formula.  If you just used a comparison of G9 to F9  (such as =IF((G9>F9,TRUE,FASE), and if G9 was blank, the formula would still return false.  So, (1) Why is it necessary to use ISBANK, and (2) Do I understand correctly that the values being listed are the false evaluation of the ISBLANK function?  If so, why force the value_if_true evaluation to be FALSE?  In other words, if ISBLANK is false, the values are returned, but if ISBLANK is true, it is also forced to false?  I am not following this.  Please explain.
khush mgia (1 year ago)
Danny truly "Rocks"
Tirth Patel (1 year ago)
May i have a copy of this excel sheet please ?
marco cabiles (2 years ago)
Flex - xie (2 years ago)
That helpped alot Thanks
Samuel Moreno (2 years ago)
si lo q dise luis
Millionaire Winnerman (1 year ago)
Quantala mela!
this video is very bad
An Ly (2 years ago)
Thank you. This is an extremely helpful tutorial.
Erico Seseko (2 years ago)
How do you extract Computer Services, Inc. from the data. Can you use advanced filter
Rafael Fu (2 years ago)
James Lam (2 years ago)
now I realize Pivot table is a more time effective tool for data filtering
Mir Shahin Rashid (2 years ago)
Saboor Momand (2 years ago)
Really helpful THANK YOU
Joyce Price (2 years ago)
Great Video, made me go from frustrated to feeling like I just might be able to learn! Thanks!
Dr Russell (2 years ago)
Many thanks for this, I will be using this for GTD action lists
Dan Cacovean (2 years ago)
Thomas Murphy (2 years ago)
Great video ! Thanks
adnan hashi (2 years ago)
oh i think you're explain  very well but still i didnt try yet
Great video! Thanks!
Udaya Sankar (2 years ago)
Thank you Danny!
HTTPCore (3 years ago)
Helped us a lot, thanks!
salmodesign (3 years ago)
Glenda Caamic (3 years ago)
hello good day im fane. i have a business which started 2 months ago. im having hard time finding solution to how can i identify the past due accounts and sum it up with the certain collectors. Because its a daily collection when i print its not automatically computed how will i do it. please its help me.. thank you and more power.
Aegis Sales & Service (3 years ago)
Great tutorial on Advanced Filters. Do you one (or could you do one) on filter certain columns only. ie: I have 9 headers and for my filter I require the first 5 headers (columns) and last 1 header (column) missing headings 6,7,8. All filtered data is being moved to another worksheet. Thanks
Mohamed Alshafaey (3 years ago)
Thank you
James Hood (3 years ago)
Very good lesson - Very professional - Well presented
Jimmy Guru (3 years ago)
Thank you very much, I found this very helpful.
Chandra Sekar (4 years ago)
I Received lots of mail how to store excel 
Relaeh (4 years ago)
Thanks matey, saved me probably a days worth of redundant work! 
ivo Pollazi (4 years ago)
Danny truly "Rocks"  thankssss
Randy Austin (4 years ago)
Thank you very much for uploading. Informative & insightful
Laimonas Daugirdas (4 years ago)
Thank you. Very helpful
KeremBJK96 (4 years ago)
thanks a bunch for the vid! liked and subscribed.
Craig Hatmaker (4 years ago)
Nice job.
Josiah Marcano (4 years ago)
good vid helped a lot Stay Frosty!!!!
Modime41 (4 years ago)
Greetings Danny Rocks. i need help in the Advance filter actually i already did my advance filter and i signed a macro for it but my data contain hyperlinks and when i filter my data the hyperlinks don't work any more!! what i missed??
Great Video! Thanks!
swapna padhee (4 years ago)
very nice
VBB (4 years ago)
Thank u
Danny Rocks (4 years ago)
My pleasure! I am happy that I could help you with my Excel Tutorial. Danny Rocks The Company Rocks
CEU 24-7 (4 years ago)
I need a mailing list sorted by counties and I have tried several ways to do this but it scrambles the data. The counties are in a-z format but the name,address etc is no longer correct. Help
Sor But (4 years ago)
Thanks for uploading this kind of excel formula
Ricks AAS (5 years ago)
Hello Mr. Rocks. I have viewed some of your videos on excel, NICE JOB! you know a lot about excel. I haven't seen all your clips but I did read your titles and I have yet to see a "How To" video on creating a spread sheet that can figure out percentage on liquid. e.g. I service asphalt so I have to figure out how many sq ft is the parking lot, once I figure out the sq ft of the lot, I then have to figure out how many gallons of sealer I need to seal coat the asphalt. for instance the lot is 10,000 sqft so I need 100 total gallons of sealer. I am allowed to cut the sealer with water (17% cut) so that means I only purchase 83 gallons of sealer not 100 gallons. Here is what I want to do. name the headings in excel "Size of Parking" "Total Gallons Needed w/water" "Gallons to Purchase w/out Water" "Cost for Gallons Needed" so when I type in the total sq ft under "Size of parking lot" I want all the rest of the fields to automatically calculate. P.s. the cost of sealer is $2.82. per gallon with tax. I need to know the formula to execute this figuration. Can you help me? 
panteneHola (5 years ago)
Really helpful!!!
Danny Rocks (4 years ago)
My pleasure. I am very happy that my Excel Tutorial helped you. Danny Rocks The Company Rocks
HellRyder18 (5 years ago)
Finally!! I couldn't find anywhere saying that the headers had to be the same and that the criteria must be BELOW the Range list. Thanks!!
Danny Rocks (4 years ago)
Yes, I agree. Most tutorials - written and on video do not stress this "gotcha" step. Honestly, it took me at least two years to realize how important this step is. I am pleased that you found my Excel Tutorial helpful. Danny Rocks The Company Rocks
sinto sadanandan (5 years ago)
Hi Mr.Danny Rocks  Thankyou very much for the for the Tutorial .It is really helpful appriciated
Adeel (2 years ago)
may i have this sample excel file
Danny Rocks (4 years ago)
My pleasure. I am happy that I could help you to understand this concept in MS Excel. Danny Rocks The Company Rocks
Nour Eldin (5 years ago)
thank you very much i've learnt alot from you and still im learning really thank you
Danny Rocks (5 years ago)
Hi Mallory - I apologize for the delay in responding to your question. Short answer, If you create - and name - your data range as a "Table" ( Excel 2007 and higher) or as a "List" (Excel 2003) the calculations should automatically update! Danny Rocks The Company Rocks
Danny Rocks (5 years ago)
Hello June - Thank You! My mottos is, "I work hard to make it easy for you to learn how to get the most our of Excel!" Frankly, I have to sift through 5 - 10 learning resources to finally get to that "I get it!" point. Hopefully, I have saved you and my viewers this time and effort! Thanks for adding your feedback! Danny Rocks The Company Rocks
june allen (5 years ago)
You make Excel 2010 seem easy. Thanks
Mallory Salas (5 years ago)
Is there a way that if you add data after the fact that it can auto populate in the other sheet... for example the companies, if you added a new company to your list, the second sheet would automatically add that new company?
Danny Rocks (5 years ago)
Thank you for your kind words. I agree - visual learning is an excellent way to "grasp" complex topics. I appreciate you taking the time to add your comment. Danny Rocks The Company Rocks
seventyfive1 (5 years ago)
Thank you for the video! A picture is worth a thousand words and it would have taken a lot of reading to understand this concept; however, you made it easy to understand.
Beth Risley (5 years ago)
When I try to reference lines on sheet 2, from the advanced filtered list, they are referenced like A37, A71, A115, A132, A149, A305, etc. I can't figure out how to get my filtered list from Sheet2 to Sheet1, because they don't take on new line numbers. There are 368 lines in the original list, after the advanced filter there will be less than 40, but I can't figure out how to get them to the table on Sheet 1, does that make sense?
Beth Risley (5 years ago)
um....you responded in about an hour! I'd say that's pretty good! ha. I know how to do the dropdown but can't figure out how to set up the criteria for the Advanced Filter and then use Data Validation - Pick from list part.
Beth Risley (5 years ago)
Hi, great video! It's SIMILAR to what I need. I have a multicolumn employee list on Sheet 2 that is generated by a link to a SQL database. On Sheet 1, I need a dropdown list generated from DISTINCT values from one of the columns (a person's Department). When the user selects the department in the dropdown, I need to generate a separate list of employee names who are in the selected department. I need the generated list to go in column E, row 20 on Sheet1. Can you help me? THANKS!
Kirk Buchanan (6 years ago)
Visit thebizmind.webs.com/download-c­enter for practical examples in expert level excel
Danny Rocks (6 years ago)
Thank you for the compliment. I am pleased that you enjoyed my Excel tutorial. I appreciate you adding your feedback. Danny Rocks The Company Rocks
simz (6 years ago)
better than my it teacher thanks alot
Danny Rocks (6 years ago)
My pleasure! Thank you for your kind words. In my experience, I find that it normally takes multiple repetitions in order to acquire a new skill. I learn best by "doing." So, I re-create exercises in Excel and repeat the steps from the book or video "over and over again." Then, I "teach" this new skill to a colleague. If they can learn this new skill from my teaching, then I know that I have "mastered" the skill. Danny Rocks The Company Rocks
Danny Rocks (6 years ago)
Hello - I apologize for the long delay in responding. I, somehow, missed the notification of your message. Re: Drop Down List. The easiest way to create a Drop Down List of Values is to use Data Validation - Pick from List. I have created several YouTube Videos demonstrating how to do this. You can then set up the criteria for your Advanced Filter and then use Data Validation - Pick from List for the cell value for your Projects. Danny Rocks The Company Rocks
Danny Rocks (6 years ago)
Thank you for the compliment! Yes, I blessed to be born with a great speaking voice. In a much earlier career, I worked - as a musician - on Broadway Musical Productions. This is where I learned how to improve the quality of my vocal delivery. Re: The "voice" for the movie trailers that you mentioned. Last week, I worked back-stage at a major convention. In the "show run" notes they reference VOG (Voice of God) for the announcements prior to a speaker entering the stage! Danny Rocks
Celio Ávila (6 years ago)
epic voice.. you are like those movie trailer voices. "From the creators of..." makes the lesson much more interesting
Danny Rocks (6 years ago)
Great! Thanks for passing this along! All best wishes, Danny Rocks The Company Rocks
Jawed Kakar (6 years ago)
thanks for the fast replay, i used auto macro when there is a change in the sheet and if the cell is filled then run the advanced filter of your and it works great.
Danny Rocks (6 years ago)
I am pleased that you found my tutorial helpful. Here are my suggesstions: 1) Beginning with Excel 2007, Natural Language Date Filters give you great flexibility. You can use them with a standard range of Excel data or convert this range into an Excel Table. 2) Use a Pivot Table - again, beginning with Excel 2007, you can take advantage of these great "Natural Language" Filters for Dates, Text & Numbers. I have created several YouTube Videos that demonstrate Natural Language Filters. Danny
Jawed Kakar (6 years ago)
Hey @Danny Rocks, i find this very useful and i wanted to ask a question. i want to do the same as what you did in this video except i want to do it like advanced auto example: if i put in invoice Date > 5-jan-2010 then i click on the advanced filter and give the criteria rage and it will show recored after 5 jan 2010 but is it possible to give the output automatically with out pressing the advanced filter button every time? if i type in invoice Date = > 5-jan-2010 it would auto change my table
Danny Rocks (6 years ago)
I suspect that the number that you calculated has been either "rounded up" or "rounded down" automatically. A good function to use is =EXACT() which return either TRUE or FALSE when comparing the values in two cells. You can control how Excel "rounds" calculations by using either the =ROUNDUP() or the = ROUNDDOWN() Functions. Danny Rocks The Company Rocks
clarence d (6 years ago)
Hello, I have a problem and here it is, I'm running excel 2008 for mac and when I manually put a number in a cell and try to compare it to another number in a different cell that was the result of 2 calculated numbers but equals that same number, the if statement and the comparision statement =xx=xx return false. Why is this and how can I correct it? Thanks C
Danny Rocks (6 years ago)
Thank you for adding your feedback. I greatly appreciate it. And, I thank you for your kind words. The quality - and caring - of your teacher makes such a huge difference. I remember having a great teacher in my Sophomore Year in High School and I excelled - 2nd highest grade score in my class. Same subject - different teacher - and I nearly "flunked" the class in my Junior Year! Thanks again for adding your comments! Danny Rocks The Company Rocks
Allison Scanlon (6 years ago)
Thank you so much! I had a horrible professor this semester, if he could only record these you-tube videos or explain it like this in class! This got me a great grade on my last project
Danny Rocks (6 years ago)
Since I do not know how - or how many times - you sorted your data, it is difficult to give you an answer. If you have been recently working with your data set - and have not saved it - I suggest using the Ctrl + Z (Undo) Keyboard shortcut. You can undo multiple actions by repeating this keyboard shortcut. Re: Filters. If you are using Excel 2007 or Excel 2010, use the keyboard shortcut Ctrl + Shift + L to clear all filters and remove filter arrows. Danny Rocks The Company Rocks
Onn Cheng (6 years ago)
HI, may i know how to remove sort data and restored back original data? TQ
Danny Rocks (6 years ago)
Hi - Sorry for the delay in responding - somehow I missed the notification of your comment. To get the SUM() of the sales using criteria you have several choices: 1) Use the DSUM() Function - this is a Database Function and the setup is quite similar to Advanced Filters. I have created several YouTube Videos on this topic. 2) Use the SUMIF() Function to sum only the values that match a single criterion. 3) Use the SUMIFS() Function when you need to refer to several Criteria. Danny Rocks
Danny Rocks (6 years ago)
Thank you! I am pleased that you enjoyed my tutorial. In my experience, it takes a few repetitions to get used to the Advanced Filter Commands. The key is understanding how to construct the Criteria for your Advanced Filter. Danny Rocks The Company Rocks
PineappleCrusher21 (6 years ago)
great tutorial! hopefully this is exactly what I need to filter out custom data sets.
Fili Rojas (6 years ago)
Danny, How do you get the summary of sales from the Invoice?
Danny Rocks (6 years ago)
Thanks for adding your comments. I am pleased that you enjoyed my tutorial. There are so many ways to filter a data set. It is fun to experiment with different options - regardless of the version of Excel that you are using. Danny Rocks The Company Rocks
Dhruv Dua (6 years ago)
Loved the video. And that's so true about the advanced filters being replaced by the default excel filters - there are some pretty cool inbuilt filters which sort of makes the advanced filters almost redundant. Thanks for uploading!
Danny Rocks (6 years ago)
My pleasure! I am delighted that I could help you with this Excel tutorial. Thank you for adding your feedback! Danny Rocks The Company Rocks
Danilo Cabanilla (6 years ago)
THANK YOU VERY MUCH...it really helps.
Danny Rocks (7 years ago)
@pagalahati Thank you for adding your comment. I am so pleased that I could help you with this tutorial. Danny Rocks The Company Rocks
Danny Rocks (7 years ago)
Thani you for adding your comment. I am pleased that my tutorial helped you to learn how to used Advanced Filters in Excel. Danny Rocks The Company Rocks
Birendra Chaudhary (7 years ago)
it helped me a lot,thanx
sri cherry (7 years ago)
@DannyRocksExcels hi.. i would appreciate if you can solve my questions could You plz tell me how to create a drop down list cell in such a way when we select a project name it should show only the data of the project selected as the way it shows in pivot table reporting list
Danny Rocks (7 years ago)
@fromRomaniaWithLove1 I apologize for the delay in responding - I did not see this message until today. In this case you would use OR Logic so that EITHER Criteria (NY OR NJ) are met. Simply, place NY on one line and NJ on the next line. Sincerely, Danny Rocks The Company Rocks.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.