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
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
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
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.
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.
@@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 🙏
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.
@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.
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 .
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.
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
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
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.
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
hi, greeting from pakistan, i am doing practice on your provided movie sheet. facing problem as i insert column align with the table, all values in table showing error ? NAME?, it don't undo and finally i had to close the sheet. anyone can guide me.
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.
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.
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.
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
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.
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
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
My whole stat course at university was lie until your lesson. Loved it .Keep it up the good work.
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.
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.
please complete this series from beginner to advance
This video is very engaging. Your explanations are so clear and helpful. Thank you for sharing your knowledge.
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!!
Suno sir dhyn se ek baat bolne thi ....we love u❤ thanks form all fresher data analyst ❤
hello brother please can i contact you
@@akershpal5905sure
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.
so far the best advise on how to become a data analyst
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.
the way you explain sir its just amazing thanks for providing this type of information free of cost
Thanks and welcome
This series is amazing. THANK YOU SO MUCH. Please continue this series
Sir, you explained Variance and SD in very simple manner, thanks 🙏
Loved the simplicity of your teaching. Excellent and easy-to-understand examples used for explaining the topics covered. wonderful tutorial.
Really an amazing tutorial. You've explained the things in a simple and easy to learn way. Thanks for the Efforts you've added.
SUPERB BRO!!!!!
BRAVO!!
THANKS ALOT.
HOPE SO ALL YOUR VIDEOS WERE FREE SO THAT I CAN INCREASE MY KNOWLEDGE 100x MORE
It was really useful for someone who just started out .
Thanks for making such valuable Content.
This is really useful, appreciate your effort and time to make this series..
Thanks Dhaval Patel Sir 🙂
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.
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....
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.
Yess
@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.
Thank you for the detailed explanation
Thank you for such an amazing series...
Thank you for this series!
Thank you very much👌👌👌👌👌👌👌👌👌👌
thank you for your wonderful class
Thank you so much sir 🙏🏻
It is super helpful, thanks a lot sir!
جزاك الله خيرًا
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
Thanks Brother🙏🙏🙏
Waiting for this video your video help for all data analysis students❤
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.
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
You havent provided movies dataset in the description if u do that so it would be easy for us to practice on it simultaneously
Around 13:33 u could just select 190 and drag and drop.That would be easier.
I can't find the files materials to keep up with the video, please help !
Thanks for your amazing video
One of the best video
duuuuuuuuuuuuude!! 💚💚💚💚💚💚💚💚💚💚💚💚💚💚
Looking forward to this 🤩
you are awesome sir
sir
why dont you write the formula as sumif(movies[industry],"hollywood",revenue usd) for calculating total revenue
at video time 43:21
You're prayed cure for looker's of job
Sir you are too good.
extremely best video
Yr tum amazing ho .
thanks a lot sir!
Sir, I need this dataset. Thank you so much.
can you please provide the link of your spreadsheet
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
Is there any possibility to get the same datasheet which has been used in the video?
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
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.
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 great information
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.
When I try to calculate the sum by category for utilities & Rent I get value 0 as result. Anyone experiencing the same ?
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.
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
Great series sir ji
Incredible
hi, greeting from pakistan, i am doing practice on your provided movie sheet. facing problem as i insert column align with the table, all values in table showing error ? NAME?, it don't undo and finally i had to close the sheet. anyone can guide me.
Thanks
Good video
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
very helpful
sir unique function is not working in Excel 2007, 2013 how i solve it ???
Hoe to get this data set , which is mentioned in above video. Please provide it.
sir im having problem with unique function . this function is not my excel so from where i can get this pls help
If i appy formula then formula bar shows cell name and you apply formula then it shows column name how ?
I have completed BA in Bengali. Can I become a data analyst ??
You can 🎉
DId i use google sheet instead of excel ?
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.
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"
I dont have unique function in my excel and I dont wanna download new version of excel is there any other solution?
Sir ap ms office ka konsa version use krrhy han
23.0
When I create new column named "budget mln" values in previous columns like budget, revenue, etc, shows '#name' errors Kindly assist.
If you would able to provide the data set it will be helpful .
where is the data set for movies?
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
I only see Name with Hashtags and questions mark in the cells that was merged I NEED HELP
I am struggling with communication and presentation skills. Can you please suggest me any youtube channels to improve my skills.
Sir there is only second excel in link not first one
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
How can I get this dataset?
after creating the table my column headers are getting hidden an can only see drop down filters.
please assist
Did you rectify that?
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
can u pls share the movie dataset
Data set not working in my excel
UNIQUE function not working in MS Office 2019. Plz Help