I don’t use filters in Excel anymore. I use this other function instead 😎

Рет қаралды 659,051

Күн бұрын

Excel is has 100s of functions, but my absolute favourite has to be the FILTER function. It let's you filter a dataset based on conditions. This makes the job of data analysis and calculating summaries so much easier. You can create any type of complex criteria with FILTER function to simplify your job.
In this video 💡⏱👉
================
0:00 - Introduction to FILTER function with simple examples
1:41 - Understanding the spill range & FILTER output
2:46 - Printing just the names, not all data
4:07 - Count of FILTER results (using # operator)
5:02 - AND condition with FILTER
7:30 - OR condition, filtering data that meets any condition
9:42 - NOT condition with Excel FILTER function
11:53 - Implementing a complex business situation with FILTER
14:48 - What if there is an ERROR?
15:28 - Next steps
Sample file & article 📗📃👉
=======================
Download the sample data & formula workbook from here - chandoo.org/wp/wp-content/upl...
Refer to this page for a detailed article on NEW Excel functions - chandoo.org/wp/dynamic-array-...
➕More Advanced Excel functions 👉
===============================
Want to learn advanced formulas & functions with Excel? Check out below videos next.
I don't use VLOOKUP either - • I don't use VLOOKUP an...
15 Essential Formulas for Data Analysis - • These are the ONLY 15 ...
A quick overview of all Dynamic array functions in Excel - • Learn about the *NEW* ...
Advanced IF formula examples - • 10 Advanced IF formula...
Advanced SUMIFS / COUNTIFS examples - • 10 *next level* SUMIFS...
Advanced VLOOKUP tricks - • 5 *Advanced* lookup tr...
1️⃣👉 My Skillshare course on Advanced Lookups & more - skl.sh/3hbR49d
Camera crew 🙍‍♀️🎥:
================
Jo, my wife. Chan2 is not real. 🤣
#Filter #ExcelTips #Chan2

Пікірлер: 641

@patrickschardt7724 2 жыл бұрын

Humor and knowledge. The best kind of learning

@chandoo_ 2 жыл бұрын

🤣💡 I call it laugh bulb moments.

@prodig1389 Жыл бұрын

I showed this to my Boss now he is my new intern 😎😎 thanks chandoo ☺️❤️

@TheAkashchitnis 8 ай бұрын

The # is a life saver for dynamic selection. I have struggled with this in the past. Thank you for the great video.

@bin-naya1431 2 жыл бұрын

Chandooo you are EXCEL GURU I swear u are so good in formulars, best teachers in KZfaq

@chandoo_ 2 жыл бұрын

Thank you Bin 😍

@nqatha2229 Жыл бұрын

True. Chandoo has been my sensei since 2016 and I always have to check on his videos. My daughters are starting to learn from him and I feel blessed. Thanks, Mr Chandoo

@picturesmeaning2500 11 ай бұрын

@@nqatha2229 ❤

@marcusmayer1055 8 ай бұрын

Але не кращий

@lokeshagarwal6373 7 ай бұрын

Yes he is right Sir

@Acheiropoietos 14 сағат бұрын

This introduction is so funny 😂 ❤ great video too!

@m2badri 8 ай бұрын

The problems that you had provided as exercises were very tricky and it really helped me to understand the filters more deeply. Thanks a lot!

@mahendrachauda3094 7 ай бұрын

Hi, Have you used this formula for the last exercise =FILTER(sales,(sales[Sales Person]"Gunar Cockshoot")*(sales[Amount]>(AVERAGE(FILTER(sales[Amount],sales[Sales Person]="Gunar Cockshoot")))))

@vaijayantigulve4883 8 ай бұрын

I think you are by far the best teacher I ve come accross for Excel. Thank you for all the knowledge. You are doing a great job. Keep it up.

@nudger1965 2 жыл бұрын

Thank you Chandoo, A well presented and paced video covering some fairly advanced topics. 👍

@peterlarsen4809 Жыл бұрын

Thank you Chandoo. I find your way of teaching really easy to understand and also really useful for my work. I really appreciate the effort you put into your videos.

@eniale33 Жыл бұрын

Excellent video! Very helpful and love the flow of the tutorial. Makes it easy to follow and understand 👏🏼

@lilianezeli5521 2 жыл бұрын

Chandoo I hope you're having an amazing day wherever you are. I wish you many more happy moments. Your videos have really made me improve my excel skills in just a week. My own way of supporting this channel is to not skip any ads.

@thexrman 6 ай бұрын

This is an amazing tutorial; filled with detailed questions, in a simple way, with a sense of humor - love it!

@eduarchavarria Жыл бұрын

Very useful, thanks. I see many similarities between the FILTER function arguments and the array formulas for counting and summing.

@Marie-nt1in Жыл бұрын

Geez, a complete unknown formula for me! Great comprehensive and funny video! Can’t wait to be next morning to try this on my spreadsheets! Thank you for the discovery 😍

@dokudowiec01 Жыл бұрын

You just made my day, pal. I was working on Sunday to catch up with my project. Had enough, opened a beer and came across your channel. I don't think you have included this in your video, but if you reference cell, with filter formula in conditional formatting plus # it will create dynamic dropdown menu. HOW AWSOME IS THAT. I will have to redo all may workbooks! God job man!!! You have a new subscriber!!!

@chandoo_ Жыл бұрын

I always say, "don't spill your beer, just spill your data". Cheers 🍻

@jnpatil9 Жыл бұрын

I didn't get the drop down thing. Please explain.

@MultiJess88 Жыл бұрын

Thankyou Chandoo. I used to dread filtering the data and copy-pasting it onto a separate sheet. This is really helpful!

@sairakhan7959 2 жыл бұрын

Thank u chandoo for this amazing tutorial.I have made a report by using your array formulas unique and countif in ten minutes

@elphasluyuku4167 8 ай бұрын

Im so happy i came across this channel. Chandoo has me feeling unstoppable. You are the GOAT!

@tutsecret499 Жыл бұрын

Thank you very much for the data file and the exercises for me to practice. God Bless you always!

@sathyam1968 8 ай бұрын

New to learn. Thanks for sharing with good example

@vijayarjunwadkar 2 жыл бұрын

Thank you Chandoo (and of course Chan2)! All doubts about FILTER function are indeed filtered now! Keep it up! 😊👍

@chandoo_ 2 жыл бұрын

Thanks Vijay :)

