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!!!
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
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.
@@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
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.
thanks for learning me the modalities of pivot table if you have any such video for power BI kindly share. Now i am learning to master power BI so be my tutor. thanks a lit again
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!
Hi... regarding delete source data, will it still work if we uncheck save data settings of pivot table? I found it as one of the feature to uncheck on reducing file size. Thank you for the video
An exceptional video. Very well done. I knew about 2/3 of these but watching the whole was well worth it. Q: re show values as % - the default option is clear. BUT I want to to show values as % of Column total but ignore filters constraints. Is there a way to do that? I haven't found one.
Really good stuff. I"m just wondering if there is a way that I bring over in columns multiple years of data in a pivot but I don't have to click each month to show it. If there is a 'magic button' or trick that does it, that'd be great.
@@HowToExcelBlog Ok let me try to picture it out for you. On an excel spread sheet across the top (row 1), you have every month from Jan-2000-Oct 2020 (that'll be 250 columns), then on column A, let's say you have the cast of "The Office" (Pam, Dwight, Jim, Stanley etc) and then each month you have the number of paper cartons each have sold (10,50,40), so that you're spreadsheet with every month filled out. Now you'll 'create a pivot' and the 'pviot table fields is open,' but when you to include each month in your pivot so you drag the 'month' into 'values' section for Jan 20000. But you need to do this for EVERY month. Is there a shortcut to have every month populated? I think the answer is 99% no but wanted to at least try asking.
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
Oo
The best video I have ever seen on advanced pivot table
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 🤣
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!!!
Awesome tutorial....thank you..
You're welcome Vivek!
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
thank you very much, this is very useful for me
A concentrated presentation of useful pivot table features in a clear and concise manner. Impressively done. Thank you.
Two words...THANK YOU. Great presentation. Just what I needed.
The most useful pivot table video I have found and can apply to what I am doing right away! Thanks a million sir~
This is great! Just discovered your Tips and Tricks. Game changer.
Thanks Claire 🙂
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 🙂
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
Best Tips on pivot tables..very well explained..excellent..
Thanks Karthik!
Fantastic and great course for 5hose MS Excel functions and Pivot Tables. Thank you.
Your tip & tricks is very helpful to my works, Thankyou
You're welcome. I'm glad you found it helpful Cornelius.
Helpful. Constantly reminds me something I forgot. Thanks.
Very Useful for data analytics. Thanks for this video
This is wonderful. In less than 1.5 mins you have solved one of my biggest headaches. Thanks!
Glad to hear it helped!
Hi John , it was awesome to find your study material that is very useful
Thanks Shahid!
Great video, very useful and easy to understand. Thank you very much
Glad to hear you found it useful Gisela.
Huge amount of info, simply presented. Thanks.
Thanks Kirk! 😀
most useful excel tips I have seen on youtube , thanks
Thank you Giorgi! Your comments help keep me motivated!
Super Helping, thanks a lot comrade
No problem my friend 👍
Very good presentation
Thanks!
Excellent and clear presentaion
Excellent video. Really helpful..
Glad it helped!
Thanks for video, it was a quick refresher for me.👍
Very useful video. Thank you so much for sharing. 👍
Your shared each and every shortcut formula cleared my concept in details...
Thanks John, this video is great! I learn a lot
Glad to hear, keep up the learning!
This is a very good video. Highly recommend.
Share, like and subscribe!
Its really good and educative thank you
You're welcome Shinu 🙂
Thumbs up button pressed with pleasure! Very well done summary of quite useful operations within a pivot table. Thanks for sharing.
Thanks Aristonville!
Impressive and concise thank you.
Very helpful, 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!
A million thanks to you for such thoughtful video.
You're welcome!
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 😎
You are great, providing very useful information regarding pivot table
Thanks Madhu!
Awesome video. Thank you.
You're welcome!
it was awesome.. Pls share u r guide to know in detail.
Super... Learned few new features of Pivot
Great!
Excellent video, useful video
Thank you for not having a super thick accent that greatly reduces the communication of the content.
Very useful. Thank you so much
You're welcome Roxana.
very useful......................thanks
You're welcome Ibrahim!
you are great teacher, god bless !
Welcome!
Excellent tutorial 👌🏻
Thanks
Good Video, mostly known but good for beginners.
Beginner and advanced are all relative terms. What's beginner to the advanced user is advanced to the beginner user 😂
Very helpful. Thank you.
No problem!
Thanks John. That's quite useful. Loved your simplicity.
Thanks Jay! Glad to hear you got some use out of it.
Great Info. Thanks
Excellent 👍👌👍
Informative....
Glad it help Mohammed!
Wonderful exlplanation & these is not shared in any videos - all were unknown tips
Glad you liked it!
thanks for learning me the modalities of pivot table if you have any such video for power BI kindly share.
Now i am learning to master power BI so be my tutor.
thanks a lit again
Excellent tips thanks for posting
No problem Khalid!
Really good to find your video courses!
😀
Thank you, I like the 17th trick
Very useful, nicely explained
Thanks Deepak! Glad you found it useful.
Hats Off...Great John.. super useful!
Glad to hear you found it useful Roshan!
Superb video. Many thanks!
Thanks Luda, glad you found it superb 😀
WOW. Excellent explanation. Thank you so much.
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?
Thank you so much. This video is very comprehensive and helpful!
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.
THANK YOU VERY MUCH , EXCELLENT VIDEO
I'm not familiar with pivot yet but this video summarized what I should know and review. Thank you.
The most helpful pivot table I have watched, thank millions for you ❤️
No problem Mohamed. Glad you found it useful.
Hi... regarding delete source data, will it still work if we uncheck save data settings of pivot table? I found it as one of the feature to uncheck on reducing file size. Thank you for 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!
This was awesome, John. Right to the point. Thank you!
You're welcome! 🙂
Very nice Tipps….learnt a lot!
Great to hear Ajay!
Thank you so much for this very useful information 😇
You're welcome. Glad you found it useful.
Excellent. Knew a few, but now know a lot more!!!!!!!😇😇😇😇😇
Thanks dear brother very very useful massage!!!!
No problem my friend!
Very useful and well presented pivot table presentation
Thanks Paul!
Excellent Stuff
Thanks!
Awesome!! Thank you for your time and dedication to present this video. Great help!!
Glad it helped you Oscar 🙂
Awesome tutorial
Learned some new stuff. Thanks!
Thank you for sharing these useful tips.
No problem Jose, glad you found them useful.
Very interesting tips--clear and concise.
Glad they were interesting to you Sonia!
Good guide! I need pivot table for my tmr presentation! And I got you and Vanna Excel to learn in 1 hour! Thanks!
I've learned a lot from this video. Thank you so much. Greetings from Turkey ;)
Glad you found it useful and hello from Canada 🇨🇦
Thank you 🙏🙏🙏
Thank you so much for great tips! Do we need always mark "Refresh data when opening the file"? if answer is no, please explain...
Is someone else going to be adding data to the workbook without you knowing? Enable that option to always refresh so you don't miss the new data.
Hi john really enjoyed your excel tips, your deliberation, your to the point explanation and clear accent and pase is Osum 😁
Thanks! Glad you liked it Muhammad!
An exceptional video. Very well done. I knew about 2/3 of these but watching the whole was well worth it.
Q: re show values as % - the default option is clear. BUT I want to to show values as % of Column total but ignore filters constraints. Is there a way to do that? I haven't found one.
You'll need to use some DAX formulas for that.
Thanks a lot. Very valuable information. 👍
No problem. Good to hear it was valuable to you.
Superb indeed
Thanks Faisal
Thank u very much sir...u r a great...got to learn a so many things....
You're welcome!
Very helpful
🙂
Thank You, Sir!
You're welcome sir 🙂
Thank you so much!
Awesome!!!
Rad!!!
One of the best Tips on pivot tables..very well explained..excellent 👍👏
Thanks for the kind words Vyshnava!
thank you for a great video!
You're welcome Michelle.
I should have checked this before my current project analysis... It could have saved me a lot of time!
Really good stuff. I"m just wondering if there is a way that I bring over in columns multiple years of data in a pivot but I don't have to click each month to show it. If there is a 'magic button' or trick that does it, that'd be great.
Not sure I understand
@@HowToExcelBlog Ok let me try to picture it out for you. On an excel spread sheet across the top (row 1), you have every month from Jan-2000-Oct 2020 (that'll be 250 columns), then on column A, let's say you have the cast of "The Office" (Pam, Dwight, Jim, Stanley etc) and then each month you have the number of paper cartons each have sold (10,50,40), so that you're spreadsheet with every month filled out. Now you'll 'create a pivot' and the 'pviot table fields is open,' but when you to include each month in your pivot so you drag the 'month' into 'values' section for Jan 20000. But you need to do this for EVERY month. Is there a shortcut to have every month populated? I think the answer is 99% no but wanted to at least try asking.
Ya, your data setup is the problem! You should use 1 column for date and 1 column for paper sales.
Google power query unpivot to fix your data.
Ok thanks!