I have been struggling with Excel for almost a decade 😢 but this 85-minute video comes to me like a blessing. You will remain one of the best teachers of mine for the rest of my life 🙏
Folks, I have consolidated all my business math and statistics videos into this one single video. The content is taken from our affordable data analyst BootCamp: codebasics.io/bootcamps/data-analytics-bootcamp-with-practical-job-assistance
excellent video. Thank you. just a small piece of information for those who are doing hands-on: 1. Excel 2019, 2016, and earlier do not support dynamic array formulas, so the UNIQUE function is not available in these versions. you can use the advanced filter for the same
Thank you so much Dhaval sir & Vadival Sir Through your guidance and my dedicated hardwork I've finally completed the course and got 3 interview calls and will get the job within 2months
I'm following the 2024 Roadmap, this was a very concise and easy to understand intro to excel. I appreciate you sharing the real life cases, as I personally retain information better when I understand the application.
What an amazing teacher you are in this series all the things which you told us is brilliant in a simple way Fantastic. Thank you SO much for providing us a free series.
Thank you so much sir. I've been struggling with math, statistics and P & L kinda questions but after this tutorial I'm 100% confident with my skills and looking forward to Data Analyst job. Great Work, Appreciate Your Work sir Thank you once again. I've Started this journey cuz of you after watching roadmap of how to become data analyst. easiest way to understand and learn that gave me so much confident..
The explanation on Variance and Standard Deviation is excellent. I have done statistics for a while and only new how to calculate the two. Great Video!!
This is such a great dive into using MS Excel for data analysis! I only need to do further studying into Variance and Standard deviation using real-world scenarios.
Ive been working for a pharma company since 2 years almost and first 20mins itself made me feel like iam an amateur in Excel and i thought i know excel to a certain level. Surely buying that data analyst 2.0 course.
Hi Sir, This comment is purely for knowledge nothing else. I have a small query in the business profit & loss session. When we are calculating the percentage above the target. we have to divide the (target - Actuals) with the target. for example, for the revenue row, the target revenue is 8000 but the actual revenue is 9054.60. Then we can say that we got 13% ( (9054.6-8000)/8000) more revenue than the target revenue. But in the video (target - Actuals) is divided by actuals. Then the definition changes, it will be the percentage with respect to actuals, not target. If I am wrong, please correct me. Thank you for this valuable video.
This video is amazing, as it gives beginner an idea how business use excel in their productive environment. Furthermore you have shared your learning here. We are grateful to have you as our mentor, thank you Dhaval sir for providing this valuable content.👍
@40:08 Sir this is because in Sumif formula, range length selected should be same i.e if you select A1:A10 as Criteria Range , You have to select same length range as Sum Range like C1:C10, you cannot use C1:C11.
@Ayaan_khan21 @pranavnesarikar5035 Guys I have figured it out. Actually the Excel Sheet provided by sir contains XLOOKUP function which is not supported by the excel version which we are using. So change XLOOKUP to VLOOKUP. Replace the VLOOKUP function with the following for the respectively columns. (Column H ) Budget =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[budget]], 2, FALSE), "Not Available") (Column I ) Revenue =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[revenue]], 3, FALSE), "Not Available") (Column J ) Unit =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[unit]], 4, FALSE), "Not Available") (Column K ) Currency =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[currency]], 5, FALSE), "Not Available") It worked out for me. Hope it works out for you as well. 😀😀
The problem is with the Excel file, the only solution I found is to copy all the values and paste them into a new Excel file and subsequently proceed. This may take a few minutes but it is better not to be stuck with this issue.
At 53:23 -> I think there is one minor mistake while calculating (Target - Actuals %), it should be D2/C2 instead of D2/B2. The real answer comes at 13.175% and just confirming -> (8000+13.175% = ~9054). Please let me know if I am wrong somewhere. Thank you for the tutorial btw.
I was practicing today with this video. But in the food category my sumif command returned me zero. but in the utilities or rent section it works perfectly. i dont know whats wrong. Can anyone help? i'm just a rookie
There is a problem with a movie sheet when i try to extend the table it gives name error . The only solution is to copy all the values from the table and paste it in a new excel sheet (Note: only paste values not the entire table format then it will again show name error)
Sir I think "40:25 min" in this video which problem you face , it happens because of which cell range you select, it have a cell with text content. Whole class was very good and easy to learn for beginners, thank you sir thank you so much to share your valuable knowledge with us .
@@codebasics sir can you please help me where from you get this thing in sum if formula currency , revenue in blue colour how you add or how can i add unique sir please help me sir 🙏
hey, such an amazing series. Thanks for teaching in such a good way. But here, I am not able to download the movies data set. following error message appears. AccessDenied Access denied
unque function unavailable for my excel version-microsoft ofice professional 2016, this function is available for version 2021 and aobove . correct me if am wrong
The problem is with the Excel file, the only solution I found is to copy all the values and paste them into a new Excel file and subsequently proceed. This may take a few minutes but it is better not to be stuck with this issue.
Give this a try. Actually the Excel Sheet provided by sir contains XLOOKUP function which is not supported by the excel version which we are using. So change XLOOKUP to VLOOKUP. Replace the VLOOKUP function with the following for the respectively columns. (Column H ) Budget =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[budget]], 2, FALSE), "Not Available") (Column I ) Revenue =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[revenue]], 3, FALSE), "Not Available") (Column J ) Unit =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[unit]], 4, FALSE), "Not Available") (Column K ) Currency =IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[currency]], 5, FALSE), "Not Available") It worked out for me. Hope it works out for you as well. 😀😀
The problem is with the Excel file, the only solution I found is to copy all the values and paste them into a new Excel file and subsequently proceed. This may take a few minutes but it is better not to be stuck with this issue.
in 50.0 minutes you say that Target - Actual But doing that Actual - Target But in Second row (Budget) doing Target - Actual If we place an variable at the wrong place then the whole information will collabse so that say clearly according to the senario we need to do the work or there is default formula
Sir, can I use Google sheets in place of ms Excel because I don't have premium version and in old/free version some of to the functions aren't there. Or if you know any other alternative of Ms office please suggest.
Not the whole concept. Just business maths. You could learn data analytics by following Code basics Data Analyst roadmap 2024. That's how I found this video.
I am currently following your Data Science roadmap and I just started this, but I am going to college for my bachelors in business. I have noticed a lot of jobs are asking for a bachelor's or masters even, specifically in data or computer science, so I'm a little concerned that I will need to change majors, or even schools. Would you have any insights on this?
I have been struggling with Excel for almost a decade 😢 but this 85-minute video comes to me like a blessing. You will remain one of the best teachers of mine for the rest of my life 🙏
Can you please help me how he put blue colour things like revenue if my budget is is this any formula fotlr this.
Folks, I have consolidated all my business math and statistics videos into this one single video. The content is taken from our affordable data analyst BootCamp: codebasics.io/bootcamps/data-analytics-bootcamp-with-practical-job-assistance
Thank you very much, this is very helpful.
excellent video. Thank you.
just a small piece of information for those who are doing hands-on:
1. Excel 2019, 2016, and earlier do not support dynamic array formulas, so the UNIQUE function is not available in these versions. you can use the advanced filter for the same
Thank you so much Dhaval sir & Vadival Sir
Through your guidance and my dedicated hardwork I've finally completed the course and got 3 interview calls and will get the job within 2months
I'm following the 2024 Roadmap, this was a very concise and easy to understand intro to excel. I appreciate you sharing the real life cases, as I personally retain information better when I understand the application.
Bro I am also learning in data science can we prepare together??
I am also following the 2024 road map
Can we study together?@@peaceokoroji4869
me too
Started today following the 2024 roadmap.
please complete this series from beginner to advance
What an amazing teacher you are in this series all the things which you told us is brilliant in a simple way Fantastic. Thank you SO much for providing us a free series.
Thank you so much sir. I've been struggling with math, statistics and P & L kinda questions but after this tutorial I'm 100% confident with my skills and looking forward to Data Analyst job.
Great Work, Appreciate Your Work sir Thank you once again.
I've Started this journey cuz of you after watching roadmap of how to become data analyst.
easiest way to understand and learn that gave me so much confident..
WOW... I like this community
It's a privilege to learn😂
The explanation on Variance and Standard Deviation is excellent. I have done statistics for a while and only new how to calculate the two. Great Video!!
Suno sir dhyn se ek baat bolne thi ....we love u❤ thanks form all fresher data analyst ❤
Sir, you explained Variance and SD in very simple manner, thanks 🙏
This is such a great dive into using MS Excel for data analysis! I only need to do further studying into Variance and Standard deviation using real-world scenarios.
My whole stat course at university was lie until your lesson. Loved it .Keep it up the good work.
Ive been working for a pharma company since 2 years almost and first 20mins itself made me feel like iam an amateur in Excel and i thought i know excel to a certain level.
Surely buying that data analyst 2.0 course.
This series is amazing. THANK YOU SO MUCH. Please continue this series
the way you explain sir its just amazing thanks for providing this type of information free of cost
Thanks and welcome
SUPERB BRO!!!!!
BRAVO!!
THANKS ALOT.
HOPE SO ALL YOUR VIDEOS WERE FREE SO THAT I CAN INCREASE MY KNOWLEDGE 100x MORE
Hi Sir, This comment is purely for knowledge nothing else. I have a small query in the business profit & loss session. When we are calculating the percentage above the target. we have to divide the (target - Actuals) with the target. for example, for the revenue row, the target revenue is 8000 but the actual revenue is 9054.60. Then we can say that we got 13% ( (9054.6-8000)/8000) more revenue than the target revenue. But in the video (target - Actuals) is divided by actuals. Then the definition changes, it will be the percentage with respect to actuals, not target. If I am wrong, please correct me. Thank you for this valuable video.
so far the best advise on how to become a data analyst
Loved the simplicity of your teaching. Excellent and easy-to-understand examples used for explaining the topics covered. wonderful tutorial.
This video is amazing, as it gives beginner an idea how business use excel in their productive environment. Furthermore you have shared your learning here. We are grateful to have you as our mentor, thank you Dhaval sir for providing this valuable content.👍
It was really useful for someone who just started out .
Thanks for making such valuable Content.
Really an amazing tutorial. You've explained the things in a simple and easy to learn way. Thanks for the Efforts you've added.
I can't find the files materials to keep up with the video, please help !
Hoe to get this data set , which is mentioned in above video. Please provide it.
Thank you very much👌👌👌👌👌👌👌👌👌👌
This is really useful, appreciate your effort and time to make this series..
@40:08 Sir this is because in Sumif formula, range length selected should be same i.e if you select A1:A10 as Criteria Range , You have to select same length range as Sum Range like C1:C10, you cannot use C1:C11.
Thank you so much sir 🙏🏻
extremely best video
@22:14 whenever i try to create new column budget(mln) and hit enter , all the numerical columns starts giving name error . please assist
Yeah.. Mee too.. Please help of any one.
Guys did you figure it out?? Even I am facing the same error.
Still stuck with the same issue which is stopping from moving forward the issue might be the excel file provided
@Ayaan_khan21
@pranavnesarikar5035
Guys I have figured it out. Actually the Excel Sheet provided by sir contains XLOOKUP function which is not supported by the excel version which we are using. So change XLOOKUP to VLOOKUP. Replace the VLOOKUP function with the following for the respectively columns.
(Column H ) Budget
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[budget]], 2, FALSE), "Not Available")
(Column I ) Revenue
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[revenue]], 3, FALSE), "Not Available")
(Column J ) Unit
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[unit]], 4, FALSE), "Not Available")
(Column K ) Currency
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[currency]], 5, FALSE), "Not Available")
It worked out for me. Hope it works out for you as well. 😀😀
The problem is with the Excel file, the only solution I found is to copy all the values and paste them into a new Excel file and subsequently proceed. This may take a few minutes but it is better not to be stuck with this issue.
You havent provided movies dataset in the description if u do that so it would be easy for us to practice on it simultaneously
Thanks Brother🙏🙏🙏
Yr tum amazing ho .
thank you for your wonderful class
At 53:23 -> I think there is one minor mistake while calculating (Target - Actuals %), it should be D2/C2 instead of D2/B2. The real answer comes at 13.175% and just confirming -> (8000+13.175% = ~9054). Please let me know if I am wrong somewhere. Thank you for the tutorial btw.
جزاك الله خيرًا
Can Somebody Explain how @ 37:38 the revenue total comes out to be 20838.01 it should be 19589 if we follow the previous video ? can anyone anwer ?
You can use named ranges to get the correct result
When I try to calculate the sum by category for utilities & Rent I get value 0 as result. Anyone experiencing the same ?
Is there any possibility to get the same datasheet which has been used in the video?
Kindly help I am getting stuck when doing the conversion into millions as it is resetting all columns to invalid
What could be the issue here
I am also facing the same issue.
hello, I don't know if its just me or what, but Whenever I try to edit the excel file an error appears just by adding a new column. Please Help
Same error for me too
Thank you for the detailed explanation
I was practicing today with this video. But in the food category my sumif command returned me zero. but in the utilities or rent section it works perfectly. i dont know whats wrong. Can anyone help? i'm just a rookie
Yes same happened with me also
u just click formulas section ,after that u use logical
There is a problem with a movie sheet when i try to extend the table it gives name error . The only solution is to copy all the values from the table and paste it in a new excel sheet (Note: only paste values not the entire table format then it will again show name error)
Same bro
Thank you for such an amazing series...
23.0
When I create new column named "budget mln" values in previous columns like budget, revenue, etc, shows '#name' errors Kindly assist.
I have completed BA in Bengali. Can I become a data analyst ??
You can 🎉
Around 13:33 u could just select 190 and drag and drop.That would be easier.
Sir I think "40:25 min" in this video which problem you face , it happens because of which cell range you select, it have a cell with text content.
Whole class was very good and easy to learn for beginners, thank you sir thank you so much to share your valuable knowledge with us .
hey....can u share ur email address....want to connect
You're prayed cure for looker's of job
Thank you for this series!
Thank you so much for such an amazing video! You're an excellent teacher!
Appreciate your kind words. 😊
@@codebasics sir can you please help me where from you get this thing in sum if formula currency , revenue in blue colour how you add or how can i add unique sir please help me sir 🙏
Very well explained. Thank you so much....
can you please provide the link of your spreadsheet
duuuuuuuuuuuuude!! 💚💚💚💚💚💚💚💚💚💚💚💚💚💚
hey, such an amazing series. Thanks for teaching in such a good way. But here, I am not able to download the movies data set. following error message appears.
AccessDenied
Access denied
Sir, how is this course different from your excel course on your website?
This course is only 5% of that Excel course. The excel course has covered many more concepts with some end to end projects
It is super helpful, thanks a lot sir!
Waiting for this video your video help for all data analysis students❤
Good video
unque function unavailable for my excel version-microsoft ofice professional 2016, this function is available for version 2021 and aobove . correct me if am wrong
Me too not having the UNIQUE function.
If i appy formula then formula bar shows cell name and you apply formula then it shows column name how ?
There is a #name? issue coming in my excel movies table of movies, whenever I am inserting a new column, how to resolve?
yes facing the same issue
@@farooquisaquib8806 Facing Same Issue..
The problem is with the Excel file, the only solution I found is to copy all the values and paste them into a new Excel file and subsequently proceed. This may take a few minutes but it is better not to be stuck with this issue.
Sir you are too good.
Thanks for this videos 😅
I dont have unique function in my excel and I dont wanna download new version of excel is there any other solution?
In my excel sheet its formula doesn't convert
I only see Name with Hashtags and questions mark in the cells that was merged I NEED HELP
when in movies table,i addbudget mln coulmns, the budget ,revenue , unit, currecny columns values become #NAME?,WHATS THE SOLUTION?
Give this a try. Actually the Excel Sheet provided by sir contains XLOOKUP function which is not supported by the excel version which we are using. So change XLOOKUP to VLOOKUP. Replace the VLOOKUP function with the following for the respectively columns.
(Column H ) Budget
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[budget]], 2, FALSE), "Not Available")
(Column I ) Revenue
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[revenue]], 3, FALSE), "Not Available")
(Column J ) Unit
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[unit]], 4, FALSE), "Not Available")
(Column K ) Currency
=IFERROR(VLOOKUP(Movies[@[movie_id]:[movie_id]], Financials[[movie_id]:[currency]], 5, FALSE), "Not Available")
It worked out for me. Hope it works out for you as well. 😀😀
The problem is with the Excel file, the only solution I found is to copy all the values and paste them into a new Excel file and subsequently proceed. This may take a few minutes but it is better not to be stuck with this issue.
im getting this error after i copy them to a new file the values becomes #names ... please provide solution if you know
@@CCODESERIES-vt6gg
sir im having problem with unique function . this function is not my excel so from where i can get this pls help
Thanks for your amazing video
Sir, I need this dataset. Thank you so much.
One of the best video
thanks a lot sir!
Thanks
in 50.0 minutes you say that Target - Actual But doing that Actual - Target But in Second row (Budget) doing Target - Actual If we place an variable at the wrong place then the whole information will collabse so that say clearly according to the senario we need to do the work or there is default formula
Looking forward to this 🤩
Great series sir ji
Data set not working in my excel
I am struggling with communication and presentation skills. Can you please suggest me any youtube channels to improve my skills.
sir great information
I DON'T KNOW IN MY EXCEL THERE IS NO UNIQUE FORMULA....IS THERE ANY ISSUE
AND EVEN I CAN'T CONVERT MY DATA INTO TABLE
Same problem
Same problem
DId i use google sheet instead of excel ?
Incredible
UNIQUE function not working in MS Office 2019. Plz Help
Hlw sir I'm a aspiring data analyst, how can we increase our knowledge on business or marketing. Please make a details video on this.
I've talked about that in my data analyst roadmap video. in youtube search "codebasics data analyst roadmap"
Unique function is not working in my excel sheet!!!
very helpful
Sir ap ms office ka konsa version use krrhy han
Sir, can I use Google sheets in place of ms Excel because I don't have premium version and in old/free version some of to the functions aren't there.
Or if you know any other alternative of Ms office please suggest.
MS Excel has its online version same as Google Sheet
If you would able to provide the data set it will be helpful .
Is this video covers the whole concept required for a data analytics? Pls amswer
Not the whole concept. Just business maths. You could learn data analytics by following Code basics Data Analyst roadmap 2024. That's how I found this video.
#name error occurs how to revover can anyone guide
I too faced same error on the Movies sheet and got stuck. @codebasics please help
after creating the table my column headers are getting hidden an can only see drop down filters.
please assist
Did you rectify that?
I am currently following your Data Science roadmap and I just started this, but I am going to college for my bachelors in business. I have noticed a lot of jobs are asking for a bachelor's or masters even, specifically in data or computer science, so I'm a little concerned that I will need to change majors, or even schools. Would you have any insights on this?
I too started recently 😊
Take statistics and maths in ug programs if you want to build carreir in data science
Lovely
where can i find this dataset???
In the description
can u pls share the movie dataset