5 Problems with PIVOTBY vs Pivot Tables in Excel
Vložit
- čas přidán 7. 07. 2024
- Microsoft recently introduced two new functions for Excel: GROUPBY and PIVOTBY. While these new features offer exciting possibilities, they come with their own set of challenges. DOWNLOAD THE WORKBOOK ► www.excelcampus.com/functions...
NOTE: The PIVOTBY and GROUPBY functions are currently available on the Beta Channel for Microsoft365 subscribers. They might still be flighted, which means only a portion of users on beta will see the functions. They will eventually roll out to all beta users, then to the other channels.
🔗LINKS
🔎 Get free weekly Excel tips: www.excelcampus.com/newsletter/
💡 Free 60-minute Excel training session: www.excelcampus.com/blueprint
📖 Join Our Comprehensive Excel Training Program: www.excelcampus.com/elevate
Related Content:
✅ Using Formulas When Pivot Tables Are a Better Option • Data Visualization Mas...
✅ 3 Amazing Tips For The Pivot Table Fields List • 3 Amazing Tips For The...
✅ Excel’s Missing Feature: Slicers for PIVOTBY • Excel’s Missing Featur...
✅ Create A High Low Stock Pivot Chart In Excel • How To Create A High L...
✅ Refresh Pivot Tables Automatically with a Macro • Pivot Tables: Automati...
00:00 Introduction
00:57 Problem #1 Complexity
01:58 Problem #2 Formatting
04:30 Problem #3 Percent of Total
05:52 Problem #4 Pivot Charts Slicers
07:28 Problem #5 Flexibility
Excellent case studies with PERCENTOF - TRANSPOSE and conditional formats. That transposition is a bombshell! And those CheckBox, a real marvel. Thanks for sharing, Jon!!
Thanks Ivan! I'm happy to hear you found it useful. 🙌
Do not have it yet in my version, but seems like going backwards.
Thank you Jon! I'm very excited about this; I have it on my home computer with the beta channel but not yet at work - it can't come soon enough. Pivot table are great, and if I need a summary immediately, I will use a pivot table; however for ongoing reports, I am excited to start building them with GROUPBY and PIVOTBY. I am all about dynamic updates. Also, I can more easily pull data from the PIVOTBY / GROUPBY spill range than i can from a pivot table. And pivot tables make the file sizes bigger because of the cached data. So for me, I will be using these two functions heavily once they are available, which I hope is soon!
Thanks for the detailed reply! 🙂
This is lovely but kinda complex unlike pivot tables
Nice video,
Pivot tables is for specific users and uses. I love that you have the option of pivotby.
Glad you like it! 😀
Brilliant video thanks you Jon 👍
You're welcome 😀
PIVOTBY seems like it might be useful for an adhoc, off the cuff, request but I'll stick with my Pivot tables and PowerPivot for the good stuff.
Haven't built a single PT since I received GROUP/PIVOTBY. MS will expand capabilities.
Thanks for the video, but I cant see any use of PivotBy, that does not seem like a backward step from Pivot Tables. Paul
❤❤❤Amazing
I imagine using pivotby to ceate custom arrays or ranges that can then be passed as arguments in other functions.
Hey.....I would like us to have a general society for Data analytics....where we all share ideas together.....how about that?
Great idea Thompson! We do have a community forum inside our Elevate Excel Training Program where members can share ideas and ask questions. www.excelcampus.com/elevate
Hi Jon! Awesome video! Could you please tell me about the secret behind double click on the picture mimicking the drop drown arrow? I get the idea with the hyperlink! It works perfect - but how did you achieve the effect that double click expands the drop down?
Oooh - I got it! This is a neat trick! the actual drop down that appears after selecting the cell with the hyperlink comes to front of the "image dropdown arrow", so the second click triggers the actual one. Awesome!
Hi Macie, You got it! The image is actually a screenshot of the dropdown button. And I usually try to set up a named range to the cell that contains the dropdown list. Then create a hyperlink to the named range. The named range isn't necessary, but prevents you from having to update the hyperlink if you insert/delete rows/columns on the sheet.
I do have an old video on a different technique. czcams.com/video/oHtVmvqgZEM/video.htmlsi=eqpRuZUr7ArG-Ndr
But you comment is a good reminder that I need to create a video on this new technique.
I hope that helps. Thanks and have a great day! 🙂
I use pivot tables so much and need the formatting functionality and graphing. I can see using pivotby for a quick summary, but for the extra work, I'll pass.
Thanks Joanne! 🙌
Very googd
Thanks! 😀
Another good explanation, but seriously... the shortfalls of Excel are too numerous to list.
If you want to refresh PivotTables automatically, you can program an Activate Event or a Change Event with very little VBA code. The argument "PivotTables do not refresh automatically" is therefore not valid (and certainly not a problem).
Great point, Marcel! I should have mentioned that we do have a video on automatically refreshing pivot tables with a macro. czcams.com/video/NI1UQcYtl1A/video.html
However, there are a lot of scenarios where users can't use macro-enabled files in their projects. I've experienced this several times myself. Therefore, it is still true that pivot tables don't automatically refresh without help from a macro. You can also have pivot tables refresh automatically when you open the file. However, it's not a true auto refresh. Hopefully we will get this feature/update in the future.
Seems like stuff that’s already possible with existing functions
I m not able to fetch those function
Sorry, the functions are still in beta. You can access them if you are on the beta channel for Microsoft 365. The feature might still be flighted to a portion of users on the beta channel. So if you are on beta and not seeing the functions, that is likely the reason.
After extensive testing on a well established financial workbook I have determined the Pivotby is a disaster not just a little difficult to set up. Literally this function corrupts the workbook. I have verified this with multiple tests over the course of a week.