☑️ Top 25 Advanced Pivot Table Tips & Tricks For Microsoft Excel
HTML-код
- Опубликовано: 3 авг 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
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
The most useful pivot table video I have found and can apply to what I am doing right away! Thanks a million sir~
Great John.. super useful! Even for some of the tips I know.. great reinforcement to see them in your video. Thumbs up!
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 🙂
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!!!
A concentrated presentation of useful pivot table features in a clear and concise manner. Impressively done. Thank you.
Fantastic and great course for 5hose MS Excel functions and Pivot Tables. Thank you.
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
This is wonderful. In less than 1.5 mins you have solved one of my biggest headaches. Thanks!
Glad to hear it helped!
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 😎
Awesome thank you for the calculated field!
Glad it helped you out 👍
Two words...THANK YOU. Great presentation. Just what I needed.
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 love this tutorial. Easy to understand and just get through the point exact what i want to know thank you!
Good guide! I need pivot table for my tmr presentation! And I got you and Vanna Excel to learn in 1 hour! Thanks!
I should have checked this before my current project analysis... It could have saved me a lot of time!
Your shared each and every shortcut formula cleared my concept in details...
Excellent. Knew a few, but now know a lot more!!!!!!!😇😇😇😇😇
thank you very much, this is very useful for me
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?
I'm not familiar with pivot yet but this video summarized what I should know and review. Thank you.
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!
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 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!
The most helpful pivot table I have watched, thank millions for you ❤️
No problem Mohamed. Glad you found it useful.
I knew all but its very good and comprehensive tutorial, definitely recommended!
You are in the top 0.1% if you knew them all 🏆 👍
Thank you, I like the 17th trick
Excellent tutorial John! Thank you for sharing it!
This is great, thank you!
No problem Rhonda!
Awesome tutorial....thank you..
You're welcome Vivek!
this is *beyond* useful. thank you!
😃
More useful video, get to know advance options for implementing. thanks
Thank you so much. This video is very comprehensive and helpful!
Thanks very much. Great Great video. well explained and very helpful. Thanks again and again.
Thanks John. That's quite useful. Loved your simplicity.
Thanks Jay! Glad to hear you got some use out of it.
This was awesome, John. Right to the point. Thank you!
You're welcome! 🙂
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
Now i realize i suffered a lot in the last 15 years not knowing so many excellent excel tips. Thank you Man
Excellent Video on PvotTable. I recommend all to watch this video.
Thanks Kowser!
One of best tutorial on pivot table
Thank you so much....
You're welcome Pawan!
I've learned a lot from this video. Thank you so much. Greetings from Turkey ;)
Glad you found it useful and hello from Canada 🇨🇦
Excellent tips, 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
One of the best Tips on pivot tables..very well explained..excellent 👍👏
Thanks for the kind words Vyshnava!
Nice tips, thank you John!
Any other topics you'd like to see in videos?
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. 😉
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.
Thanks for video, it was a quick refresher for me.👍
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 🙂
Thumbs up button pressed with pleasure! Very well done summary of quite useful operations within a pivot table. Thanks for sharing.
Thanks Aristonville!
Concise tutorial, very impressive ! Thanks for sharing
That's so amazing! Explained with such an ease! can you suggest any exercises to practice the learned info?
Gergana Eugenie squats
Awesome video thanks!
I have a question... When I show detail of a value in the table, I can only see 1000 records and no more, is there a way to override that?
Thanks for the help!
Thank you for sharing these useful tips.
No problem Jose, glad you found them useful.
WOW. Excellent explanation. Thank you so much.
This is amazing. Thank you !
You're welcome!
This video is beautiful :)
Very Useful for data analytics. Thanks for this video
Thank you so much, love your video. Very helpful
Thanks Tien, glad you enjoyed it!
Perfect video on Pivot tables.
Thanks!
Very helpful, thank you!
This is great! Just discovered your Tips and Tricks. Game changer.
Thanks Claire 🙂
Huge amount of info, simply presented. Thanks.
Thanks Kirk! 😀
Very interesting tips--clear and concise.
Glad they were interesting to you Sonia!
the best Tips on pivot tables...Awesome..pls, need this Excel file
THANK YOU! THIS VIDEO IS REALLY AN EXCELLENT TUTORIAL TOOL!
Thanks John. Glad you think it's EXCELLENT! 😊
Thank you so much!
Impressive and concise thank you.
A million thanks to you for such thoughtful video.
You're welcome!
Got learn about pivet tables thank you
Yes, there's a lot to learn about them!
Great Info. Thanks
wow thank you for this 😀
Very useful video. Thank you so much for sharing. 👍
Awesome!! Thank you for your time and dedication to present this video. Great help!!
Glad it helped you Oscar 🙂
Superb video. Many thanks!
Thanks Luda, glad you found it superb 😀
Thank you very much for sharing :)
Helpful tips. Thanks for providing the video
No problem, you're welcome 👍
Absolutely Wow !
thank you for this very useful vedio
很棒。谢谢
a great video, thank you so much !
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.
amazing, thanks a lot
Hats Off...Great John.. super useful!
Glad to hear you found it useful Roshan!
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
most useful excel tips I have seen on youtube , thanks
Thank you Giorgi! Your comments help keep me motivated!
THANK YOU VERY MUCH , EXCELLENT VIDEO
Thank you so much
Thanks John, this video is great! I learn a lot
Glad to hear, keep up the learning!
Awesome video. Thank you.
You're welcome!
Super... Learned few new features of Pivot
Great!
Hi John , it was awesome to find your study material that is very useful
Thanks Shahid!
Thanks for the informative tips and tricks that you have shown us. But the grouping function does not seem to come up when I use the latest Excel version. Need your advice