Advanced Pivot Table Tricks ONLY Experts Know
ฝัง
- เผยแพร่เมื่อ 7 มิ.ย. 2024
- Advanced Pivot Table tips and tricks to make you an expert-level Excel user.
👉 Learn Excel for Business & Finance: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video:
view.flodesk.com/pages/62daae...
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑💻 LinkedIn - / careerprinciples
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Intro
1:26 - Percentage of Grand Total
2:15 - Timeline
3:14 - Top 10 Filter
5:26 - Grouping & PivotChart
6:47 - Slicers
7:58 - Report Filter
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
✅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?
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.
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.
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!
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
Kenji, thank you for the amazing content! your channel is my go to place for Excel and PPT tricks.
Thanks Kenji.Really appreciate your efforts to equipp us with knowledge.
preparing for an excel case interview, find this very helpful! thank you!
Excellent . Crisp and clear explanation. Thanks
Very useful, particularly the price grouping and the report filter pages. Thank you for this content.
Wow at last a soft tutorial I can follow! you sir are a genius
Wow!! I needed that video, thankss kenjii
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 😁
The final tip was really useful. Thanks
Really appreciate your effort.Thanks you so much!
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!
I use excel a lot in work. Your example questions are very useful. Thank you so much!!
Good info Kenji. I learned something new. Thanks!
Great to hear! Thanks for watching
As always - excellent video man!
Thanks for the great tips Kenji. I just subscribed.
Very much informative, like your way of explanation in depth on each individual topics, which helps us understand very clearly.
Awesome video. Thank you for sharing.
Thanks! Appreciate with this useful video!
"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."
Thank you Kenji ! you are the best :)
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.
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!
This is once of the best places to learn about basic-advanced level Excel concepts. Thank you Kenji..
Thank you… clearly explained
You are just an excel genius. wow!
Amazing, filter to sheets incredible, every day is a school day☘️❤️
Thanks so much 🙏 namaste!
Amazing!! thanks
Real word example easy to implement in XL PT. Thanks for unhidding this is great way.
Great video for learning pivot tricks.
Some great tricks!!!
Cheers!
Excellent presentation, i have learnt a lot from your channel.
best regards from Egypt
Thank you dear. this help alot.
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
Excellent!
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?
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.
Amazing tricks, waiting more videos
simply amazing
This is so cool. Thank you sir
thank you !!!
Thank you!
I like this video an also I learned a lot. Thanks a lot for this great tutorial.
Well done! Wouldn't call those operations advanced though. Going to browse for more videos!
amazing video!
Wow! I knew all of these except for the last one - duplicating the reports for the different regions.
Nice... thanks
Learnt a lot from this video
thank you, thank you, thankyou
Wooow i am new to pivot tables and this is excellent
wooaah big help🤗🤗🤗
Thanks for the knowledge.
My pleasure!
Thanks brother
Last tip is fire. Thanks
Glad it was helpful!
Timelines button will really help in for day to day work ..superb
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
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.
Thank you Kenji
Vamos!
Love it
Good job bro 🤝
Trank you so much
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.
Why do you select the [Accounting] format over [Currency]?
Pivot tables are lifesavers
Agreed! Thanks for watching
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?
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.
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
Hi thanks for this very useful video, somehow grouping is not working for me. I have some blank rows in table also
Hey Kenji, can you please make a video on merger and acquisition model. It will be really helpful.
grouping is very cool thanks sir
I know! Thanks for watching
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😆
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 ?
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.
Sweet!
Thank you!
i wish there was heart in my keyboard hehe. thank you so much.
Useful
Great to hear!
I'm in awe of these skills😅
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)?
I’m waiting for your 100K day Kenji! Almost there
Coming!! Thanks for supporting Tristan much appreciated 👊
Thank you Boss
Thanks for watching!
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?
Can we add RS. 10 in grand total single row for tally the adjustment amount.
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 also wanna ask a question I started with the BIWS course recently to learn Modelling but would modelling be different since I am from India and we have accounting standards 80% similar to IFRS would that be a problem as probably these courses Breaking into wall street and Wallstreet prep probably follow US GAAP that would make Modelling different for US GAAP, IFRS, or Indian Standards?
Modelling would be the same regardless of the accounting standards (IFRS, US GAAP, Indian Standards) that you use. Modelling is about being able to visualize and to explain the data to others and / or yourself in a way that makes sense beyond just showing them a bunch of numbers. Accounting standards is majorly about allowable treatment of different accounting items / components (how you come up with the figures / numbers).
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
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?
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
Please give a brief description of the plugin, how to use it. How to use the plugin. How to use it.
minute 5:53: its strange that you have duplicate rows for price per unit. is that due to rounding?
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.
Can i open all the data sheets without double clicking one by one. Is there any one option?
Dang. I now know what ONLY the experts knew. My commenting on this video also helped out the content creator.
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
Does anyone know how to fix the problem (or bug) where a pivot table is formatted for most of the table, but the last section the formatting is different and doesn't follow the same format as the upper part of the pivot table?