Pivot Tables will NEVER go away for the simple fact that many of us still need to drill down into subsets of data created by a Pivot Table. The new function is nice but honestly I will not be using it much when iit's so very easy to create a Pivot Table with more functionality.
Thank you for taking the time to post this, very helpful. Pivot Tables are quick and easy, drag and drop type functionality, but I have many use cases where I need to update data, preferably automatically, and this resolves the issue without the need for VBA. Keep up the good work!
Nice description. I am definitely on the side of the minority in this discussion! For me, pivot tables are hugely powerful but it comes at a price. That is, the effort it takes to familiarise oneself with the myriad of options and controls required to make the code do what you want rather than what it thinks you should want. That and the fact that I invariably forget the refresh step, just as I do using 'calculation manual'. GroupBy and PivotBy offer valuable functionality as part of the formula environment with a wide range of uses (for example I have used the functions to provide alternative presentations of the costs calculated on the basis of FIFO. A check box switches to result from simple list to crosstab).
Thank you! It has been one of the most diverse conversations I’ve seen with any new function that’s for sure 😃. I love the use case you’ve created with them, very nice!
Looks promising but won't replace pivottables. It shouldn't.. You'd end up using both. One point to note on Subtotals is that the label is still the same as the data items whereas in Pivot Tables the subtotal carries the words "xxx total"
Pivot table has the function to see what rows are part of a value just with a double click. I think that, while other formula doesnt have that, the pivot tables never die.
In which version this formula is available? I have office 365 from my organisation which might be an educator license but i haven't seen this function...
It’s being rolled out with the beta channel at the moment. May not be able to sign up on your work laptop though as they usually don’t allow. I released a video on how to sign up Friday just gone if interested
I suspect these functions would be better performance wise though that's just a hunch. These look pretty good but not sure they'll replace pivot tables, will definitely give them a shake though. The fact you don't need to refresh is a bonus, unrefreshed pivot tables have been the cause of many inaccurate reporting over the years no doubt. Not everyone knows how to add a refresh to a worksheet change event.
I am actually stunned that youtube excel evangelists have not acknowledged that Pivotby causes workbooks to crash which is why it has not been generally released. Sure it will work on simple little tables but not on one of a larger size. Mike Girvin acknowledged as much when I relayed a number of error messages and workbook crashes I experienced after extensive testing on a well established financial workbook.
Shall I use pivot tables that have a visual way to create summaries or shall I learn functions that are looking more and more like SQL language? Or use new SQL features that are looking like Excel tables? I don't know. Given a choice I will avoid dealing with data😂
Seriously? Being Dynamic is only "advantage" which was never a big deal with pivots in a first place. No show me how memory consuming this function is. I bet it's at least 20x slower.
It really depends on circumstance. If you’ve got a massive data set you want to achieve this on then you should use Power Query but if it’s small then this is always an option
@@CunningQuestion @How_2_Excel I didn't mean to be rude. I only think that title of video is clickbait. It's obvious that this formula is no replacement for Pivots. I reckon it should not be presented as a competition for pivots, but rather as an alternative in very rare cases. I missed such showcase. Also there was no mention about it's drawbacks. Better title would be "Groupby when to use it instead of pivots".
@@TomaszBI thanks, no probs. Understand what you’re saying, will aim to have more objective viewpoints in future videos as my fundamental aim is to provide real value 👍🏼
Is the most contradictory comment of the year. You say they are useless, and then say users can modify them easily, so clearly you acknowledge they have a use. This just provides an alternative, which I make good use of by creating tabs and virtual tables that spill over, and lock the formulas down so that users cannot mess with them. They all reflect the source data dynamically, and I retain full control of the tables and reports. For every useless scenario you can name, there will always be a useful one. Nice fry.
@@X22GJP "Road is closed all day, except 9-10am" that's not a contradiction. And the formulas don't have this benefit. Hence its my opinion that they will not replace Pivot tables.
You’re welcome!! Glad you found it a helpful tutorial. I’ve got a beginners tutorial on Pivot Tables coming out on Friday if you did want to give them another go 😉☺️
I have been a huge fan of spilled array formulas and have completely moved over to them at my work. I now use pivot tables for the quick and dirty analysis. I like building all my dashboards with spilled arrays especially for users that struggle with excel. It eliminates more user error as they don't have to think about refreshing pivots... which is often hard to remember for some non excel users. This Group By feature is EXACTLY what I've been needing/asking for. Any idea when it will be rolled out to the general public?
Ah, ok nice. It does make it easier when things are fully dynamic and the end user doesn’t have to worry about refreshing. I’m not sure when it’s likely to come out for general release to be honest though. Hopefully soon 🤞🏼
Pivot Tables will NEVER go away for the simple fact that many of us still need to drill down into subsets of data created by a Pivot Table. The new function is nice but honestly I will not be using it much when iit's so very easy to create a Pivot Table with more functionality.
Don’t disagree. Overall Pivot Tables are currently way more versatile
Seems like a good idea for simple summaries that will update without a refresh. But not a substitute for full pivot functionality.
Yep. Someone mentioned for quick on tab analysis it’s good and I’d definitely agree with that. Pivot Tables overall are way more versatile (currently)
@@How_2_Excel thanks for featuring this function. I earn my living from Excel and tutorials like this are very valuable.
Thank you for taking the time to post this, very helpful.
Pivot Tables are quick and easy, drag and drop type functionality, but I have many use cases where I need to update data, preferably automatically, and this resolves the issue without the need for VBA. Keep up the good work!
Awesome, thank you very much for the kind comment!! 😊 Glad to hear it’ll be helpful
In my view, these (spill) functions are handy for one-off questions. For regular, standardized, reporting and analysis I still prefer pivots.
Think that’s a spot on view to be honest
great info in a small video, Great Job
Awesome, thank you for the kind comment ☺️
Nice description. I am definitely on the side of the minority in this discussion! For me, pivot tables are hugely powerful but it comes at a price. That is, the effort it takes to familiarise oneself with the myriad of options and controls required to make the code do what you want rather than what it thinks you should want. That and the fact that I invariably forget the refresh step, just as I do using 'calculation manual'.
GroupBy and PivotBy offer valuable functionality as part of the formula environment with a wide range of uses (for example I have used the functions to provide alternative presentations of the costs calculated on the basis of FIFO. A check box switches to result from simple list to crosstab).
Thank you! It has been one of the most diverse conversations I’ve seen with any new function that’s for sure 😃. I love the use case you’ve created with them, very nice!
Pivotby and groupby haven’t been released yet for most of us. I am on the 365 Insider channel but haven’t seen these functions yet.
Are you on the beta channel or the current preview one?
Very interesting and learn able. So glad I subscribed it.
Awesome, glad you liked it ☺️
I identify so much with what Tom van der Linden is saying in this video he is literally me.😉
WOW! I like how it puts the information in a regular range not Pivot-Table-Like.
Great! Hopefully you’ll find it useful 👍🏼
I won't ditch pivot tables, but there will be more graph options 😊
I’m with you. Pivots are more versatile currently
Looks promising but won't replace pivottables. It shouldn't.. You'd end up using both. One point to note on Subtotals is that the label is still the same as the data items whereas in Pivot Tables the subtotal carries the words "xxx total"
Yeah, I agree with you. Pivots are much more versatile, think I’d use this more for real quick and dirty results. Great point 👍🏼
Pivot table has the function to see what rows are part of a value just with a double click. I think that, while other formula doesnt have that, the pivot tables never die.
That’s a great benefit of Pivot Tables
Great! Thx
You’re welcome ☺️
In which version this formula is available?
I have office 365 from my organisation which might be an educator license but i haven't seen this function...
It’s being rolled out with the beta channel at the moment. May not be able to sign up on your work laptop though as they usually don’t allow. I released a video on how to sign up Friday just gone if interested
If the functions could be... I dunno, custom? That would be epic
That would be!
Functions any day over pivot tables
Nice 👌🏼
Also available on a Mac?
I believe so, but can’t test myself.
Sticking with DAX
Nice. Yep, if you know DAX then you’re winning
I have office 365 but unable to find pivotby & Groupby functions
You’ll need to join the insider channel. Here’s a video I did on how to join.
ruclips.net/video/Ydct1P8jzGg/видео.htmlsi=QpwS2_ui54RMO_2k
I suspect these functions would be better performance wise though that's just a hunch. These look pretty good but not sure they'll replace pivot tables, will definitely give them a shake though.
The fact you don't need to refresh is a bonus, unrefreshed pivot tables have been the cause of many inaccurate reporting over the years no doubt.
Not everyone knows how to add a refresh to a worksheet change event.
Yeah, not sure about the performance, would be interesting to see. They’re definitely worth having in the toolbox.
Groupny functiom is not available on my current excel, i am using office 365, please advice
Hi, you’ll need to join the beta channel as it’s currently not fully released. It’s pretty simple to do from your account settings
I am actually stunned that youtube excel evangelists have not acknowledged that Pivotby causes workbooks to crash which is why it has not been generally released. Sure it will work on simple little tables but not on one of a larger size. Mike Girvin acknowledged as much when I relayed a number of error messages and workbook crashes I experienced after extensive testing on a well established financial workbook.
I’ve not experienced any of these issues myself, but sure, there can be bugs / issues with any new functions.
Shall I use pivot tables that have a visual way to create summaries or shall I learn functions that are looking more and more like SQL language? Or use new SQL features that are looking like Excel tables? I don't know. Given a choice I will avoid dealing with data😂
Haha, love it 😉
Seriously? Being Dynamic is only "advantage" which was never a big deal with pivots in a first place.
No show me how memory consuming this function is. I bet it's at least 20x slower.
It really depends on circumstance. If you’ve got a massive data set you want to achieve this on then you should use Power Query but if it’s small then this is always an option
wow Tomek, did you intend the tone of your reply to read as rude / aggressive?
@@CunningQuestion thanks, unfortunately can’t please everyone
@@CunningQuestion @How_2_Excel I didn't mean to be rude. I only think that title of video is clickbait.
It's obvious that this formula is no replacement for Pivots. I reckon it should not be presented as a competition for pivots, but rather as an alternative in very rare cases. I missed such showcase. Also there was no mention about it's drawbacks.
Better title would be "Groupby when to use it instead of pivots".
@@TomaszBI thanks, no probs. Understand what you’re saying, will aim to have more objective viewpoints in future videos as my fundamental aim is to provide real value 👍🏼
Pivot tables are useless except that users can modify then easily. So no, these formulas will not replace them.
I think the formula just gives another option to pivots. Each will likely have their place
Is the most contradictory comment of the year. You say they are useless, and then say users can modify them easily, so clearly you acknowledge they have a use. This just provides an alternative, which I make good use of by creating tabs and virtual tables that spill over, and lock the formulas down so that users cannot mess with them. They all reflect the source data dynamically, and I retain full control of the tables and reports. For every useless scenario you can name, there will always be a useful one. Nice fry.
@@X22GJP "Road is closed all day, except 9-10am" that's not a contradiction. And the formulas don't have this benefit. Hence its my opinion that they will not replace Pivot tables.
Here is the link to download the file 1drv.ms/x/s!AkFFIE88DTrqgaRGyMoz83o6OYeGWw?e=n1t5kJ
Thank you so much for this upload. What a RELIEF! not to need pivot tables anymore.. I never really liked them. Many, many thanks.
You’re welcome!! Glad you found it a helpful tutorial. I’ve got a beginners tutorial on Pivot Tables coming out on Friday if you did want to give them another go 😉☺️
Somehow I don't believe the name names of the people in the data....Miley Lyrus??? Henry Kord???
😂 are you sure? 😉
@@How_2_Excel 🤣🤣
For quick on tab analysis, this is pretty awesome
Definitely, that’s mostly what I’d be using it for
Why can’t they just create a dinamic pivot table function?
🤔 not sure, but that would be great
Wow, Ms Lardashian had a decent output 😅😅
Haha 😂
I have been a huge fan of spilled array formulas and have completely moved over to them at my work. I now use pivot tables for the quick and dirty analysis. I like building all my dashboards with spilled arrays especially for users that struggle with excel. It eliminates more user error as they don't have to think about refreshing pivots... which is often hard to remember for some non excel users. This Group By feature is EXACTLY what I've been needing/asking for. Any idea when it will be rolled out to the general public?
Ah, ok nice. It does make it easier when things are fully dynamic and the end user doesn’t have to worry about refreshing. I’m not sure when it’s likely to come out for general release to be honest though. Hopefully soon 🤞🏼
Intresting 👐 Can you please add link of that excel sheet so viewers can utilize it for practice.
Hi, yep, I’ll do that in the morning and let you know once available 👍🏼
Hi, here is the link. 1drv.ms/x/s!AkFFIE88DTrqgaRGyMoz83o6OYeGWw?e=n1t5kJ