Sir we are learning many things from your video however i have to know 2 things. 1st i have big data with approx 100 items and 150 sales areas so i want to make pivot table bar chart with scroll button and spin button. 2nd I have to see 1 item sales % in whole sales of stationary shop. example my total sales is of Rs100 for all items including of pen sale of 15 Rs. i will select pen to know the sale data of pen. this show pen sale data. but i want to know when i select pen it also shows how much % of sale of this pen is in total sale of 100. coz when i select pen it shows 100% in pie chart pen sale which i dont want. Show less Sir we are learning many things from your video however i have to know 2 things. 1st i have big data with approx 100 items and 150 sales areas so i want to make pivot table bar chart with scroll button and spin button. 2nd I have to see 1 item sales % in whole sales of stationary shop. example my total sales is of Rs100 for all items including of pen sale of 15 Rs. i will select pen to know the sale data of pen. this show pen sale data. but i want to know when i select pen it also shows how much % of sale of this pen is in total sale of 100. coz when i select pen it shows 100% in pie chart pen sale which i dont want.
At 4:28, how would you take the Total of the Top Ten as a percentage of your Grand Total for all states? Would you just reference off of the [unfiltered] Master Table or is there a better way?
BC there are competing software alternatives out there. I interned at a facility that knew they needed a Data Analyst. They spent so much time and money having a Tableau account no one knew how to use. The DA was hired, I had a conversation about a small data project that I was using Power Query for and uploaded to Power BI for visuals. She looked at me with so much confusion bc she said she didn't use Excel much, she used Python, wasn't familiar with Excel PQ either. I merely asked the VP why the company was spending much more with Tableau instead of just using Power Query/BI. Understanding they could get the same results at a cheaper price, their realization was obvious. Of course each have their nuances, but how DAs are trained and what platforms are used, is a matter of competition. Having multiple knowledge application of all these platforms and understanding their differences can save companies time and money. It also requires continuous learning as the competition continues to provide updates too.
I wish you were a professor at an Undergrad Business school. These things are 100% more important than anything I learned in school. After being in the working world for + 15 yrs techy people are great in finance - not the pure finance geeks. Techies make the best files, macros and excel models and everyone deems them the "gods" of finance.
"Kenji, thank you so much Your explanation was exceptionally clear, and it made it much easier for me to understand the topic. I appreciate your help."
Best mentor ever for working professional....thank you for sharing valuable information...most effective part is you cover topic in short time.Kudos to you
I love the "Report Filter" functionality - I only have an issue; I would need to keep the Conditional Formatting created in the original Pivot (i am using Icons to signal visually if a course is complete, in progress or only enrolled) - is it possible?
In the section with the dynamic pivot table chart, where you are filtering by product, how do you show a chart where you can show total units sold by price range for the different products (i.e., not the sum of both women's + men's apparel, but two different bars showing the units sold in each price range for these different product types)?
These are the best videos I have seen so far, thank you. I have one question though, does anyone knows how to permanently fix the dimensions of the Pivot table and Chart that they don't resize and/or move during filtrations? Especially their width and if possible their heights? Thank you.
The grouping units feature redundant endpoints, e.g. 40, 60, 80, etc. appear twice. Thus the viewer can't know if a $40.00 entry has been grouped in the 20-40 or the 40-60 bin. This suggests a round-off issue in which a value such as $40.50 may have in effect fallen between two bins, which work with integers.
Nicely explained Kenji, been using excel a long time, always more changes on new updates. Always learning, like a lot of people. Just came across your channel by chance and now subscribed too. Thank you!
Use Case: I have a pivot table connected to splicers. The pivot table has 20 rows of data and multiple columns. The splicer filters between Fiscal Quarters. Is there a way to set up excel or pivots to not truncate the rows I apply a filter? For example...I have 20 rows when in Q1, but when I apply the filter for Q2 it goes down to 15 rows...hiding rows without data. Can I set up the pivot to keep the 20 rows and show rows without data as blank?
hey sorry but I wanna ask something at 0:35 how can you change the header automatically from alphabets to text like that? I would be gratefully happy if you can explain it tho, cause it looks so useful😆
I didn´t understood well the region part and the cells arrangement , from who come that layer for ? What is the sheet for , for sales because I have no idea about it.
I subscribed ur channel couple of days ago and loved the way u explain since then i have been watching ur videos…. Can u please make an detailed advance level file on conditional formatting plz and if u have make one can u plz share the link thanks and u r doing a great job.
Great video as always with lots of handy tips. But at formatting I have a question. I guess it is simple, but why the accounting format and not just the financial formatting?
hello, thanks for the information, query: how can I select in a drill down the number of columns that I want to show in the new sheet. If the base data has many columns, perhaps not all of them are of interest in a drill down, thank you very much in advance
Dear Kenji, can you share ideas in which ways we can do product analysis of a Company. We have many products and our Management wants to know which product we should continue and which one to discontinue selling. How can we make this decision. Please share your thoughts.
How can we update automatically pivot tables when we add some datas inside of our source table? I don’t wanna use refresh it every minutes or open the workbook options. May you help me about this question?
Kenji, when I refresh the pivot table The field filter disappears and the data for all years are displayed, Can you help trouble shoot this type of error? Thanks, Ed
If yu are business analyst with that vocabulary than I am the president of US. The stupidity have no limits. But you can talk anyway, you can copy-paste like a parrot everything you see-.Remeber that you are mental ill.
I HAVE A PIVOT TABLE. IN THIS TABLE IN THE TOP ROW & COLUMN 1 IS THE DATE RANGE AND I WANT TO USE THIS DATE RANGE COMMAND WITH VBA USER FORM. WHEN IN USER FORM I CHANGE THE DATE AND CLICK ON SHOW IN USER FORM THEN PIVOT TABLE TOP ROW & COLUMN 1 IS THE DATE RANGE IS AUTOMATIC CHANGE AND UPDATE WITH DATE DATA. IF IS IT POSSIBLE ??? PLEASE RESPONSE ME. AND MAKE A VIDEO ON IT. THANKS (AMARENDRA KUMAR)
Just one doubt: When grouping price ranges within intervals, for those products that cost exactly $40,00, would go on to the 20-40 or the 40-60 interval? It would be useful to be able to do something like 21-40 and 41-60
I've got no idea, but if I had to guess since we started from 0 then maybe it's 0.00-19.99 but I really need to double check. Did you find the solution?
Great course as always, almost 100k congrats for this whole entrepreneurial project that seems to thrive (Ytb channel, Excel course you created etc). If you have the knowledge for that, could you please do the same format video for VBA ? I think it could help almost as many people as for Excel.
Could someone explain to me how to do it, to the value to be changed in all pivot table column, since my excel does it only for the cell chosen (1:14) :/ Of course, I know it can be done by choosing all the area, but I would like to fix it that it orks the same as in the video.
✅To learn more, check out our Excel Course here: www.careerprinciples.com/courses/excel-for-business-finance
Sir we are learning many things from your video however i have to know 2 things. 1st i have big data with approx 100 items and 150 sales areas so i want to make pivot table bar chart with scroll button and spin button. 2nd I have to see 1 item sales % in whole sales of stationary shop. example my total sales is of Rs100 for all items including of pen sale of 15 Rs. i will select pen to know the sale data of pen. this show pen sale data. but i want to know when i select pen it also shows how much % of sale of this pen is in total sale of 100. coz when i select pen it shows 100% in pie chart pen sale which i dont want.
Show less
Sir we are learning many things from your video however i have to know 2 things. 1st i have big data with approx 100 items and 150 sales areas so i want to make pivot table bar chart with scroll button and spin button. 2nd I have to see 1 item sales % in whole sales of stationary shop. example my total sales is of Rs100 for all items including of pen sale of 15 Rs. i will select pen to know the sale data of pen. this show pen sale data. but i want to know when i select pen it also shows how much % of sale of this pen is in total sale of 100. coz when i select pen it shows 100% in pie chart pen sale which i dont want.
At 4:28, how would you take the Total of the Top Ten as a percentage of your Grand Total for all states?
Would you just reference off of the [unfiltered] Master Table or is there a better way?
I'm now crossing to that "intermediate excel" fuzzy zone and this video just made a LOT of things clearer. Thank you.
Great to hear Aaron, thank you!
Man your explanation and editing is so clean that i got lost and distracted into thinking how clean and smooth a video can be like that.
Why do people think excel is no longer useful. It is so dynamic. Like this.
Who told that? 100% of the businesses corporates use excel as a key software till date
I agree with you. If people take the time to learn they can get twice the work done in half the time!
It is people who don't know how to use it or bother to spend the time asking "can excel...."
Because there is PowerBI - there you can crossfilter between tables. Excel is not dynamic, it’s just a calculation tool
BC there are competing software alternatives out there. I interned at a facility that knew they needed a Data Analyst. They spent so much time and money having a Tableau account no one knew how to use.
The DA was hired, I had a conversation about a small data project that I was using Power Query for and uploaded to Power BI for visuals.
She looked at me with so much confusion bc she said she didn't use Excel much, she used Python, wasn't familiar with Excel PQ either.
I merely asked the VP why the company was spending much more with Tableau instead of just using Power Query/BI. Understanding they could get the same results at a cheaper price, their realization was obvious.
Of course each have their nuances, but how DAs are trained and what platforms are used, is a matter of competition.
Having multiple knowledge application of all these platforms and understanding their differences can save companies time and money. It also requires continuous learning as the competition continues to provide updates too.
I wish you were a professor at an Undergrad Business school. These things are 100% more important than anything I learned in school. After being in the working world for + 15 yrs techy people are great in finance - not the pure finance geeks. Techies make the best files, macros and excel models and everyone deems them the "gods" of finance.
"Kenji, thank you so much Your explanation was exceptionally clear, and it made it much easier for me to understand the topic. I appreciate your help."
preparing for an excel case interview, find this very helpful! thank you!
Timelines button will really help in for day to day work ..superb
I use excel a lot in work. Your example questions are very useful. Thank you so much!!
I had no idea about the timeline feature. Thank you!
Kenji is really doing absolute great🤗
Real word example easy to implement in XL PT. Thanks for unhidding this is great way.
thanks Kenji very useful subject, i just wanna clarify, why my option panel to show Report filter pages is not active?
You are just an excel genius. wow!
Pivot tables are lifesavers
Agreed! Thanks for watching
Good info Kenji. I learned something new. Thanks!
Great to hear! Thanks for watching
This is once of the best places to learn about basic-advanced level Excel concepts. Thank you Kenji..
Best mentor ever for working professional....thank you for sharing valuable information...most effective part is you cover topic in short time.Kudos to you
Awesome
Thanks!! For making the video scenario based rather than simple explanation… n yes Ur accent makes it more engaging..
Glad you liked it! Hahah love to hear that about my accent XD
The final tip was really useful. Thanks
Excellent presentation, i have learnt a lot from your channel.
best regards from Egypt
Very much informative, like your way of explanation in depth on each individual topics, which helps us understand very clearly.
Really appreciate your effort.Thanks you so much!
Wooow i am new to pivot tables and this is excellent
Excellent . Crisp and clear explanation. Thanks
Amazing, filter to sheets incredible, every day is a school day☘️❤️
Thank you Kenji ! you are the best :)
keep the good work up! These vids are really nice for Finance majors to learn new skills in Excel
Great to hear Kevin than you!
Last tip is fire. Thanks
Glad it was helpful!
Wow!! I needed that video, thankss kenjii
Thank you… clearly explained
I love the "Report Filter"
functionality - I only have an issue; I would need to keep the Conditional Formatting created in the original Pivot (i am using Icons to signal visually if a course is complete, in progress or only enrolled) - is it possible?
Never knew about the timeline, I'd have just used a slicer, but that's really good to know.
It is user view for time waste, personas sales and what time line sheet shall I make ?
In the section with the dynamic pivot table chart, where you are filtering by product, how do you show a chart where you can show total units sold by price range for the different products (i.e., not the sum of both women's + men's apparel, but two different bars showing the units sold in each price range for these different product types)?
Thank u for ur presentation. I am requesting u to please increase the zoom so that visibility would be clear and slightly explain in slow motion.
Some great tricks!!!
Cheers!
Well done! Wouldn't call those operations advanced though. Going to browse for more videos!
Wow at last a soft tutorial I can follow! you sir are a genius
Why do you select the [Accounting] format over [Currency]?
These are the best videos I have seen so far, thank you. I have one question though, does anyone knows how to permanently fix the dimensions of the Pivot table and Chart that they don't resize and/or move during filtrations? Especially their width and if possible their heights? Thank you.
Learnt a lot from this video
The grouping units feature redundant endpoints, e.g. 40, 60, 80, etc. appear twice. Thus the viewer can't know if a $40.00 entry has been grouped in the 20-40 or the 40-60 bin. This suggests a round-off issue in which a value such as $40.50 may have in effect fallen between two bins, which work with integers.
Hi thanks for this very useful video, somehow grouping is not working for me. I have some blank rows in table also
Wow! I knew all of these except for the last one - duplicating the reports for the different regions.
On a timeline slicer how can I make the quarters in line with the fiscal year the data is intended to show? CY Q4 is actually my FY Q1.
wonderful simplicity and thank you for getting to the point. high signal/noise ratio. g
Glad it was helpful!
I like this video an also I learned a lot. Thanks a lot for this great tutorial.
Nicely explained Kenji, been using excel a long time, always more changes on new updates. Always learning, like a lot of people. Just came across your channel by chance and now subscribed too. Thank you!
That’s great to hear Derek thank you for the nice comment 😁
Use Case: I have a pivot table connected to splicers. The pivot table has 20 rows of data and multiple columns. The splicer filters between Fiscal Quarters. Is there a way to set up excel or pivots to not truncate the rows I apply a filter? For example...I have 20 rows when in Q1, but when I apply the filter for Q2 it goes down to 15 rows...hiding rows without data. Can I set up the pivot to keep the 20 rows and show rows without data as blank?
Thank you dear. this help alot.
hey sorry but I wanna ask something at 0:35 how can you change the header automatically from alphabets to text like that? I would be gratefully happy if you can explain it tho, cause it looks so useful😆
Amazing tricks, waiting more videos
This is so cool. Thank you sir
I didn´t understood well the region part and the cells arrangement , from who come that layer for ? What is the sheet for , for sales because I have no idea about it.
Thanks so much 🙏 namaste!
I subscribed ur channel couple of days ago and loved the way u explain since then i have been watching ur videos…. Can u please make an detailed advance level file on conditional formatting plz and if u have make one can u plz share the link thanks and u r doing a great job.
Thank you! Noted your suggestion for a future video :)
Great idea for a video, conditional formatting is an underrated topic
Thanks! Appreciate with this useful video!
Thank you Kenji
Vamos!
Great video as always with lots of handy tips. But at formatting I have a question. I guess it is simple, but why the accounting format and not just the financial formatting?
amazing video!
simply amazing
Thanks for the great tips Kenji. I just subscribed.
Great video for learning pivot tricks.
Amazing!! thanks
Thanks brother
Thank you!
Excellent!
Awesome video. Thank you for sharing.
I'm in awe of these skills😅
hello, thanks for the information, query: how can I select in a drill down the number of columns that I want to show in the new sheet. If the base data has many columns, perhaps not all of them are of interest in a drill down, thank you very much in advance
Kenji, thank you for the amazing content! your channel is my go to place for Excel and PPT tricks.
grouping is very cool thanks sir
I know! Thanks for watching
Dear Kenji, can you share ideas in which ways we can do product analysis of a Company. We have many products and our Management wants to know which product we should continue and which one to discontinue selling. How can we make this decision. Please share your thoughts.
Create a column with budget and sale per product. Then check for products that you're not getting revenue
How can we update automatically pivot tables when we add some datas inside of our source table? I don’t wanna use refresh it every minutes or open the workbook options.
May you help me about this question?
Thanks for the knowledge.
My pleasure!
Dang. I now know what ONLY the experts knew. My commenting on this video also helped out the content creator.
thank you, thank you, thankyou
Very useful, particularly the price grouping and the report filter pages. Thank you for this content.
As always - excellent video man!
thank you !!!
Can we add RS. 10 in grand total single row for tally the adjustment amount.
Kenji, when I refresh the pivot table The field filter disappears and the data for all years are displayed, Can you help trouble shoot this type of error? Thanks, Ed
Super, helps me as business analyst.
If yu are business analyst with that vocabulary than I am the president of US. The stupidity have no limits. But you can talk anyway, you can copy-paste like a parrot everything you see-.Remeber that you are mental ill.
I HAVE A PIVOT TABLE. IN THIS TABLE IN THE TOP ROW & COLUMN 1 IS THE DATE RANGE AND I WANT TO USE THIS DATE RANGE COMMAND WITH VBA USER FORM. WHEN IN USER FORM I CHANGE THE DATE AND CLICK ON SHOW IN USER FORM THEN PIVOT TABLE TOP ROW & COLUMN 1 IS THE DATE RANGE IS AUTOMATIC CHANGE AND UPDATE WITH DATE DATA. IF IS IT POSSIBLE ??? PLEASE RESPONSE ME. AND MAKE A VIDEO ON IT. THANKS (AMARENDRA KUMAR)
Just one doubt:
When grouping price ranges within intervals, for those products that cost exactly $40,00, would go on to the 20-40 or the 40-60 interval? It would be useful to be able to do something like 21-40 and 41-60
Replying to seek an answer
Maybe you could perform a quick check before and after grouping
I've got no idea, but if I had to guess since we started from 0 then maybe it's 0.00-19.99 but I really need to double check. Did you find the solution?
Wow.... I use pivot table a lot, but never have I explore these tricks
Great course as always, almost 100k congrats for this whole entrepreneurial project that seems to thrive (Ytb channel, Excel course you created etc).
If you have the knowledge for that, could you please do the same format video for VBA ? I think it could help almost as many people as for Excel.
Thank you Augustin! Yes I’ve used vba a good bit. Will note it down for a future video idea!
Please give a brief description of the plugin, how to use it. How to use the plugin. How to use it.
Can i open all the data sheets without double clicking one by one. Is there any one option?
Nice... thanks
The report filter tool is super useful!
Cheers mate!
Good job bro 🤝
Could someone explain to me how to do it, to the value to be changed in all pivot table column, since my excel does it only for the cell chosen (1:14) :/ Of course, I know it can be done by choosing all the area, but I would like to fix it that it orks the same as in the video.
Hi everyone,
I'd a small doubt, from where do we got the months column when its not present in the dataset?
i wish there was heart in my keyboard hehe. thank you so much.
choose whether you want to analyze multiple tables” . why not showing in my pivot excel. What setting to be changed.
Thank you Boss
Thanks for watching!
Thanks for the video , who says excel is basic fr
wooaah big help🤗🤗🤗
What happened to sheet3!?!? Just kidding great information! Good job