Best Excel Formulas in an hour - Beginner to PRO Masterclass

  Рет қаралды 37,887

Chandoo

Chandoo

Күн бұрын

Пікірлер
@chandoo_
@chandoo_ 3 ай бұрын
🎉 Unlock AI for $10 per month: ntn.so/Chandoo
@deepthichandrala142
@deepthichandrala142 2 ай бұрын
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")
@aravindsuresh4147
@aravindsuresh4147 2 ай бұрын
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 🎉
@rashv25
@rashv25 2 ай бұрын
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
@OxyGenatic
@OxyGenatic 2 ай бұрын
Following your mentorship since 10/11 years and it was really amazing every time you bring something new and smart. BIG FAN of u...
@chandoo_
@chandoo_ 2 ай бұрын
Always a pleasure hearing from long-time fans such as yourself. More Power to you ⚡
@rekulapallysuresh7273
@rekulapallysuresh7273 3 ай бұрын
Sir we are waiting for telugu classes.....And also give some job requirement details at particular day its better for Saturday. u r big opportunity for unemployed youth
@dineshwagle9832
@dineshwagle9832 3 ай бұрын
You are a mixture of Talent and Generosity. Thank you very much Chandoo!!!!
@g_18_sanathmeshram42
@g_18_sanathmeshram42 2 ай бұрын
I have watched all of your videos in this series till now . Really Informative and Helpful . Keep Up the good Work.
@MR.JAM_687
@MR.JAM_687 2 ай бұрын
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.
@Whityyy_13
@Whityyy_13 Ай бұрын
I am watching your videos is very easly understand and improve myself tq so much sirr
@dailyuploads3959
@dailyuploads3959 3 ай бұрын
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
@Whatshouldidooooo 16 күн бұрын
Thank you chando all the way from Nigeria
@gauravnarwar4541
@gauravnarwar4541 13 күн бұрын
Fabulous teacher.
@ajaykumar_7__
@ajaykumar_7__ 9 күн бұрын
Very informative video . Thank you👏
@suryasabniveesu6946
@suryasabniveesu6946 2 ай бұрын
This video is 1000 times better than time wasting Web series
@bhuvanmohinidevi194
@bhuvanmohinidevi194 2 ай бұрын
Bro..dats da irony, u would criticise watching webseries but would be proud and brag about it if u get a data scientist job at Netflix..and cry when Netflix lays u off because people aren't renewing their subscriptions lol.
@ttb-travel5912
@ttb-travel5912 2 ай бұрын
Like you say "Stay Awesome". This video really is awesome. Learned tons of things.
@isaackodera9441
@isaackodera9441 2 ай бұрын
Wonderful. Good job. I'm using your lessons to prepare for an interview.
@khaleel898
@khaleel898 2 ай бұрын
Hi Chandoo, i really like you man. im a begginer in excel with some basic knowledge. Your content is really helpful. First formula is =COUNTIFS(staff[Employee type],"Permanent",staff[Department],B3:B14) right? it took me sometime to figure out.
@chandoo_
@chandoo_ 2 ай бұрын
Bingo 😎
@awazaarkhan2423
@awazaarkhan2423 2 ай бұрын
i could not find it. but your comment helped and i really wanna cry in happiness
@chrism9037
@chrism9037 3 ай бұрын
Great job Chandoo, very helpful for newer Excel users!
@chandoo_
@chandoo_ 3 ай бұрын
Thanks Chris :)
@vru5696
@vru5696 2 ай бұрын
​@@chandoo_ Any detail video on vlookup with real time scenarios.
@vru5696
@vru5696 2 ай бұрын
​@@chandoo_ Any detail video on vlookup with real time scenarios with 2 different excel files.
@ravipunati4530
@ravipunati4530 2 ай бұрын
Thank you. ❤ From Hyderabad..
@Luciano_mp
@Luciano_mp 3 ай бұрын
Very good video, very useful! Chandoo, thank you very much!
@DataStadium0902
@DataStadium0902 3 ай бұрын
Thank you guru!
@computerworldtutorial
@computerworldtutorial 3 ай бұрын
Nice video for every data analyst
@coachaliraza
@coachaliraza 3 ай бұрын
Excellent 🎉
@VonMellArevalo
@VonMellArevalo 3 ай бұрын
Thankyou Chandooooo it really helps
@GeorgeS-f9c
@GeorgeS-f9c 2 ай бұрын
for female ratio (headcount per department) divide (female count per department)
@mulengachibamba2001
@mulengachibamba2001 2 ай бұрын
Median Salary = MEDIAN(IF(staff[Department]=B187,staff[Salary]))
@venkatiyer7459
@venkatiyer7459 2 ай бұрын
Truly very useful video ....
@tamunamchedlishvili4677
@tamunamchedlishvili4677 2 ай бұрын
I learned a lot of things from your channel you are my favorite mentor @Chandoo
@usmaniqbal1836
@usmaniqbal1836 2 ай бұрын
Excellent dear
@mohd.zeeshan8581
@mohd.zeeshan8581 2 ай бұрын
Median Salary solution -> =MEDIAN(FILTER(staff[Salary],staff[Department]=A7))
@abicricket
@abicricket 2 ай бұрын
Hey Chandoo, waiting for your next video to get uploaded in this series playlist
@user.name--oreneus21
@user.name--oreneus21 3 ай бұрын
Very Helpful indeed!
@manishwason8495
@manishwason8495 2 ай бұрын
33:42 TAKE(SORT(FILTER(staff[Salary],staff[Gender]=D$4),,-1),5)
@mr.silence4096
@mr.silence4096 3 ай бұрын
fan from Tamilnadu 😍
@torquewrenching
@torquewrenching 2 ай бұрын
33:42 =TAKE(SORT(FILTER(staff[Salary],staff[Gender]=C$3),,-1),5)
@MuhammadAfzal-gx1oc
@MuhammadAfzal-gx1oc 2 ай бұрын
=FILTER(Staff[[Emp ID]:[Start Date]],(Staff[Salary]>A1)*(Staff[Gender]="Female")*(YEAR(Staff[Start Date])>=2020)) that's how i did it
@DatabySurya
@DatabySurya 3 ай бұрын
Please continue data analyst playlist sir im waiting for a long
@mkmukki666
@mkmukki666 2 ай бұрын
most valueable video good!
@anbuperumal8539
@anbuperumal8539 2 ай бұрын
Really great
@Tony_Montana659
@Tony_Montana659 3 ай бұрын
❤ From Kerala palakkad ❤
@davui_21
@davui_21 2 ай бұрын
Thank you once again, Sir. While following I realised my Excel 365 has only CHOOSE but not CHOOSECOLS under the second concept. Here, in this fascinating one, it has only SORT but not the TAKE function. Please how can I fix this? I am really enjoying the lessons in this series. Appreciations from Ghana.
@chandoo_
@chandoo_ 2 ай бұрын
Some of these functions are added into newer versions of Excel and if you have not update Excel in a while, you won't see them. One easy fix is to try them with Excel online. Another option is to sign up for "faster" or "beta" updates in Office. To do that, go to File menu in Excel, click on Account and click on Microsoft Insider and enable the option.
@hardeepsingh-sf7bb
@hardeepsingh-sf7bb 3 ай бұрын
Thank u very much 🙏🙏
@prasadgrandhi2056
@prasadgrandhi2056 3 ай бұрын
Thankyou 💌
@pavithrav945
@pavithrav945 3 ай бұрын
Thankyou 😊
@ranjan4495
@ranjan4495 2 ай бұрын
thankyou sir
@MicrosoftExcel-o8w
@MicrosoftExcel-o8w 2 ай бұрын
Excellent job! I'd love to hear your thoughts on my work.👍
@DataEntry-v9v
@DataEntry-v9v 2 ай бұрын
=FILTER(CHOOSE({1,2,3,4},staff[Emp ID],staff[First Name],staff[Last Name],staff[Gender]),(WEEKDAY(staff[Start Date])=2)*(staff[Gender]="Female")) , i wrote this formula for female employees for monday start in excel 2021
@DeepakSharma-bv2kw
@DeepakSharma-bv2kw 3 ай бұрын
I am waiting for it
@NileshKumar-yj4sb
@NileshKumar-yj4sb 3 ай бұрын
Great
@mianhamzo
@mianhamzo 2 ай бұрын
Sir I have a issue in ChooseColumn Function. This function can't Show in my Excel 365 how to add this Function into the excel. You are very great tutor love from Pakistan.
@chinuzoejima8971
@chinuzoejima8971 2 ай бұрын
i also had same issue
@awazaarkhan2423
@awazaarkhan2423 Ай бұрын
i also dont have TAKE function working on mine.
@JyotiJyoti-zm3nh
@JyotiJyoti-zm3nh 2 ай бұрын
Hi chandoo, thankyou for your videos, they are really helpful. Female Ratio for a department: =count(female)/GCD(count(female),Total Headcount) & ":" & Total Headcount/GCD(count(female),Total Headcount), is it correct?
@chandoo_
@chandoo_ 2 ай бұрын
Looks good. I assume count(female) is actually COUNTIFS(..) with gender as "Female".
@JyotiJyoti-zm3nh
@JyotiJyoti-zm3nh 2 ай бұрын
@@chandoo_ yes yes thankyou
@Kinginthenorth64
@Kinginthenorth64 2 ай бұрын
Even if you have 365 and if you have formatted your data in table and using formula with criteria as range instead of reference to cell; You will still get 'Spill'.
@chandoo_
@chandoo_ 2 ай бұрын
Yes, that is the power of "Dynamic array" behaviour. Everything can spill now (well almost everything).
@ShivaKumar-py8oj
@ShivaKumar-py8oj Ай бұрын
Female Ratio: COUNTIF(staff[Department],B5#)/GCD(COUNTIF(staff[Department],B5#,COUNTIFS(staff[Gender],"Female",staff[Department],B5#))&":"&COUNTIFS(staff[Gender],"Female",staff[Department],B5#)/GCD(COUNTIF(staff[Department],B5#),COUNTIFS(staff[Gender],"Female",staff[Department],B5#)) Correct?
@Ismailfarhang
@Ismailfarhang 26 күн бұрын
Does it work Sir?
@ompratihary2844
@ompratihary2844 2 ай бұрын
Hello sir For the last challenge of determining the median salary for each department can this be a solution : Median(xlookup(referenced cell as lookup_value,staff[department],staff[salary])) ?
@chandoo_
@chandoo_ 2 ай бұрын
XLOOKUP only returns the first salary value in department. So it won't work. Try again.
@Ismailfarhang
@Ismailfarhang 26 күн бұрын
=MEDIAN(FILTER(Staff[Salary],Staff[Department]=B6)) I think it is work properly. check it once
@anjanakoduri4808
@anjanakoduri4808 2 ай бұрын
I can't locate practice file sir, can you please put it in description box itself like as you did it for previous vedios? Its hard to find in website sir.
@suranjitbarua1719
@suranjitbarua1719 2 ай бұрын
Chandoo do you have a plan changing your KZbin profile picture? 😊
@chandoo_
@chandoo_ 2 ай бұрын
That is my logo, so not really.
@ayanbhatta
@ayanbhatta 17 күн бұрын
What is the ,all department ratio formula
@mr.silence4096
@mr.silence4096 3 ай бұрын
1st cmd
@devarajpriyadharshini9503
@devarajpriyadharshini9503 2 ай бұрын
Hi @chandoo_, I wanted to add one comments and insights column alongside in pivot table. Bcs, whenever we include new data the report changes accordingly, so the insights that we made have to be copy pasted again to the respective rows. Any idea for it.
@davui_21
@davui_21 2 ай бұрын
Challenge 6: =XLOOKUP(120000, staff[Salary], staff, "Not found",0,1) I know the FILTER function will come in if you could help me with the exact portion I can fix it that'd be awesome, Sir
@davui_21
@davui_21 2 ай бұрын
I did it !!!!!!!!!! 😂😂 =FILTER(staff,staff[Salary]=120000)
@mohd.zeeshan8581
@mohd.zeeshan8581 2 ай бұрын
Solutuon 9 Assignment : =FILTER(staff[[Emp ID]:[Last Name]],(TEXT(staff[Start Date],"dddd")="Monday")*(staff[Gender]="Female"))
@vintagewear7862
@vintagewear7862 2 ай бұрын
wrong
@tehreemzainab8068
@tehreemzainab8068 2 ай бұрын
=MEDIAN(staff[Salary])
@lavatr8322
@lavatr8322 2 ай бұрын
From where do i download the Essential Excel formula sheet and sample Data table?
@daringx4385
@daringx4385 5 күн бұрын
Where can I find the sample file of this video? Can u pls provide it's link
@DataEntry-v9v
@DataEntry-v9v 2 ай бұрын
=COUNTIFS(staff[Gender],"Female",staff[Department],A5) &":"& COUNTIFS(staff[Gender],"male",staff[Department],A5) , this is for Female ratio is this correct A5 for Department?
@Acheiver143
@Acheiver143 3 ай бұрын
@KapilSinghSenwal
@KapilSinghSenwal 2 ай бұрын
Female Employees with Monday start =FILTER(staff,(TEXT(staff[Start Date],"DDDD")="Monday") * (staff[Gender]="Female"))
@the_pest_the_best
@the_pest_the_best 3 ай бұрын
❤❤
@radhathakore4423
@radhathakore4423 2 ай бұрын
@Chandoo - Can you pls help on the 3rd challenge with condition for females having greater $100k salary and joined after 1st jan 2020 . I am using this formula and is displaying all the values of salary below 100k too. Not sure where am i going wrong Formula used =FILTER(staff[[Emp ID]:[Salary]],(staff[Salary Bucket]>100000)*(staff[Gender]="Female")*(staff[Start Date]>H12))
@chandoo_
@chandoo_ 2 ай бұрын
Change the [salary bucket] to [salary] column.
@seemanazir
@seemanazir 2 ай бұрын
Reasons for changing the salary bucket to salary??? explain?​@@chandoo_
@himanshusarkar5856
@himanshusarkar5856 Ай бұрын
Good evening sir, sir, can u send me this file for practice please sir it will be very helpful for me.
@sailakshmithadimalla8707
@sailakshmithadimalla8707 2 ай бұрын
hi where i can get this file in your website?
@veerprathap5158
@veerprathap5158 17 күн бұрын
Female employees with Monday Start (=FILTER(staff,(staff[Gender]="Female")*(TEXT(staff[Start Date],"dddd")="Monday"))
@awazaarkhan2423
@awazaarkhan2423 27 күн бұрын
how to put "All dept in one cell comma seperated"
@manojgowda6154
@manojgowda6154 2 ай бұрын
Sir we don't have Excel 365 we have old version 😢
@guruprasath3097
@guruprasath3097 3 ай бұрын
Female employees Start Date on Monday: =FILTER(staff[[Emp ID]:[Last Name]],(staff[Gender]="female")*(TEXT(staff[Start Date],"ddd")="Mon"))
@ShubhamXD
@ShubhamXD 2 ай бұрын
It can be done using weekday function lol, The count is 62 right? of female employees whose start date is Monday
@geniphermiller7429
@geniphermiller7429 Ай бұрын
I appreciate and enjoy your videos, but I find the background music to be distractingly annoying. Just my feedback sir. I wish you well.
@chandoo_
@chandoo_ Ай бұрын
Thanks for the feedback. I experiment with music on / off on my videos to see what produces better outcome for viewers. Largely, having music on seems to help people enjoy the content. But may be I got the balance (music too loud) wrong in this video.
@esrAsnataS
@esrAsnataS 2 ай бұрын
Excellent, but I can't believe you "merge & centered". 🤣
@chandoo_
@chandoo_ 2 ай бұрын
There is nothing wrong with that. Anyone who tells you otherwise is just being pedantic.
@Mysteresque
@Mysteresque 2 ай бұрын
I want to know that why cant we give the logical conditions using AND() & OR() in the 'include' parameter of FILTER() function
@andypereira362
@andypereira362 Ай бұрын
That's because and() & or() work on a single line formula not in an array. An array basically takes your data and multiplies it by 0 or any other number (mostly 1). If you take the * it basically asks is this condition met? if yes then it gives the value one else zero. So when 2 conditions are met it becomes 1*1 =1. If one condition is false it will be 1*0= 0. The array will only show data where the condition is not zero. Now take the +. if 1 condition is met it will be 1+0=1. Both conditions are met it will be 1+1=2. Hence it will show if either condition is true.
@dullavivekkumar5476
@dullavivekkumar5476 2 ай бұрын
How to hide cell formula in google sheet.
@allshorts3324
@allshorts3324 2 ай бұрын
Sir, Why can't we copy the filtered data from sheet and paste it to another sheet.... instead of using filter function... Copy and paste is easy and it will give as same as original sheet
@chandoo_
@chandoo_ 2 ай бұрын
But it is manual and becomes stale if your criteria or data changes.
@allshorts3324
@allshorts3324 2 ай бұрын
@@chandoo_ okk sir...I understood Sir...I don't have excel 365 ....I am unable to use functions like CHOOSECLMS.....what should I do?
@ugiagbeimarnoel8094
@ugiagbeimarnoel8094 Ай бұрын
Hi please my excel is not performing some of the functions I use an apple laptop it’s just so strange
@vinaypanduhii9833
@vinaypanduhii9833 Ай бұрын
hoe to download the sample data there is no link in the video description
@gaganwadhwafreelancer
@gaganwadhwafreelancer 3 ай бұрын
Plz share Practice file.
@chandoo_
@chandoo_ 3 ай бұрын
It is here - chandoo.org/wp/free-data-analyst-course/
@sanjuktabarua3213
@sanjuktabarua3213 2 ай бұрын
can anyone help me with the median challenge..tried a lot but couldnt resolve
@gauravupreti5612
@gauravupreti5612 2 ай бұрын
@sanjuktabarua3213 =MEDIAN(FILTER(staff[Salary],staff[Department]=B6)) Give it a try..
@emlih1898
@emlih1898 2 ай бұрын
i can’t download the practice file, the link looks to be broken on the site.
@chandoo_
@chandoo_ 2 ай бұрын
I just tested and both practice and complete file links work fine. Try again.
@ahmedrapii641
@ahmedrapii641 Ай бұрын
great job but can you just turn off this music ...i can not focus with it
@ahmedafifi87
@ahmedafifi87 Ай бұрын
(YEAR(Staff[Start Date])>=2020)
@arunzone4477
@arunzone4477 2 ай бұрын
bro plz help to explain what is the error in this command and plz correct female employees starting on monday =FILTER(staff, (staff[Gender]="Female") * (staff[Start Date]=DATE(Year, 1, 1)))
@saurabh-zg7qc
@saurabh-zg7qc 2 ай бұрын
Date function you used will give the first date of the month for example 1 january, 1 March , if you want Monday start date use text function =Filter(staff,(staff[Gender]="Female")*(Text(staff[start date],"dddd")="Monday"))
@arunzone4477
@arunzone4477 2 ай бұрын
@@saurabh-zg7qc thanks bro
@saurabh-zg7qc
@saurabh-zg7qc 2 ай бұрын
@@arunzone4477 happy to help
@ravindracomedyzone3027
@ravindracomedyzone3027 3 ай бұрын
చందు గారు ఈ ఫార్ములా మరియు ఫంక్షన్స్ మేధ ఒక పూర్తి వీడియో తెలుగులో చేసి పుణ్యం కట్టుకోండి అయ్యా... చాల మంది మీ వీడియోల కోసం ఎదురుచూస్తున్నారు ప్లీజ్ సార్
@chandoo_
@chandoo_ 2 ай бұрын
This is already in the pipeline and should be out on the channel in the next 4 weeks.
@vintagewear7862
@vintagewear7862 2 ай бұрын
IS THERE ANYONE WHO FINDOUT THE LAST ONE? THE FEMALE RATIO?
@chandruGG05
@chandruGG05 2 ай бұрын
sir provide pratice data about this video
@chandoo_
@chandoo_ 2 ай бұрын
Refer to the video description. The link is in there.
@seemanazir
@seemanazir 2 ай бұрын
Video quality is poor
@chandoo_
@chandoo_ 2 ай бұрын
Must be your connection. The video is a HD quality and works just fine.
@invinciblesoul7
@invinciblesoul7 2 ай бұрын
plz teach in hindi..it will be more easily understood
@manishwason8495
@manishwason8495 2 ай бұрын
01:01:35 =FILTER(staff,staff[Gender]="Female",WEEKDAY(staff[Start Date]=2))
@vinc.7765
@vinc.7765 3 ай бұрын
@chandoo_ sei fantastico 🤩
@TsehayAshiber
@TsehayAshiber 2 ай бұрын
solution for the challenge to filter female employees with >100k salary and start date after 2020 : =FILTER(staff, (staff[Salary]>A22)*(staff[Gender] = "female")*(staff[Start Date]>DATE(2020,1,1)))
@manishwason8495
@manishwason8495 2 ай бұрын
=FILTER(staff,(staff[Salary]>100000)*(staff[Gender]="Female")*(staff[Start Date]>DATE(2020,1,1)))
Excel Pivot Tables - Master Data Analysis in just 45 minutes
50:06
Как Я Брата ОБМАНУЛ (смешное видео, прикол, юмор, поржать)
00:59
Натурал Альбертович
Рет қаралды 4,4 МЛН
Farmer narrowly escapes tiger attack
00:20
CTV News
Рет қаралды 7 МЛН
А я думаю что за звук такой знакомый? 😂😂😂
00:15
Денис Кукояка
Рет қаралды 4,3 МЛН
Yay😃 Let's make a Cute Handbag for me 👜 #diycrafts #shorts
00:33
LearnToon - Learn & Play
Рет қаралды 117 МЛН
Power Query - Beginner to PRO Masterclass in 30 minutes
35:17
Beginner to Pro FREE Excel Data Analysis Course
49:16
Chandoo
Рет қаралды 2 МЛН
50 things you didn't know Excel can DO 💡
30:03
Chandoo
Рет қаралды 281 М.
The SECRET to Matching TWO Lists in Microsoft Excel!
4:13
Microsoft Excel Tutorials
Рет қаралды 13 М.
Learn 80% of DAX in an Hour (with FREE sample file)
1:02:21
Chandoo
Рет қаралды 50 М.
Mastering Excel: Top 10 Formulas You Need to Know
14:30
Kenji Explains
Рет қаралды 49 М.
Как Я Брата ОБМАНУЛ (смешное видео, прикол, юмор, поржать)
00:59
Натурал Альбертович
Рет қаралды 4,4 МЛН