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!
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!!!!!!!
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! 🙂
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!
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)
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. 🎉
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,,👏
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.
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
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.
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.
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
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.
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 )
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...) 😉
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
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: th-cam.com/video/5kUQSxBVlZ8/w-d-xo.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.
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?
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
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
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
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??
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. :-(
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).
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.
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
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.
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 😊
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.
An engagingly humorous way to show better ways to work with data. Love it.
So pleased you enjoyed it 🙏
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! 🙏😊
Absolutely beautiful! So many tips and tricks, yet delivered in an entertaining way!
Thank you so much! 🙏😊
i'm not going home, i'm going back to work right now to update my pivot tablesssssss!
Awesome to hear 😁
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 😊
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. 😊
0:28 Group dates into months, quarters and years. That's great info to know.
Great to hear 👍
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 😊
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! 😁🙏
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!
Age groupings this way is such a time saver, thanks Paul
Great to hear, Paul.
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. 👌
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! 😊🙏
It just keeps getting better and better! Is there no end to this awesomeness?!
😁 too kind! 🙏
Mind blowing! 🤯🥳
Wonderful to hear 🙏😊
Another few new things learnt, thanks Mynda, Kirsty, and Excelcia
Great to hear 🙏
What a fun way to deliver content, Mynda, don't get to see Excelsia often enough ;-)
Glad you enjoyed it!
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!
Nice tricks, especially using PQ! Very helpful
Great to hear! 🙏😊
Loved it! And you seemed to be having so much fun!
Thanks so much! It was lots of fun 😁
did not know about custom grouping. Love that
Glad you learned something new 😊
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 🍀
Besides being a charming video, this is magical. Unbelievable. Incredible. Otherworldly...
Wow, awesome to hear, Cathy! 😊🙏
Great intro!! LOVE IT!
I'm glad you like it 😁
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 😊
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.
Your video is so fun. Thank you for sharing the tips and tricks in Excel❤
Glad you enjoyed it! 😁
Everyday is a good day to learn something new! Well done!
Thanks! 😃
Awesome. Mynda you make it look so easy!
Thank you! 😊
Mynda...
Number 1 in Content
Number 1 in Video Editing.
Ta-Da!!! :=)))
😁 thanks, Ivan!
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 😉
✔ 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.
Amazing , your videos always helped me alot 😊
Wonderful to hear 🙏😊
Very creative! As always, thank you.
Glad you enjoyed it 🙏
Thank you very much indeed, EXCELSIA!
😁 thanks for watching!
You had a lot of fun doing this eh? Fun to watch!
Awesome to hear! Yes, lots of fun 😁
I really enjoyed the way this Video was crafted!
Thank you so much!
Really beautiful video, really helpful, you are such an amazing and talented woman!
You're too kind! Thanks so much 😊🙏
There's always something new to learn. Thanks Mynda
I'm so glad!
Love Kristy and Excelsia. Thank you.
😁 thanks so much!
That was surprisingly entertaining. Very nice 😊👏.
So pleased you enjoyed it 😁🙏
Pesky looks like a lovely nerd! Thank you for sharing your knowledge.
My pleasure!
I like the new format. 👍
Yay, thank you! 😁
The greatest intro to an Excel video of all time 😂
😁 so glad you enjoyed it!
So fun and factual! Nice! You're awesome! Thank you!
Aw, thanks so much!
That was fun and educational. Keep them coming
Glad you liked it, Dave 🙏😊
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: th-cam.com/video/uIpNti9WYeM/w-d-xo.html
@@MyOnlineTrainingHub i watched your video 1 thing tell me i have multiple caches then i couldn't connect my pivot with a slicer
Excellent and fun video, thanks Mynda!
Glad you enjoyed it, Chris! 🙏
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 😊
Thank you got Maynda for this insightful Video 📹 👏
You're most welcome! 😊
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.
Awesome presentation Excelsia
Glad you liked it!
Really awesome stuff. Thank you!
Glad it was helpful!
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!
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: th-cam.com/video/L4BuUzccLpo/w-d-xo.html
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
Haha! I love this story format! :) Fantastic! :D :D :D
Yay, thank you! 😁
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 )
brilliant video. keep up the good work
So pleased 🙏😊
Thanks for sharing. Awesome.
Glad you liked it. Thanks for watching! 😊
Thanks, very clear 😊
Thank you! Great to hear 😊
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. 😊
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 😊
Undoubtedly you're an EXCEL magician
Thanks so much!
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
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: th-cam.com/video/5kUQSxBVlZ8/w-d-xo.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
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. th-cam.com/video/QKgS3hrrmvw/w-d-xo.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.
Everyday we learn little.... thanks 😊
Great to hear!
Hi , You always 'Rock'. Thanks!
Thanks so much for watching and your support!
Good job Excelsia (love the name btw) 💚
Thank you! 😄
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
You look really good with glasses on 👍🏼
Thanks! Just as well as I might need them soon 😅
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
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
U r awesome Mynda
Thanks so much!
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 🙏
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.
Love Kirsty ❤. Tx
😁
that was fun. Thanks
Glad you enjoyed it 😊
Thank you. Mynda!
Thanks for watching, Marjorie!
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
really impressive
Thanks so much 🙏😊
I was like, what whoa, twins 👬, my brains not going to be able to take double info......
🧠 + 👬 = 💥
😎
🤣🤣🙏
NOt only helpful but also funny. I laughed a lot with Excelsia 😂
😁 Glad you enjoyed it!
Excellent
Thank you!
Just knowing something is possible in excel will trigger using it or wanting it
😁yep!
greetings from egypt
Thanks for watching 😊
nice tips!
Thank you 🙏
Love it. :)
So glad!
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
Ha! Wonderful 😊
Glad you enjoyed it, Ian! 🙏
That was so cute!
Aw, thanks, Rory!
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.
They say learning takes time, I think in the long run, learning saves time....a lot of it...
YES! Well said!
Amazing!!!
Thanks for watching! 😁
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.
Woww) Theatre performance with Excelsia😅
😁not sure I'd go that far, but thank you for your support!
😮👏🏽
Thank you! 😁