Your videos gave me direction and clarity about what I wanted to work for in life. You've no idea but you're a lifesaver, truly an angel to me. I hope you read this and this brings a bright smile to your face as you have brought to mine.💖
NAMASTE CHANDOO SIR🙏. I HAVE COMPLETED YOUR EXCEL DATA ANALYSIS COURSE. THANK YOU VERYMUCH.I HAVE WORKED AS A GOVT HIGH SCHOOL MATH TEACHER AND THEN AS A HEADMATER FOR 24 YEARS AND NOW GOING TO TAKE VRS.I'M PLANNING TO LEARN SOME SOFT SKILLS AS IT WAS MY DREAM TO BECOME A SOFTWARE.I WAS ADVISED BY SOMEONE TO GO FOR "SAP".I'M LEARNING EXCEL NOW. YOU ARE VERY DEDICATED.THANK YOU ONCE AGAIN FOR HELPING ME UNDERSTAND EXCEL BETTER🙏
A good teacher is known by his ability to explain complex concepts in simple language. I believe in my heart that you’re indirectly putting food on many people’s tables by making your tutorials easy to follow, and by extension less frustrating. Thanks, Chandoo. You are a star. I'm not familiar with why and how you generated the KPIs(not sure this is the right word)used in the video. I'm a complete beginner in the world of analysis. Could you recommend a beginner-friendly course that would help me grasp the underlying concepts of Data Analysis, first; then, how to analyze data with Excel? Thanks.
I took the challenge of the Dynamic Country-Level Sales Report. In my case, I assigned a target value to each country and used the IF statement to measure whether the selected country met the target or not and I also integrated it with conditional formatting to show Green when target is met and Red when is met.
Hi Chandoo sir, it's simply a superb video, and the way you explain the concepts is very much understandable to everyone. Your videos are made me learn more concepts. I have taken ur challenge to find the best salesperson by country using formulas and this is the way I did, Step1, I have taken the distinct of salesperson based on every geography and created a separate table like Geography, Salesperson and Amount. Step 2, I have summed up the amount of each salesperson to their every Geography location using sumifs function. Step 3, Taken the distinct of Gegrophy location and created table like Geography, Salesperson and Amount and find out the top 1 amount based on Geography using max and if condition and using the index, match, max and if condition findout the salesperson.
I just want to once again thank you for your time and for this beautiful tutorial. It’s well planned and filled with so many great tips. Thank you, sir!
Awesome, simply awesome video! I just finished watching at one go and I already feel more confident and keen to apply these techniques at work, especially the last one on which product to discontinue. Thank you for your effort and time ...and please make more videos like this.
You can use free excel online or goolge sheets to practice. Purchase office 365 student subscription for discounted rate or get full version from office.com
I've been looking myself for free versions of Excel and the only thing I came up with was Google Sheets. Very similar to Excel but personally? I prefer Excel over Google Sheets. My opinion shouldn't count so much because I'm still learning functions and I'm still at the beginner stage. I've read the google version your documents are better protected over the Excel version (unless you pay for the 365 version) but Google Sheets is a dependable tool to have. I now own Excel the 2019 version and I now find myself using both Google Sheets and Excel 2019 business version. The only thing I truly dislike about owning the application Excel is that if you don't pay for the 365 Excel version you wont have the full benefit of all the functions with is only available on the 365 version. I high recommend just using Google Sheets, its very similar to Excel but for me personally doesn't have all the beautiful features of Excel, hope this helps.
Hi Chandoo, Just did the analysis by myself. It was really helpful. USing your tutorial, I am able to say that my excel skills is good. I just need to understand business terms and metrics so that I can start to understand what data should I fetch from the original dataset.
Such an eye opener! Great teaching style and very detailed content, instantly subscribed in the first few minutes of the video. I love the fact that you not only inform us about Excel tools or functions but give us a glimpse of how you would analyse a dataset yourself. Your perspective is so refreshing and gives us much confidence as learners. These 49 minutes were super helpful and I'm very grateful to have stumbled upon your channel. Thanks for being one my online mentors, Chandoo! Sending good vibes your way!
Very good video! Learned a lot. Also, other things to consider when discontinuing a product is its contribution margin %. The product may have a high cm% but it may not be performing well in terms of profit % because the fixed expenses on a particular geography is high. Sometimes this can still be remediated especially when the product is newly introduced to the market.
Good point. I've noticed Chandoo sometimes explains those details or provides such disclaimer, but other times he simply focuses on teaching us Excel. I take it this is an excel class and not a class on financial concepts.
@@josevaldesv Chandoo asked for suggestions from his viewers and other logic in the comments at the end of the video. He probably is aware of every possible way to slice through this dataset to get the most out of it. However, he gained 3 comments from the 3 of us just because he just left it for discussions in the comments section. He's a smart bloke, knows how to work the youtube algorithm for his benefit too. He's probably done extensive analysis on data from his channel too haha!
Thank you so much sir. I took crazy amount of time on this video due to my laziness(kept pausing and continuing for 3 weeks) But I've learned a lot. Idk if these will be sufficient to start a job But It increased my confidence. I'll keep watching your other videos and practicing (already watched many during pausing this one). Keep up the good work! God Bless you!
Hi Chandoo great content its really helpful CHALLENGE 7: Step 1 :Have a copy the Geography and sales person column then remove the duplicates. Step 2 :Make separate table for each Geography with sales person. Step 3 :Then use =sumifs([Amount],[geography],Column of the geography to be filtered, [Sales person],Column of the Sales person to be filtered. Step 4 :Sort each table largest to smallest and filter with top 1 and bottom 1.
=SUMIFS(data6[Amount],data6[Sales Person],[@[Sales Person]], data6[Geography],[@Geography]) ... You just say gime the specific name (cell reference @) and take account the specific country in that formula i wrote. A sumifs with two criteria the name and the country in a new table as you said with removed duplicates.
Thanks for the great tutorial, just trying to break into analytics and apply it to my current role to get some experience. Rather than using a splicer, I just put product in rows, geography into columns and profit into values and then applied conditional formatting to especially highlight the losses and less profitable products. This way you can also easily see which locations we dont sell some of the products in and which locations we may want to drop existing products in. My short term memory kind of sucks, and using a splicer means you have to kind of remember as you flick through the different countries.
Respected Sir Chandoo, love your method and respect your mentoring. I got a lot that could not be achieved yet. I will continue and do a lot like my Sir "Chandoo".
This is fantastic, thank you so much! I have been using Excel for years and considered myself pretty decent at it, but your videos show me how much I have to learn. Thank you for the great examples!
Thank you so much Respected Sir. No one can tell these important things. You are best explainator I have seen ever. Best Teacher Ever. Thank you so much once again.
Thanks Sir. Your this tutorial is very beneficial to those who know how to use table, pivot tables, formulas , conditional formatting etc. but do not know when to use these all and how to use them efficiently. Thanks a lot Sir for providing us the best data analysis tutorial. I am a beginner , enjoyed and learned a lot .
Is it not helpful for beginners? Because you said ,it is beneficial for those who know how to use table ,pivot tables, formulas etc To es sy phly excel ki basics ,pivot charts ,tables etc ki practice krein r phr es tutorial ko follow krein ??? Plz guide me
This was simply superb to follow and imbibe. It takes a lot to deliver something just as simple yet with clarity and with minimum noise. You have done this Mr. Chandoo. I like your Power BI one too. Thanks
Hi Chandoo! To count the Total Profit at "chapter 9" I created a new column at Data tab called Total Profit =[@Amount]-[@Cost], Then =SUMIFS(data[Total Profit], data[Geography],$E$3). Later I saw how you did :D for the name list I used unique formula. Great Tutorial Thank you very much :)
at 3:30, sir i have first used that "LARGE" function to find the first highest value in the amount section, then I applied v-lookup formula to find the name of sales person that correspond with that amount, the answer was - gigi which had the highest amount score of 16,184
Absolutely superb - I was similar to Wayne but I created a column of names (unique), a drop down list for the countries, a column of total sales and used sumifs to collect total sales for each person. I then just used the drop down list to filter by country. I added a Max cell at the bottom of the values and a xlookup cell to relate this to the list of names, I hope it makes sense!!
Chandoo bhai I'm so happy and grateful that I came across your channel! I'm a finance and accounting student doing my MBA with no work experience in my field :( but watching your videos to increase my knowledge in Excel is a good way to show employers I can do the work required. God bless you!🥰❤
Wow.. I just discovered your RUclips channel just today. I have been following your blogs right from when I started my career. I literally jumped out of joy like a kid at the sight of your channel. 😀👍🏻
One of the best tutorials ever! Top notch explanations, very user-friendly and easy to follow along. The project was great for me, as I needed to dust off a few cobwebs out of Excel, as I haven't used it that much lately. Plus, I was able to learn something new along the way 🙂 You are the man Chandoo!
I am fascinated about computer and to learn data analysis. I am sure to familiarize myself with excel and data entry by just learning from your wealth of experience in this field. Thank you
Chandoo, you have a unique teaching method and clarity of thought. I loved this video and many thanks for sharing it with everyone! May you get lots of success in your work !
Oh my god. There are so many courses on excel online but they are far too stretched out. This one is to the point, clear concise, jampacked with a lot of content as well as very well made!!! Thank you so much for this amazing piece. Would love to see this series continue. This is a boon for students who are trying learn by themselves online.
You are welcome Rishav. Don't forget to practice the concepts you learned. Please watch the other videos in my data analyst playlist and Pivot tables for data analysis playlist. Data analyst playlist - ruclips.net/video/gVr9f1GJdZc/видео.html Pivot tables playlist - ruclips.net/video/Gx9ZVlGkbOk/видео.html I am adding new videos on the channel every week. so check back in sometime for more.
Ho chandoo, this is really awesome. In my opening, we can make a comparative analysis based on previous performance in order to discontinue a product because some products may under perform due to seasonal changes or any ptemporary issues. Again it depends whether we have the historic data.
Man, loved your video! I was struggling with calculations in the PivotTables and looking at this video I found the solution, not to mention all knowledge about data analysis, with such a simple and rich explanation. Thank you and great job!
Im stuck here too....Im working with ecxel 365 online and the 'Add this data to the data model' option doesn't appear. @Chandoo_ Please assist. We need help pls
Wow.. I've reached the part where you can analyze which products need to be sunsetted and i'm now realizing just how useful this tutorial video actually is! But I wish you would have maximized the column headers.. they are cutting out.
Hey Chandoo! Firstly thanks so much for your videos! It's really taken my Excel to a new level. I tried thinking about your challenge to use formula to show the top/bottom salesperson by geography. I think it's quite challenging. Do you have a video on it? Thank you once again! 😁
thank you for your content! easy to follow along and appreciate that you provide the templates for practicing. I've been introducing into Data Analysis and your becoming a good reference into Excel skill development, I will continue to watch your videos
Wow, thank you! This tutorial is awesome on many points of view. I believe, I could even demonstrate some final results as proof of my newly learned Excel skills. As for task 9, I also took product data to check what products are the most profitable in the current country. Also, I decided to make the Top 5 and The Bottom 5 products and visualize this. This task turned out to be a little more complicated than I thought first. I had to find a way to make data dynamic and escape using several filters. But after a few hours of struggling I did that 😁 As for task 10, I would definitely check what products make the least profit. I would also pay attention to those products with the least profit by the country. Maybe it would be a good idea to have a slightly different assortment for different countries.
Hey Chandoo, loved this tutorial. While working along with you I found something interesting in the Profit Analysis part. I used Geography as a slicer and then added No of units to the pivot table. Then sorted the number of units from largest to smallest for each country. I then realized that for most of the countries, we have at least one or two products being sold in high numbers but earn very less profit. For example in Canada the highest profit earning product has only 96 units sold. I know what you are thinking, maybe it is just the cost per unit that is high for these products. But I checked that too the cost per item is not that high to make such a difference (7th highest). So my question is, would it make sense to recommend sales and marketing team of each country to up the number of sales of these profit making products and lower the units similarly for not so profit making products. Maybe they could spend more on advertising the high profit products? Can you share your thoughts on this? Thanks once again.
Thank you chandoo 1st i did the project along with you ( as i am a newbie in excel for data analysis) and now i just did this by myself : ) Thankkkkkk youuuuuuuu
Hi Chandoo, great video...I do have a doubt though. In the dynamic country level report you have the total and average for PROFIT...How did you calculate Total and Average Profits using SUMIFS ..Did you add a profit column to the original data table or is there a way to extract Profit data from the pivot chart we created. Your help would be really appreciated
Thanks for video, It's been great experience learning from you.✌👏😊 Best Sales person by Country using Formulas. I did using Index,Unique,Max,Sumifs and Match function,In column H I am taking unique geography.Below are the formulas I used. Do let me know if you used any other ways. Top Sales Person =INDEX(UNIQUE(Data5[Sales Person]),MATCH(MAX(SUMIFS(Data5[Amount],Data5[Geography],H4,Data5[Sales Person],UNIQUE(Data5[Sales Person]))),SUMIFS(Data5[Amount],Data5[Geography],H4,Data5[Sales Person],UNIQUE(Data5[Sales Person])),0)) Total Amount =MAX(SUMIFS(Data5[Amount],Data5[Geography],H4,Data5[Sales Person],UNIQUE(Data5[Sales Person])))
@@chandoo_ Hey I followed the same solution but instead of using the UNIQUE formula, which doesn't sort the values in any order, I used the SORTBY function so as to give the country column arranged in descending order with the following formula: =SORTBY(UNIQUE(data5[Geography]), MAX(SUMIFS(data5[Amount],data5[Geography],UNIQUE(data5[Geography]),data5[Sales Person],UNIQUE(data5[Sales Person]))),-1) However, the sorting is not happening. Please guide me if the above formula needs some changes or is it completely wrong.
This tutorial compilation is really good, I learn so many new things. The only problem i faced is the model analysis which shows as an option in your pivot window but not in mine, although i was able to add them as calculated fields under Pivot Table Analyze. The only caveat to that is can't assign a format to the values every time i have to keep formatting the values.
I’m super glad, and lucky that i found this amazing channel, how useful this video was for me. I loved your teaching strategy. Seriously, this channel is a treasure. Keep going please.
Thanks #Chando, I have been following your videos for a couple of days and am becoming a fan of yours. You are such a good tutor. Your way of teaching is simple and precise. Thanks again for your valuable time and efforts.
I made some machine learning model today. It just was so heavy for me . I thought i was done for the day and don't wanna do something related to data science And then i saw this vid I'm already 50% done and still enjoying it , there is something soothing about excel and specially on this channel 😂
Thank you, really enjoyed watching the video/lesson! One other factor can be the ability of the Salesperson to price effectively. One other check I would make is to see the profit for each against the Salesperson.
Stuble upon a data analysis video the other day but was discouraged when a Lady said you must know a lot of mathematics to be a data analyst. With this, I think I'm understanding this.
So far I love the tutorial, excellent tutorial. I'm almost 30 minutes in and so far I've learnt so much, thank you. Also, if you could please recommend more videos in reference to learning about becoming a data analyst? I started learning about the certification course fairly recently and I am eager to learn as much as possible, any information you could recommend would greatly be invaluable, thank you once again!!!
You are welcome William. Please watch the other videos in my data analyst playlist and Pivot tables for data analysis playlist. Data analyst playlist - ruclips.net/video/gVr9f1GJdZc/видео.html Pivot tables playlist - ruclips.net/video/Gx9ZVlGkbOk/видео.html I am adding new videos on the channel every week. so check back in sometime for more.
ThanQ v much Chandoo, this is a great selfless service u have done. By this tutorial plenty of people shall benefit greatly both freshers and experienced as well. Your way explaining things is very good. You have explained the difference btw Median & Avg and many more things like that so nicely, just by listening once it fits in the brain & learning happens instantly. Request you to keep your good work cotinuously. God bless you 🙏
I am Impressed, inspired and Thankful, that you have shared with us a gold mine of informative & Practical tips to achieve desirable results in Excel. Regards & Thank from across the Border.
Well explained Chandoo! A quick fix for the formula at 15:39, when typing the formula in D6, use &C6 to fix the column for country. From there you can just drag the formula down or sideways, without typing it all over again for units. Cheers!
Thanks for the video. I had learn much from this video. As you said in last part from this video, how to decide which product should be discontinue? to answer this, first i try to make sort for the 3 worse product in several factor such as by Sales, By Unit, Total Profit, By Profit %, By each country. The worse get 3 for the score, the second get 2 and so on. I also give additional for the 3 worse product if they have the minus profit %. And the outcome is the same as you said Almond Choco is the worse product. So it should be discontinued. I also found that in USA, it has more product that have the minus profit % than any other country. Thanks for the knowledge
GOT AN ERROR ⛔ "CAN'T CHANGE PART OF ARRAY"
Here is a fix 👉 chandoo.org/wp/errors-with-data-analysis-course/
sir how to create cost per unit column
Sir I can't find the link to download the data for practical follow up
How did you bring the median in blank table. It would be good if you showed how to bring it across.
Hello Chandon, I would like to become Data Analyst. Do you have a roadmap to become Data Analyst? I live in USA and working as a JR BA.
16:50 why did you disabled the filter
I've been using Excel for...ages...and I can't tell you how many times I've said "I didn't know you could do that" in this tutorial. Great work!
This is one of the best data analysis tutorials I've ever watched. So easy to follow. You are a great tutor, Chandoo
Very interested mon
@@muhammadislam5367 @maria aduba, Its the best, concise and great.
Ok
agree
🔥🔥🔥🔥🔥
Your videos gave me direction and clarity about what I wanted to work for in life. You've no idea but you're a lifesaver, truly an angel to me. I hope you read this and this brings a bright smile to your face as you have brought to mine.💖
Thanks Roli. I really appreciate this thoughtful comment. It did bring a bright smile to my face 😍
NAMASTE CHANDOO SIR🙏. I HAVE COMPLETED YOUR EXCEL DATA ANALYSIS COURSE. THANK YOU VERYMUCH.I HAVE WORKED AS A GOVT HIGH SCHOOL MATH TEACHER AND THEN AS A HEADMATER FOR 24 YEARS AND NOW GOING TO TAKE VRS.I'M PLANNING TO LEARN SOME SOFT SKILLS AS IT WAS MY DREAM TO BECOME A SOFTWARE.I WAS ADVISED BY SOMEONE TO GO FOR "SAP".I'M LEARNING EXCEL NOW. YOU ARE VERY DEDICATED.THANK YOU ONCE AGAIN FOR HELPING ME UNDERSTAND EXCEL BETTER🙏
you are "Excel Superstar" This is awesome. Thanks for being generous enough to share your knowledge.
A good teacher is known by his ability to explain complex concepts in simple language. I believe in my heart that you’re indirectly putting food on many people’s tables by making your tutorials easy to follow, and by extension less frustrating. Thanks, Chandoo. You are a star.
I'm not familiar with why and how you generated the KPIs(not sure this is the right word)used in the video. I'm a complete beginner in the world of analysis. Could you recommend a beginner-friendly course that would help me grasp the underlying concepts of Data Analysis, first; then, how to analyze data with Excel? Thanks.
this is the first video and from today i am starting the data analytics
me too
It is the most detailed, clear and inspiring review of all the most important and useful functions and tools in Excel I've ever seen. Thanks a lot.
I took the challenge of the Dynamic Country-Level Sales Report. In my case, I assigned a target value to each country and used the IF statement to measure whether the selected country met the target or not and I also integrated it with conditional formatting to show Green when target is met and Red when is met.
Bhai mujhe bhi btao kaise krun mai
Hi Chandoo sir, it's simply a superb video, and the way you explain the concepts is very much understandable to everyone. Your videos are made me learn more concepts. I have taken ur challenge to find the best salesperson by country using formulas and this is the way I did, Step1, I have taken the distinct of salesperson based on every geography and created a separate table like Geography, Salesperson and Amount. Step 2, I have summed up the amount of each salesperson to their every Geography location using sumifs function. Step 3, Taken the distinct of Gegrophy location and created table like Geography, Salesperson and Amount and find out the top 1 amount based on Geography using max and if condition and using the index, match, max and if condition findout the salesperson.
Can you share the formula of index, match, max and if to see how you found the sales person
Systematic and clear like always. Thank you Chandoo. This video is my new Data Analysis Bible.
I just want to once again thank you for your time and for this beautiful tutorial. It’s well planned and filled with so many great tips. Thank you, sir!
It's a pleasure to have a tutor like you chandoo. Your committed work has made it real for me. Congrats 👏
Thank you so much for making me feel like a data scientist within these 49 minutes sir
Awesome, simply awesome video! I just finished watching at one go and I already feel more confident and keen to apply these techniques at work, especially the last one on which product to discontinue. Thank you for your effort and time ...and please make more videos like this.
So true...please make more video like this and real world examples based learning..👍
Hey Aju, can you tell if there is any free version of Excel? Or do we have to take the paid version only?
You can use free excel online or goolge sheets to practice. Purchase office 365 student subscription for discounted rate or get full version from office.com
I've been looking myself for free versions of Excel and the only thing I came up with was Google Sheets. Very similar to Excel but personally? I prefer Excel over Google Sheets. My opinion shouldn't count so much because I'm still learning functions and I'm still at the beginner stage. I've read the google version your documents are better protected over the Excel version (unless you pay for the 365 version) but Google Sheets is a dependable tool to have. I now own Excel the 2019 version and I now find myself using both Google Sheets and Excel 2019 business version. The only thing I truly dislike about owning the application Excel is that if you don't pay for the 365 Excel version you wont have the full benefit of all the functions with is only available on the 365 version. I high recommend just using Google Sheets, its very similar to Excel but for me personally doesn't have all the beautiful features of Excel, hope this helps.
Thank you Chandoo- love your teaching style, it is so ‘chill’ and full of ‘gold nugget’ tricks!
Thanks Irene...
@@chandoo_ VLookup formula is not working in my case.
@@ankurtripathi8305 .
Hey, I am having problem while sorting data in sales analysis part. It shows can't change the array. Please help 🥺
@@chandoo_ I want to give you data can you solved it. I can not do this.
Hi Chandoo, Just did the analysis by myself. It was really helpful. USing your tutorial, I am able to say that my excel skills is good. I just need to understand business terms and metrics so that I can start to understand what data should I fetch from the original dataset.
Such an eye opener! Great teaching style and very detailed content, instantly subscribed in the first few minutes of the video. I love the fact that you not only inform us about Excel tools or functions but give us a glimpse of how you would analyse a dataset yourself. Your perspective is so refreshing and gives us much confidence as learners. These 49 minutes were super helpful and I'm very grateful to have stumbled upon your channel. Thanks for being one my online mentors, Chandoo! Sending good vibes your way!
same here
Very good video! Learned a lot. Also, other things to consider when discontinuing a product is its contribution margin %. The product may have a high cm% but it may not be performing well in terms of profit % because the fixed expenses on a particular geography is high. Sometimes this can still be remediated especially when the product is newly introduced to the market.
Good point. I've noticed Chandoo sometimes explains those details or provides such disclaimer, but other times he simply focuses on teaching us Excel. I take it this is an excel class and not a class on financial concepts.
@@josevaldesv Chandoo asked for suggestions from his viewers and other logic in the comments at the end of the video. He probably is aware of every possible way to slice through this dataset to get the most out of it. However, he gained 3 comments from the 3 of us just because he just left it for discussions in the comments section. He's a smart bloke, knows how to work the youtube algorithm for his benefit too. He's probably done extensive analysis on data from his channel too haha!
Thank you so much sir. I took crazy amount of time on this video due to my laziness(kept pausing and continuing for 3 weeks) But I've learned a lot. Idk if these will be sufficient to start a job But It increased my confidence. I'll keep watching your other videos and practicing (already watched many during pausing this one). Keep up the good work! God Bless you!
Hi Chandoo great content its really helpful
CHALLENGE 7:
Step 1 :Have a copy the Geography and sales person column then remove the duplicates.
Step 2 :Make separate table for each Geography with sales person.
Step 3 :Then use =sumifs([Amount],[geography],Column of the geography to be filtered, [Sales person],Column of the Sales person to be filtered.
Step 4 :Sort each table largest to smallest and filter with top 1 and bottom 1.
Step: 1Do I need to remove the duplicates for Geography & Sales person columns both?
Step: 2 Separate table to be made manually?
Kindly help!!!!!
during this,i have a problem, during sorting i am getting message, u cant change it is part of an array, what should i do?
@@SurajSingh-hp7pd bro you should use cpoy of geographical data instead of using unique
=SUMIFS(data6[Amount],data6[Sales Person],[@[Sales Person]], data6[Geography],[@Geography]) ... You just say gime the specific name (cell reference @) and take account the specific country in that formula i wrote. A sumifs with two criteria the name and the country in a new table as you said with removed duplicates.
just use sumifs and indexsmatch function
I think that I'm quite advanced with excel, but still learned some new tricks from your video. Thanks!
Thanks for the great tutorial, just trying to break into analytics and apply it to my current role to get some experience. Rather than using a splicer, I just put product in rows, geography into columns and profit into values and then applied conditional formatting to especially highlight the losses and less profitable products. This way you can also easily see which locations we dont sell some of the products in and which locations we may want to drop existing products in. My short term memory kind of sucks, and using a splicer means you have to kind of remember as you flick through the different countries.
Why is this tutorial easy to understand? Even a newborn baby could become an Excel expert after watching this video.😍
Respected Sir Chandoo, love your method and respect your mentoring. I got a lot that could not be achieved yet. I will continue and do a lot like my Sir "Chandoo".
i came here for #3, sales by country, and got so much more that i can use in my day to day! thank you so much!
This is fantastic, thank you so much! I have been using Excel for years and considered myself pretty decent at it, but your videos show me how much I have to learn. Thank you for the great examples!
Thank you so much Respected Sir. No one can tell these important things.
You are best explainator I have seen ever.
Best Teacher Ever.
Thank you so much once again.
Hi Chandoo- I love your teaching style. you are indeed a great teacher. thanks a lot. I will keep following you online
Just venturing into Data Analysis... Your channel is my sure plug!. Simple, Clear & Precise. Thanks for always doing a wonderful job
My pleasure!
How's the journey been? Just learning now.
Thanks Sir. Your this tutorial is very beneficial to those who know how to use table, pivot tables, formulas , conditional formatting etc. but do not know when to use these all and how to use them efficiently. Thanks a lot Sir for providing us the best data analysis tutorial. I am a beginner , enjoyed and learned a lot .
Is it not helpful for beginners?
Because you said ,it is beneficial for those who know how to use table ,pivot tables, formulas etc
To es sy phly excel ki basics ,pivot charts ,tables etc ki practice krein r phr es tutorial ko follow krein ???
Plz guide me
I suggest watching this tutorial to get started with Excel - ruclips.net/video/F7aPazuS8QY/видео.html
This was simply superb to follow and imbibe. It takes a lot to deliver something just as simple yet with clarity and with minimum noise. You have done this Mr. Chandoo. I like your Power BI one too. Thanks
Hi Chandoo! To count the Total Profit at "chapter 9" I created a new column at Data tab called Total Profit =[@Amount]-[@Cost], Then =SUMIFS(data[Total Profit], data[Geography],$E$3). Later I saw how you did :D for the name list I used unique formula. Great Tutorial Thank you very much :)
at 3:30, sir i have first used that "LARGE" function to find the first highest value in the amount section, then I applied v-lookup formula to find the name of sales person that correspond with that amount, the answer was - gigi which had the highest amount score of 16,184
This is one of the best data analysis tutorials I've ever watched. So easy to follow. You are a great tutor, Chandoo ji
Sir, you are just a treat to watch. I have never enjoyed learning something technical this much before, best wishes may you get more growth.
You are most welcome Sameer 😀
Lots of love
My grandpa used to learn from your blogs and videos,
Now I'm learning from you.
Good bless you sir.
Wow.. I am going to cherish this comment 😀
PS: Say hello to your Grandpa.
Absolutely superb - I was similar to Wayne but I created a column of names (unique), a drop down list for the countries, a column of total sales and used sumifs to collect total sales for each person. I then just used the drop down list to filter by country. I added a Max cell at the bottom of the values and a xlookup cell to relate this to the list of names, I hope it makes sense!!
I have followed the same approach but I'm having trouble in using MAX formula. XLOOKUP is not available in my excel.
I’ve really learnt so much from here which I didn’t learn in the Google data analytics course
Chandoo bhai I'm so happy and grateful that I came across your channel! I'm a finance and accounting student doing my MBA with no work experience in my field :( but watching your videos to increase my knowledge in Excel is a good way to show employers I can do the work required. God bless you!🥰❤
Brother mereko excel mai kuch nhi aata? Kaha se dekhna shuru kru?
You are a great man
Just imagine what you are giving us free of charge.
God bless you abundantly.
Thank you for all your hard work! It’s a high quality educational content compacted with useful information. Really appreciate it!
Wow.. I just discovered your RUclips channel just today. I have been following your blogs right from when I started my career. I literally jumped out of joy like a kid at the sight of your channel. 😀👍🏻
Thank you Krithika... So awesome to have you here 😀
One of the best tutorials ever! Top notch explanations, very user-friendly and easy to follow along. The project was great for me, as I needed to dust off a few cobwebs out of Excel, as I haven't used it that much lately. Plus, I was able to learn something new along the way 🙂 You are the man Chandoo!
I am fascinated about computer and to learn data analysis. I am sure to familiarize myself with excel and data entry by just learning from your wealth of experience in this field. Thank you
Chandoo, you have a unique teaching method and clarity of thought. I loved this video and many thanks for sharing it with everyone! May you get lots of success in your work !
Thanks Rajiv for that lovely testimonial. 😀
@@chandoo_ hello, sir. if I want to count males and females by creating data table. what should be the formula?
@@humanandlifefacts9217 you can use "countif"
Oh my god. There are so many courses on excel online but they are far too stretched out. This one is to the point, clear concise, jampacked with a lot of content as well as very well made!!!
Thank you so much for this amazing piece. Would love to see this series continue. This is a boon for students who are trying learn by themselves online.
You are welcome Rishav. Don't forget to practice the concepts you learned.
Please watch the other videos in my data analyst playlist and Pivot tables for data analysis playlist.
Data analyst playlist - ruclips.net/video/gVr9f1GJdZc/видео.html
Pivot tables playlist - ruclips.net/video/Gx9ZVlGkbOk/видео.html
I am adding new videos on the channel every week. so check back in sometime for more.
Ho chandoo, this is really awesome. In my opening, we can make a comparative analysis based on previous performance in order to discontinue a product because some products may under perform due to seasonal changes or any ptemporary issues. Again it depends whether we have the historic data.
Incredible. I just watched the whole video now and you are an amazing teacher. Thank you 🙏🏽
As usual, so many gems in this video! Thanks, Chandoo!
Man, loved your video! I was struggling with calculations in the PivotTables and looking at this video I found the solution, not to mention all knowledge about data analysis, with such a simple and rich explanation. Thank you and great job!
Im stuck here too....Im working with ecxel 365 online and the 'Add this data to the data model' option doesn't appear. @Chandoo_ Please assist. We need help pls
Wow! I learned more actions during this tutorial than taking the google analytics course. Thank you!
Wow.. I've reached the part where you can analyze which products need to be sunsetted and i'm now realizing just how useful this tutorial video actually is! But I wish you would have maximized the column headers.. they are cutting out.
Hey Chandoo! Firstly thanks so much for your videos! It's really taken my Excel to a new level. I tried thinking about your challenge to use formula to show the top/bottom salesperson by geography. I think it's quite challenging. Do you have a video on it? Thank you once again! 😁
Good to hear that Titya. You can read other's comments to get some ideas or even get solutions for the problem. All the best.
Hi Titya, I guess you could use the sumifs formula and the sort largest to smallest or smallest to largest.
This content is very useful and can be recommended to those who had lost in the ocean of data analysis.
thank you for your content! easy to follow along and appreciate that you provide the templates for practicing. I've been introducing into Data Analysis and your becoming a good reference into Excel skill development, I will continue to watch your videos
Wow, thank you! This tutorial is awesome on many points of view. I believe, I could even demonstrate some final results as proof of my newly learned Excel skills.
As for task 9, I also took product data to check what products are the most profitable in the current country. Also, I decided to make the Top 5 and The Bottom 5 products and visualize this. This task turned out to be a little more complicated than I thought first. I had to find a way to make data dynamic and escape using several filters. But after a few hours of struggling I did that 😁
As for task 10, I would definitely check what products make the least profit. I would also pay attention to those products with the least profit by the country. Maybe it would be a good idea to have a slightly different assortment for different countries.
Hey Chandoo, loved this tutorial. While working along with you I found something interesting in the Profit Analysis part. I used Geography as a slicer and then added No of units to the pivot table. Then sorted the number of units from largest to smallest for each country. I then realized that for most of the countries, we have at least one or two products being sold in high numbers but earn very less profit. For example in Canada the highest profit earning product has only 96 units sold.
I know what you are thinking, maybe it is just the cost per unit that is high for these products. But I checked that too the cost per item is not that high to make such a difference (7th highest).
So my question is, would it make sense to recommend sales and marketing team of each country to up the number of sales of these profit making products and lower the units similarly for not so profit making products. Maybe they could spend more on advertising the high profit products?
Can you share your thoughts on this?
Thanks once again.
Very insightful
Thank you chandoo
1st i did the project along with you ( as i am a newbie in excel for data analysis)
and now i just did this by myself : )
Thankkkkkk youuuuuuuu
Hi Chandoo, great video...I do have a doubt though. In the dynamic country level report you have the total and average for PROFIT...How did you calculate Total and Average Profits using SUMIFS ..Did you add a profit column to the original data table or is there a way to extract Profit data from the pivot chart we created. Your help would be really appreciated
I need to know the same thing. How to aad profit and quantity in Dynamic country level sales report.
A playlist or series of videos like this would be amazing. Thanks for the video!
Here is one. ruclips.net/video/v2oNWja7M2E/видео.html
You can see more here - ruclips.net/channel/UC8uU_wruBMHeeRma49dtZKAplaylists
Thanks for video, It's been great experience learning from you.✌👏😊
Best Sales person by Country using Formulas.
I did using Index,Unique,Max,Sumifs and Match function,In column H I am taking unique geography.Below are the formulas I used.
Do let me know if you used any other ways.
Top Sales Person
=INDEX(UNIQUE(Data5[Sales Person]),MATCH(MAX(SUMIFS(Data5[Amount],Data5[Geography],H4,Data5[Sales Person],UNIQUE(Data5[Sales Person]))),SUMIFS(Data5[Amount],Data5[Geography],H4,Data5[Sales Person],UNIQUE(Data5[Sales Person])),0))
Total Amount
=MAX(SUMIFS(Data5[Amount],Data5[Geography],H4,Data5[Sales Person],UNIQUE(Data5[Sales Person])))
Good ones Akhilesh...
@@chandoo_ Hey I followed the same solution but instead of using the UNIQUE formula, which doesn't sort the values in any order, I used the SORTBY function so as to give the country column arranged in descending order with the following formula:
=SORTBY(UNIQUE(data5[Geography]), MAX(SUMIFS(data5[Amount],data5[Geography],UNIQUE(data5[Geography]),data5[Sales Person],UNIQUE(data5[Sales Person]))),-1)
However, the sorting is not happening. Please guide me if the above formula needs some changes or is it completely wrong.
This tutorial compilation is really good, I learn so many new things. The only problem i faced is the model analysis which shows as an option in your pivot window but not in mine, although i was able to add them as calculated fields under Pivot Table Analyze. The only caveat to that is can't assign a format to the values every time i have to keep formatting the values.
This is one of the best tutorials I’ve ever watched on youtube!
wow, what a video. I am really impressed by your teaching style. you have covered some of the important concepts. Hats off!
One of the best videos I have watched so far about data analysis. Thank you so much!
I’m super glad, and lucky that i found this amazing channel, how useful this video was for me. I loved your teaching strategy. Seriously, this channel is a treasure. Keep going please.
Welcome aboard!
Thank you so much for these, I feel like an Excel pro after a day of watching your videos!
U r master of Excel....how easily u have done the data analysis on a set of data.... thanks for sharing ur knowledge
Imagine when you are feeling helpless and this video shows up. Thanks a bunch
I am so glad I could help. All the best Hasib.
@@chandoo_ yeah I am grateful as well
Thanks #Chando, I have been following your videos for a couple of days and am becoming a fan of yours. You are such a good tutor. Your way of teaching is simple and precise. Thanks again for your valuable time and efforts.
Best ever tutorial. Thank you so much Sir for your efforts and sharing your knowledge with us in order to grow in our career. You are Great Sir
Thanks! You have helped me take my new position from analog to digital!
Wow.. congratulations on that Lisa. Thank you so much for the super 😊
I made some machine learning model today. It just was so heavy for me . I thought i was done for the day and don't wanna do something related to data science
And then i saw this vid I'm already 50% done and still enjoying it , there is something soothing about excel and specially on this channel 😂
Thank you, really enjoyed watching the video/lesson! One other factor can be the ability of the Salesperson to price effectively. One other check I would make is to see the profit for each against the Salesperson.
33:00 for maximum sales men per country use =maxifs(amount, "geography","geography range") then use conditional formatting with unique values
Stuble upon a data analysis video the other day but was discouraged when a Lady said you must know a lot of mathematics to be a data analyst. With this, I think I'm understanding this.
You are a blessing for aspiring data analysts. Thank you so much for these videos - they are very easy to understand and follow.
You're very welcome!
So far I love the tutorial, excellent tutorial. I'm almost 30 minutes in and so far I've learnt so much, thank you. Also, if you could please recommend more videos in reference to learning about becoming a data analyst? I started learning about the certification course fairly recently and I am eager to learn as much as possible, any information you could recommend would greatly be invaluable, thank you once again!!!
You are welcome William. Please watch the other videos in my data analyst playlist and Pivot tables for data analysis playlist.
Data analyst playlist - ruclips.net/video/gVr9f1GJdZc/видео.html
Pivot tables playlist - ruclips.net/video/Gx9ZVlGkbOk/видео.html
I am adding new videos on the channel every week. so check back in sometime for more.
thanks chandoo
ThanQ v much Chandoo, this is a great selfless service u have done. By this tutorial plenty of people shall benefit greatly both freshers and experienced as well. Your way explaining things is very good. You have explained the difference btw Median & Avg and many more things like that so nicely, just by listening once it fits in the brain & learning happens instantly. Request you to keep your good work cotinuously. God bless you 🙏
You are awesome. Really, I have hardly seen this type of teaching.
Thank you
I have not found such an engaging video regarding excel anywhere!
Best tutorial Mr chandoo 👍🏼👍🏼👍🏼👍🏼👍🏼👍🏼 thank-you for educating us. Appreciate it
MR.CHANDOO THIS IS SUCH A BLESSING FOR ME THANK YO SO MUCH (FROM ANDHRA) THANK YOU SO MUCH 🤩
Following you since early excel days. Glad to have found you on RUclips. Thanks bro
Bro, I love this channel!
Thanks Persaud 😍 Welcome aboard.
Hi, before deciding on product to drop I would also look at R12 plus trend after stripping out non recurring price drops or promotions🙂
I am Impressed, inspired and Thankful, that you have shared with us a gold mine of informative & Practical tips to achieve desirable results in Excel.
Regards & Thank from across the Border.
Just got started,and am so much interested in this,I wish to get the best from here
Learned new ways of analyzing the data. Thank you
Well explained Chandoo!
A quick fix for the formula at 15:39, when typing the formula in D6, use &C6 to fix the column for country. From there you can just drag the formula down or sideways, without typing it all over again for units. Cheers!
Thank you, that was very comprehensive for a beginner. I’ll look up more of your videos on it.
You are toooo good in data analysis. I used to get headache looking at 70k column x 40 row data...now got an idea...let me try
Sir, I want analyze the previous month and current month salary of employees.. Please upload the video relating to that.
Thank you
Thank you so much, Chandoo! This was incredibly helpful and informative. I am looking forward to watching more of your tutorials.
Amazing tutorial..... Although I understand most of it but you dumbed down all the things which is just great! Kudos!
Thanks for the video. I had learn much from this video.
As you said in last part from this video, how to decide which product should be discontinue? to answer this, first i try to make sort for the 3 worse product in several factor such as by Sales, By Unit, Total Profit, By Profit %, By each country. The worse get 3 for the score, the second get 2 and so on. I also give additional for the 3 worse product if they have the minus profit %.
And the outcome is the same as you said Almond Choco is the worse product. So it should be discontinued. I also found that in USA, it has more product that have the minus profit % than any other country.
Thanks for the knowledge
thank you for this beautiful It is very useful for learners', well-planned, key skills of excel.
the video is clear and efficient , i learn so much.
You' re the best and amazing teacher
Thanks for sharing.