You DON'T Need PivotTables Anymore (Here Is Why)

The GROUPBY & PIVOTBY functions as alternatives to PivotTables.
👩‍🏫 LEARN MORE in my Excel courses: bit.ly/groupbypivotby
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.myonlinetraininghub.com/e...
🔔 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.myonlinetraininghub.com/e...
#PivotTable #GROUPBY #PIVOTBY

[@MyOnlineTrainingHub]

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

[@Mindspiel]

Still in beta for my tenant. 😞

[@jimfitch]

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!

[@MyOnlineTrainingHub]

You’re going to love these functions, Jim 😁

[@FPANook]

Thank you so much for sharing all these updates! LOVE the videos!

[@MyOnlineTrainingHub]

Thanks so much 🙏😊

[@GrainneDuggan_Excel]

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!

[@MyOnlineTrainingHub]

Awesome to hear 🙏😁

[@dazzathedrummer74]

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 😊

[@MyOnlineTrainingHub]

Awesome to hear 😁

[@tiger131071]

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!

[@MyOnlineTrainingHub]

So great to hear 😁

[@RadAlzyoud]

Great timing. I got the new formulas in my Excel 2 days ago. Thx for sharing.

[@MyOnlineTrainingHub]

Thank you 😃

[@ksumar]

Excellent... Thank you for sharing these amazing features!

[@MyOnlineTrainingHub]

My pleasure! 🙏😊

[@gillescharrault1148]

Thanks so much to updated us about it🙏

[@MyOnlineTrainingHub]

Thanks for watching 🙏

[@kebincui]

Excellent and clearly explained 👍🌹❤❤

[@MyOnlineTrainingHub]

Thank you so much 🙂

[@astatine0085]

Great stuff. Thanks for sharing

[@MyOnlineTrainingHub]

My pleasure 🙏😊 thanks for watching.

[@garydunken7934]

Nice. I love these new functions from Excel.

[@MyOnlineTrainingHub]

Great to hear!

[@happymystic9800]

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 …

[@MyOnlineTrainingHub]

Great to hear you'll be trying these new functions out!

[@aprashant1048]

Brilliant, thx for sharing!! Handy

[@MyOnlineTrainingHub]

Glad you like it 🙏

[@emarcos40]

Lovet it. Thank you for sharing

[@MyOnlineTrainingHub]

Thanks for watching! 😁🙏

[@tmb8807]

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.

[@MyOnlineTrainingHub]

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.

[@olliehopnoodle4628]

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.

[@YoucefYoucef-ft9ce]

Many thanks teacher I'm an accountant from the US

[@TahaGemini]

Awesome. Thanks

[@MyOnlineTrainingHub]

Glad you liked it!

[@luisvillagra]

Saludos desde Chile , Gracias Gracias Gracias ... Luis

[@MyOnlineTrainingHub]

Gracias! 😊🙏

[@the_legends1989]

You are awesome ❤

[@MyOnlineTrainingHub]

Glad you enjoyed the video 🙏

[@joeromero7165]

This is one of those game changing updates to Excel for me. Very excited to start using when it's GA.

[@MyOnlineTrainingHub]

Awesome to hear!

[@luisvillagra]

Hola gracias desde chile . Luis

[@MyOnlineTrainingHub]

Gracias 🙏 😊

[@IvanMiletic]

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"

[@MyOnlineTrainingHub]

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.

[@IamTheReaper911]

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 😉

[@MyOnlineTrainingHub]

I love PivotTables too 😁

[@chrism9037]

Excellent Mynda! I assume we cannot use these functions to replace Data Model pivot tables that have DAX measures (?)

[@MyOnlineTrainingHub]

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.

[@jerrydellasala7643]

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.

[@MyOnlineTrainingHub]

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.

[@tmb8807]

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!

[@rleigh5204]

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.

[@archonmax]

谢谢分享

[@MyOnlineTrainingHub]

My pleasure!

[@richardhay645]

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?

[@MyOnlineTrainingHub]

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.

[@Fabi_terra]

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]

Not sure what you have in mind to modify but you could possibly wrap it in SUBSTITUTE to replace text.

[@Fabi_terra]

@@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.

[@MyOnlineTrainingHub]

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.

[@Fabi_terra]

​@@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?

[@MyOnlineTrainingHub]

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: kzbin.info/www/bejne/eqaaZHaGprx6h9k

[@steven.h0629]

Eva's going to like this one 👍😎✊

[@MyOnlineTrainingHub]

I reckon too 😊

[@user-ub5of1jm6g]

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!

[@AlNikon]

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.

[@MyOnlineTrainingHub]

Thanks for sharing your views and use case for traditional PivotTables.

[@tapio_m6861]

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.

[@MyOnlineTrainingHub]

I demonstrate using conditional formatting to format the subtotal rows in the video.

[@rudiklein]

Is it possible to reference the fields in the output, like the totals?

