Advanced Pivot Table Tricks ONLY Experts Know

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.

[@KenjiExplains]

✅To learn more, check out our Excel Course here: www.careerprinciples.com/courses/excel-for-business-finance

[@emamul1987]

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.

[@wilfdarr]

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?

[@rathernot6660]

Why do people think excel is no longer useful. It is so dynamic. Like this.

[@RafiTAHasani]

Who told that? 100% of the businesses corporates use excel as a key software till date

[@learningdistilled]

I agree with you. If people take the time to learn they can get twice the work done in half the time!

[@traciesmobile683]

It is people who don't know how to use it or bother to spend the time asking "can excel...."

[@florianschmidt2971]

Because there is PowerBI - there you can crossfilter between tables. Excel is not dynamic, it’s just a calculation tool

[@soxpuff]

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.

[@sha.aesthetic]

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.

[@isuruchathuranga4404]

Really appreciate your effort.Thanks you so much!

[@aaronwalcott513]

I'm now crossing to that "intermediate excel" fuzzy zone and this video just made a LOT of things clearer. Thank you.

[@KenjiExplains]

Great to hear Aaron, thank you!

[@user-ef4vs8xd1v]

Thanks Kenji.Really appreciate your efforts to equipp us with knowledge.

[@omarahmedmohamed6711]

Kenji, thank you for the amazing content! your channel is my go to place for Excel and PPT tricks.

[@Fazari5]

Very useful, particularly the price grouping and the report filter pages. Thank you for this content.

[@sreenivaskorada5067]

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

[@minghui9525]

Wow at last a soft tutorial I can follow! you sir are a genius

[@user-rp9oo1ft2m]

preparing for an excel case interview, find this very helpful! thank you!

[@derekpage8924]

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!

[@KenjiExplains]

That’s great to hear Derek thank you for the nice comment 😁

[@skshah309]

Excellent . Crisp and clear explanation. Thanks

[@syljihomom]

I use excel a lot in work. Your example questions are very useful. Thank you so much!!

[@Illuminous56]

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.

[@Kevin-bz4nt]

keep the good work up! These vids are really nice for Finance majors to learn new skills in Excel

[@KenjiExplains]

Great to hear Kevin than you!

[@user-oi2rf2dj3z]

"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."

[@V_Dubya]

The final tip was really useful. Thanks

[@samfried7153]

As always - excellent video man!

[@hnmsmultivideos6044]

Very much informative, like your way of explanation in depth on each individual topics, which helps us understand very clearly.

[@andreugiro2807]

Wow!! I needed that video, thankss kenjii

[@moesadr3342]

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.

[@augustinm1284]

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.

[@KenjiExplains]

Thank you Augustin! Yes I’ve used vba a good bit. Will note it down for a future video idea!

[@c.e.bingham2079]

Good info Kenji. I learned something new. Thanks!

[@KenjiExplains]

Great to hear! Thanks for watching

[@bartomiejonak5013]

Real word example easy to implement in XL PT. Thanks for unhidding this is great way.

[@michaelmiller2833]

Awesome video. Thank you for sharing.

[@foodandrecipes6195]

Thanks so much 🙏 namaste!

[@autobahn1974]

Thanks for the great tips Kenji. I just subscribed.

[@JJ_TheGreat]

Wow! I knew all of these except for the last one - duplicating the reports for the different regions.

[@myfirstpet6813]

This is once of the best places to learn about basic-advanced level Excel concepts. Thank you Kenji..

[@hendrasetiawan7220]

Thanks! Appreciate with this useful video!

[@abdulaseefk9548]

Thank you… clearly explained

[@sunilkumar3209]

Great video for learning pivot tricks.

[@adrieanneirwin5328]

Amazing, filter to sheets incredible, every day is a school day☘️❤️

[@tef83]

Thank you Kenji ! you are the best :)

[@joshliu2410]

You are just an excel genius. wow!

[@joukenienhuis6888]

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?

[@Funnyvideos-kf1eo]

Amazing tricks, waiting more videos

[@camlex6310]

Some great tricks!!!

[@KenjiExplains]

Cheers!

[@harissiddiqui5857]

Thank you dear. this help alot.

[@user-zl8bl3ui1s]

simply amazing

[@liquiduser]

Thank you!

[@robwin0072]

Why do you select the [Accounting] format over [Currency]?

[@rubinashaikh1965]

Awesome Thanks!! For making the video scenario based rather than simple explanation… n yes Ur accent makes it more engaging..

[@KenjiExplains]

Glad you liked it! Hahah love to hear that about my accent XD

[@degigerel6286]

i wish there was heart in my keyboard hehe. thank you so much.