@vaijayantigulve4883 8 ай бұрын

Awesome teaching. Thank you for sharing your knowledge and experience. I am so thrilled to see and practice all these features in Excel. And I do them for sheer joy. Though I did my engg in civil, I have been interested in DA. Though I have joined - in the race a bit late, I am so grateful to utubers like you who are sharing their knowledge so freely to the world. More success and power to you... keep up the good work... 👍👍

@luizklebermarinho Жыл бұрын

As ALWAYS, Chandoo being the BEST! For Excel tips and Power BI tips, Chandoo is great. Thank you very much Chandoo for sharing knowledge

@Levince36 9 ай бұрын

Thanks to your video, I also find out we can use SUMPRODUCT to sum all values in case of displaying filtered data and get the sum at the same time.

@kennyjain5695 Жыл бұрын

You are absolutely amazing Chandoo, just love your tips and tricks.

@suryasabniveesu6946 Жыл бұрын

I saw this video earlier...but practically there was no requirement. Now I got a situation to use Filter...I immediately referred this video...thank you chandoo garu...u r excel God

@shashikkantlohar1163 Жыл бұрын

Hello sir, I tried this, it's amazing. You are really awesome in excel.. and your humour is the best part of learning

@toddgriffith4851 Жыл бұрын

Very powerful. Thanks for the tips. Great video!

@paramveerssachdeva 7 ай бұрын

Can't Thank you enough for sharing so much knowledge sir.

@alfredtomiamubiaya3330 Жыл бұрын

