Disclaimer: GROUPBY and PIVOTBY are currently in Beta and are only available to 50% of Microsoft 365 Insiders. 👩🏫 LEARN MORE in my Excel courses: bit.ly/groupbypivotby
Thanks, Mynda. I look forward to these functions landing in my Excel. I can’t stand requiring to refresh manually, so I have been writing (complicated) LET formulas for a few years now to mimic pivot tables. The new functions will make that so much easier. Hooray!
Brilliant - I’ve been doing a similar thing with Unique(Filter()) and Sumifs() but this consolidates the process. I’ve been trying to move away from pivot tables for years so this is a step in the right direction 😊
If you have access to the new trimrange capabilities (still in beta, I think), the first pivot table can be constructed via =GROUP(B:.B,D:.D,SUM,3) This alternative provides for the header row if needed, generates de facto dynamic fields that can process new records, and obviates the need to transform a dataset into a table - enabling you to. write dynamic array formulas to the dataset if necessary.
Marvelous explanation, Mynda! This post has so many different things to learn, that I added it to my 'great tips for future reference' spreadsheet. Thanks!
Thank you. I discovered I had these functions a couple of weeks ago so I've been playing around with them, and they certainly simplify things compared to manually grouping and aggregating using LET formulas. Always interesting to see how you can put new features to work, although as you said, I'm sure there will still be uses for traditional PivotTables. It might just be me, but it feels like with the advent of dynamic arrays and the whole new world they've opened up, my mental process in writing these kind of formulas is becoming quite similar to if I was doing the same operations in something like Python - obviously not that the syntax is exactly the same, but there's a logic to some operations that's not _too_ dissimilar. These two functions are probably the best example of that so far. And with the upcoming integration of Python within Excel, maybe that's no coincidence.
Great to hear you've been giving these new functions a try. They do share similarities with other languages, so those familiar with programming will no doubt gravitate towards them more than non-programmers.
I agree. Dynamic arrays, LAMBDA, LET and some of the 365 functions have taken Excel to the next level. I just started dabbling in Python, it's so much better than the stuff I learned on, so I kkow Excel is still limited but you have to hand it to the developers of Excel. They've dropped out some great stuff and to me it's totally worth the yearly subscription fee. Like you I now think "Can I Lambda this" "Can I do this with a dynamic array"? Often times the answer is yes. The interfaces are still clunky at times but they are working on that too.
Awesome! So many times, the need to refresh pivot tables has been an issue when I create spreadsheets to be used by others. Never understood how google sheets was ahead of excel on this point. At least this helps a bit! Thank you very much for the excellent explanations once again Mynda 🙏🏽 I tried it out and it works well!
The first thing I said to myself when I read your title is, "But I love private tables!" Very Interesting I might have to change some of my thought processing 😉
Hi Mynda, very informative! I have a problem with conditional formatting. I give the rows with an empty cell a fill colour. That goes well. But when I add a new shop (e.g. Sydney) to my source table, the conditional formatting is not dynamic. It does not colour the row with the subtotal for Sydney. What could be the problem? Kind regards, Willem from the Netherlands
Typically, conditional formatting rules in Tables will automatically fill down, so there must be something preventing it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks for the great content as usual. Could you give a possible reason why you'd want to use option 1 for the field headers argument? It would be great if the Excel team added the ability to reference the spill operator in the conditional formatting formula to allow the format to grow with the data dynamically.
Great video, especially the slicers bit. Is it possible to tweak the formula to show a compact form of the pivot table? Also can I somehow use vstack to show data from two tables combined? thank you
Very nice! I especially like the Slicer trick - nice and simple. However, I set up a workbook with a table of 100 rows, and the first GROUPBY and PIVOTBY examples on the same Worksheet as the table. I created a second Worksheet for the Slicers, but with more complex Conditional Formatting on the first worksheet (Bold subtotal rows, Bold and fill Grand Total row, light fill every odd row), Excel repeatedly crashed. I don't know what was causing the crash, but I SUSPECT the problem was that applying the slicer filter to the table also hid rows of the GROUPBY and PIVOTBY examples on the same sheet. I'd suggest leaving the table on its own sheet if you're using Slicers or Filters on it, and creating the GROUPBY and PIVOTBY formulas on their own worksheet.
Great to hear you’re giving them a try. I also found excel crashed with the slicers, so this’ll be a big they need to fix. I’ve reported it to Microsoft.
I've had this a couple of times too. I think you're right about keeping the source data separate from the formulas. In my head I conceptualise it like: A traditional PivotTable's source data isn't in a worksheet, but rather a duplicate of that data in the PivotCache, so when you're filtering/slicing you don't see the original data in the grid changing accordingly (hence the PT requiring a refresh if the data changes). But when using these new functions and then using slicers directly on the source data, there is no PivotCache; the source data in the grid effectively fills that role by itself. That being the case I think it's safest to keep it on a separate sheet to everything else if you plan to do any manual filtering/slicing. I usually do this anyway as it makes the distinction between the raw data and any processing I've done clearer. Of course this might be unrelated to the crashing!
Glad to know I'm not the only one having Excel crash using GroupBy. I used your filter tip with a table of 2500 rows and tried to attach charts to the GroupBy results. Crashed at least a dozen times then I gave up.
Title: "You DON'T Need PivotTables Anymore" Beginning of the video: "You never need to use Pivot Tables again" End of the video: "Here are the reasons to still use Pivot Tables"
Not all of those reasons to continue using PivotTables will apply to everyone 😉 there will be loads of people who switch to use these functions and never look back.
Certainly interesting and has potential usability for some complex use cases requiring dynamic pivot tables. However, I don't think it's likely to replace 'traditional' pivot tables purely from a UX standpoint. Having to edit the formula every time you want to change the rows/columns is much more time consuming in most situations as compared to selecting them from a list, dragging them around. I won't even get started with the conditional formatting issue, or the other auxiliary functions that traditional pivot tables have available. For the cost of having to manually refresh the pivot, we don't need pivot tables anymore, but I certainly prefer them. Thanks for the overview though, clear and useful presentation!
I agree.. I use Pivot Tables using data with up to a couple of dozen columns and can't see me swapping this option for a traditional pivot table where I can instantly add and remove columns.
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??
Hello! Thank you for the tutorial, I really appreciate your clear explanations 🤗. I have a question: Is it possible to modify data in a "Groupby" formula?
@@MyOnlineTrainingHub Thank you so much for your reply🤗. The modifications I want to do are quite simple. Add another row to the table and mark checkboxes.
If you're adding new data to the source table the GROUPBY is referencing, then as long as it's in an Excel Table, the formula will pick it up automatically.
@@MyOnlineTrainingHub Thank you! And if I'm trying to add new data in the other table (not the source table)? Is it possible? Will both tables be updated?
What 'other' table. GROUPBY is referencing multiple tables? If so, just make sure they're both 'Excel Tables', and any references are structured references, then it will automatically include new data. More on Excel Tables here: th-cam.com/video/Du73CPqWGQw/w-d-xo.html
I had just started trying my hands on that beautiful dashboard of yours for actuals vs plans based on PivotTables, but it looks like PivotBy would do a better job. Would you be so kind as to doing a video on doing that kind of dashboard using PivotBy? That'd be so appreciated! Btw, I am just a newbie with excel!
@@MyOnlineTrainingHub Many thanks, Mynda, for replying so quickly, appreciate that. I had another question, in the video you didn't explain how you came upon the forecast numbers; and what you may have taken as the baseline for it. Thanks.
If you do decide to do a video on a similar dashboard (budget vs actual) using groupby/pivotby function, could you please include a section on multiple values, with different functions, and if variances are a possibility to be added therein? Many thanks in advance.
How to use conditional formatting for just the Total row where there are no sub-totals ? Tried to use text containing total but of course that only highlights the word "Total" and not the amount in the next column (total amount)
The total row is called 'Grand Total', so you first select the cells you want to highlight, then your formula would reference the cell containing the text 'Grand Total' e.g. =$A10="Grand Total" See this tutorial for an in depth lesson on conditional formatting with formulas: th-cam.com/video/Rzz9PyfwiVQ/w-d-xo.html
did i catch this correctly, that the only reason group by and pivot by are better than a pivot table is bc of the manual refresh of the pivot table? seems like a lot of extra steps
I've been using them and they definitely been r the hype. I've stopoed using PTs for the past 2 years as the DA functions became more rebust. Why are they being rolled out so slowly even to insider Beta? Any insight with Microsoft? Were there problems with these functions in the initial rollouts?
I like most of new functionality in XLS365 but I still cannot grasp real advantages of these „game changing“ formulas - I will be always faster to setup a pivottable then to write formulas in this way ? Other thing - when using PQ and creating connected tables > power pivot - can that functionality be replaced with these formulas???
PivotTables will be quicker to create but that’s not always the most important requirement. If you want the results to update immediately, then these functions can do that.
I never said they were simpler or easier 😜but they do overcome one of the biggest problems with PivotTables in that you have to manually refresh them after your source data changes, whereas formulas instantly include changes.
You should mention this point in a pinned comment to save people wasting time trying to access it and repairing their whole Office and uninstalling/reinstalling etc @@MyOnlineTrainingHub
Interesting, however I am not sure to see how this is easier or more efficient than a pivot table, unless it has something to do with AI which would be a step up
Not necessarily more efficient, but more flexible and if you want instant updating of the results when the source data changes, then they're the way to go. However, I'll still be using PivotTables most of the time 😉
It's like anything, it takes practice. I recommend downloading my file from the video description and re-watching the video so you can practice using the same data before trying to implement yourself.
@@MyOnlineTrainingHub it looks bit more work than just inseting a pivot. But this will be handy when we need to copy pivoted/groupby data. BTW nice explanation 👍🏻
It baffles me as to why Microsoft have done so little to update the flexibility of pivot Tables and slicers over the years. There've been tweaks but they still have some failings in key areas. I guess GROUPBY and PIVOTBY are an attempt to maybe do this but why not just make PTs and slicers easier to use, customise and format? Those of us who aren't so adept at formulas shouldn't need to go to these lengths just to get the most out of the app.
I think it’s a case of everything is easy when you know how. I agree, ☝️ I’ll be using PivotTables unless it’s important to have dynamic updates or charts that aren’t available with PivotTables.
@@MyOnlineTrainingHub I'm less skilled with Excel, but isn't a dynamic update achievable with a simple macro? Maybe that's more complicated than this, but if I'm guessing, it's still easier than group by.
This misses the point of what a pivot table is. This method might be suitable for displaying static information that you want reference as you add information to your data set. However, a pivot table is still a much better method for analyzing.
I'm not sure I agree with it displaying static information. The functions are actually more dynamic than PivotTables in that they automatically pick up new data added to the source, whereas a PivotTable needs to be manually refreshed. However, if you're doing exploratory analysis, then PivotTables are easier to use.
Not true. You still need pivot tables, because Microsoft hasn’t released these functions!!! It really sucks that Microsoft is delaying bringing these functions into general availability.
Instead of thinking of it as Microsoft delaying bringing these functions into GA, it's more a case of them making sure the testing phase irons out any issues, and the syntax of the functions satisfies the needs of the users. Often during the beta phase, the function arguments will be changed/added to. If they rush to release new functions, we miss the opportunity to give feedback and have changes made to make them better. If you'd like to get these functions sooner, you can join the Microsoft Insider channel (it's free) and get the beta version of Excel.
@@MyOnlineTrainingHub I am a member, and the functions aren’t available. I read the MS only deployed to about 50% of Insider users. Agree - it should be working prior to deployment to GA. But, this was initially beta released almost a year ago. It obviously isn’t a priority.
Excel is overrated, you can't even make groups, collapsing rows based on columns with identical values, without being forced to use a function "sum" or any other function I don't need. Why such basic feature is missing is beyond me.
Hmm, you can create custom groups in PivotTables. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I don't feel this is an improvement from Excel. What should have been a function in this AI era is the possibility of writing in the formula cell what you want to do, and Excel will do it for you without actually doing any formula yourself. However, there should always be a formula ready by the AI when done so you can do some QA and editing.
Disclaimer: GROUPBY and PIVOTBY are currently in Beta and are only available to 50% of Microsoft 365 Insiders.
👩🏫 LEARN MORE in my Excel courses: bit.ly/groupbypivotby
Still in beta for my tenant. 😞
Amazing! saves effort creatively. thanks for helping others!
Glad it was helpful!
Thanks, Mynda. I look forward to these functions landing in my Excel. I can’t stand requiring to refresh manually, so I have been writing (complicated) LET formulas for a few years now to mimic pivot tables. The new functions will make that so much easier. Hooray!
You’re going to love these functions, Jim 😁
Brilliant - I’ve been doing a similar thing with Unique(Filter()) and Sumifs() but this consolidates the process. I’ve been trying to move away from pivot tables for years so this is a step in the right direction 😊
Awesome to hear 😁
Excellent and clearly explained 👍🌹❤❤
Thank you so much 🙂
If you have access to the new trimrange capabilities (still in beta, I think), the first pivot table can be constructed via
=GROUP(B:.B,D:.D,SUM,3)
This alternative provides for the header row if needed, generates de facto dynamic fields that can process new records, and obviates the need to transform a dataset into a table - enabling you to. write dynamic array formulas to the dataset if necessary.
Thanks for sharing. I don't have the new trimrange functionality yet 😭
@@MyOnlineTrainingHub You're welcome. Yes, as you know, the availability of these beta features seems to be hit or miss.
Marvelous explanation, Mynda! This post has so many different things to learn, that I added it to my 'great tips for future reference' spreadsheet. Thanks!
Awesome to hear 🙏😁
This is one of those game changing updates to Excel for me. Very excited to start using when it's GA.
Awesome to hear!
Great timing. I got the new formulas in my Excel 2 days ago. Thx for sharing.
Thank you 😃
Nice. I love these new functions from Excel.
Great to hear!
Thank you. I discovered I had these functions a couple of weeks ago so I've been playing around with them, and they certainly simplify things compared to manually grouping and aggregating using LET formulas. Always interesting to see how you can put new features to work, although as you said, I'm sure there will still be uses for traditional PivotTables.
It might just be me, but it feels like with the advent of dynamic arrays and the whole new world they've opened up, my mental process in writing these kind of formulas is becoming quite similar to if I was doing the same operations in something like Python - obviously not that the syntax is exactly the same, but there's a logic to some operations that's not _too_ dissimilar. These two functions are probably the best example of that so far. And with the upcoming integration of Python within Excel, maybe that's no coincidence.
Great to hear you've been giving these new functions a try. They do share similarities with other languages, so those familiar with programming will no doubt gravitate towards them more than non-programmers.
I agree. Dynamic arrays, LAMBDA, LET and some of the 365 functions have taken Excel to the next level. I just started dabbling in Python, it's so much better than the stuff I learned on, so I kkow Excel is still limited but you have to hand it to the developers of Excel. They've dropped out some great stuff and to me it's totally worth the yearly subscription fee. Like you I now think "Can I Lambda this" "Can I do this with a dynamic array"? Often times the answer is yes. The interfaces are still clunky at times but they are working on that too.
Thank you so much for sharing all these updates! LOVE the videos!
Thanks so much 🙏😊
Awesome! So many times, the need to refresh pivot tables has been an issue when I create spreadsheets to be used by others. Never understood how google sheets was ahead of excel on this point. At least this helps a bit! Thank you very much for the excellent explanations once again Mynda 🙏🏽 I tried it out and it works well!
So great to hear 😁
Saludos desde Chile , Gracias Gracias Gracias ... Luis
Gracias! 😊🙏
Very well explained ❤ and always pleasant to watch. I will definitely give it a try as it always pays to have options. 😊 Thank you, cheers …
Great to hear you'll be trying these new functions out!
3:44 If you select more rows and columns, he will format also the cells below this table, because cells on Q column are also blank
Many thanks teacher I'm an accountant from the US
The first thing I said to myself when I read your title is, "But I love private tables!"
Very Interesting I might have to change some of my thought processing 😉
I love PivotTables too 😁
Thanks so much to updated us about it🙏
Thanks for watching 🙏
Excellent... Thank you for sharing these amazing features!
My pleasure! 🙏😊
Hi Mynda, very informative!
I have a problem with conditional formatting. I give the rows with an empty cell a fill colour. That goes well. But when I add a new shop (e.g. Sydney) to my source table, the conditional formatting is not dynamic. It does not colour the row with the subtotal for Sydney. What could be the problem?
Kind regards,
Willem from the Netherlands
Typically, conditional formatting rules in Tables will automatically fill down, so there must be something preventing it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks for the great content as usual. Could you give a possible reason why you'd want to use option 1 for the field headers argument? It would be great if the Excel team added the ability to reference the spill operator in the conditional formatting formula to allow the format to grow with the data dynamically.
Yes, the Conditional Formatting referencing is long overdue for an update. 🙄
These dynamic array formulas are great, but not so much as long as there's no dynamic formatting possibility
You can use conditional formatting as shown in the video to dynamically format them. Of course this is an extra step that PivotTables have built in.
Good information. However, for all my current needs, Pivot Tables are still easier and faster to use.
Me too 😉
Great stuff. Thanks for sharing
My pleasure 🙏😊 thanks for watching.
Thank you Mynda. For some reason, I can't do multiple sort on GROUPBY.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Great video, especially the slicers bit. Is it possible to tweak the formula to show a compact form of the pivot table? Also can I somehow use vstack to show data from two tables combined? thank you
Thank you! You can't show the data in compact form, but you could use VSTACK to show data from two tables.
You are awesome ❤
Glad you enjoyed the video 🙏
Brilliant, thx for sharing!! Handy
Glad you like it 🙏
Very nice! I especially like the Slicer trick - nice and simple. However, I set up a workbook with a table of 100 rows, and the first GROUPBY and PIVOTBY examples on the same Worksheet as the table. I created a second Worksheet for the Slicers, but with more complex Conditional Formatting on the first worksheet (Bold subtotal rows, Bold and fill Grand Total row, light fill every odd row), Excel repeatedly crashed.
I don't know what was causing the crash, but I SUSPECT the problem was that applying the slicer filter to the table also hid rows of the GROUPBY and PIVOTBY examples on the same sheet. I'd suggest leaving the table on its own sheet if you're using Slicers or Filters on it, and creating the GROUPBY and PIVOTBY formulas on their own worksheet.
Great to hear you’re giving them a try. I also found excel crashed with the slicers, so this’ll be a big they need to fix. I’ve reported it to Microsoft.
I've had this a couple of times too.
I think you're right about keeping the source data separate from the formulas. In my head I conceptualise it like:
A traditional PivotTable's source data isn't in a worksheet, but rather a duplicate of that data in the PivotCache, so when you're filtering/slicing you don't see the original data in the grid changing accordingly (hence the PT requiring a refresh if the data changes). But when using these new functions and then using slicers directly on the source data, there is no PivotCache; the source data in the grid effectively fills that role by itself. That being the case I think it's safest to keep it on a separate sheet to everything else if you plan to do any manual filtering/slicing. I usually do this anyway as it makes the distinction between the raw data and any processing I've done clearer.
Of course this might be unrelated to the crashing!
Glad to know I'm not the only one having Excel crash using GroupBy. I used your filter tip with a table of 2500 rows and tried to attach charts to the GroupBy results. Crashed at least a dozen times then I gave up.
Lovet it. Thank you for sharing
Thanks for watching! 😁🙏
Title: "You DON'T Need PivotTables Anymore"
Beginning of the video: "You never need to use Pivot Tables again"
End of the video: "Here are the reasons to still use Pivot Tables"
Not all of those reasons to continue using PivotTables will apply to everyone 😉 there will be loads of people who switch to use these functions and never look back.
I'm so ready for these to hit my Excel version! I told Santa I was a good girl this year and hoped would come from Christmas, but no!
😁 Hopefully they won’t be too far away 🤞
Certainly interesting and has potential usability for some complex use cases requiring dynamic pivot tables.
However, I don't think it's likely to replace 'traditional' pivot tables purely from a UX standpoint. Having to edit the formula every time you want to change the rows/columns is much more time consuming in most situations as compared to selecting them from a list, dragging them around. I won't even get started with the conditional formatting issue, or the other auxiliary functions that traditional pivot tables have available.
For the cost of having to manually refresh the pivot, we don't need pivot tables anymore, but I certainly prefer them.
Thanks for the overview though, clear and useful presentation!
I agree.. I use Pivot Tables using data with up to a couple of dozen columns and can't see me swapping this option for a traditional pivot table where I can instantly add and remove columns.
Thanks for sharing your views and use case for traditional PivotTables.
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??
Not using formulas. Best to add this as a column to your source data, then use that in your GROUPBY/PIVOTBY formulas.
Hola gracias desde chile . Luis
Gracias 🙏 😊
Yes, looks a great function, but it's been an 'insider' Beta thing for ages and as I understand sit, it's not due for general release any time soon.
Glad you’re looking forward to using it.
Furthermore, it’s not even available for the latter 50% of the Beta users…
But once we get these functions… oh my! :-)
@@GeertDelmulle I still like the pivot wizard.
Eva's going to like this one 👍😎✊
I reckon too 😊
Awesome. Thanks
Glad you liked it!
Is it possible to reference the fields in the output, like the totals?
No, but you can just write another formula that returns the total. 😉
Hello! Thank you for the tutorial, I really appreciate your clear explanations 🤗. I have a question: Is it possible to modify data in a "Groupby" formula?
Not sure what you have in mind to modify but you could possibly wrap it in SUBSTITUTE to replace text.
@@MyOnlineTrainingHub Thank you so much for your reply🤗. The modifications I want to do are quite simple. Add another row to the table and mark checkboxes.
If you're adding new data to the source table the GROUPBY is referencing, then as long as it's in an Excel Table, the formula will pick it up automatically.
@@MyOnlineTrainingHub Thank you! And if I'm trying to add new data in the other table (not the source table)? Is it possible? Will both tables be updated?
What 'other' table. GROUPBY is referencing multiple tables? If so, just make sure they're both 'Excel Tables', and any references are structured references, then it will automatically include new data. More on Excel Tables here: th-cam.com/video/Du73CPqWGQw/w-d-xo.html
I had just started trying my hands on that beautiful dashboard of yours for actuals vs plans based on PivotTables, but it looks like PivotBy would do a better job. Would you be so kind as to doing a video on doing that kind of dashboard using PivotBy? That'd be so appreciated! Btw, I am just a newbie with excel!
Thanks for the suggestion. In the meantime, I would use the PivotTable method.
@@MyOnlineTrainingHub Many thanks, Mynda, for replying so quickly, appreciate that. I had another question, in the video you didn't explain how you came upon the forecast numbers; and what you may have taken as the baseline for it. Thanks.
It's just dummy data. Not real forecasting 😁
No worries! Thanks, Mynda.
If you do decide to do a video on a similar dashboard (budget vs actual) using groupby/pivotby function, could you please include a section on multiple values, with different functions, and if variances are a possibility to be added therein? Many thanks in advance.
How to use conditional formatting for just the Total row where there are no sub-totals ? Tried to use text containing total but of course that only highlights the word "Total" and not the amount in the next column (total amount)
The total row is called 'Grand Total', so you first select the cells you want to highlight, then your formula would reference the cell containing the text 'Grand Total' e.g. =$A10="Grand Total"
See this tutorial for an in depth lesson on conditional formatting with formulas: th-cam.com/video/Rzz9PyfwiVQ/w-d-xo.html
did i catch this correctly, that the only reason group by and pivot by are better than a pivot table is bc of the manual refresh of the pivot table? seems like a lot of extra steps
IMO yes. But if you want your reports to update immediately, then it's a small price to pay.
So ... Excel basically copies more and more functions from standard relational databases? When will the user permissions be implemented?
Good question!
Excellent Mynda! I assume we cannot use these functions to replace Data Model pivot tables that have DAX measures (?)
Great question! You could use custom lambdas to replicate some DAX measures but not those that iterate over tables etc., so we’ll still need DAX.
When will us ‘regular users’ get new features in Excel?
I wish I had a crystal ball. Microsoft never set a date because it always depends on how things go in beta testing.
I've been using them and they definitely been r the hype. I've stopoed using PTs for the past 2 years as the DA functions became more rebust. Why are they being rolled out so slowly even to insider Beta? Any insight with Microsoft? Were there problems with these functions in the initial rollouts?
Great to hear. New functions are often in beta for a while and these have complex syntax, so I expect that will contribute to the time it’ll take.
How easy would it be to format the subtotal rows so that they are distinctive? I know that we can use macros to get this done, but nevertheless.
I demonstrate using conditional formatting to format the subtotal rows in the video.
I like most of new functionality in XLS365 but I still cannot grasp real advantages of these „game changing“ formulas - I will be always faster to setup a pivottable then to write formulas in this way ? Other thing - when using PQ and creating connected tables > power pivot - can that functionality be replaced with these formulas???
PivotTables will be quicker to create but that’s not always the most important requirement. If you want the results to update immediately, then these functions can do that.
I like before I watch
Thank you 😊🙏
If you have a huge data, still it's easy to open? of very slow? because of the formula?
I haven’t tested it with big data, so can’t say.
How is this method simpler and easy then using pivot table? I am just curious.
I never said they were simpler or easier 😜but they do overcome one of the biggest problems with PivotTables in that you have to manually refresh them after your source data changes, whereas formulas instantly include changes.
No an option if you work with Power Pivot, is that correct?
Correct.
i have office 365 enterprise edition but not show this function what to do
Currently, it is still in beta. Hopefully, everyone will get them soon.
You should mention this point in a pinned comment to save people
wasting time trying to access it and repairing their whole Office and uninstalling/reinstalling etc @@MyOnlineTrainingHub
Unfortunately not all excel versions have this yet
No, and only 365 and future releases will have them.
Interesting, however I am not sure to see how this is easier or more efficient than a pivot table, unless it has something to do with AI which would be a step up
Not necessarily more efficient, but more flexible and if you want instant updating of the results when the source data changes, then they're the way to go. However, I'll still be using PivotTables most of the time 😉
hstack instead of choose ?
Sure, why not? 😁
谢谢分享
My pleasure!
Thanks again ..... everything looks easy while watching your videos but when put into practical .... 😔
It's like anything, it takes practice. I recommend downloading my file from the video description and re-watching the video so you can practice using the same data before trying to implement yourself.
Hmm, interesting, this looks bit complicated and harder than pivot. So pivot would be best I guess.😊
Interesting! Why does it seem more complicated and harder? 😃
@@MyOnlineTrainingHub it looks bit more work than just inseting a pivot. But this will be handy when we need to copy pivoted/groupby data. BTW nice explanation 👍🏻
Thank you for your feedback! 😊
When will they allow conditional formatting to operate on a dynamic range of cells!? Ugh
You already can by simply selecting a range of cells bigger than you currently need to allow for growth.
Maybe a personal budget with these new functions?
It baffles me as to why Microsoft have done so little to update the flexibility of pivot Tables and slicers over the years. There've been tweaks but they still have some failings in key areas. I guess GROUPBY and PIVOTBY are an attempt to maybe do this but why not just make PTs and slicers easier to use, customise and format? Those of us who aren't so adept at formulas shouldn't need to go to these lengths just to get the most out of the app.
Agree, I think adding a button to allow PivotTables to refresh instantly would make them 10 times better.
Thanks, Mynda. I would like to take online course on Power Query, Pivot and Dax. Sent Email. Awaiting for your response please.
Great to hear 😊
3:01 pivotby
well this is going backwards. i will keep the pivottable. this way is just over-complicated
I think it’s a case of everything is easy when you know how. I agree, ☝️ I’ll be using PivotTables unless it’s important to have dynamic updates or charts that aren’t available with PivotTables.
@@MyOnlineTrainingHub I'm less skilled with Excel, but isn't a dynamic update achievable with a simple macro? Maybe that's more complicated than this, but if I'm guessing, it's still easier than group by.
I love "spilling" data:)
No file????
The link to the file is in the video description.
Where?? Put the link here, please.@@MyOnlineTrainingHub
Where????????????@@MyOnlineTrainingHub
This misses the point of what a pivot table is. This method might be suitable for displaying static information that you want reference as you add information to your data set. However, a pivot table is still a much better method for analyzing.
I'm not sure I agree with it displaying static information. The functions are actually more dynamic than PivotTables in that they automatically pick up new data added to the source, whereas a PivotTable needs to be manually refreshed. However, if you're doing exploratory analysis, then PivotTables are easier to use.
Good but not easy and not fast them Pivot table - for me :D
I agree, pivottables are quicker to build but not as flexible.
💚
Thanks for watching!
No more refreshing 👍
You got that right! 😁
Not true. You still need pivot tables, because Microsoft hasn’t released these functions!!!
It really sucks that Microsoft is delaying bringing these functions into general availability.
Instead of thinking of it as Microsoft delaying bringing these functions into GA, it's more a case of them making sure the testing phase irons out any issues, and the syntax of the functions satisfies the needs of the users. Often during the beta phase, the function arguments will be changed/added to. If they rush to release new functions, we miss the opportunity to give feedback and have changes made to make them better. If you'd like to get these functions sooner, you can join the Microsoft Insider channel (it's free) and get the beta version of Excel.
@@MyOnlineTrainingHub I am a member, and the functions aren’t available. I read the MS only deployed to about 50% of Insider users. Agree - it should be working prior to deployment to GA. But, this was initially beta released almost a year ago. It obviously isn’t a priority.
Excel is overrated, you can't even make groups, collapsing rows based on columns with identical values, without being forced to use a function "sum" or any other function I don't need. Why such basic feature is missing is beyond me.
Hmm, you can create custom groups in PivotTables. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I don't feel this is an improvement from Excel.
What should have been a function in this AI era is the possibility of writing in the formula cell what you want to do, and Excel will do it for you without actually doing any formula yourself.
However, there should always be a formula ready by the AI when done so you can do some QA and editing.
I’m sure the AI will get to that point.