☑️ Top 25 Advanced Pivot Table Tips & Tricks For Microsoft Excel
Vložit
- čas přidán 10. 07. 2024
- In this video we're going to look at the top 25 advanced pivot table tips and tricks for Microsoft Excel. These tips are going to super charge your pivot tables!
Table of Contents
1. Use a Table for Your Source Data 0:09
2. Show Details Behind a Value 2:22
3. Delete The Source Data 3:11
4. Replace Blank Cells 4:08
5. Show Items with No Data 5:11
6. Group Text Fields 6:16
7. Group Number Fields Into Ranges 7:53
8. Group Date Fields Into Ranges 8:39
9. Automatically Refresh Data when Opening Your Workbook 10:02
10. Automatically Create a Pivot Table for each Item in a Filter 10:34
11. Allow Multiple Filters Per Field 12:05
12. Sort Items According to a Corresponding Value 14:00
13. Create a Custom Sort Order 14:45
14. Change the Layout 16:35
15. Repeat All Item Labels 17:34
16. Turn Off GETPIVOTDATA 18:17
17. Prevent Column Width Changing on Update 19:03
18. Add A Calculated Field 19:57
19. Create a PivotChart 21:03
20. Add a Slicer 22:15
21. Add a Timeline 23:05
22. Connect Slicers or Timelines to Multiple Pivot Tables 24:06
23. Filter on Top N Items 24:42
24. Add Multiple Subtotal Calculations 25:35
25. Show Value As 27:03 👇
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
DOWNLOAD the example workbook here
📖 www.howtoexcel.org/downloads/
SUBSCRIBE & get my 3 FREE eBooks
📧 www.howtoexcel.org/newsletter/
CONNECT with me on social
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
Oo
I had subscribed to a paid 6 hours long course on Pivot tables.
This video of 20 minutes worth 10 times to that.
Thanks very much for sharing.
Thanks 🤣
The best video I have ever seen on advanced pivot table
Great John.. super useful! Even for some of the tips I know.. great reinforcement to see them in your video. Thumbs up!
The most useful pivot table video I have found and can apply to what I am doing right away! Thanks a million sir~
A concentrated presentation of useful pivot table features in a clear and concise manner. Impressively done. Thank you.
Excellent video. Even though I already knew some options, I learned a lot. It is always surprising what Excel has to offer. Thank you for presenting the options in a bundled way.
Yes, there's even more features than this video has 🙂
Great tips, including a couple that were new to me. Thanks!
Glad to hear you learned a couple new tips!
Great value in a single video. Big thumbs up, thank you
Two words...THANK YOU. Great presentation. Just what I needed.
This is wonderful. In less than 1.5 mins you have solved one of my biggest headaches. Thanks!
Glad to hear it helped!
I was on Sabbatical for 5 years and resumed working
Learning these advanced features gives me high. I have been big fan of excel throughout my life.
Power query is in my next to do list.
Thank you for helping me catch-up so quickly. Regards Respect and Gratitude
Awesome thank you for the calculated field!
Glad it helped you out 👍
No more struggling with pivot tables! Great video, wish I'd found it sooner.
Glad to hear you'll soon be slaying data in pivot tables with ease 😎
The most helpful pivot table I have watched, thank millions for you ❤️
No problem Mohamed. Glad you found it useful.
Good guide! I need pivot table for my tmr presentation! And I got you and Vanna Excel to learn in 1 hour! Thanks!
i love this tutorial. Easy to understand and just get through the point exact what i want to know thank you!
Wow! I did not know that there are so many pivot table options. Most of them I do not use, but now I can't wait to start using them! Great tutorial and very useful! Thanks very much!
Yep, pivot tables have a lot of great features and there are still many others not featured in the video.
I'm a big fan of pivot tables and your tips gonna help me a lot. Thank you so much!!
Glad to help you out!
I should have checked this before my current project analysis... It could have saved me a lot of time!
I'm not familiar with pivot yet but this video summarized what I should know and review. Thank you.
Thank you for the insight.
It is a great tutorial for pivot table users to advance their job skills! Thank you very much!
Thanks for the note! Any tips you're going to start using for work?
Thanks John. That's quite useful. Loved your simplicity.
Thanks Jay! Glad to hear you got some use out of it.
Wow. This was an incredible video. You helped me solve more than for annoying problems that I face daily.
Thanks Dan. Glad it helped.
What were the annoying problems?
I knew all but its very good and comprehensive tutorial, definitely recommended!
You are in the top 0.1% if you knew them all 🏆 👍
Fantastic and great course for 5hose MS Excel functions and Pivot Tables. Thank you.
Excellent tutorial John! Thank you for sharing it!
Man....You really changed my life here!!! I work with data every single day and wherever you are - if you could have heard my scream of joy when I saw “repeat header rows” you would have laughed so hard! I LOVE Pivot Tables, my friends actually make fun of me because of my fascination with them - I use them constantly to view data and analyze it and I’ve been doing it for years. This video is incredible and I really appreciate your putting it out here! THANK YOU SIR!!!
If you love pivot tables, then you might have an emotional breakdown when you discover power pivot 💪😮
How To Excel that’s how I found you!!!
This was awesome, John. Right to the point. Thank you!
You're welcome! 🙂
Thanks, the very first tip on creating a table for your source was exactly what I needed!
Make sure to watch the rest too, it might have stuff you don't know you need yet. 😉
This is great, thank you!
No problem Rhonda!
Excellent Video on PvotTable. I recommend all to watch this video.
Thanks Kowser!
Thank you so much. This video is very comprehensive and helpful!
I've learned a lot from this video. Thank you so much. Greetings from Turkey ;)
Glad you found it useful and hello from Canada 🇨🇦
One of the best Tips on pivot tables..very well explained..excellent 👍👏
Thanks for the kind words Vyshnava!
this is *beyond* useful. thank you!
😃
i thought that there only a few things that i dont know about pivot table but i was wrong, there are still new things that i learned here. Many thanks. i will share this to my team members.
Good to hear you learnt a few new things!
Your shared each and every shortcut formula cleared my concept in details...
Huge amount of info, simply presented. Thanks.
Thanks Kirk! 😀
You deserve much more subscribers. Your videos are highly informative and to the point, much more so than other channels. Great job!
Thanks Hendrick, it all takes time. I'll get more eventually. This month I was able to add 1k subscribers 🎉
Helpful. Constantly reminds me something I forgot. Thanks.
Excellent tips, Thanks 👍
Nice tips, thank you John!
Any other topics you'd like to see in videos?
One of best tutorial on pivot table
Thank you so much....
You're welcome Pawan!
thank you very much, this is very useful for me
More useful video, get to know advance options for implementing. thanks
Thanks! Great job explaining, learned new tips.
Great Video. I've learned new tips which will be definitely helpful for my career and studies. Thank you
This is great! Just discovered your Tips and Tricks. Game changer.
Thanks Claire 🙂
Thumbs up button pressed with pleasure! Very well done summary of quite useful operations within a pivot table. Thanks for sharing.
Thanks Aristonville!
Thanks very much. Great Great video. well explained and very helpful. Thanks again and again.
WOW. Excellent explanation. Thank you so much.
Very Useful for data analytics. Thanks for this video
Concise tutorial, very impressive ! Thanks for sharing
Great Info. Thanks
Superb video. Many thanks!
Thanks Luda, glad you found it superb 😀
This video is beautiful :)
Great video! Will be signing up for newsletter and I've subscribed to your newsletter. I work for a very large corp. and will be passing along these tips. I would like to see a video where we could actually download practice exercises. Thanks! Joanne
A million thanks to you for such thoughtful video.
You're welcome!
Thank you, I like the 17th trick
Excellent. Knew a few, but now know a lot more!!!!!!!😇😇😇😇😇
Excellent information on Pivot Tables in a very quick and concise manner - well done and thank you!
Glad you enjoyed it Kevin!
@@HowToExcelBlog Yeah, I've already subscribed to your channel. Great work you're sharing. Any chance you could point me to the best video or maybe tell me a good approach to get the functionality of 2 different functions: CountIf (E.g. COUNTIF(G8:G2009,">0") as I don't want to count entries with a 0, but also the Aggregate function (E.g. AGGREGATE(3,5,G$8:G2009) so I can ignore hidden rows when I filter my data? I'd rather not just create an additional column of data to hold the CountIf value. But I know I can apply the aggregate to the CountIF data in that column, but hoping there is a better option? Thanks
Thanks for video, it was a quick refresher for me.👍
Thank you for sharing these useful tips.
No problem Jose, glad you found them useful.
Impressive and concise thank you.
This is amazing. Thank you !
You're welcome!
Awesome!! Thank you for your time and dedication to present this video. Great help!!
Glad it helped you Oscar 🙂
Very helpful, thank you!
Perfect video on Pivot tables.
Thanks!
great content, i knew most of it, but few useful things i had no idea existed in excel, like creating multiple sheets with pivot tables
Good to hear it expanded your knowledge 🙂
THANK YOU! THIS VIDEO IS REALLY AN EXCELLENT TUTORIAL TOOL!
Thanks John. Glad you think it's EXCELLENT! 😊
Thank you so much, love your video. Very helpful
Thanks Tien, glad you enjoyed it!
Thank you so much!
Awesome video!
most useful excel tips I have seen on youtube , thanks
Thank you Giorgi! Your comments help keep me motivated!
Very interesting tips--clear and concise.
Glad they were interesting to you Sonia!
Hats Off...Great John.. super useful!
Glad to hear you found it useful Roshan!
That's so amazing! Explained with such an ease! can you suggest any exercises to practice the learned info?
Gergana Eugenie squats
wow thank you for this 😀
THANK YOU VERY MUCH , EXCELLENT VIDEO
Very useful video. Thank you so much for sharing. 👍
a great video, thank you so much !
amazing, thanks a lot
Got learn about pivet tables thank you
Yes, there's a lot to learn about them!
Excellent video, useful video
Now i realize i suffered a lot in the last 15 years not knowing so many excellent excel tips. Thank you Man
You are great, providing very useful information regarding pivot table
Thanks Madhu!
Excellent - I will make my life much easier. GREAT! I knew some of them, but some new very good tools!
Yes, pivot tables have a few great hidden features.
Excellent and clear presentaion
Excellent compilation!!
Thanks Sarah!
Hi John , it was awesome to find your study material that is very useful
Thanks Shahid!
Awesome tutorial
Superb!!!!
Awesome video. Thank you.
You're welcome!
Thanks John, this video is great! I learn a lot
Glad to hear, keep up the learning!
Excellent tips thanks for posting
No problem Khalid!
Awesome tutorial....thank you..
You're welcome Vivek!
Helpful tips. Thanks for providing the video
No problem, you're welcome 👍
Super... Learned few new features of Pivot
Great!