[@andrewsvega2023]

thank you, thank you, thankyou

[@kirstinhartmann]

I like this video an also I learned a lot. Thanks a lot for this great tutorial.

[@freska4uu]

Excellent presentation, i have learnt a lot from your channel. best regards from Egypt

[@johnhanson2532]

Excellent!

[@balkrishna83]

Timelines button will really help in for day to day work ..superb

[@user-mb3hw2mp9k]

thank you !!!

[@njerimbugua3724]

Wooow i am new to pivot tables and this is excellent

[@fabiolasilva6623]

Amazing!! thanks

[@isawhimfirstpodcast]

I'm in awe of these skills😅

[@ararsahaile9494]

Thanks brother

[@Soul_Frost]

Well done! Wouldn't call those operations advanced though. Going to browse for more videos!

[@piusnwapa6009]

This is so cool. Thank you sir

[@sasavienne]

Trank you so much

[@kastorzhang8188]

Learnt a lot from this video

[@2u15A]

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

[@CrazyGuy9990]

Last tip is fire. Thanks

[@KenjiExplains]

Glad it was helpful!

[@AlexandreOliveira-ei5ig]

Pivot tables are lifesavers

[@KenjiExplains]

Agreed! Thanks for watching

[@BenDieselBasicAutomotive]

Thanks for the knowledge.

[@KenjiExplains]

My pleasure!

[@solomonbhandari-young4154]

Thank you Kenji

[@KenjiExplains]

Vamos!

[@hisokaamorou4211]

amazing video!

[@soumensaha4426]

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.

[@giorgiastamadze167]

Good job bro 🤝

[@willzinner8813]

grouping is very cool thanks sir

[@KenjiExplains]

I know! Thanks for watching

[@jagdishagarwal4173]

Hi thanks for this very useful video, somehow grouping is not working for me. I have some blank rows in table also

[@williamarthur4801]

Never knew about the timeline, I'd have just used a slicer, but that's really good to know.

[@Potencyfunction]

It is user view for time waste, personas sales and what time line sheet shall I make ?

[@micahsnow346]

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)?

[@BABYDOVIO]

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?

[@ridasjourney2716]

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.

[@KenjiExplains]

Thank you! Noted your suggestion for a future video :)

[@learningdistilled]

Great idea for a video, conditional formatting is an underrated topic

[@ImranAhmed1988]

Love it

[@jdno-body1884]

Nice... thanks

[@abbottkatz8830]

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.

[@SolomonSherryAnn]

wooaah big help🤗🤗🤗

[@selvanaiyadurai8967]

Super, helps me as business analyst.

[@Potencyfunction]

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.

[@scottbalderas524]

Dang. I now know what ONLY the experts knew. My commenting on this video also helped out the content creator.

[@saurabhramane1478]

Hey Kenji, can you please make a video on merger and acquisition model. It will be really helpful.

[@mingcuangcu]

Thank you Boss

[@KenjiExplains]

Thanks for watching!

[@kaMMakaZZi9]

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.

[@LuzArlos]

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😆

[@Clifffffffffford]

Sweet!

[@KenjiExplains]

Thank you!

[@LBerlinforever]

Useful

[@KenjiExplains]

Great to hear!

[@tristanontheway]

I’m waiting for your 100K day Kenji! Almost there

[@KenjiExplains]

Coming!! Thanks for supporting Tristan much appreciated 👊

[@Potencyfunction]

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.

[@enriquesuch4917]

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?

[@shivanshshukla5883]

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?

[@that_gal_duff8416]

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).

[@teoxengineer]

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?

[@mahathavenramasamy6890]

Can we add RS. 10 in grand total single row for tally the adjustment amount.

[@arifabdulazziz9014]

Wow.... I use pivot table a lot, but never have I explore these tricks

[@vishalkhadanga7669]

Thanks for the video , who says excel is basic fr

[@edmerzlak16]

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

[@harissiddiqui5857]

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.

[@alexp429]

Create a column with budget and sale per product. Then check for products that you're not getting revenue

[@Himanshudetwal06921]

Please give a brief description of the plugin, how to use it. How to use the plugin. How to use it.

[@alejandrogonzalezdelgado9212]

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

[@Sparta.10]

Replying to seek an answer

[@nursemoncho9514]

Maybe you could perform a quick check before and after grouping

[@andreatorriglia8010]

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?

[@financnifitness2583]

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.

[@huginnq291]

minute 5:53: its strange that you have duplicate rows for price per unit. is that due to rounding?

[@ashantharuka5836]

Can i open all the data sheets without double clicking one by one. Is there any one option?