3 PivotTable Grouping Tricks to Summarize Data FAST!
Vložit
- čas přidán 30. 07. 2024
- Battling to consolidate monthly figures into quarters, or segment data into numeric bands? Perhaps you're aiming for a bespoke grouping in your PivotTables.
PivotTable grouping lets you intuitively classify dates, times, and number intervals, while also allowing for tailored group creations to reveal the hidden narratives in your data.
⬇️DOWNLOAD the example file here: www.myonlinetraininghub.com/e...
With just a few clicks:
📅 Group dates into months, quarters and years
🕰️ Group Time into seconds, minutes and hours
🔢 Group numeric data into intervals or bands
⁉️ Group items into custom groups
LEARN MORE
===========
⁉️ Power Query course: www.myonlinetraininghub.com/e...
🎓 PivotTable Quick Start course: www.myonlinetraininghub.com/e...
🏫 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
📰 EXCEL NEWSLETTER - join here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 CONNECT with me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 Advanced PivotTable Grouping
0:28 Group dates into months, quarters and years
4:16 Group numeric values into bands
5:45 Custom Groups - Věda a technologie
That was adorable...AND I didn't know I could create my own pivot table groupings!! Always something new to learn. Thanks Mynda!
So pleased you discovered something new that you can use 😊
An engagingly humorous way to show better ways to work with data. Love it.
So pleased you enjoyed it 🙏
Absolutely beautiful! So many tips and tricks, yet delivered in an entertaining way!
Thank you so much! 🙏😊
Excellent video with so many tips Mynda/Kirsty/Excelcia - what a team! Never actually knew you could group like this. Keep them coming!
😀
Awesome to hear you discovered something new!
Amazing Mynda! Plenty of tricks supported by a lovely storyboard ♥ The Grouping function within a pivot table is absolutely new for me, could have saved me a lot of time... Thank you so much!
Thanks so much! Glad you'll be able to make use of PivotTable grouping...better late than never 😊
Don’t know what to say. Yes, I learned a bundle of pivot table tricks … with a smile on face the whole time. The only problem is that you have seriously raised the bar for your videos!
😁 wonderful to hear, Tom! Thank you. Yes, the videos have become an industry of their own...but I'm enjoying trying new things.
I wonder, how I missed this video earlier, but thank god, I found it. Excellent as usual, learnt new things about grouping , I didn't knew before! Thanks a lot Mynda, you are doing great service to Excel community, keep it up! Would love to see more of this series with Krysta! 🙂
Thanks so much, Vijay! Glad you enjoyed it and found some new tips. 😊
What an amazing video and I love the conversation, makes it so easy to follow and have fun at the same time. You simply make magic with all the tricks you shared,,👏
Wow, thank you! 😊🙏
I am also an excel trainer consultant and it’s good to re-affirm that your own top tips align with others.
This was an excellent video.
Particularly in the main benefit which is if you do things efficiently through knowledge and understanding you free up time.
I.e. you can get your work done and get the bus at 5 instead of working till 7 inefficiently.
This why companies need to train their staff properly!!!!!!!
Well said, John! 🙏😊
This is a really easy to grasp and very cute video, thank you so much Mynda and Excel-sior and well done Kirsty for "seeing the light"! (I also learnt new stuff about the pivot groupings btw)
So glad you enjoyed it! 😁🙏
The Excel Enchantress! Yes! I don't even own Excel 365 and I don't miss a single one of these videos.
Aw, thanks so much! The good news is these features are in earlier versions of Excel too 😊
Awesome. Mynda you make it look so easy!
Thank you! 😊
What a fun way to deliver content, Mynda, don't get to see Excelsia often enough ;-)
Glad you enjoyed it!
Excellent and fun video, thanks Mynda!
Glad you enjoyed it, Chris! 🙏
Loved it! And you seemed to be having so much fun!
Thanks so much! It was lots of fun 😁
That was a fun video! More like that one, please!
So pleased you liked it, Roy! Will see what I can do to help Kirsty again 😉
Congratulations on the new production style, Mynda! I can only imagine how much work and time it took you and your team, but it was worth it. I had a great time watching it. 🎉
Thanks so much, Celia! If only I had a team 😁 I will have to seriously consider getting some help because it's not sustainable for one person 🤦♀️
@@MyOnlineTrainingHub wow! Even more kudos to you if you do not have help. 👌
Thanks for this useful PivotTable Grouping video.
It was a good refresher for me especially the Age grouping and the Categorical data grouping!!!
Great to hear!
Amazing , your videos always helped me alot 😊
Wonderful to hear 🙏😊
Another few new things learnt, thanks Mynda, Kirsty, and Excelcia
Great to hear 🙏
Nice tricks, especially using PQ! Very helpful
Great to hear! 🙏😊
Your video is so fun. Thank you for sharing the tips and tricks in Excel❤
Glad you enjoyed it! 😁
The CANON! lol I am prepping for an interview and studying a lot of Excel. I needed this cuter video today. Thanks
Glad it was helpful! Good luck with your interview 🍀
It just keeps getting better and better! Is there no end to this awesomeness?!
😁 too kind! 🙏
did not know about custom grouping. Love that
Glad you learned something new 😊
Thank you very much indeed, EXCELSIA!
😁 thanks for watching!
i'm not going home, i'm going back to work right now to update my pivot tablesssssss!
Awesome to hear 😁
Very creative! As always, thank you.
Glad you enjoyed it 🙏
Everyday is a good day to learn something new! Well done!
Thanks! 😃
You had a lot of fun doing this eh? Fun to watch!
Awesome to hear! Yes, lots of fun 😁
Really beautiful video, really helpful, you are such an amazing and talented woman!
You're too kind! Thanks so much 😊🙏
That was fun and educational. Keep them coming
Glad you liked it, Dave 🙏😊
There's always something new to learn. Thanks Mynda
I'm so glad!
I really enjoyed the way this Video was crafted!
Thank you so much!
Thank you so much for this! I was spending hours trying to group data by month using Pivot Tables but failed. Cleaning the data on Power Query fixed it straight away.
Glad I could help 😊
So fun and factual! Nice! You're awesome! Thank you!
Aw, thanks so much!
That was surprisingly entertaining. Very nice 😊👏.
So pleased you enjoyed it 😁🙏
Besides being a charming video, this is magical. Unbelievable. Incredible. Otherworldly...
Wow, awesome to hear, Cathy! 😊🙏
Really awesome stuff. Thank you!
Glad it was helpful!
Thank you got Maynda for this insightful Video 📹 👏
You're most welcome! 😊
Great intro!! LOVE IT!
I'm glad you like it 😁
brilliant video. keep up the good work
So pleased 🙏😊
Thanks for sharing. Awesome.
Glad you liked it. Thanks for watching! 😊
Mind blowing! 🤯🥳
Wonderful to hear 🙏😊
Mynda...
Number 1 in Content
Number 1 in Video Editing.
Ta-Da!!! :=)))
😁 thanks, Ivan!
I like the new format. 👍
Yay, thank you! 😁
Love Kristy and Excelsia. Thank you.
😁 thanks so much!
Thanks, very clear 😊
Thank you! Great to hear 😊
Hi , You always 'Rock'. Thanks!
Thanks so much for watching and your support!
Haha! I love this story format! :) Fantastic! :D :D :D
Yay, thank you! 😁
Pesky looks like a lovely nerd! Thank you for sharing your knowledge.
My pleasure!
Thank you. Mynda!
Thanks for watching, Marjorie!
0:28 Group dates into months, quarters and years. That's great info to know.
Great to hear 👍
I just learned something new. I always format the number in the list field, one filed at a time. I just notice how you did in the data filed that changed the format for all the data. I always thought there gotta be an easier way, and there is, and I just didn't know.
So pleased you learned something new 😊
Hy Mynda, this new way of presenting fits you very well. I liked this!! Maybe you can help Christy also with a default layout for the pivots...?(table lay out...) 😉
Thanks so much, Bart! Will keep it in mind 😊
that was fun. Thanks
Glad you enjoyed it 😊
Ah Ah Ah... In addition to teaching Excel very well, she has now shown herself to be a good actress! Thanks Mynda.
You're too kind, Luciano! An actress, I am not, but it's fun all the same.
U r awesome Mynda
Thanks so much!
Excellent
Thank you!
Love it. :)
So glad!
Thank you, I enjoyed this video so much hahahaha! Kirsty is so lucky to have a friend like you. Mynda, I have an accounting case that I have not been able to solve using PQ. Can I email you the case information for your review? Perhaps, there is content for a good video.
Thanks so much for watching! Please post your question and sample Excel file (anonymized) on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
nice tips!
Thank you 🙏
Undoubtedly you're an EXCEL magician
Thanks so much!
Everyday we learn little.... thanks 😊
Great to hear!
Thanks a lot, I will try to collect the data of months ( feom columns B,C,D to AZ) into tabular formate as I hate to copy paste feom each column into one row
Great to hear. Please check out my Power Query video for more: czcams.com/video/L4BuUzccLpo/video.html
Ha! Wonderful 😊
Glad you enjoyed it, Ian! 🙏
Good job Excelsia (love the name btw) 💚
Thank you! 😄
really impressive
Thanks so much 🙏😊
The greatest intro to an Excel video of all time 😂
😁 so glad you enjoyed it!
4:30 I had no idea you could group row fields into categories like a frequency distribution! I thought grouping only applies to dates! That's amazing!
Brilliant! Glad it was worth your time to watch 😁
That was amazing indeed!
Great video and insights. Thanks. I watched your two separate videos and wondering if we can combine to have a decent P&L excel. Can we have the date groupings to quarters and years and also at the same time have gross margin and net profit calculations at the same pivot table. Thanks.
Yes, you can group the dates and display the gross margin and net profit in the same PivotTable. Did you see this PivotTable Profit & Loss video: czcams.com/video/5kUQSxBVlZ8/video.html
@@MyOnlineTrainingHub Yes, I watched that as well, learned a lot of things on that one also. But I need somewhat a little bit different than that. Consider that we put all gross margin and net profit calculations and instead of Budget and Actual columns we have months in the columns to be grouped to quarters and years. If we need long term plans we would like to see years next to each other not in slices. Your help is much appreciated. Thanks.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
That was so cute!
Aw, thanks, Rory!
Excellent, concise, simple, easy-to-understand narrative. Mynda, while replicating the exercise, when I expand the entire YEAR field I only get "QTR 1" and not the other 3 qtrs for the years. I do, however, change the table format to NONTABLE format and then change it back to table format before adding and cleaning it in Power query. Why?
Thank you! Sounds like a locale issue with the dates. czcams.com/video/QKgS3hrrmvw/video.html
Good morning Mynda
or should I say 'good evening' since you are at the other end of the globe since I am in the land of the Igloos, CANADA.
Mynda I am using your example demo data just removing the table format and then applying the table format before I take it into Power query and then when I expand years I only get quarter 1 and NOT all 4 quarters. However if I use the table in your file as is and take it to the power query, I do get all 4 quarters
I'd still say it's a date locale issue, assuming your dates are mm/dd/yyyy on your system.
Funny format :-)
Leila has Noob Leila, in Australia she’s called Kirsty ;-)
On topic: grouping in standard pivot tables is indeed a powerful tool, perfect for histograms - too bad we loose this feature when we use the data model. :-(
😁Cheers, Geert! Power Pivot can't group, but you can create sets: www.myonlinetraininghub.com/excel-power-pivot-named-sets
Hi Mynda, Thanks for referring to that blog post. 🙂
Looks like it dates back quite a while - maybe this is a good time to make a new video about this, as an extension to this grouping video?
(maybe you can use a Sci-Fi video format, since it's more advanced 😉)
PS: I use Custom MDX sets 'as we speak' because my users have a need to show 3 metrics with only 1 drilled-down to the year-level.
Works like charm, but managing this needs to be done with care since it can easily corrupt the data model beyond any recognition. 😞
Experienced this only yesterday (not the first time).
I actually copied and pasted the link to that post into my To-Do video list after sharing it with you 😉 Love the Sci-Fi idea 🙏
You look really good with glasses on 👍🏼
Thanks! Just as well as I might need them soon 😅
NOt only helpful but also funny. I laughed a lot with Excelsia 😂
😁 Glad you enjoyed it!
Love it.
I vote more videos in the KIRSTY DRAGONBYTE series going forward.
P.S, feel free to name it the KIRSTY DRAGONBYTE series. 😃
🥰 great idea! thanks so much!
@@MyOnlineTrainingHub You're very welcome. 😊
I was like, what whoa, twins 👬, my brains not going to be able to take double info......
🧠 + 👬 = 💥
😎
🤣🤣🙏
Great video and very interesting. I am trying to group some data but I get an error saying I cannot group this selection. The data in the rows is a numeric field (the weight of products) coming from power query. I have transformed nulls and errors into 0. Any ideas why it is not working?
So pleased you liked it. When you get grouping errors it's for one of two reasons: there's text in the column you want to group, or you have already inserted custom fields or items. If you don't think it's either of those, please post your question and Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
greetings from egypt
Thanks for watching 😊
Hi Excelsia, I'm looking for a way to permanently keep the "Grouping" labels even when adjusting parameters. I want the grouping to revert to its original set up each time I change the parameters. Do you have any tutorials or step-by-step guides that could help me to achieve this? I appreciate your assistance on this matter. Thank you.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
✔ Btw, at 4:30, how do I sort column "Sum of Age" from largest to smallest? Kirsty is a 🌟now. Thanks
Thanks for watching! To sort by Sum of Age right-click a cell in the column you want to sort > Sort > Sort Largest to Smallest.
Great video! However, each time I try to group in a pivot table I'm presented with "Cannot group that selection." Any idea what might I might be doing wrong?
You get that message when you have text in your numeric fields (sometimes dates look like dates, but they're actually stored as text, or you have one or more cells containing actual text), or you have already added a calculated field or column to a PivotTable that shares the same Pivot Cache. i.e. any other PivotTables based on the same source data. If you're still stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Mynda, how can we sort clicer buttons which we want to see desired order?
There are a few options here: www.myonlinetraininghub.com/sorting-excel-date-slicers (says it's for sorting dates, but there are techniques in the post for all types of data )
Hi Mynda, great to know about groupings. I have a couple of questions. 1st question, I can group without the data model enabled, but not with the data model. 2nd question, I couldn't see the option to rename the new active field as shown in 6:29. Please could you help?
Correct, the data model uses a different approach called Named Sets: www.myonlinetraininghub.com/excel-power-pivot-named-sets
Another place to rename the field is right-click > Field Settings: Custom Name.
@@MyOnlineTrainingHub That's very helpful to know. Thank you Mynda! Always enjoy your videos and the way you present them.
Amazing!!!
Thanks for watching! 😁
😮👏🏽
Thank you! 😁
hi i like your videos but i have a problem please guide me i will be thankful to you actually i have data set that crate more than pivot tables and 2 slicers when try to connect my slicer to that pivot table the slicer only shows two pivot table not all what should i do to connect my 10 pivot tables with two slicers I hop you will help your junior
It will be a Pivot Cache problem as explained here: czcams.com/video/uIpNti9WYeM/video.html
@@MyOnlineTrainingHub i watched your video 1 thing tell me i have multiple caches then i couldn't connect my pivot with a slicer
Hi Madam, I had a query regarding grouping. Can you please help me out? I want to be able to group my team's office attendance data (no of days in office in a month, rest are from home) into 3 groups which are - attended less than 8 days, attended 8 days, attended more than 8 days. How can I create this custom grouping using pivot tables??
Sure, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi
I've got so many questions. Bcos the data I am using it has more text than numbers. Only one column has numbers.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
What if I need to work with Females age under five , 18-35 , 36-59 ,greater than 60 , and males age under five , 18-35 , 36-59 ,greater than 60 ,in one pivot table , I need your support quickly as possible , thank you
If you need custom groups, then add a column to your source data and classify each row into its respective using a lookup formula that looks them up in a table. If you need more specific instructions, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda Treacy, i like your videos and i cant downloand the excel file. It says Access Denied - Sucuri Website Firewall
Sorry you're having trouble. I'm not able to replicate the issue. Please reach out via email so I can help you: website at MyOnlineTrainingHub.com and be sure to include the link to the video that you want the file for.
Woww) Theatre performance with Excelsia😅
😁not sure I'd go that far, but thank you for your support!
I just wish the quarters didn’t automatically appear when you make a new pivot table. I’ve rarely needed to group by quater.
Yeah, I know what you mean.
Great but how you remember all these thing u teach lot of thing us can u give tip us how to remember so many thing like dax,power bi feauture,excel formulas and so many other thing😊
Great question. I don't remember everything. I tend to remember there was a way to do something, but the details aren't retained, so I go back to my notes/video/blog and refresh my memory when it comes time to using it again.
They say learning takes time, I think in the long run, learning saves time....a lot of it...
YES! Well said!
"Excel Enchantress"
Go ahead, girl.
😁🙏
Pivot tables are awful. What a pain ! I'd much rather have a root canal that do pivot tables
😁 wow, I am the opposite.
I've had a good teacher before I found you, like Mike Gervin. However, these days, I'm watching your videos a lot. I think people are not watching both your and Mike's channels. You need to make the thumbnails more creative, instead of the technical names I usually see ("Saving two more hours for your family, Pivot"). In addition, if you don't mind me saying, it would help if you can change the excel t shirt. I'm sorry, but I think it'd help, and I really think people will get more benefit of they see your videos.
Thanks so much for taking the time to share your feedback. 🙏 Learning all the time 😊
@777kiya I've changed the thumbnail. What do you think?
@@MyOnlineTrainingHub I think it's better. Thank you, I'll share it to my friends, 🙏🙏🙏
Thanks so much!
God this was actually painful to watch.
Please go back to the normal tutorial format without the cringe dramatisation
Thanks for sitting through it 😉It was just a bit of fun for a change. We'll see if Kirsty makes a repeat appearance.
@@MyOnlineTrainingHub Lol. Kirsty’s ok but you’re much better.
Btw I’m sorry for being rude in my original comment - sometimes I forget that there’s actual humans on the internet and your reply reminded me that there is.
I only discovered you last week, and followed your personal finance dashboard video step by step (tweaking some parts for my personal needs) and can honestly say that you’re the best excel tutorial channel I found in my search to get better at excel.
Again, apologies for being rude - I’m actually really grateful I discovered you.
No problem. I appreciate the follow up. 🙏😊 And awesome to hear you found my Personal Finance Dashboard useful.