These are the ONLY 15 functions you need to know in Excel (to get most things done)

Рет қаралды 265,711

Күн бұрын

Learn the most important Excel functions and how to use them for business data analysis.
Formulas & Techniques covered in this video:
====================================
SUMIFS
COUNTIFS
AVERAGEIFS
XLOOKUP
MAXIFS
INDEX + MATCH
FILTER
MAX
LARGE
~
How and when to use each formula
Working with wildcards & patterns
Using date ranges in your criteria
Multiple criteria with formulas
How to combine formulas to solve a problem
Download the Sample Workbooks:
============================
✅ Completed Workbook: chandoo.org/wp/wp-content/upl...
🟩 Blank Workbook (to practice): chandoo.org/wp/wp-content/upl...
⏱Topics in the video:👇
===================
0:00 - Introduction to the data & 15 Formulas
1:20 - Q1 Total Downloads of SugarRush App (SUMIFS)
3:17 - Q2 Total downloads for C apps (wildcards)
5:04 - Q3 Uninstalls in December 2021 (Using Dates)
7:00 - Q4 Downloads in Oct, Nov, Dec 2021 (Date Ranges)
9:21 - Q5 How many times we had more than 8000 downloads per app month? (COUNTIFS)
10:52 - Q6 5 star rating % for Sugar Rush (Complex Problem)
12:31 - Q7 Average uninstalls for Subway Wafers (AVERAGEIFS)
13:10 - Q8 Which month has highest uninstall ratio? (MAX & XLOOKUP)
16:23 - Q9 Average monthly downloads (AVERAGEIFS)
17:06 - Q10 Most downloads in any month / app? 2nd and 3rd places also? (MAX, LARGE)
18:18 - Q11 Which month and app was that? (XLOOKUP, INDEX+MATCH)
20:02 - Q12 In October 2021, which app has most 1star ratings? (XLOOKUP vs. FILTER)
23:35 - Q13 Does it change by December 2021? (XLOOKUP vs. FILTER)
24:50 - Q14 What are the best download figures for each app? (MAXIFS)
26:00 - What is the least downloaded app in each month? (FILTER)
28:03 - How to learn more?
👋 ALTERNATIVE FORMULAS FOR OLDER EXCEL VERSIONS:
==================================================
XLOOKUP alternative = Use INDEX MATCH.
Instead of =XLOOKUP(F61,F53:F59,C53:C59)
use = INDEX(C53:C59, MATCH(F61, F53:F59,0))
MAXIFS alternative = Use MAX(IF()) Array formula.
Instead of =MAXIFS(data[Downloads],data[App],C99)
Use =MAX(IF(data[App] = C99, data[Downloads])) and press CTRL+Shift+Enter to get the correct result.
NEXT STEPS FOR YOU 👌
====================
💥💥💥
Do you want to learn how & when to use various Excel formulas, how they compare with other ways of analysing data in Excel? Consider going for Excel School program. In this online class, I teach how to use Excel and how to do your work better. Visit chandoo.org/wp/excel-school-p... for full info & to enrol.
💥💥💥
Additional videos on the formulas:
===========================
IF formula & Advanced IF formulas - • 10 Advanced IF formula...
SUMIFS formula (10 examples) - • 10 *next level* SUMIFS...
INDEX MATCH in detail - • Excel's INDEX + MATCH ...
XLOOKUP - • STOP using INDEX MATCH...
VLOOKUP - • Excel VLOOKUP - Compl...
FILTER - • I don’t use filters in...
~
Don't forget to be AWESOME 😎
#Excel #DataAnalysis

Пікірлер: 296

@utubeAgape 2 жыл бұрын

Really appreciate this video, using common analysis questions and then walking through how you would get the answers. This is teaching method is one of the best ways to learn - thank you!

@chandoo_ 2 жыл бұрын

Thanks Irene 😀

@shujashakir9952 Жыл бұрын

The claim that knowing these 15 function in excel is enough to get most your analysis work done is true. In fact, It wouldn't be incorrect to say that this one video was enough for me to understand the entire logic on which Excel works. After watching it, I did not have to look any further. Thanks Chandoo bhaiyya for this. You may not be aware of the miracle you created here!

@harismehmood12345 Жыл бұрын

I hope this comment was not paid

