Advanced Filter in Excel - Explained with Easy Examples

Excel Advanced Filter is one of the most underrated and under-utilized features that I have come across.

If you work with Excel, I am sure you have used (or at least heard about the regular Excel filter). It quickly filters a data set based on selection, specified text, number or other such criteria.

In this video, I will show you some cool stuff you can do using the Excel advanced filter.

This video on Excel Advanced Filter covers how to:
- To extract a list of unique items
- To filter records using multiple AND criteria - such as sales rep name and geography.
- To filter records using multiple OR criteria - such as sales rep is Joe OR John
- To filter records using wildcard characters - filter records with sales rep name beginning with J
- To use numerical conditions - filter records with sales more than 20000

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[dragonbeast]

bro's out there saving lives, thanks

[djstone]

Thank you. I recommend using the =UNIQUE function for single and multiple criteria to very quickly provided the data is in contiguous columns for multiple criteria. Very quick way to get unique lists. If you need to establish uniqueness for noncontiguous fields (e.g. Col A, Col C, Col E) I'll quickly concatenate the data in the respective cells [ =A2&C2&E2 or if spaces are of a concern, add in the TRIM function to remove space characters at the start or end =TRIM(A2)&TRIM(C2)&TRIM(E2) ] and the UNIQUE function will give you what you need referencing only the concatenated column for reference. I live in Filtered lists for analysis but resort to the Advanced Filter using the AND criteria (same Criteria row) and the OR criteria (subsequent rows in Criteria) when needed. Very helpful.

[mahamohan]

Use of Advanced filter in Excel would be a great tool to look for information in a large database. Clear and crisp explanation

[gopalakrishnarao]

Excellent very very useful tip/trick. Thanks on behalf of all the XL user community for taking pains and trouble in uploading these type of useful videos. A proud Indian indeed.

[jpny]

I subscribed only recently and have to say that the videos I’ve seen so far were excellent. Not only the content, but also the quality of the recording — you speak really clearly and put in some effort to slow down a little I think, just to make sure you are understood. Please continue doing that, it works. I am a beginner Excel user and while many people think of it as financial or numbers tool, it is not just that. It is excellent for data work, data manipulation, even database. Not sure if you made any videos about that, but if you have not, it may be a good idea to show some uses of Excel that are non-traditional, not financial. Maybe even using Excel to retrieve data from some API and parse it, etc.

[IbrahimKhalil-mskf]

Alhamdulillah, Your all vedio is nice & very to understand all kind of person. Lot of thanks brothers. Really unique teaching.

[hbdfnny]

REALLY GREAT VIDEO MAN, thank you so much

[johnhollis]

Hello, Great Video. I have a question, if I may? How would you pull selected columns of information from the first table into a second table? So from your example, could you create a table showing date and sales columns only rather than date, sales rep, geography and sales?

[wayneedmondson]

Hi Sumit.. thanks for the video and tips on using Advanced Filter.. great stuff! Thumbs up!

[hmsh]

Really thank you for sharing this info with us, we are really great full

[TVSCDN]

Thanks 😘.. can you please show how to analyze a downloaded Excel sheet into different account heads indicating the debits or credit amounts for each unique account head

[shockjockey]

This is a really well structured video, thanks

[mehaboobsinsy]

Thank you so much my Dear brother before I don't no how to is the advance filter when I look in video I learned the advanced filter good explanation

[nicolaflowers]

Hello,

Question - If I update the information in the first data range, will it automatically update on to the copied ranges?

[sudhasomasundaram]

Is it possible to use advanced filter to only copy certain columns from the range? For example, I have data from A to Z, but I only want to copy E, K, N, O, Q based on certain criteria for each of these. Since these are non-adjacent cells, I am having trouble using a range name

[barisuyelik]

hi, i am working on these days a file which includes approximetaly 20000 rows and 15 column. and i want to use advanced filter but this process takes so much time to finish like hours.is there anyway to make it shorter?

[bhupalsamalla]

Sumit Sir.. thanks for ur help and i appreciate u but my bad luck is u r in Vizag...

[MohAboAbdo]

Excellent Video, Thanks ...Thanks ...Thanks .

[Deepakdas-gjyv]

Hi, thank you for such informational video, really helpful but at 3:21sec of video you chose to filter out unique values to eradicate repetitions, just for correction I saw John India was repeated even after you applied filter rather than there all things were unique. Thank you once again for all your videos which helps a lot .

[binayakarmacharya]

on unique record of sales rep, why there is John in first and last row. Should not this be one record only?