Hints for the homework: 1. You can use a PIVOT Report to do this. Select your tracker data, insert a pivot table. Add date to row label area, amount & boxes to values. Here is a getting started with Pivots video - th-cam.com/video/aofsdpjvK7w/w-d-xo.html 2. Select the data. Go to Sort > Custom Sort in home ribbon. Apply two level sort 3. Go to the end of the data. In the next column type = and select amount column, press / and select boxes column. Next steps after this tutorial: 1) GET MORE BASICS - chandoo.org/wp/excel-basics/ 2) LEARN DATA ANALYSIS WITH EXCEL - th-cam.com/video/v2oNWja7M2E/w-d-xo.html
sir are u sure of last hint that u gave ....because i get value error using this ....while if i do it as =sum(table1[amount])/sum(table1[boxes]) it works instead of =table1[amount]/table1[boxes] what u have to say ????????????
Hey Chandoo, I am just become your fan. If i don't want to use pivot than is there any way to get totals by date? You made awesome videos and deserve million subscribers atleast.
This was really really helpful. The sub-total button didn't work until I converted table to range and I used it to get the amount and boxes. The sort button worked fine
Sir I have a huge problem to sit and listen to videos. But your pacing, your voice, the way you teach and everything has kept me focused. Thank you for the video. ❤
You are a gifted instructor, everything from your demeanor, your voice, your presentation, etc, is just so easy to follow and understand. Please continue the excel course, I learned more in 32 minutes than I have in the previous 10 videos I tried from other content creators. You rock…. Thank you Sir. MORE PLEASE….
@Chandoo. Please continue this series. I hope you begin to cover more advanced aspects of EXCEL. This can include a more beginner to advanced tutorial using Power Pivot and also using EXCEL to compute Simple and Multiple Linear Regression.
Please arrange your excel playlist from beginners to advance level one by one Iam confused which i should see first But your teaching method is beautiful
Hi Rahmatulla.. you can create such a playlist. I won't be doing it as I am not creating them in a sequence. Alternatively, you can join the Excel School program if you want to learn things in one place without distractions. Visit chandoo.org/wp/excel-school-program/
For q1 I used Sumif function Sumif(,,) .... Then drag this Q2. To do double sorting select entire dataset click on advance filter and then apply sorting on diff levels ) Q3.amount /boxes and drag the formula. Is my approach right for the h.w?
I solved it using PivotTable: Step 1) Click on any cell in sales table and insert a PivotTable. Step 2) In the PivotTable, drag Date to Rows. Step 3) In the PivotTable, drag Amount to Values. Step 4) In the PivotTable, drag Boxes Sold to Values. Hope, this helped.
🙏 Thanks Sir God Bless You and Madam. Kindly make outdoor You n Madam videos on real-time project with important topic or concept , we all waiting to see both of you once again.
Best excel class ever.. I just finished my crash course and really excited to finish all the videos from beginner to advance... Thank you so much for this
Thanks a million 😊 I have learnt a lot from this video, and looking forward to learn more and become Pro in Excel. 🤘 I have completed 2 homeworks, 1)Total of amounts and boxes by date. [ Pivot Table] 2) Amount per box. [ =([Amount]/[Boxes sold])] But not able to do the 3rd, i.e. Sort by date and amount. As, the dates and amounts are different, they can't be sorted at a time. Didn't find any option to do it. Please help me with this !
Hi thank you for the video sir, For homework questions I tried a pivot table to solve the first question Is it the correct approach? Q.1 calculate totals by each date - for amount and box columns
Chandoo You are the best man... the way you expound the concepts,seriously very clear and meticulous ....lots of love to you ...please keep up the great work. And a small request from my side can you please upload Business analyst course playlist that will be of great help to me Cheers:) Best wishes -Sathya💙
Dear chandooo I have solved the homework, alsome video, Solve number 1 using pivot table, Puting the dates in rolls and amounts and boxes in the calculate session using sum 2. I used the sorting dialogue box to add two levels of sorting 3. I added a new column and calculated the amount per box by dividing the total amount by the number of boxes. I hope am correct
Chandoo, I tried the 1st Homework using pivot tables. The 2nd Homework, I tried using the sort and filter command, but it's not showing any effect on the table. For the 3rd, I don't know how to approach it.
Thank you so much Chandoo for this very useful session! It's really helpful, I'm going to watch and learn from your other videos! God bless you abundantly!
@Chandoo. Love your videos. Also a complete tutorial from Beginner to Advanced on how to prepare EXCEL Dashboards would be much appreciated. Your videos have helped me so much in learning to love and master EXCEL.
Thanks Sasitheran. I have recently done a masterclass on KPI dashboards. See it here (2+ hours) - th-cam.com/video/kU3SdT8couk/w-d-xo.html You can also join my online class for complete Excel training. Visit chandoo.org/wp/excel-school-program/ for that.
@Chandoo. There is a comment that copies my initial comment on your videos. Something sexual. I reported it but it appears to be still in the comments section. Just letting you know, that I didn’t write that comment😀
When i sort SR NO. Change how i fix this problem? And I watched your beginner (12 years ago one) and this one (intermediate level) video what is the next step?
Lol, if your sales team is adding formulas to their sales sheets, they are just adding more work for the actual analysts on the back-end... Please people, use the least amount of formatting and formulas necessary to present the data... Sincerely, the guy that cleans up your excel mess...
Hello Sir, I need a suggestion. I have a excel workbook with two columns will be filled by users. after they fill the both the columns. cells need to be locked and cannot be edited by the user. I tried many ways its not working. Please suggest me.
Sir I have applied the required currency to the amounts column. But when I add data the currency format is not applied to it. I have to keep selecting and apply the Indian rupee currency to it otherwise its not recognized as currency. What should I do?
Hii sir your study method is very impressive...sir, I want to know how to become a good Data analyst? can I learn through the youtube study material and get a job within 3 months? Is it possible...
Thank you. I already have a few on Power BI. Check out them here 1️⃣ Power BI with a full & short example (10 mins) - th-cam.com/video/6lzdovy1uCw/w-d-xo.html 2️⃣ Power BI Masterclass (2+hours) - th-cam.com/video/mXD0Peot4hY/w-d-xo.html 3️⃣ Power BI Playlist - th-cam.com/video/CJW-Yb4PfZ8/w-d-xo.html
How can beginner perform a pivot table on the provided data if he has not learned and learning should be in chronological order then how the homework assignment can be completed?
Not really. Started slow true but he kept a nice pace afterward. I think if you simultaneously doing it it will be too fast. But other wise just to listen first its a good pace.
Sir I want become a power bi developer please make video on power bi road map... What need to learn to become a power bi developer.. Please suggest anyone...
Hello, I want to learn your Excel course completely as a beginner because I want to grow my career in data analysis sector. Can you please provide the instruction about how to purchase your complete excel course ?
Thank you so much Sir your video is very helpfull for me. I want to be freelancing and there is too much data entry work. I learnt excel sheet for your videos
Hi chandoo ! i completed the homework but got stuck in one situation: when calculating totals by each date - for amount & box columns i calculated the sum by using sumif but the sum is getting repeated for ex: 25 aug is available in 3 rows then the calculated total is getting duplicated in 3 rows for this date . my question is how do i club/merge these repeating totals.?
Hi Chandoo Sir, I have started this tutorial, Homework questions, in that 1st I know how to do manually, like i have sorted according to the dates so i can get those desired result of total box sold and total amount, but using any filters or displaying in charts i am not understanding, and since it is in table format, manually also i am not getting proper result, could you please explain me
![[Full Episode] The Restaurant War Thailand ศึกพ่อค้าซ่าแม่ค้าแซ่บ Episode 2 | 29 ก.ย. 67](http://i.ytimg.com/vi/r3lfLNH6Ke4/mqdefault.jpg)
Chandoo rocks! I know because I teach Excel Basics and I learned a lot from him! Thanks, Chandoo!
💪😁🌴🤙
One of the best channel in TH-cam for Msexcel Tq sir
Thank you Sairam 😀
Hints for the homework:
1. You can use a PIVOT Report to do this. Select your tracker data, insert a pivot table. Add date to row label area, amount & boxes to values. Here is a getting started with Pivots video - th-cam.com/video/aofsdpjvK7w/w-d-xo.html
2. Select the data. Go to Sort > Custom Sort in home ribbon. Apply two level sort
3. Go to the end of the data. In the next column type = and select amount column, press / and select boxes column.
Next steps after this tutorial:
1) GET MORE BASICS - chandoo.org/wp/excel-basics/
2) LEARN DATA ANALYSIS WITH EXCEL - th-cam.com/video/v2oNWja7M2E/w-d-xo.html
sir are u sure of last hint that u gave ....because i get value error using this ....while if i do it as =sum(table1[amount])/sum(table1[boxes]) it works instead of =table1[amount]/table1[boxes]
what u have to say ????????????
Hey Chandoo, I am just become your fan.
If i don't want to use pivot than is there any way to get totals by date?
You made awesome videos and deserve million subscribers atleast.
@@savesoil8549 =[@Amount]/[@[Boxes Sold]]
How to do shorting?
Q2
@@luckygarg9315 use SUMIFS
Dear Sir .....well explained looking forward to completing it, thanks for making it so simple ...
This was really really helpful.
The sub-total button didn't work until I converted table to range and I used it to get the amount and boxes. The sort button worked fine
is there sollution for homework questions given ??
Do you teach privately
hi chandoo!! in amount coloumn , if I entered a very big value like 200000..it is showing ######...why is it so??
If the cell is not big enough to show the values, Excel will show ####. Just widen the column and it will show up.
Sir I have a huge problem to sit and listen to videos. But your pacing, your voice, the way you teach and everything has kept me focused. Thank you for the video. ❤
This is the best source to learn Excel. pls, continue this series. I have recently started watching your videos sir.
Thank you.
You are a gifted instructor, everything from your demeanor, your voice, your presentation, etc, is just so easy to follow and understand. Please continue the excel course, I learned more in 32 minutes than I have in the previous 10 videos I tried from other content creators. You rock…. Thank you Sir. MORE PLEASE….
Wow, thank you!
@Chandoo. Please continue this series. I hope you begin to cover more advanced aspects of EXCEL. This can include a more beginner to advanced tutorial using Power Pivot and also using EXCEL to compute Simple and Multiple Linear Regression.
I have never seen a better ,more clear tutorial for Excel. Thank you for sharing your knowledge.
You are welcome!
Thank you Chandoo,
You are the best Excel teacher I have listened to.
I am almost addicted to your Excel videos now.
Thank you
Thank you Daniel 😀
Please arrange your excel playlist from beginners to advance level one by one
Iam confused which i should see first
But your teaching method is beautiful
Hi Rahmatulla.. you can create such a playlist. I won't be doing it as I am not creating them in a sequence.
Alternatively, you can join the Excel School program if you want to learn things in one place without distractions.
Visit chandoo.org/wp/excel-school-program/
@@chandoo_ I have a problem in joining the class. It says the safari can't open the page. I really want to join your class man.
hey guys someone help me ...whether we should practice this or we should take notes else watching the video is alone enough
For q1 I used Sumif function Sumif(,,) .... Then drag this
Q2. To do double sorting select entire dataset click on advance filter and then apply sorting on diff levels )
Q3.amount /boxes and drag the formula.
Is my approach right for the h.w?
Hi Chandoo! I am new subscriber and already in love with your teachings method! Now,I feel that I am I right direction! Stay blessed!
This video is 2yrs ago and it’s the BEST that has taught me so far! Thank you Chandoo🤗
Nice tutorial
Am having problems with sum (sales(amount).. its showing me #NAME?
tysm .....i already recommended to my circle ....they r glad i did.....its so true there are no students only bad teachers 😅😅😅😅😅😅😅
Sir ,I have watched your maximum videos . Here I request you . Please make video on "hands on peoject " how to use SQL and BI Tools.
can anyone explain question 1?
I solved it using PivotTable:
Step 1) Click on any cell in sales table and insert a PivotTable.
Step 2) In the PivotTable, drag Date to Rows.
Step 3) In the PivotTable, drag Amount to Values.
Step 4) In the PivotTable, drag Boxes Sold to Values.
Hope, this helped.
Halo sir whenever I’m trying to apply formula with =sum in summary it’s showing a value 0 will u plz help me?
I have not come across better teacher than you for teaching Excel skills. I am grateful to you Sir,
let me tell you, you are not a good teacher for a beginner.
Thank you. Please go find a teacher that works for you. I am happy with what I do.
I cant explain chandoo how easily you explain the things in excel marvolus.
Hi chandoo! , Please give me a project only based on excel.
🙏 Thanks Sir
God Bless You and Madam.
Kindly make outdoor You n Madam videos on real-time project with important topic or concept , we all waiting to see both of you once again.
You are welcome Jagat. Thanks for your love and appreciation.
Best excel class ever.. I just finished my crash course and really excited to finish all the videos from beginner to advance... Thank you so much for this
hi, did you purchase the full course?
Thanks a million 😊 I have learnt a lot from this video, and looking forward to learn more and become Pro in Excel. 🤘
I have completed 2 homeworks,
1)Total of amounts and boxes by date.
[ Pivot Table]
2) Amount per box.
[ =([Amount]/[Boxes sold])]
But not able to do the 3rd, i.e. Sort by date and amount.
As, the dates and amounts are different, they can't be sorted at a time. Didn't find any option to do it. Please help me with this !
In the ribbon, go to sort&filter->Custom sort, thereby adding a level you can do sorting to different columns
Why my total and averaged table doesnt automatically update like the chart? Anyone can help
Please give answer to the homework 😢 i am confused
Excellent video like always. Thank you!!
Thanks again!
Hi thank you for the video sir,
For homework questions I tried a pivot table to solve the first question
Is it the correct approach?
Q.1 calculate totals by each date - for amount and box columns
Instead use sumif
i cant underestand second and third task. Please can anyone explain
hi, have anyone done the 3 tasks kindly tell i need guidance. thank you
Thanks Chandoo! Never hurts to review the basics. Always something new to learn :)) Thumbs up!!
YES!!!
Hi Chandoo,
I am in love with the Tutorials, the way you simplify the formulas is amazing. Do you have any Discord where we can post our doubts?
Thanks a lot sir, it really helps !
Chandu sir yadi ho sake to Aap ka all video Hindi me koi Dube kar de 😊
Chandoo You are the best man... the way you expound the concepts,seriously very clear and meticulous ....lots of love to you ...please keep up the great work.
And a small request from my side can you please upload Business analyst course playlist that will be of great help to me
Cheers:)
Best wishes
-Sathya💙
Dear chandooo I have solved the homework, alsome video,
Solve number 1 using pivot table, Puting the dates in rolls and amounts and boxes in the calculate session using sum
2. I used the sorting dialogue box to add two levels of sorting
3. I added a new column and calculated the amount per box by dividing the total amount by the number of boxes. I hope am correct
Chandoo, I tried the 1st Homework using pivot tables.
The 2nd Homework, I tried using the sort and filter command, but it's not showing any effect on the table.
For the 3rd, I don't know how to approach it.
Homework 1: used Sumif() function
homework 2 : Sort option inside Data, selected multiple column to sort
homework 3: =amount/boxes sold
i have gone sparatly through each day by sum function (the hard way hahaha)
i used sumif after reading your comment it is better and practical
After sumif what to type?
Thank you so much Chandoo for this very useful session! It's really helpful, I'm going to watch and learn from your other videos! God bless you abundantly!
@Chandoo. Love your videos. Also a complete tutorial from Beginner to Advanced on how to prepare EXCEL Dashboards would be much appreciated. Your videos have helped me so much in learning to love and master EXCEL.
Thanks Sasitheran. I have recently done a masterclass on KPI dashboards. See it here (2+ hours) - th-cam.com/video/kU3SdT8couk/w-d-xo.html
You can also join my online class for complete Excel training. Visit chandoo.org/wp/excel-school-program/ for that.
@Chandoo. There is a comment that copies my initial comment on your videos. Something sexual. I reported it but it appears to be still in the comments section. Just letting you know, that I didn’t write that comment😀
Not sure about adding per box to the table.
When i sort SR NO. Change how i fix this problem? And I watched your beginner (12 years ago one) and this one (intermediate level) video what is the next step?
Lol, if your sales team is adding formulas to their sales sheets, they are just adding more work for the actual analysts on the back-end...
Please people, use the least amount of formatting and formulas necessary to present the data...
Sincerely, the guy that cleans up your excel mess...
What this trick for sum of boxes and amount don't work for excel 2010? I'm not getting my answer it is automatically changed (cell address)
Hello Sir,
I need a suggestion. I have a excel workbook with two columns will be filled by users. after they fill the both the columns. cells need to be locked and cannot be edited by the user. I tried many ways its not working. Please suggest me.
Sir I have applied the required currency to the amounts column. But when I add data the currency format is not applied to it. I have to keep selecting and apply the Indian rupee currency to it otherwise its not recognized as currency. What should I do?
Hi,with all my greetings. Kindly help me to sort out first 2 questions in the homework page, when ever you get time. Thank you
I've an issue! can't complate this task! Help me more! Thank you
Hii sir your study method is very impressive...sir, I want to know how to become a good Data analyst? can I learn through the youtube study material and get a job within 3 months? Is it possible...
Thank you Chandoo I found a new inspiration to learn things by myself 🤗.
"Just watched the very first video on your channel, and I absolutely loved it! I'm excited to see more!"
Great video. Request you please do similar video with powerbi.
Thank you. I already have a few on Power BI. Check out them here
1️⃣ Power BI with a full & short example (10 mins) - th-cam.com/video/6lzdovy1uCw/w-d-xo.html
2️⃣ Power BI Masterclass (2+hours) - th-cam.com/video/mXD0Peot4hY/w-d-xo.html
3️⃣ Power BI Playlist - th-cam.com/video/CJW-Yb4PfZ8/w-d-xo.html
How can beginner perform a pivot table on the provided data if he has not learned and learning should be in chronological order then how the homework assignment can be completed?
If I use sum formula and I want fill below and rows are 66000 ..how I fill ? Scroll down for selecting below is taking too much time...please suggest
Wow, this is actually fantastic. I have learned a lot today. Thank you Chandoo
Thank you sir
you're explanation was very nice, please upload more and more videos with tips and tricks.
i couldnt understand the homework, should we use Charts for the last one " Add amount per box to the table "?
I need same table of info for practice, how can i get this same date? Can you please provide me this data?
Thank Bhai it is really very helpful, and i really appreccite my friends for suggesting me tih channel.
Hi ,i have entered new details after the table is made but the table does not automatically extend or take data in the same manner...pls help
Dear sir, why didn't do subtotal instead of sum becos there is a filter applied to it.
from 20:48 ,when i entered new data the calculation do not happen automatically...pls help
Hello guys, I am looking for a friend or group who can help me to clear my problems
Hello Sir , I tried to do the word you have give but got confused when we have add date wise total of sum
You started off slow but then went too fast.
Not really. Started slow true but he kept a nice pace afterward. I think if you simultaneously doing it it will be too fast. But other wise just to listen first its a good pace.
Hello sir here is vishal I didn't understand small data 2 and large data 3 in the video; so can I get the clarification sir.
Hi mate
I have finish learn excel and practicing daily, what next should I do to get job as data analysis in excel thanks for advise .
how to calculate total sales by date?
Please share the answers to your questions...This is the best tutorial I have ever gone thru..so please continue with the series !!
Very helpful...I always recommend your channel to every excel learner.
Thanks Nirmal for the love and support. :)
where are next videos link related to excel. someone please share the full course link?
i want to move myself from inter to advance what should i do
Can you please explain how to slove the 3rd question?
Hiya, for some reason, my total boxes come out as 0. Strange...
This is outstandingly beautiful. Thank you for taking your time to do this🙏
Sir
I want become a power bi developer please make video on power bi road map... What need to learn to become a power bi developer.. Please suggest anyone...
Hello,
I want to learn your Excel course completely as a beginner because I want to grow my career in data analysis sector. Can you please provide the instruction about how to purchase your complete excel course ?
Hi Chandoo, the three assignments are difficult for me, can you assist me pleaseeeeee?
Very informative sessions helps a lot to understand, keeps us focussed throughout the sessions
Thank you sir😊😊
new sub here..as im replicating what u do on the spread sheet im having trouble in formula and using the graph..thanks!
Thank you so much Sir your video is very helpfull for me. I want to be freelancing and there is too much data entry work. I learnt excel sheet for your videos
Hi chandoo Thanks for this wonderful video but i have a problem here
Currency is not getting applied in summary sheet. why it is happening ?
Amazing thanks alot❤
Hi Chandoo dear many thanks i was thinking what is microsoft excel today i am learning from yours video really appriciated Waheed UK
Hi chandoo ! i completed the homework but got stuck in one situation: when calculating totals by each date - for amount & box columns i calculated the sum by using sumif but the sum is getting repeated for ex: 25 aug is available in 3 rows then the calculated total is getting duplicated in 3 rows for this date . my question is how do i club/merge these repeating totals.?
How did you calculate bro?
Hi Chandoo Sir,
I have started this tutorial,
Homework questions,
in that 1st I know how to do manually, like i have sorted according to the dates so i can get those desired result of total box sold and total amount,
but using any filters or displaying in charts
i am not understanding,
and since it is in table format, manually also i am not getting proper result, could you please explain me
Sir r u tamilian origin?
Chandoo, you are amazing! Do you have the video to show the answer for the homework problems. I am dying to know the answer. Thanks!!!
This is the best Excel Beginners Video I have ever watched.
Amount per box Calculation by Power Pivot :
- Total Amount
- Total Boxes Sold
- Total Amount / Total Boxes
You have to do it with Power Pivot
And the The Top Five are :
Shakunt Ranadhir ₹ 50
Anbumadi Tummala ₹ 48
Krishna Sreenivasa ₹ 45
Kashiprasad Mahajan ₹ 45
Arav Neela ₹ 44
hello sir.. can we use same thing google spreadsheets also..
How can i create login in your website ? I can not find register option
Hi Sir,
I am planning to buy MS office.. Which one should I buy, does excel 360 useful. Can you please suggest.
Go for Office 365. I have Family plan so we all can use Office on our devices.