@Vikas.Verma06 Жыл бұрын

🙏

@nitanjyotiradityamishranit6415 11 ай бұрын

@@harismehmood12345why u think so

@hstppt 2 жыл бұрын

I am so happy I found your channel. You're a great teacher. I no longer feel overwhelmed by the prospect of learning data analysis in Excel!

@chandoo_ 2 жыл бұрын

Welcome aboard!

@michelemontano8923 Жыл бұрын

I learn so quickly after watching your videos, especially this one. Your real work examples and explanations make all the difference. Love this video. Look forward to seeing more. Thanks

@largeroomnolight Жыл бұрын

Thank you so much!!! 🥰 You’re the best. 🌟🌟🌟 I’ve been working on this spreadsheet all night then I found your video and now I was able to use these formulas to make everything work. You’re a blessing 🙏🏾

@snowwhite2146 Жыл бұрын

Many thanks Chandoo. You're a brilliant teacher!

@legacygk2811 Жыл бұрын

Thank you so much! Your commitment and charisma are second to none.

@BRUNOABG Жыл бұрын

Even if you're very familiar with all the functions presented here, there's always the chance of getting a alternative way of doing things that you weren't familiar with. Good content.

@kennethgray5447 Жыл бұрын

You are the BEST, Chandoo! Thanks from Georgia, USA.

@chunksssstv 2 жыл бұрын

Thank you Chandoo for providing us such resources for our learning. Really appreciate your efforts

@yashmittal4744 2 жыл бұрын

Just loved the video Chandoo. Would love it if you do more of these.

@sujitsengupta8398 7 күн бұрын

Excellent! Judicious selection of 15 most useful functions! Intelligent demonstration!

@alperkins66 Жыл бұрын

Truly a great course and teacher!

@jeanlambert3875 2 жыл бұрын

great video, thanks @Chandoo for posting it showing some of the most useful functions that are available only in Excel 365 !

@sarikadatta3706 2 жыл бұрын

THANKS A LOT Chandoo you don't know how much you're helping the newbies as well as the experienced. Many blessings your way

@chandoo_ 2 жыл бұрын

You are welcome Sarika :) My mission is to help a million people every month. I am glad I have your blessings on my journey 🙏

@MonAlde 3 ай бұрын

I enjoyed following this exercise. Thank you man!

@nipunshetty9640 Жыл бұрын

Great Teacher and Inspiration for Many People Sir👌👌👌

@theh1ve 2 жыл бұрын

Once again wonderfully thought out examples, and excellently presented. Thank you

@chandoo_ 2 жыл бұрын

Glad you enjoyed it Adam 😀

@anjalipandey3730 Жыл бұрын

Thank you for such contents, it really helped me brush my concepts and real life application in business use cases.

@sukhomoyb Жыл бұрын

Valuable Tips ... Thanks a lot Chandoo ...

@saaneroa5024 2 ай бұрын

Great video Chandoo. Love your videos.

@peternolder8145 2 жыл бұрын

Great stuff Chandoo, good to see you on KZfaq :)

@livestrong1992 Жыл бұрын

Number 14 is answered like number 15. Love the channel, thank you!!

@osmonddsilva1577 7 ай бұрын

Excellent Presentation and Examples! Thanks. 😃😃😃

@karimallahwala7022 2 жыл бұрын

Thank you so much for sharing your great Knowledge

@harshshah9006 11 ай бұрын

Wowww!!! Superb revision of these formulas and their application . Thanks Chandoo Sir !!

@kashifbacha4679 10 ай бұрын

Thank you so much Chandoo Sir, for arranging such type of learning video.

@Amr-Ibrahim-AI 2 жыл бұрын

Thank you for the very useful tutorials. I'm an advanced Excel user but I learned some new techniques here. Much appreciated Chandoo!

@chandoo_ 2 жыл бұрын

You're very welcome Amr.. :)

@espoirk.kouevi6212 7 ай бұрын

Sumifs, countifs, xlookup, averageifs, maxifs, large,index match. Thank you very much.

@sportshungers Жыл бұрын

It is the best channel for learning data analysis!

@HuongNguyen-wm8nb 2 жыл бұрын

Thank you boss man!! Love the content

@nidhumanoj1552 Жыл бұрын

Thanks Chandoo for this tutorial.. It is a great learning for me....