I called this filter function demystified,coming from the master himself. Kudos and thanks for the good work as always.

@BurakBorhan 9 ай бұрын

Thanks for the great video. Is there a way to sort the output of the FILTER function while printing?

@sudhasomasundaram1509 Жыл бұрын

One of the most useful videos of my life today. Fantastic.

@pranotodj.8573 7 ай бұрын

Thanks for the tutorial

@Life_of_Poonthendral 9 ай бұрын

Thanks for sharing the wonderful tricks

@sofip5001 Жыл бұрын

This is a perfect crash course for analytic management that we had in uni. Thank you so much for being very detailed!! Hope you can cover more financial related analysis! 😊

@vishalbharakhada3357 2 ай бұрын

Awesome. Thank you for sharing

@abhilashn2993 7 ай бұрын

Your videos make uninterested excel more fun and rememeber all options you mention... As always hats off to you Chandoo...

@atiqrahman138 8 ай бұрын

This is helpful. How about duplicate values in the source file? If there are two rows of the same info with different sales volume, how do you add up?

@carlosbaptista7512 Жыл бұрын

Chandoo, thank so much for sharing and the way you make things easy to understand.

@sajithjames4692 Жыл бұрын

Love your work Chandoo, really has taught me a bit! Also want to check more on your Power BI related videos, in time

@MattP79 Жыл бұрын

I watched and read many sources explaining FILTER and nothing else clicked. This video alone enabled me to create the spreadsheet I envisaged but didn't know how to achieve and I can't thank you enough!

@chandoo_ Жыл бұрын

Wow.. that is awesome to hear. More power to you RM ⚡

@taimoor4313 2 жыл бұрын

you always add value in my contents Thanks

@nsanch0181 2 жыл бұрын

Thank you for the great Filter video. I had fun practicing with it this morning.

@chandoo_ 2 жыл бұрын

That is awesome to hear 😀

@uyenbui4301 Жыл бұрын

Excellent video! thank you so much

@maheshsanghani6380 Ай бұрын

wonderful way to teach ! Thanks !!!!

@maksms Жыл бұрын

Very well explained, thanks alot for such videos.

@waqarshaikh86 2 жыл бұрын

It was just yesterday I discovered filter function, however I had no idea about all these amazing ways this function can be used. Thanks for great video Chandoo 👍

@chandoo_ 2 жыл бұрын

Wow... that is a coincidence. 😀

@berescucristi7954 2 жыл бұрын

@@chandoo_ a nb utesseco lit

@sarfrazakhtar9887 Жыл бұрын

Please tell me i watched videos for beginners to intermediate level on this channel and here i didn't understand in last tricky formula in playlist videos aren't in sequence which one I'd watch?

@thiagovilha7570 2 жыл бұрын

If I could, I would like the video twice. This helped me a lot. Great video 👍

@chandoo_ 2 жыл бұрын

Aww.. so sweet. I can thank you twice for saying it though. Thank you. Thank you :)

@vick3379 2 ай бұрын

Thank you, Chandoo. As always, top-notch content❤

@mahendrachauda3094 7 ай бұрын

Hi Chandoo you are such a great mentor for the people who wants to learn more & more advance techniques of data analytics, I have attended so many lectures of excel, Query and Power Query but I always end up my learning with your lectures only. I have gone through your video but i am feeling difficulty in exercise Sr. no. 2 & 5 (Completed). can you please share the formula for these exercise ?

@sowmyasrinivas6259 4 ай бұрын

awesome video thanks a lot for your insights 🙌

@excelland1922 Жыл бұрын

Chandoo's Excel is easy to digest. I salute your effort sir!

@santoshgujar5237 9 ай бұрын

Thank you, Sir, 🙏🌺😇

@xxredxiiixx Жыл бұрын

Thank you really informative, learned a few things, understood it all!

@eivanlc74 Жыл бұрын

Great content. Very useful. And fun! Thanks from Peru

@pathapathipraveen9321 Жыл бұрын

