That last example took a LOOONG time and had a very complex INDIRECT formula. By the time someone was to do all that I'd already be looking at the data in a PivotTable. The auto refresh is definitely an advantage but the time it takes to use PIVOTBY makes that advantage a moot point.
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.
Great video of how the Groupby & Pivotby are awesome new Excel formulas. I have been playing around with them and they are great. One thing I was wondering is there anywhere to group together multiple items (like with pivot table groupings), for example if you have data on fruit and want apple, oranges, and bananas separate but what to group all other fruit together as Misc (grapes, peaches, plums, watermelon, ect..)? Any thoughts on how can be done using these formulas??
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
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.
Excellent case studies with PERCENTOF - TRANSPOSE and conditional formats. That transposition is a bombshell! And those CheckBox, a real marvel. Thanks for sharing, Jon!!
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!
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.
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. ua-cam.com/video/NI1UQcYtl1A/v-deo.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.
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. ua-cam.com/video/oHtVmvqgZEM/v-deo.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! 🙂
Do not have it yet in my version, but seems like going backwards.
That last example took a LOOONG time and had a very complex INDIRECT formula. By the time someone was to do all that I'd already be looking at the data in a PivotTable. The auto refresh is definitely an advantage but the time it takes to use PIVOTBY makes that advantage a moot point.
This is lovely but kinda complex unlike pivot tables
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! 🙌
Great video of how the Groupby & Pivotby are awesome new Excel formulas. I have been playing around with them and they are great. One thing I was wondering is there anywhere to group together multiple items (like with pivot table groupings), for example if you have data on fruit and want apple, oranges, and bananas separate but what to group all other fruit together as Misc (grapes, peaches, plums, watermelon, ect..)? Any thoughts on how can be done using these formulas??
My pivot tables are pretty basic so PIVOTBY works better for me, easy to just copy paste and data is already refreshed
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.
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
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.
Seems like stuff that’s already possible with existing functions
Another good explanation, but seriously... the shortfalls of Excel are too numerous to list.
Thanks for the video, but I cant see any use of PivotBy, that does not seem like a backward step from Pivot Tables. Paul
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. 🙌
I imagine using pivotby to ceate custom arrays or ranges that can then be passed as arguments in other functions.
Can't see any advantage over pivot tables tbh
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! 🙂
❤❤❤Amazing
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.
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. ua-cam.com/video/NI1UQcYtl1A/v-deo.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.
Very googd
Thanks! 😀
Nice video,
Pivot tables is for specific users and uses. I love that you have the option of pivotby.
Glad you like it! 😀
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. ua-cam.com/video/oHtVmvqgZEM/v-deo.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! 🙂
Brilliant video thanks you Jon 👍
You're welcome 😀