@HagaagAccounting 2 жыл бұрын

These kind videos in your youtube is awesome and i am becoming awesome In Excel 😂❤

@chandoo_ 2 жыл бұрын

You are welcome Casmi :)

@songexplain Жыл бұрын

This channel is the best channel for learn excel, power bi, and all things ❤️

@behzadghah 10 ай бұрын

It was a very good exercise, thank you, Chandoo🙏

@andriim3661 Жыл бұрын

Really great! Many thanks - it is really helpful

@kareemahmed5839 Жыл бұрын

U are legend chandoo ❤️ Respect from Egypt Thanks 🙏

@aiasaiascon3894 Жыл бұрын

Really appreciate this video!!!! so cool!!!!

@roadsailor79 Ай бұрын

The coolest teacher under the stars. Thank you sir!

@daviddiazsanchez7292 Жыл бұрын

Chandoo you are Awesome!!

@DataAnalystprep Ай бұрын

Very much useful,thanks.

@jimberglund6838 2 жыл бұрын

Excellent exercise - a great way to reinforce the way these formulas work.

@chandoo_ 2 жыл бұрын

Glad you liked it!

@prachisharma799 2 жыл бұрын

Really helpful videos and great explanation.. Thank you🙏

@H99x2 Жыл бұрын

The way you explain these topics make it super easy to follow. Subscribed for more videos like these. Thanks bhai!

@ashutoshbafna6617 Жыл бұрын

I really appreciate this video, it was very helpful. It requires in daily life. Thanks a lot.

@jeffreypalma4561 8 ай бұрын

Excellent. Thanks a lot!

@jeffreymethusala30 Жыл бұрын

Chandoo, you make me a happy learner

@PhelixJangu Жыл бұрын

That was a wonderful video tutorial. It has really helped me a lot, thanks.

@wayneedmondson1065 2 жыл бұрын

Hi Chandoo. Great lookup problem and function workout! Very useful to work real examples to learn and reinforce methods and concepts. Many thanks for the sample file and demo / challenge! Thumbs up!!

@chandoo_ 2 жыл бұрын

Glad you enjoyed it Wayne... 😎

@parusharamudugunaganti2658 Жыл бұрын

Excellent Chandoo sir...Very helpful

@Azhar_Khan383 2 жыл бұрын

This is a great & very informative video, your teaching style is simply amazing. Thank You Sir for keeping us up to date in excel & making our job is simple.

@chandoo_ 2 жыл бұрын

You are most welcome Azhar. My mission is "To make YOU Awesome"

@sanuyadav-ys3fb Жыл бұрын

Thanks a lot Sir!

@kavitanagpal8863 2 жыл бұрын

These videos are awesome, I love watching your excel post they helped me a lot. Thank you.

@chandoo_ 2 жыл бұрын

Great to hear!

@PK-cj1pg 4 ай бұрын

Very useful insights (secrets). Thanks

@m.e.chishti786 Жыл бұрын

What a transformation, excellent work Sir G 🙂Hats Off..

@chrism9037 2 жыл бұрын

This was great, thanks Chandoo!

@chandoo_ 2 жыл бұрын

You are welcome Chris 😀

@ahmadhasnain6381 Жыл бұрын

You are amazing, with no excessive bullshit just to the point and useful tricks that are easily understandable.

@richardplester Жыл бұрын

Professional level training here, good luck with the channel!

@shashankks2454 2 жыл бұрын

Helped Me lot Sir...I was searchimg for Excel Excersices..I found It You are GEM of a Person Sir

@chandoo_ 2 жыл бұрын

Thanks Shashank :)

@dan.6398 4 ай бұрын

Great Thank you !

@rajkumarpalle8217 Жыл бұрын

Thank you for sharing nice video.

@nweisi Жыл бұрын

One of the best Microsoft exel you tuber

@nenavathnagaraj5580 2 жыл бұрын

Awesome content 👏 Keeping sharing your knowledge and help us grow 😇🤗

@chandoo_ 2 жыл бұрын

Always!

@flickhunter8900 4 ай бұрын

Chandoo never disappoints 😊. Thx bro

@shriram8088 2 жыл бұрын

Thank you so much Chandoo for this video . You always try to cover small small things, which help us to understand each and every points. Last but not least your video graphics are too good.💖💖💖