Sir you tells in simply method in filter function thank you

@patrickleavydatadrivenfina1491 2 жыл бұрын

Can combine with the UNIQUE function to e.g. give a list of unique staff in months only when they are paid. Thank you for the tutorial Chandoo!

@chandoo_ 2 жыл бұрын

Great idea!

@shivarepw Жыл бұрын

Such a great video.. Thanks a lot sir.. Keep us teaching..

@Nick-ec4mr 8 ай бұрын

I'm about to make some training hitlists for work using =filter!

@TheCainak Жыл бұрын

I used to refer to ur website all the way back in 2014! what a discovery keep up the good work!

@chandoo_ Жыл бұрын

Welcome back!

@krishnaRaog21 6 ай бұрын

Very informative ...thanks

@VideoAndrega 2 жыл бұрын

Thank you for the useful hints.

@chandoo_ 2 жыл бұрын

My pleasure!

@user-qj4ru1nc8m Жыл бұрын

A Perfect Teacher, with a Great Artistic Intelligence Application

@h1tm4st3r5 9 ай бұрын

Man, i just love the way you teach excel, you should get a prize for that. Thank you!!!

@chandoo_ 9 ай бұрын

Wow, thanks!

@suryasabniveesu6946 2 жыл бұрын

I am extremely thankful for this video..not only this video..there are much more videos of you. But =counta(c5#) is always giving me result as 1

@alperkins66 Жыл бұрын

If you get the count = 1, you are referencing a spill cell, not the column filtered....also notice that COUNTA only works with a range where you are returning a single column, else it counts every cell.

@maikudiumar3060 Жыл бұрын

This is actually nice and a boost to my inspirations

@alexrosen8762 2 жыл бұрын

Amazing tutorial. Thank you!

@chandoo_ 2 жыл бұрын

You're very welcome!

@user-in3br6dw1r 3 ай бұрын

Chandoo Bhaiya, Maza aa gaya, Its Excel My Mind.......... I Love the MS Excel........

@rameshharidhass9862 2 жыл бұрын

Hi Chandoo, Your presentation is so clear. It shows your highly skill and experienced person. Thank so much for explanation🎉🎉🎉

@chandoo_ 2 жыл бұрын

You are welcome Ramesh :)

@bondnikunj Жыл бұрын

The more I learn filter, the more I appreciate pivot table. Its all so easy for pivot table.

@TrekwithIslam Жыл бұрын

Hey Chandoo Great work brother..keep sharing this knowledge with the world..wish you all the best❤️

@syedaneesdurez7197 2 жыл бұрын

Awesome . How can we filter if we have range of values like the way we are doing for single value. Normally we do the same in power query and appreciate if u can show the same in filter function .

@pegmolter3542 7 ай бұрын

Loved this lesson, Chandoo! However, I cannot figure out how to solve problem 2 on the FILTER Problems sheet. I've tried COUNTIF and COUNTIFS, but they return errors. I've read that you cannot use the MONTH function to glean the month out of the date column in the same formula with the FILTER function. Can you let me know how you resolved this, please.

@mahmoudfawzi Жыл бұрын

Fantastic, thank you for the great video :)

@internet_wanderer8316 9 ай бұрын

Thanks 😊

@bdwoods08 2 жыл бұрын

Thank you for sharing this!

@chandoo_ 2 жыл бұрын

My pleasure!

@dataisfun4964 2 жыл бұрын

Thanks Chandoo, This was exactly what i was looking for tried on a sample data and it worked. =FILTER(A2:C12,(A2:A12="Anambra")*(B2>=50000)*(B2

@chandoo_ 2 жыл бұрын

That is awesome :)

@amanmalalo8653 2 жыл бұрын

Great tips! Thanks for sharing.

@chandoo_ 2 жыл бұрын

You are so welcome!

@amanmalalo8653 2 жыл бұрын

@@chandoo_ Thanks.

@sanjayaais8495 Жыл бұрын

Thanks Chandoo for sharing a very helpful video. Small question ... could you please let us know how to filter two non-adjacent columns? Thnks, Sanjaya

@youhavemail8463 Жыл бұрын

: )) you put a smile on my face as well as feed my brain cells. Thanks :)

