Best Excel Formulas in an hour - Beginner to PRO Masterclass

~

Here is something that most people get wrong when learning Excel. They learn the functions, but not "when" and "how" to use them. So in this masterclass, let me tell you "when" and "how" to use Excel functions with 10 business problems.

What you will learn in this video:
◉ Working with Excel formulas & functions
◉ COUNTIFS, SUMIFS, AVERAGEIFS
◉ FILTER Functions, multiple criteria
◉ CHOOSECOLS function
◉ MIN, MAX
◉ SMALL, LARGE
◉ SORT + TAKE functions
◉ UNIQUE
◉ # OPERATOR
◉ COUNTA vs. COUNT functions
◉ VLOOKUP
◉ XLOOKUP
◉ IFERROR
◉ How to nest formulas to get the result you want
◉ Complex business problems and formula solutions for them
❤ And plenty of homework problems

📁 Sample files:

⏱ Video timestamps:

0:00 - The problem with learning Excel functions
1:51 - Total salary and headcounts by department (COUNTIFS, SUMIFS, AVERAGEIFS)
8:28 - All employees with more than $100k salary (FILTER)
12:21 - Seeing only a few columns with FILTER using CHOOSECOLS
16:17 - How to learn & remember all this info
20:36 - All female employees with more than $100k salary (FILTER multiple conditions)
24:16 - Lowest, highest, top 5 salary values (MIN, MAX, MINIFS, MAXIFS, SMALL, LARGE, SORT+TAKE)
33:44 - Listing all departments with UNIQUE and using # operator
37:03 - Lookup functions (VLOOKUP, XLOOKUP, INDEX+MATCH, IFERROR)
53:22 - Highest paid person (XLOOKUP + MAX)
57:22 - All the employees that joined in March (FILTER + DATE FUNCTIONS)
1:01:41 - Making a report with formulas (COUNTIFS, SUMIFS, UNIQUE, #, Conditional Formatting)
1:09:09 - What's next?

📺 Learn more:

📺 Excel formula videos:

🎶 Free Data Analyst Course Playlist:

~

#excel #dataanalyst #chandoo #freedataanalystcourse #fdac

[OxyGenatic]

Following your mentorship since 10/11 years and it was really amazing every time you bring something new and smart. BIG FAN of u...

[aravindsuresh]

Even thought I was using excel for 4 years, but am happy to say that I learned some thing new in every exercises which u have thought am gratefully to you for this wonder full video sir hats off 🎉

[rashv]

Sir, you are "NOTION" personified for me :-) I say this because I have been studying a lot of tutorials in the past year to upgrade my knowledge on Excel formulas and this single video helped me test my application prowess . High quality content.Thank you Sir for putting in so much thought and effort to curate such videos

[Shuvoorahman]

When Chandu uploads videos like this, I just see how beautifully Chandu conveys. I always wait for videos like this from Chandu. Thank you Chandu. Love from Bangladesh.🥰

[g__-dx]

I have watched all of your videos in this series till now . Really Informative and Helpful . Keep Up the good Work.

[roshisrivastava]

Thanks a ton Chandoo for explaining everything in detail. It is extremely helpful. I look out for your channel only for excel topics, it has boosted my confidence in Excel and advance concepts as well.

[dineshwagle]

You are a mixture of Talent and Generosity. Thank you very much Chandoo!!!!

[_sanjana_]

For finding employee details with the lookup function, i found something cool.
If u make a list out of names(using data validation), so when u wanna get details of people, u could literally choose the name from the list itself, it'll automatically give you the details because u chose the name from the list itself while using the lookup function.

[Michael_Alaska]

Thank you. I am picking up a lot from your videos. For the LARGE function you can use the curly brackets for the K value. i.e. {1, 2, 3, 4, 5} but it spills across in columns unless you add TRANSPOSE. It would be nice if that could be changed to spill into rows.

[ttb-travel]

Like you say "Stay Awesome". This video really is awesome. Learned tons of things.

[MR.JAM_]

Sir thank you ❤.
You are literally amazing and work is awesome as you. You are helping a lot of people by just sharing your knowledge and it's cost of freee.

[dailyuploads]

When i watch you videos. I feel its like a movie, each scene(video frames) is trilling and sensational. Seriously! personally i learn a lot from that channel. Love from pakistan. Make some awesome videos on padas, matplotlib, seaborn python libraries💌🍀

[Whatshouldidooooo]

Thank you chando all the way from Nigeria

[Whityyy_]

I am watching your videos is very easly understand and improve myself tq so much sirr

[isaackodera]

Wonderful. Good job. I'm using your lessons to prepare for an interview.

[rekulapallysuresh]

Sir we are waiting for telugu also give some job requirement details at particular day its better for Saturday. u r big opportunity for unemployed youth

[Luciano_mp]

Very good video, very useful! Chandoo, thank you very much!

[chrism]

Great job Chandoo, very helpful for newer Excel users!

[manishwason]

33:42 TAKE(SORT(FILTER(staff[Salary], staff[Gender]=D$4), , -1), 5)

[deepthichandrala]

I would appreciate it if you could confirm whether these answers are correct
Answers:
1.=COUNTIFS(staff[Department], A4, staff[Employee type], "Permanent")
2.=FILTER(staff, staff[Start Date]>=DATE(2020, 1, 1))
3.=TEXTJOIN(", ", TRUE, UNIQUE(staff[Department]))
4.=FILTER(staff[Employee], staff[Salary] >= 120000, "No Match Found")

5.=FILTER(staff, (staff[Gender]="Female")* (WEEKDAY(staff[Start Date], 2)=1))
6.=MEDIAN(FILTER(staff[Salary], staff[Department] = A4))

7.=COUNTIFS(staff[Department], A4, staff[Gender], "Female")