@chandoo_ 2 жыл бұрын

Thanks Shriram :)

@salahadinadous9147 6 ай бұрын

Thank you you are great

@Eternal_connection 5 ай бұрын

Thanks a ton 💐

@kiransankardebnath1449 Жыл бұрын

This is amazing.

@PrakashNagaraj1993 2 жыл бұрын

You are helping most of us to transform our career. Thanks for paving our future and helping to knowledge us.. thanks..

@chandoo_ 2 жыл бұрын

Thanks Prakash :)

@riyat777 2 жыл бұрын

Great job 🙏🤗

@J0YJiT 9 ай бұрын

Really amazing informative video Chandu

@ArjunKumar-cz4qr 2 ай бұрын

Thanks Sir Very informative video, good to have this

@rafiabdullah2890 Жыл бұрын

Man.....!!!! this guy deserves 100M likes & 100M subscribes. absolute peace of art....💖💖💖 love u brooo.

@mhkhan0466 Жыл бұрын

Excellent.

@equiwave80 Жыл бұрын

Thanks for this excellent video!!! Crystal clear explanation of some of the functions that I always find difficult to understand. Thanks Chandoo for all your efforts!!!

@adwityamehra4279 2 жыл бұрын

Loved this Chandoo! ❤️😊

@chandoo_ 2 жыл бұрын

Thanks Adwitya...

@SanjayGulatiMusafir 2 жыл бұрын

Hi @Chandoo for Q 14 & 15, I think UNIQUE formula would have been another useful formula that you could have covered. In fact UNIQUE would have helped fetching results a bit faster.

@chandoo_ 2 жыл бұрын

Good points Sanjay. I talk about UNIQUE in few other videos. So decided to skip that bit here. 😀

@josephoseni7319 9 ай бұрын

I like this tutorials ❤

@rajivramki Жыл бұрын

I love doing homeworks after seeing Chandoo's video. Please give us more homeworks in coming days. BTW I'm your new Subscriber. Thank you for making my Excel journey, a interesting one👍Big fan of your work🤝

@chandoo_ Жыл бұрын

Thanks for subbing! You can check more homework problems here - chandoo.org/wp/tag/homework/

@sameerh3111 7 ай бұрын

I love to watch your videos

@poojahakke5462 8 ай бұрын

Thankyou so much for this amazing video and for the sample file ❤For the 12th and 13th questions, what is the alternative to filter function? (if it is not working on the current excel version)

@drsuniti1122 Жыл бұрын

Very nice sir, thanks

@gavidiharish13 Жыл бұрын

Thanks master❤

@largeroomnolight Жыл бұрын

Thanks!

@natarajang6125 2 жыл бұрын

Great video as usual 🤝🙏

@chandoo_ 2 жыл бұрын

Thank you 🙌

@chandrimachoudhury1057 Жыл бұрын

Thank you sir for sharing this video. It indeed helped me to learn the most important Advanced excel formulas for data analysis !! I am a follower of your You-tube channel & appreciate the lessons you provide.

@chandoo_ Жыл бұрын

You are most welcome

@rejoanislamrishad8208 5 ай бұрын

thank you dada from Bangladesh ❤

@mukhtarabdi824 9 ай бұрын

Thanks, Chandoo, for sharing this insightful content. I was wondering how can I find the solutions to the homework problems. I have tried them, but struggling with problem H3.

@UdaiKs 10 ай бұрын

Great Bro

@lj6203 Жыл бұрын

Excellent 👌👌👌👌👌

@farshidrafi7824 Жыл бұрын

Love you Dada keep going ❤️❤️

@detectivethinker3637 9 ай бұрын

Very useful video and I love your explanation! I have downloaded a free sample copy of the Excel sheet and have been following your steps through memory, but I keep getting zero as a result.

@rosaluna5220 Жыл бұрын

God bless Baaya 😍

@abhinandkp6808 Жыл бұрын

Thank you sir

@mohitpareek3305 Жыл бұрын

Hi Chandoo, Thanks for sharing such an informative video, Regarding Homework exercise please check if my understanding is correct: H1: =SUMIFS(data[Downloads],data[App],C125) H2:=SUMIFS(data[Downloads],data[App],C125)-SUMIFS(data[Uninstalls],data[App],C139)