@bskrmusic3932 Жыл бұрын

Awesome tricks Anna !

@rakeshmahakud110 2 жыл бұрын

Awesome explanation

@thebibidu 8 ай бұрын

Hey Chandoo, very useful info. I cannot find info about the dynamic sum with the #. My excel is set up in french, but I can't find info on Microsoft pages either... ! Where to look for this?

@DinoDelight Жыл бұрын

Great video, never knew of this function

@ravitassayag8868 2 жыл бұрын

Thanks a lot. Tou are great 👍 What if I need to filter this table in 1 or more criteria but I want the results shown specific columns that are not ajesent?

@martyc5674 2 жыл бұрын

Great video- well explained. Another operator worth exploring is the “-“ minus operator, where you only want 1 condition satisfied.

@chandoo_ 2 жыл бұрын

Great tip! That is brilliant. I don't think it will work beyond two conditions though.

@martyc5674 2 жыл бұрын

Chandoo yeah I’d guess not - but I haven’t tried it, it’s not one you would use often. Filter is such an amazing function, filter wrapped in filter or index can do some pretty awesome stuff!!

@VideoAndrega 2 жыл бұрын

Since False = 0 and True = 1, the results of your operations should end-up giving you either value. Thus, with „-„ if you meet the second condition and not the first, you shall seek an absolute amount from your test.

@r.igormortis149 Жыл бұрын

@@VideoAndrega Usually False is coded as 0 and true is coded as not false = -1. That beeing said: You should never ever use arithmetic functions on boll values. This is bad practice for a number of reasons, first and foremost this: No one garantees that integer representation of true and false is stable. The operations you want are AND(), OR() and NOT() in Excel. However: These functions can not be used for the filter function. This is where Microsoft really dropped the ball

@VideoAndrega Жыл бұрын

@@r.igormortis149 Thank you for your explation. I will consider your valuable advice in my practice. Best Regards.

@testingbeta7169 Жыл бұрын

in filters i found what i was looking for, :D thanks

@ysaleung Жыл бұрын

THANK YOU

@kaustubhpujari Жыл бұрын

Thanks Chandoo Bhai. You make learning excel a Fun.

@wayneedmondson1065 2 жыл бұрын

Thanks Chandoo. Great tips! FILTER is an awesome function :)) Thumbs up!!

@chandoo_ 2 жыл бұрын

Glad you think so!

@dheer4u130 Жыл бұрын

Chandu bhai you are clear, to the point tutor. 🙏👍👍

@aaronmoonga3669 Жыл бұрын

Genious video, perfect help in a click. Thank you so much

@chandoo_ Жыл бұрын

Glad it helped!

@learningwithmaliha 2 ай бұрын

Thanks for the amazing video.

@norbertpie9724 Жыл бұрын

I have been laughing my socks off. And very informative as well. Thx Chandoo😂

@kunalgoel2559 Жыл бұрын

Superb. ❣️❣️

@learnexcelwithexcelexperts1313 Жыл бұрын

very well explained ...

@atnafunega2206 Жыл бұрын

Really interesting video with clear explanation. But the problem is my office 2019 pro, window for doesn't recognize "filter" and other option functions. is there any solution? Thank you

@gerrythomas6297 11 ай бұрын

Awesome, thanks!

@MrsCathT 2 жыл бұрын

OMG, this is simply awesome, I really should have watched this video when it came out! Thank you Chandoo for sharing, your videos; website and templates always give you the desire to continue to learn more! :-) Thanks again.

@chandoo_ 2 жыл бұрын

You are so welcome!

@kareemelkafi7845 6 ай бұрын

The Best video that Explain Filter Function .. the other great thing is the practice problem that you put in filter problem .. watch and test your self 😍❤ Realy you are a great Chandoo ❤❤