[@MyOnlineTrainingHub]

No, but you can just write another formula that returns the total. 😉

[@deluxelex9056]

Good information. However, for all my current needs, Pivot Tables are still easier and faster to use.

[@MyOnlineTrainingHub]

Me too 😉

[@CAKimberlyLewis]

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!

[@MyOnlineTrainingHub]

😁 Hopefully they won’t be too far away 🤞

[@briancarr8592]

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)

[@MyOnlineTrainingHub]

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: kzbin.info/www/bejne/iKvdaoOvm9ycjLM

[@aleb8243]

These dynamic array formulas are great, but not so much as long as there's no dynamic formatting possibility

[@MyOnlineTrainingHub]

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.

[@kernel_cataclysm7306]

So ... Excel basically copies more and more functions from standard relational databases? When will the user permissions be implemented?

[@MyOnlineTrainingHub]

Good question!

[@petros4502]

I like before I watch

[@MyOnlineTrainingHub]

Thank you 😊🙏

[@JessBalmes10]

If you have a huge data, still it's easy to open? of very slow? because of the formula?

[@MyOnlineTrainingHub]

I haven’t tested it with big data, so can’t say.

[@MGA19a]

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???

[@MyOnlineTrainingHub]

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.

[@williamarthur4801]

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.

[@MyOnlineTrainingHub]

Glad you’re looking forward to using it.

[@GeertDelmulle]

Furthermore, it’s not even available for the latter 50% of the Beta users… But once we get these functions… oh my! :-)

[@williamarthur4801]

@@GeertDelmulle I still like the pivot wizard.

[@HenryGreijmans]

No an option if you work with Power Pivot, is that correct?

[@MyOnlineTrainingHub]

Correct.

[@cjjr85]

How is this method simpler and easy then using pivot table? I am just curious.

[@MyOnlineTrainingHub]

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.

[@zafarmomin]

i have office 365 enterprise edition but not show this function what to do

[@MyOnlineTrainingHub]

Currently, it is still in beta. Hopefully, everyone will get them soon.

[@jodym985]

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

[@tihomirhristov77]

💚

[@MyOnlineTrainingHub]

Thanks for watching!

[@ahmedshalaby9343]

hstack instead of choose ?

[@MyOnlineTrainingHub]

Sure, why not? 😁

[@sheelar8618]

Thanks, Mynda. I would like to take online course on Power Query, Pivot and Dax. Sent Email. Awaiting for your response please.

[@MyOnlineTrainingHub]

Great to hear 😊

[@arkai59]

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

[@MyOnlineTrainingHub]

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 😉

[@001478]

3:01 pivotby

[@billgyrotech]

Maybe a personal budget with these new functions?

[@tedmoy]

I don't have it yet. :(

[@MyOnlineTrainingHub]

Hopefully, soon 🤞

[@nczioox1116]

Unfortunately not all excel versions have this yet

[@MyOnlineTrainingHub]

No, and only 365 and future releases will have them.

[@DPete27]

When will they allow conditional formatting to operate on a dynamic range of cells!? Ugh

[@MyOnlineTrainingHub]

You already can by simply selecting a range of cells bigger than you currently need to allow for growth.

[@martinadamcz]

Good but not easy and not fast them Pivot table - for me :D

[@MyOnlineTrainingHub]

I agree, pivottables are quicker to build but not as flexible.

[@tomjones1502]

No file????

[@MyOnlineTrainingHub]

The link to the file is in the video description.

[@tomjones1502]

Where?? Put the link here, please.@@MyOnlineTrainingHub

[@tomjones1502]

Where????????????@@MyOnlineTrainingHub

[@pruthuviishak6750]

Hmm, interesting, this looks bit complicated and harder than pivot. So pivot would be best I guess.😊

[@MyOnlineTrainingHub]

Interesting! Why does it seem more complicated and harder? 😃

[@pruthuviishak6750]

@@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 👍🏻

[@MyOnlineTrainingHub]

Thank you for your feedback! 😊

[@SeaOfMadness]

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.

[@MyOnlineTrainingHub]

Agree, I think adding a button to allow PivotTables to refresh instantly would make them 10 times better.

[@IkaikaArnado]

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.

[@MyOnlineTrainingHub]

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.

[@777kiya]

No more refreshing 👍

[@MyOnlineTrainingHub]

You got that right! 😁

[@JustMe-sh8nd]

well this is going backwards. i will keep the pivottable. this way is just over-complicated

[@MyOnlineTrainingHub]

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.

[@seanorlando6375]

​@@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.

[@qrsimon]

I love "spilling" data:)

[@axel_r_]

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.

[@MyOnlineTrainingHub]

I’m sure the AI will get to that point.

[@tedmoy]

I still don't have these functions available to my work account nor my personal account.

[@MyOnlineTrainingHub]

You need to be on the Microsoft 365 beta channel.