You DON'T Need PivotTables Anymore (Here Is Why)
ฝัง
- เผยแพร่เมื่อ 3 ต.ค. 2024
- The GROUPBY & PIVOTBY functions as alternatives to PivotTables.
👩🏫 LEARN MORE in my Excel courses: bit.ly/groupby...
Disclaimer: GROUPBY and PIVOTBY are currently in Beta and are only available to 50% of Microsoft 365 Insiders.
The problem with PivotTables is that you have to manually click the "Refresh All" button to update them when your source data changes. But the new GROUPBY and PIVOTBY functions solve that by enabling you to easily group and aggregate in a formula. However, one of the best things about PivotTables are Slicers that allow you to quickly filter the data at the click of a button. Let's check out these exciting new functions, and I'll show you a trick that allows you to use Slicers with them, so you never need to use a PivotTable again.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetr...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetr...
#PivotTable #GROUPBY #PIVOTBY
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. 😞
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.
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 😁
Many thanks teacher I'm an accountant from the US
Saludos desde Chile , Gracias Gracias Gracias ... Luis
Gracias! 😊🙏
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.
Excellent and clearly explained 👍🌹❤❤
Thank you so much 🙂
This is one of those game changing updates to Excel for me. Very excited to start using when it's GA.
Awesome to hear!
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 😁
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
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 😁
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 🙏😁
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. 🙄
Great timing. I got the new formulas in my Excel 2 days ago. Thx for sharing.
Thank you 😃
Good information. However, for all my current needs, Pivot Tables are still easier and faster to use.
Me too 😉
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 😁
Hola gracias desde chile . 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!
Thank you so much for sharing all these updates! LOVE the videos!
Thanks so much 🙏😊
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.
Nice. I love these new functions from Excel.
Great to hear!
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.
Thanks so much to updated us about it🙏
Thanks for watching 🙏
You are awesome ❤
Glad you enjoyed the video 🙏
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.
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.
Excellent... Thank you for sharing these amazing features!
My pleasure! 🙏😊
Great stuff. Thanks for sharing
My pleasure 🙏😊 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.
Eva's going to like this one 👍😎✊
I reckon too 😊
Awesome. Thanks
Glad you liked it!
Lovet it. Thank you for sharing
Thanks for watching! 😁🙏
Brilliant, thx for sharing!! Handy
Glad you like it 🙏
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 🤞
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.
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
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.
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
I like before I watch
Thank you 😊🙏
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.
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.
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.
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. 😉
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.
So ... Excel basically copies more and more functions from standard relational databases? When will the user permissions be implemented?
Good question!
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 don't have it yet. :(
Hopefully, soon 🤞
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.
Unfortunately not all excel versions have this yet
No, and only 365 and future releases will have them.
Maybe a personal budget with these new functions?
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 😉
3:01 pivotby
谢谢分享
My pleasure!
No an option if you work with Power Pivot, is that correct?
Correct.
hstack instead of choose ?
Sure, why not? 😁
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 😊
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.
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.
No file????
The link to the file is in the video description.
Where?? Put the link here, please.@@MyOnlineTrainingHub
Where????????????@@MyOnlineTrainingHub
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
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! 😊
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.
💚
Thanks for watching!
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:)
Good but not easy and not fast them Pivot table - for me :D
I agree, pivottables are quicker to build but not as flexible.
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.
I still don't have these functions available to my work account nor my personal account.
You need to be on the Microsoft 365 beta channel.